• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

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.

Mitch
0
mcs1169
Asked:
mcs1169
  • 6
  • 5
1 Solution
 
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.


Kelvin
0
 
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).
0
 
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)?


Kelvin
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 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
 
mcs1169Author Commented:
Can anyone help?
0
 
Kelvin SparksCommented:
Sorry, missed your comment of about 8 days ago. Will review and come back with some code shortly


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

K
0
 
Kelvin SparksCommented:
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
 
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.


Kelvin


Kelvin
0
 
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.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now