Create Linked Table

[Wayne Wengert]

I am trying to create a VB.NET Windows application to move some data from a
local Access DB table to a table in a SQL Server. The approach I am trying
is to open an OLEDB connection to the local Access DB and then add a Linked
Table pointing to the table on the SQL Server and then run an "Insert Into
(linked table)" query to add the new rows.

I am having a problem getting the syntax to add that linked table to my
local Access DB. When I used to create a LinkedTable to another Access DB I
used to use the code below but I haven't been able to find the correct
syntax to make linked table from SQL Server.

Pointers to any helpful information will be much appreciated.

Wayne


============== Code =================
Public Sub AddLink()
Dim objCat As ADOX.Catalog
Dim objTbl As ADOX.Table
objCat = New ADOX.Catalog
objTbl = New ADOX.Table

objCat.ActiveConnection = myConn
objTbl.Name = "DC"
objTbl.ParentCatalog = objCat
objTbl.Properties("Jet OLEDB:Create Link") = True
objTbl.Properties("Jet OLEDB:Link Provider String") = "MS
Access;DATABASE=" & strLocalDBPath
objTbl.Properties("Jet OLEDB:Remote Table Name") = "DC"

objCat.Tables.Append(objTbl)
objCat.Tables.Refresh()

objCat = Nothing
objTbl = Nothing
End Sub
========================================

 

[Paul Clement]

¤ I am trying to create a VB.NET Windows application to move some data from a
¤ local Access DB table to a table in a SQL Server. The approach I am trying
¤ is to open an OLEDB connection to the local Access DB and then add a Linked
¤ Table pointing to the table on the SQL Server and then run an "Insert Into
¤ (linked table)" query to add the new rows.
¤
¤ I am having a problem getting the syntax to add that linked table to my
¤ local Access DB. When I used to create a LinkedTable to another Access DB I
¤ used to use the code below but I haven't been able to find the correct
¤ syntax to make linked table from SQL Server.
¤
¤ Pointers to any helpful information will be much appreciated.
¤
¤ Wayne
¤
¤
¤ ============== Code =================
¤ Public Sub AddLink()
¤ Dim objCat As ADOX.Catalog
¤ Dim objTbl As ADOX.Table
¤ objCat = New ADOX.Catalog
¤ objTbl = New ADOX.Table
¤
¤ objCat.ActiveConnection = myConn
¤ objTbl.Name = "DC"
¤ objTbl.ParentCatalog = objCat
¤ objTbl.Properties("Jet OLEDB:Create Link") = True
¤ objTbl.Properties("Jet OLEDB:Link Provider String") = "MS
¤ Access;DATABASE=" & strLocalDBPath
¤ objTbl.Properties("Jet OLEDB:Remote Table Name") = "DC"
¤
¤ objCat.Tables.Append(objTbl)
¤ objCat.Tables.Refresh()
¤
¤ objCat = Nothing
¤ objTbl = Nothing
¤ End Sub
¤ ========================================
¤

See if the following helps:

Sub CreateAttachedSQLTableWithADOX()

Dim ADOXTable As New ADOX.Table
Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection

Try

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.ActiveConnection = ADOConnection

ADOXTable.ParentCatalog = ADOXCatalog
ADOXTable.Name = "Customers"
ADOXTable.Properties("Jet OLEDB:Remote Table Name").Value = "Customers"
ADOXTable.Properties("Jet OLEDB:Link Provider String").Value = "ODBC;Driver={SQL
Server};" & _
"Server=(local);" & _
"Database=Northwind;" & _
"Uid=sa;" & _
"Pwd="
ADOXTable.Properties("Jet OLEDB:Create Link").Value = True
ADOXCatalog.Tables.Append(ADOXTable)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ADOConnection.Close()
End Try

End Sub


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Wayne Wengert]

Paul;

Thanks for the complete example. That really helps. I tried adapting the
example to my data (changed to the IP address of the SQL server and entered
the correct database, AID and PW but it keeps failing saying that it cannot
connect to my server. The "Driver={SQLServer};" is a constant, right?

Wayne

 

[Wayne Wengert]

Paul;

When I copied your example I dropped the space between "SQL" and "Server".
That broke it! I think I have it working now. Thanks for the help!

Wayne

 

[Paul Clement]

¤ Paul;
¤
¤ Thanks for the complete example. That really helps. I tried adapting the
¤ example to my data (changed to the IP address of the SQL server and entered
¤ the correct database, AID and PW but it keeps failing saying that it cannot
¤ connect to my server. The "Driver={SQLServer};" is a constant, right?
¤
¤ Wayne

Yes, you shouldn't need to change the Driver parameter. I'm running a local version of SQL Server so
you would probably need to change that parameter.

You may want to check the below link for other variations:

http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForSQLServer


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)