By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 941 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Create Linked Table

P: n/a
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
========================================
Nov 21 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Mon, 7 Mar 2005 07:40:02 -0700, "Wayne Wengert" <wa***************@wengert.com> wrote:

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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #2

P: n/a
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

"Paul Clement" <Us***********************@swspectrum.com> wrote in message
news:89********************************@4ax.com...
On Mon, 7 Mar 2005 07:40:02 -0700, "Wayne Wengert" <wa***************@wengert.com> wrote:
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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

Nov 21 '05 #3

P: n/a
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" <Us***********************@swspectrum.com> wrote in message
news:89********************************@4ax.com...
On Mon, 7 Mar 2005 07:40:02 -0700, "Wayne Wengert" <wa***************@wengert.com> wrote:
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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

Nov 21 '05 #4

P: n/a
On Mon, 7 Mar 2005 12:14:55 -0700, "Wayne Wengert" <wa***************@wengert.com> wrote:

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/...erForSQLServer
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.