Linking tables and Views from MS SQL via OLE DB

Question

Linking tables and Views from MS SQL via OLE DB

Ladislav Zvolánek

SSC Veteran

Points: 210
More actions
November 14, 2006 at 8:14 am

#367996

Please, can anybody tell me, if it is possible to link staticaly tables and views from MS SQL 2000 database to the Access 2003 mdb file via SQLOLEBD provider.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jfmccabe SSCrazy Points: 2547 More actions · Answer 1

Yes. This is based on the code in Chipman and Baron Microsoft Access Developer's Guide to SQL Server. (qv) I assume that all tables are owned by dbo and that when you link them, you give them the same name as they have in the SQL database i.e. you don't have dbo_ in front of the table name.

Public Sub LinkTableDAO(TableName As String, _ ConnectionString As String, _
IndexString As String)
Dim db As DAO.Database Dim tdf As DAO.TableDef Dim SQLString As String On Error Resume Next
Set db = CurrentDb
Set tdf = db.TableDefs(TableName) If Err.Number = 0 Then ' Found an existing tabledef. db.TableDefs.Delete TableName db.TableDefs.Refresh Else ' No existing tabledef. ' Ignore error and reset. Err.Clear End If
On Error GoTo HandleErr ' Create a new TableDef object Set tdf = db.CreateTableDef(TableName) ' Set the Connect and SourceTableName ' properties to establish the link With tdf .Connect = ConnectionString .SourceTableName = "dbo." & TableName End With ' Append to the database's TableDefs collection db.TableDefs.Append tdf db.TableDefs.Refresh 'Set up index SQLString = "CREATE INDEX PrimaryKey ON " SQLString = SQLString & TableName & " (" SQLString = SQLString & IndexString & ") WITH PRIMARY" DoCmd.RunSQL SQLString ExitHere: Exit Sub
HandleErr:
Select Case Err.Number Case Else MsgBox "Error: " & Err.Number & " " _ & Err.Description, , "Link Tables DAO" End Select Resume ExitHere
End Sub

IndexString is a list of the fields making up the primary key of the table or view.

Bob Baldwin Valued Member Points: 53 More actions · Answer 2

Here is the code that we use to connect to all the tables in a SQL database. This does not attach to views. Hope this helps.

Bob

Sub Attach()

DoCmd.Hourglass True

'This code removes all links that currently exist

Dim MyDB As Database, TD As TableDef, strTd(1000) As String, intCount As Integer, x As Integer

Set MyDB = CurrentDb

intCount = 1

For Each TD In MyDB.TableDefs

If TD.Connect <> "" Then

strTd(intCount) = TD.Name

intCount = intCount + 1

End If

MyDB.TableDefs.Delete strTd(x)

Dim strTblName As String, strConn As String, st As String

Dim sqldb As Database

Dim remoteTbl As TableDef

Set sqldb = OpenDatabase("", , , strConn)

strConn = sqldb.Connect

sqldb.QueryTimeout = 0

st = "Update System set ConnectionString='" & strConn & "'"

CurrentDb.Execute st, dbSQLPassThrough

'This code attaches to all the tables from the specified SQL Database.

For Each remoteTbl In sqldb.TableDefs

If InStr(remoteTbl.Name, ".sys") = 0 And InStr(remoteTbl.Name, "sys.") = 0 And InStr(remoteTbl.Name, ".dtp") = 0 And InStr(remoteTbl.Name, "Information_") = 0 Then

Set TD = MyDB.CreateTableDef(Mid(remoteTbl.Name, InStr(remoteTbl.Name, ".") + 1))

TD.Connect = strConn

TD.SourceTableName = remoteTbl.Name

MyDB.TableDefs.Append TD

TD.RefreshLink

End If

MsgBox "Attaching complete"

End Sub

Bob Baldwin Valued Member Points: 53 More actions · Answer 3

I see that you need to remove two lines of code that was for our application that stored the current link in one of our tables named "System"

Remove the following two lines:

st = "Update System set ConnectionString='" & strConn & "'"

CurrentDb.Execute st, dbSQLPassThrough

Ladislav Zvolánek SSC Veteran Points: 210 More actions · Answer 4

Thanks to all for your advice. Both contribution brought me several good ideas.

But, I thing, I did not describe my problem clearly.

What I want to know is If there is a way, how to link table via OLE DB technology (which is technology of ADODB a of the MS Access project /.adp/). I would like to avoid use of DSN and unify the data connection technology of our Access/MSSQL application.

I have fount out that the OLE DB connection string does not work in both procedures. Possibly, I had not correct one, but I used that which worked correctly with ADODB technology in the same application.

I use ADODB technology with OLEDB in our application quite extensively, even as recordsource for forms and reports. But I did not find the way, how to use it for staticaly or dynamicaly linked tables.