Create ODBC to SQL Server in VBA 2

[jpl458]

Created a small application the uses linked SQL Server tables which were set up manually. I would like to create the link using VBA so I can transport the application to PC's that don't have MS Office 10 on them. The application iscreated in Office 10 and the tables are in SQL Server Express 8. I have tried executing the following bit of code with no success, although it compiles:

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

I get this error:

Connection failed:
SQLState:01S00
SQLServer Error:0
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
Connection failed:
SQLState:’0100’
SQL Server Error:53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Connection open
(connect()
Connection failed:
SQL Server Error:17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or is denied.

After reading the web a lot, I tried using SQL Native Client as:

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

Then I get 'ODBC--connection to {SQL Native Client} John-PC failed

and "Error 3151". for which there are many description on the web

As stated, all I want to do is create a link to two tables in an SQL Server database in vba.

Any help would be appreciated.

Thanks.

jpl

 

[genomon]

You will some helphul ideas here:

http://www.connectionstrings.com/

http://www.accessmvp.com/djsteele/DSNLessLinks.html

Gluais faicilleach le cupan làn

 

[SkipVought]

So from your example

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

You LITERALLY have a server with the ACTUAL NAME ServerNme, because that is EXACTLY what your code is saying, and a Database with the ACTUAL NAME DbNme.

If these are VARIABLES, then your code is incorrect!

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

and so on for each variable.

Skip,

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

 

[jpl458]

No, those are not the names, I just didn't want to publish them about. (Upon reflection, I should have mentioned that I changed the names.) I will give your suggestions a run, and will let you know.

I also went to the sites Genomon suggested.

Thanks for the help, really appreciate it.

jpl

 

[jpl458]

Here is what I have, and I keep getting a divide by zero error:

DoCmd.TransferDatabase acLink, _
"ODBC Database", _
"ODBC;Driver={SQL Server};Server=" & John - PC \ SQLExpress & ";Database=" & trandatsql & ";" & _
"Uid=John;Pwd=turbott", acTable, "dbo_Transaction_Table", "dbo_Transaction_Table"

(The line breaks are correct in the ap)

I want the same name in the ACCESS ap as in SQL Server

Sorry to keep beating this but it's my first connect string.

 

[PHV]

What about this ?
DoCmd.TransferDatabase acLink, _
"ODBC Database", _
"ODBC;Driver={SQL Server};Server=John - PC \ SQLExpress;Database=trandatsql;" & _
"Uid=John;Pwd=turbott", acTable, "dbo_Transaction_Table", "dbo_Transaction_Table"


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

 

[SkipVought]

here's your divide by ZERO error

"ODBC;Driver={SQL Server};Server=" & John - [highlight]PC \ SQLExpress[/highlight] & ";Database=" & trandatsql & ";" & _
"Uid=John;Pwd=turbott", acTable, "dbo_Transaction_Table", "dbo_Transaction_Table"

Your code implies that John, PC & SQLExpress are ALL NUMERIC VALUES.

Use

Option Explicit

in EVERY module to avoid these kinds of errors.

Skip,

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