"ODBC Connection failed" in DoCMd.TransferDatabase "ODBC Database"

[Alika]

"ODBC Connection failed" in DoCMd.TransferDatabase "ODBC Database"

The following statement:
DoCmd.TransferDatabase acExport, "ODBC Database", _
"ODBC;SERVER=ServerName;UID=UserID;PWD=password;" _
& "DATABASE=NewAccounts", acTable, "tblNewAccounts", "tblNewAccounts"
executes fine exept a user get prompt for DSN name.

I tryed to hardcode DSN:
DoCmd.TransferDatabase acExport, "ODBC Database", _
"ODBC;DSN=DSNName;UID=UserID;PWD=password;" _
& "DATABASE=NewAccounts", acTable, "tblNewAccounts", "tblNewAccounts"

I got an Error message:
"ODBC Connection failed"
How can I code "exporting tables" so end user won't have poped up Server Window "Select Data Source" with which he/she is not familiar att all.
Thanks

 

[kissinger]

make a macro

Use a macro to export a table to an other database. (or to a text file or to excel file)

action: TransferDatabase
parameters:
transfer type: export
etc....

To prevent user intervention start macro with:
SetWarnings ----> No
end your macro with SetWarnings ----> Yes

 

[Alika]

Use a macro

It works with macro! Thank you very much!