Upsizing to SQL Server. 1

[Ed2020]

Hi,

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?
End Sub

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.

TIA,

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Update:

I've found an anwer (although not necessarily the best answer) to my first question. DAO code can be modified by replacing all references to CurrentDB() with a call to the following function:

Public Function OpenThisDB() As DAO.Database
    Dim db As DAO.Database
    Dim wk As DAO.Workspace

    Set wk = DBEngine.Workspaces(0)
    Set db = wk.OpenDatabase("", dbDriverNoPrompt, True, _
        "ODBC;DSN=BV;UID=_AppADP;Trusted_Connection=Yes;DATABASE=D315_Bulk_Load_Vader_AppADP;")
    Set OpenThisDB = db
    Set db = Nothing
    Set wk = Nothing
End Function

Always open to alternative suggestions though.

Ed Metcalfe.

Please do not feed the trolls.....

 

[dhookom]

I would not implement an ADP as MS seems to be promoting linked tables.

If you move the data to SQL Server, I would create a generic pass-through query to the SQL database and use code like:

Currentdb.QueryDef("qsptMyGenericPT").SQL = "DELETE FROM tblAsaData" 
Currentdb.Execute "qsptMyGenericPT", dbFailOnError

I actually use a function that changes the SQL property of a saved query.

Duane
Hook'D on Access
MS Access MVP

 

[Ed2020]

Duane,

Thank you for your reply.

If we stick with MDB files with linked tables is there likely to be any work required to tune existing code and queries? Is there any value in rewriting all queries as passthrough queries? Or rewriting DAO into ADO?

Thanks again,

Ed.

Please do not feed the trolls.....

 

[dhookom]

I wouldn't worry about ADO vs DAO. You can drastically improve performance if you can use pass-through queries. However, keep in mind these are read-only.

I expect there are some great resources on the web regarding upsizing your Access tables to SQL Server.

Duane
Hook'D on Access
MS Access MVP