Home > Archive > Visual Basic > September 2004 > create linked table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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?
| |
| Paul Clement 2004-09-28, 3:55 pm |
| 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.CallReportCategory"
¤ 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)
|
|
|
|
|