create linked table

I need to create a table in Access which is linked to a SQL Server table.
Here's the code I'm trying to use:

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table
Dim sLocalDB As String
Dim sProv As String

On Error GoTo ErrorHandler
Set catDB = New ADOX.Catalog
sLocalDB = IniLineRead(sIniFileName, "LOCATIONS", "LocalDB")
catDB.ActiveConnection = sLocalDB
Set tblLink = New ADOX.Table

With tblLink
.Name = "Test_Server"
Set .ParentCatalog = catDB
sProv = "ODBC;Data Source=GVLSNT113;Initial Catalog=CPFdb;" + _
"User ID=;Password=;"
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Provider String") = sProv
.Properties("Jet OLEDB:Remote Table Name") = "dbo.CallReportCategory"
End With
catDB.Tables.Append tblLink
Set catDB = Nothing

I'm getting an ODBC error on the .append method.

What have I missed?

On Mon, 27 Sep 2004 07:37:02 -0700, "Erin" <Erin@discussions.microsoft.com> 
wrote:

¤ I need to create a table in Access which is linked to a SQL Server table.
¤ Here's the code I'm trying to use:
¤
¤     Dim catDB As ADOX.Catalog
¤     Dim tblLink As ADOX.Table
¤     Dim sLocalDB As String
¤     Dim sProv As String
¤
¤     On Error GoTo ErrorHandler
¤     Set catDB = New ADOX.Catalog
¤     sLocalDB = IniLineRead(sIniFileName, "LOCATIONS", "LocalDB")
¤     catDB.ActiveConnection = sLocalDB
¤     Set tblLink = New ADOX.Table
¤
¤     With tblLink
¤         .Name = "Test_Server"
¤         Set .ParentCatalog = catDB
¤         sProv = "ODBC;Data Source=GVLSNT113;Initial Catalog=CPFdb;" + _
¤                 "User ID=;Password=;"
¤         .Properties("Jet OLEDB:Create Link") = True
¤         .Properties("Jet OLEDB:Link Provider String") = sProv
¤         .Properties("Jet OLEDB:Remote Table Name") = "dbo.CallReportCatego
ry"
¤     End With
¤     catDB.Tables.Append tblLink
¤     Set catDB = Nothing
¤
¤ I'm getting an ODBC error on the .append method.
¤
¤ What have I missed?

I think your link provider string may be incorrect. Here is a simple example
:

sProv = "ODBC;Driver={SQL Server};" & _
"Server=(local);" & _
"Database=Northwind;" & _
"Uid=sa;" & _
"Pwd="


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)