Import ODBC tables with script (1 Viewer)

Hi All,

I want to import tables automatically from a server database. I don't really know where to start. I have searched the net, but can't seem to get anything to work. I have a macro but I get the error "could not find installable ISAM"

When I import manually Here is the sequence of popup screens:
Import "ODBC"
Machine Data Source "name"
Oracle ODBC Driver Connect "Service Name", "User Name" & "Password"
Import Objects "TableName"

Is anyone familiar with a script that can import tables automatically (I will use a command button).

Thanks
Michael

You need to have an Oracle ODBC driver installed on the machine you want to be able to run the import routine on. There is no standard Oracle driver installed so you have to have it installed as part of the user tools from Oracle.

And, once you do, here's where you can find the actual connection string info:
http://www.connectionstrings.com

Thanks Boblarson for pointing me in the right direction.

now have it sorted.

FYI, here is the final code:

Dim dsn As String
Dim uid As String
Dim pwd As String
Dim odbcConnectStr As String

DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "GUEST_PROT_CDRL_DOC"
DoCmd.DeleteObject acTable, "GUEST_PROT_CN_DATA"
DoCmd.DeleteObject acTable, "GUEST_PROT_CN_AFFECTS"
DoCmd.DeleteObject acTable, "GUEST_PROT_PR_DATA"
DoCmd.SetWarnings True

dsn = "TeamCenter"
uid = "guest"
pwd = "guest"

' build up the connect string
odbcConnectStr = "ODBC;DSN=" & dsn & ";UID=" & uid & ";PWD=" & pwd


DoCmd.TransferDatabase acImport, "ODBC", odbcConnectStr, acTable, "GUEST.PROT_CDRL_DOC", "GUEST_PROT_CDRL_DOC", False
DoCmd.TransferDatabase acImport, "ODBC", odbcConnectStr, acTable, "GUEST.PROT_CN_AFFECTS", "GUEST_PROT_CN_AFFECTS", False
DoCmd.TransferDatabase acImport, "ODBC", odbcConnectStr, acTable, "GUEST.PROT_CN_DATA", "GUEST_PROT_CN_DATA", False
DoCmd.TransferDatabase acImport, "ODBC", odbcConnectStr, acTable, "GUEST.PROT_PR_DATA", "GUEST_PROT_PR_DATA", False

Cheers
Michael

Glad you got it sorted.