Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I import tables from an ODBC database

Posted on 2009-05-14
11
Medium Priority
?
385 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

609 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