4 Jan 12 7:46
My employer has asked me to investigate options for migrating some of our larger MS Access 2003 databases to a more secure, stable and scaleable platform. One of the options I am looking at is migrating the databases to SQL Server 2008 whilst retaining the existing frontends.
As a proof-of-concept I have used the upsizing wizard in Access 2003 to do two trial migrations - one creating an ADP frontend, the other creating an MDB frontend with ODBC linked tables. I'm only in the early stages of the trial however it has already raised a couple of questions:
1. The MDB file contains a lot of DAO code which no longer works. For example the following fails with an "Object variable or With block variable not set" error:
CurrentDB.Execute "DELETE * FROM tblAsaData;", dbFailOnError 'Error occurs here, presumably because of reference to CurrentDB()
Public Sub TestDAO()
Dim ThisDB As DAO.Database
Dim rstTest As DAO.Recordset
Set ThisDB = CurrentDb()
Set rstTest = ThisDB.OpenRecordset("tblAsaData") 'Error occurs here, but why not on the previous line?
Will all of this DAO code need to be rewritten? How could the above me modified to work without rewriting to use the ADODB library?
2. What is the preferred way of using SQL Server as a backend to an Access frontend nowadays? It's been a while since I've done much with MS Access but I seem to recall that ADP wasn't going to be around for much longer. If my employer is going to be moving to Office 2010 in the future should we stick with MDB or ADP?
3. If anyone has any experience of using any other backends to Access frontends (Oracle is another possibility for us) I'd appreciate your views.
Please do not feed the trolls.....