Import from multiple ODBC's 1

[Sandman83]

Hi Everyone,

I'm fairly new to VBA and accessing external databases, and have a couple of questions. I am working on an Access db that I what to query several different Oracle db's through ODBC. The Access db will import several tables from a specific db on command. I have written the following code to connect to one of the Oracle db's.

Public Sub ConnectAlbion()

    Dim Albion As Database

    Set Albion = OpenDatabase("ACD",dbDriverComplete, True, "ODBC; DSN=ACD;UID=xxx;PWD=xxx")

  End Sub

I have tested this code and it does appear to connect. My thought here is once the code has run, I can reference Albion to access the Oracle db. Is this correct?

Also, once I have connected, I need to import specific tables from the db through VBA. The tables I will be importing will need to update/overwrite existing tables. Any ideas?

If anyone knows of any good sites/tutorials/books that deal with this I would greatly appreciate that as well.

 

[Leeeon]

I may be wrong, but I don't think Access gives a way of accessing specific tables through VBA. I use ODBC a lot through Lotus Notes, and have found I need to link each table manually through File -> Get External Data -> Link Tables.

If there is a way of doing it in VBA I'd be very interested.

 

[nashman]

You can automate the linking of tables from Oracle or any other ODBC database to Access. The simplest approach that I have used is to use the DoCmd.TransferDatabase method from Access.

A word of caution here...some databases will require you to still make some kind of manual selection ( for key fields for example ). You can use the ..."gulp"...SendKeys method to work around this.

 

[Sandman83]

Thanks nashman, that worked great! I was able to use DoCmd.TransferDatabase to connect, sign in, and transfer the table I wanted over to Access in one step! A Star for you!