Link to SQL Server via code

[Bill Sturdevant]

I am trying to set up an Access 2002 front end to connect to a SQL Server
backend database. I am successfully able to link a table mannually using
"File==>Get External Data==>Link Tables". I am trying to use the following
code to refresh the links via code. When I do, I get a message:

Run-time error '-2147467259 (80004005)': ODBC - connection to 'CS' failed

What am I doing wrong or not doing?

Call LinkToSQL("TblSales", "Company Sales", "Sales", "CS", "MyUserName",
"12345")


Sub LinkToSQL(strAccessTable, strDBName, strTableName, _
strDataSourceName, strUserID, strPassWord)

'strAccessTable -- name of the table as I want to see it in my front end
'strDBName -- the name of the SQL Server database
'strTableName -- the name of the table in the server
'strDataSourceName -- the name of the File DSN
'strUserID -- the name of the authorized user
'strPassWord -- the authorized user's password

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = New ADOX.Table
tbl.Name = strAccessTable
Set tbl.ParentCatalog = cat

tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC" & _
";DATABASE=" & strDBName & _
";UID=" & strUserID & _
";PWD=" & strPassWord & _
";DSN=" & strDataSourceName
tbl.Properties("Jet OLEDB:Remote Table Name") = strTableName

cat.Tables.Append tbl
End Sub

 

[Douglas J. Steele]

Check Carl Prothman's http://www.able-consulting.com/ado_conn.htm

You're missing the driver information (i.e.: what tells ADO what sort of
database it's going against):

tbl.Properties("Jet OLEDB:Link Provider String") = _
"Driver={SQL Server}" & _
";DATABASE=" & strDBName & _
";UID=" & strUserID & _
";PWD=" & strPassWord & _
";DSN=" & strDataSourceName

 

[Douglas J. Steele]

Sorry, typo there. You need the ODBC as well:

tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC" & _
";Driver={SQL Server}" & _
";DATABASE=" & strDBName & _
";UID=" & strUserID & _
";PWD=" & strPassWord & _
";DSN=" & strDataSourceName

 

[Bill Sturdevant]

Doug -- tried as you suggested -- it did not work either -- same error

 

[Douglas J. Steele]

Sorry: I've never actually tried doing this using ADOX.

See http://members.rogers.com/douglas.j.steele/DSNLessLinks.html for one way
to do it using DAO.

 

[Bill Sturdevant]

Got it to work by adding some more parameters to the link provider string:

";DRIVER={SQL Server}" & _
";SERVER={LAPTOP-WES}" & _
";WSID={LAPTOP-WES}" & _
";NETWORK={DBMSLPCN}" & _

I found the SERVER, WSID and NETWORK values by looking at the properties of
an already "hand-linked" table.

 

[david epsom dot com dot au]

Since it is the connection that is reported as failing, can you
test the connection? Does the DSN work when tested in the ODBC
setup system? Does it work if you attempt an ADO connection?

(david)