import table via ODBC 2

[sinyce]

Every day I import a table from our AS/400 using:

File | Get External Data | Import | ODBC Databases | Machine Data Source [NYC400]

Here I insert the Password and select my Table and
I am done. This works beautiful.

I'm trying to automate this with VBA code without any luck so far this is where I am stuck:

DoCmd.TransferDatabase acImport, _
"ODBC Database", "ODBC;DSN=NYC400;UID=XXXX;PWD=YYYY;" _
& "DATABASE=NYCPROD", acTable, "CONTACTS"

Here are the facts:
DSN NAME: NYC400
USER ID: XXXX
P/W: YYYY

DATABASE: NYCPROD
TABLE NAME [imported into Access] CONTACTS

Full code:

Public Sub DWBase()
Dim db As DAO.DATABASE
Set db = CurrentDb
DoCmd.TransferDatabase acImport, _
"ODBC Database", "ODBC;DSN=NYC400;UID=ACBS;PWD=ACBS;" _
& "DATABASE=NYCPROD", acTable, "CONTACTS"
End Sub

Help

 

[borg2002]

Hello sinyce,

I have never worked with the TransferDatabase command, but
I compared your importstring with the default example from the microsoft knowledge base and found that you have no destination provided for your import.

DoCmd.TransferDatabase [transfertype], databasetype, databasename[, objecttype],
úsource, destination[, structureonly][, saveloginid]

Hope this helps...
Borg

 

[DerickD]

The connection string that you are looking for is easy to find :

1) Link the table into your access dd.
2) With the table selected switch to Design mode.
(it will say that you can not change any setting...blah... but just click through that)
3) Right click on the Design screen and select 'Properties'
4) You will then find the exact string that you need for connecting to the Database.


Hope this is what you are looking for.

 

[coco86]

Nifty little trick there, DerickD. I was trying to figure out how to import a Visual Foxpro table and this was a big help.

Thanks!

-Coco

 

[janeatwork]

Hi DerickD

I like this one too.

Jane