October 10, 2008 at 1:00 pm
Hello,
Apologies if this has been previously answered. I have a menu (forms) driven Access application that runs several VBA calculation modules. I need to upsize to SQL Server and have used the upsizing wizard to migrate to SQL Server Express. I now have a new project (.adp) file. The VBA modules give errors and I've discovered the problem is they are using DAO while SQL Server requires ADO. The VBA modules are extensive with many calculations and database calls. I have started manually converting the DAO to ADO code but have realized this will take far too long due to the size and complexity of the modules. Do I have any options available to keep using existing DAO code as a short term solution?
Is linked tables via ODBC an option? Should that be done using the .mdb or the .adp file? One issue I have is the Access application uses a lot of queries as data sources. I can't find a way to link a query (view) as I would a table.
I appreciate any help you can provide.
Thanks,
KB
October 11, 2008 at 4:27 am
You can use ODBC linked tables in the mdb files as a short term workaround.
There could be some change of behaviour in record locking ...
Also have a look at
http://www.sqlservercentral.com/Forums/Topic247770-131-1.aspx
where they discuss about migrating access 2003 to sqlserver 2005
October 11, 2008 at 11:33 am
Thanks Jo. I set up Access linked tables to SQL Server via ODBC. I find the VBA code runs much slower now. The code loops through large datasets, perform calculations, and writes results to new tables. It looks like I will have to bite the bullet and convert the DAO code to ADO, use a project .adp file and hope that performance is better.
Thanks for the migration link, helpful discussion.
Cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply