Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I am so glad that I found your site, it is an excellent resource and has helped me greatly..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Other topics Forum

Upsizing to SQL Server.Helpful Member! 

thread181-1670872
Read More Threads Like This One
Ed2020 (Programmer)
4 Jan 12 7:46
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:

CODE

CurrentDB.Execute "DELETE * FROM tblAsaData;", dbFailOnError 'Error occurs here, presumably because of reference to CurrentDB()

CODE

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 (Programmer)
4 Jan 12 8:33
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:

CODE

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.  smile

Ed Metcalfe.

Please do not feed the trolls.....

Helpful Member!  dhookom (Programmer)
4 Jan 12 11:17
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:

CODE

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 (Programmer)
4 Jan 12 11:55
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 (Programmer)
4 Jan 12 12:52
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

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Other topics

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close