Solved

How do I import tables from an ODBC database

Posted on 2009-05-14
11
369 Views
Last Modified: 2013-11-27
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.

Mitch
0
Comment
Question by:mcs1169
  • 6
  • 5
11 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24390043
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.


Kelvin
0
 
LVL 1

Author Comment

by:mcs1169
ID: 24390162
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).
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24390236
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)?


Kelvin
0
 
LVL 1

Author Comment

by:mcs1169
ID: 24390372
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 hacking...er....I 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.
0
 
LVL 1

Author Comment

by:mcs1169
ID: 24455682
Can anyone help?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24455699
Sorry, missed your comment of about 8 days ago. Will review and come back with some code shortly


Kelvin
0
 
LVL 1

Author Comment

by:mcs1169
ID: 24455712
Thank you, Kelvin.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24455717
What version of SQL Server are you connecting to, 2000,2005, 2008??

K
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 250 total points
ID: 24455911
Hi

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

Kelvin
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

    Next

    DoCmd.TransferDatabase acImport, "ODBC_Database", strConnect, acTable, rs!LocalName, rs!RemoteName

    rsTables.MoveNext

Loop
 

MsgBox "Tables Set Up"

Open in new window

0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24455937
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.


Kelvin


Kelvin
0
 
LVL 1

Author Closing Comment

by:mcs1169
ID: 31581713
Thank you for the time and effort on this. I have not had the chance to try it out but hope to soon.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now