?
Solved

How do I import tables from an ODBC database

Posted on 2009-05-14
11
Medium Priority
?
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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
 
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 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

770 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