After Upsizing

[Cheryl]

I converted my Access 97 backend tables to Access 2002, then I used the
Upsizing Wizard to upload those backend tables to SQL Server 2000. It
was a royal headache, but they are finally on the server. How do I now
upsize my forms, queries, etc. to SQL 2000? The wizard and DTS only
transfer tables. My forms are all based on queries and VBA code. I am
trying to get faster performance from SQL by putting some of those
views/queries on the server instead of in the Access front end. I did
not have any views/queries in the backend tables when I upsized, so
there is nothing there now. Any ideas?

 

[Alex - IntraLAN]

Hi Cheryl,

Personally every time I use the upsizing wizard I end up fighting it,
so I use it just to get the table layout into SQL, I then spend time
reformatting the table layouts to be better in terms of performance
e.g. make relational as a lot of the older access db's I have worked
with have been flat file implimentations rather than relational.
Spending time up front on this has a good return on my time invested. I
then write vb code to do the import, and I nearly always find errors in
the data at this point.

I move away from using fields with spaces in them e.g. [first name] to
first_name if your forms are based on tables with joins then things get
interesting, in most cases you can only update one of the tables in the
join.

Look for the 'Unique table' property for the form and set this to the
table you want to update. Your vba code should work if you are building
dynamic SQL statements.

I personally don't use views instead I use stored procedures as they
have advantages in grouping, building temp tables on the server etc.

Move your db functions that do loop modifications to data to stored
procedures, as these will perform many times faster than DAO code as
all the code is processing on the server.

A tip on moving your old queries to sql, do to design the old query and
then switch to SQL view of the query and then copy that query into the
SQL query analyser to see if it runs.

Another performance tip is if you have any code that is processed when
a record is inserted,edited or deleted use triggers on the tables, this
moves away quite a lot of code from the front-end into the SQL server.

remember things like the wild character for access is '*' and in SQL it
is '%' also if you use stored procedures to do your queries your SQL
code becomes easer in terms of puntuation e.g. access formatting for
searching on a date is #01/01/2004# in a stored procedure that has a
date parameter is mydate=@mydateparameter much easer.

Hope this helps.

Regards

Alex