link to access table from sql server

[toryee]

Hi,
I have created a table in MS Access. Mass manipulation such as deleting the records is very slow in Access so I’d like to have a table in sql server that links to this table. The Import/Export Wizard in sql server is not what I wanted because it copies the table from Access but not link to it. How do I link to an Access table from sql server?

Thanks for any help.

 

[cmmrfrds]

A couple of ways to get at the Access table in sql server are by using the access table as a linked server, which needs to be done through sql server - dba knowledge. Another is the OPENROWSET function in sql server. If you have the Northwinds database installed on your sql server the run the following example. Paste the code in a module and run.


Public Function rowset()

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
'--- could put variables in at this point for location and database name
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If
End Function

 

[toryee]

I figured out a way to link the table through sql server. Thank you for the input.