RE: Link to SQL Server via code

From: Bill Sturdevant (BillSturdevant_at_discussions.microsoft.com)
Date: 01/16/05 

Date: Sun, 16 Jan 2005 09:03:02 -0800

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.

"Bill Sturdevant" wrote:

> 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
>

Relevant Pages