Problems with Docmd.Transferdatabase statement 1

[jpl458]

Have this bit of code in "onload" in the opening form of an application. I want to link to an SQLServer database, but I keep getting a "Compile Error:Expected: End of statement" on the semi-colon after the Uid=nn. (I have changed the Uid, Password etc in this post. I get the same results with real names in the code.)

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;Driver={SQL Server};Server=ServerNme;Database=DbNme;_"
Uid=nn;Pwd=xxxxxx",acTable,"MYSQLTABLE","MYACC ESSTABLE" _

After I get this to work I will creating a runtime verson of this ap for distribution so I need to find and log onto the database from other PC's in the domain.

Any help would be appreciated.

Thanks in advance

jpl

 

[SkipVought]

hi,

You would get better results posting in one of the many MS Access forums, like...

forum705 or forum702

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;Driver={SQL Server};Server=ServerNme;Database=DbNme;[!]" & _
"[/!]Uid=nn;Pwd=xxxxxx",acTable,"MYSQLTABLE","MYACC ESSTABLE"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

Well your suggestion got rid of the error. I will try to execute in the morning when I am at work. Is there someplace or book that explains the rules on building these types of expressions? I been through book stores and the web, and only get bits and pieces.

Thanks so much for your help.

jpl

 

[jpl458]

Got the statement to compile, but I get errors when I execute the code. The first error is :Sql Server error:14. I looked that up on the web and found a couple of posts indicationg the I should use {SQL Native Client} rather than {SQL Server}. I made the change and get "ODBC--connection to'{SQL Native Client}Servername' failed. I've looked that up and one suggestion is change an entry in the register with some software I have to buy.

Here is the statement;

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;Driver={SQL Server};Server=ServerNme;Database=DbNme;" & _
"Uid=nn;Pwd=xxxxxx",acTable,"MYSQLTABLE","MYACC ESSTABLE"

Any additional help would be greatly appreciated.

jpl