[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How can I use VBA to automatically link via ODBC to a Database and Table from Access 2007?

Posted on 2009-04-02
7
Medium Priority
?
372 Views
Last Modified: 2013-11-27
I am using Access 2007. I have created an ODBC connection. Using the External Data tab I can manually select my ODBC connection and link to a Table in an external database. Is it possible to do the same in VBA so that I can AUTOMATICALLY open the ODBC connection and AUTOMATICALLY link to the Table, or more than one Tables? If you can help with the code and briefly explain what the code does/how it works, that would be very helpful! Thanks. David.
0
Comment
Question by:DataTrain
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 1000 total points
ID: 24048251
the command to use to link tables is DoCmd.TransferDatabase

If you know the tablename, you can link directly like this


DoCmd.TransferDatabase acLink, "ODBC Database", sConnect, acTable, sLocalTable, sRemoteTable

where sConnect is the connection string to your ODBC connection
acTable is the type of object to link
sLocalTable is the name of the table you want it called in Accss
sRemoteTable is the name of the table on the remote db

Given that you dont know the tables, you could make a connection then iterate thru the tables

I think I have sample code somewhere, let me see if I can find it
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24048287
what is your backend db?

Here is an example, depending on your db, it may end up creating schema owners in the tablename.

I think this should do it though. Using ADOX, we make a connection to your ODBC then list tables then connect


    Dim adoConn As adodb.Connection
    Dim adoRecSet As adodb.Recordset
    Dim sSql As String
   
   
    Set adoConn = New adodb.Connection
    adoConn.Open "ODBCDSNName", "USERID", "PASSWORD"

    Set adoCat = CreateObject("ADOX.Catalog")
    Set adoCat.ActiveConnection = adoConn

    For Each adoTbl In adoCat.Tables
        DoCmd.TransferDatabase acLink, "ODBC Database", "CONNECTIONSTRING", acTable, adoTbl.Name, adoTbl.Name, False
    Next

    Set adoCat = Nothing
    adoConn.Close
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24048314
You need to know what your ODBC coinnect string would be, once you know that then you can manipulate the Connect String of a TableDef or even create a new tabledef.

To find the ODNC connect string design the attached table and take a look at the DESCRIPTION property, the DESCRIPTION property will show you the CONNECTION and SOURCETABLENAME properties, the CONNECTION is everything beore ;Tablename=

Cheers, Andrew
Private Sub cmdDAOConnectString_Click()
Dim db As DAO.Database
Dim td As DAO.TableDef
 
    Set db = CurrentDb
    
' Change Existing Table
    Set td = db.TableDefs("Example_AttachedTableName")
    td.Connect = "ODBC;DSN=COMPAQ;Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=EETest"
    td.RefreshLink
    
' Create New Table
    Set td = db.CreateTableDef("Example_AttachedTableName1")
    td.Connect = "ODBC;DSN=COMPAQ;Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=EETest"
    td.SourceTableName = "tblB"
    db.TableDefs.Append td
    RefreshDatabaseWindow
 
End Sub

Open in new window

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

 

Author Comment

by:DataTrain
ID: 24048427
Thanks for your quick response. My backend database is SQL Server 2005. I will need a little time to work out my ODBC connect string and try a few things out.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24048444
My example is using a DSN to SQL Server with Trusted connections.
Cheers, Andrew
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 1000 total points
ID: 24048494
have a look here at connection strings. Useful site
http://www.connectionstrings.com/

for sql server  2005 it is http://www.connectionstrings.com/sql-server-2005

One connection string I have, kept as reference but dont have sql server anymore is this

    sConnect = "ODBC;Driver={SQL Server};SERVER=servernamehere;DATABASE=dbnamehere;UID=useridhere;PWD=pswdhereifone;"

you should hopefully be able to use this in the transferdatabase call
0
 

Author Closing Comment

by:DataTrain
ID: 31565717
Thank you - I have got this working now using DoCmd.TransferDatabase. I appreciate your help. David.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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: …

656 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