Import ODBC table using VBA code?

[Jason-W]

Hi,

I'm trying to import an ODBC table (from MS Project
Server) using a VBA macro. The code I'm using is at the
end of the post.

I'm using the TransferDatabase command, by I have a
problem where it can't find the File DSN I want to use for
the connection. So when the code runs it brings up
the "Select Data Source" dialog. After I select the
FileDSN the code executes as expected.

Any help is appreciated.

Thanks,
--Jason


Sub ImportNewTables()
'DSN Settings for connecting to Project Server
Const MSP_DSN = "G:\General\Building Systems\MECH
General\MS_Project\Programming\SQL\File
DSN\Project_Server_DSN.dsn" 'MSP_DSN Server name (setup
in the Admin -> ODBC thru control panel)
Const MSP_STR_USER_IDENT = "MSProjectServerUser" ' Server
Username that has rights to run SQL Queries
Const MSP_STR_PASSWORD_IDENT
= "MSProjectServerUser" 'Password for said user

DatabaseNameLogin = "ODBC; DSN=" & MSP_DSN & _
"; UID=" & MSP_STR_USER_IDENT & _
"; PWD=" & MSP_STR_PASSWORD_IDENT & _
"; LANGUAGE=us_english;
DATABASE=PMK_MSProject"

DoCmd.TransferDatabase _
TransferType:=acImport, _
DatabaseType:="ODBC Database", _
DatabaseName:=DatabaseNameLogin, _
ObjectType:=acTable, _
Source:="MSP_CONVERSIONS", _
Destination:="dbo_MSP_CONVERSIONS_TEST"

end sub

 

[Jason-W]

Hi,

I found a problem with my connection string. I found a
part in the help file that shows where to get a correct
one from under "TransferDatabase Action". If anyone else
needs to know, here's the info from the help file:
Connectivity (ODBC) connection string. To see an example
of a connection string, link an external table to Access
by pointing to Get External Data on the File menu and
clicking Link Tables. Open the table in Design view and
view the table properties. The text in the Description
property setting is the connection string for this table.<<

Regards,
--Jason