Import ODBC database tables

[AndrewCorkery]

Hi,

I need to import a few tables using an ODBC driver.

I have used the Transfer database macro and then converted it to VBA. Everything works fine except that I have to enter the UID and Password even though I have specified it in the code.

Is there any other way of doing this?

Thanks

Andrew

 

[jksmi]

Hi,

There are 2 ways I know of that you could do it... by setting up your connection as a named data source (system or user DSN) in the ODBC Datasource Administrator. You can save your username and password here. In your Access database go to File->Get External Data->link tables and when the box opens change the filetype to ODBC databases.

The other way is to use a passthrough query but you would need to know the connection string and you have to write this directly in SQL. This method can also cause problems if your system administrator wants you to change passwords as you have to go into every query and change them...

Hope this helps...

Jamie