How do I import tables from an ODBC database

Hi all. On a daily basis, I need to copy 7 tables from a SQL database to an Access database. The process that I go through is such:

Open the access db --> File --> Get External Data --> Import --> Select my ODBC database and log in --> Select my 7 Import Objects (tables) and OK to start.

I would love to create a macro or a DTS package to do this but am very short on knowledge in that area.

Any help is appreciated.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Kelvin SparksConnect With a Mentor Commented:

The code snippet beloe assume SQL Server 2005 and that you have the SQL Native Client drivers installed.

It uses a dsn-less connection, so you can delete and dsn's on PCs

Yo need to create a table called USysTables (is treated as a system table and doesn't show in the tables list unless you select System Objects). It needs two columns - LocalName and RemoteName. In this you list the seven tables you want to import - LocalName is the name that Access will give the table and Remote Name is the name in SQL Server

If the default name for the table havs been used in Access it will be dbo_Table1 and RemoteName is dbo.Table1, but you can use any local name that was in use before (changing this will mean recoding your database).

You need to enter you database name, and Server name into this code

The code uses your table to loop through the tables you list and delete the local copy, then import them from the SQL Database

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strServer As String
Dim strDatabase As String
Dim strConnect As String
Dim rsTables As DAO.Recordset
Dim sSQL As String
strServer = "YourServerName"
strDatabase = "YourDatabaseName"
strConnect = "ODBC;Driver={SQL Native Client};Server=" & strServer & ";Database=" & strDatabase & ";Trusted_Connection=yes"
sSQL = "SELECT * FROM USysTables where Not IsNull(RemoteName);"
Set rsTables = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do Until rsTables.EOF
    For Each tdf In db.TableDefs
        If tdf.Name = rsTables!TableName Then
            db.TableDefs.Delete rsTables!TableName
            Exit For
        End If
    DoCmd.TransferDatabase acImport, "ODBC_Database", strConnect, acTable, rs!LocalName, rs!RemoteName
MsgBox "Tables Set Up"

Open in new window

Kelvin SparksCommented:
You could code it.

A simpler method would be to create a permanent link to these, and if you cannot work on those links then create 7 "Make table queries" based on these 7 linked tables that make your local copies.

Run the 7 from VBA with setWarnings set to False while running so that they do not prompt you that they are deleting the former copy each time. (remember to setwarnings back to True at the end.

mcs1169Author Commented:
The access db is being used by a person who has no access (nor do we want him to have access) to the SQL db (on a production server) so I do not think that links will help.

In regards to the VBA script, no clue. Along with being short on the finer details of Access management, I haven't a clue when it come to VBA. Sorry (sigh).
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Kelvin SparksCommented:
OK, so you do it for them??

Do you log onto SQL using a trued connection, or with username/password (SQL authentication)?

Will have top give you some VBA to put behind a command button. Do you want it to prompt for your login or cann it be stored (in which case anyone can run it)?

mcs1169Author Commented:
Yep. We just give him what he needs. He did have access until he brought the server to it's knees with an endless loop in some code that he threw together. He has upper management OK to do the mean.... programming.

We use SQL Authentication. I would prefer to be prompted since he has full access to the MSA database. However, if you feel ambitious, please provide both solutions and I will confer with my partner to see what she would prefer.
mcs1169Author Commented:
Can anyone help?
Kelvin SparksCommented:
Sorry, missed your comment of about 8 days ago. Will review and come back with some code shortly

mcs1169Author Commented:
Thank you, Kelvin.
Kelvin SparksCommented:
What version of SQL Server are you connecting to, 2000,2005, 2008??

Kelvin SparksCommented:
Oh, if your not using SQL Server 2005 replace {SQL Native Client} with {SQL Server} for SQL 2000 (will also work for 2005) or {SQL Native Client  10.0} if using SQL Server 2008.
If usure, goto Control Panel> Administrative Tools, Data Sources(ODBC) and then select the File  DSN, Click Add and scroll to end then of the drivers list and see what SQL ones are listes, use which ever one is the latest in the code. Cancel out of there to avoid creating a new DSN.


mcs1169Author Commented:
Thank you for the time and effort on this. I have not had the chance to try it out but hope to soon.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.