
Can VBA DAO access linked tables?
Below is a direct copy from the Connection Object in help. So no guarantees
but hopefully it will point you in the right direction
Terry
Sub ConnectionObjectX()
Dim wrkJet as Workspace
Dim dbsNorthwind As Database
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim conPubs2 As Connection
Dim conLoop As Connection
Dim prpLoop As Property
' Open Microsoft Jet Database object.
Set wrkJet = CreateWorkspace("NewJetWorkspace", _
"admin", "", dbUseJet)
Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")
' Create ODBCDirect Workspace object and open Connection
' objects.
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("Connection1", , , _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")
Set conPubs2 = wrkODBC.OpenConnection("Connection2", , _
True, "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")
Debug.Print "Database properties:"
With dbsNorthwind
' Enumerate Properties collection of Database object.
For Each prpLoop In .Properties
On Error Resume Next
Debug.Print " " & prpLoop.Name & " = " & _
prpLoop.Value
On Error GoTo 0
Next prpLoop
End With
' Enumerate the Connections collection.
For Each conLoop In wrkODBC.Connections
Debug.Print "Connection properties for " & _
conLoop.Name & ":"
With conLoop
' Print property values by explicitly calling each
' Property object; the Connection object does not
' support a Properties collection.
Debug.Print " Connect = " & .Connect
' Property actually returns a Database object.
Debug.Print " Database[.Name] = " & _
.Database.Name
Debug.Print " Name = " & .Name
Debug.Print " QueryTimeout = " & .QueryTimeout
Debug.Print " RecordsAffected = " & _
.RecordsAffected
Debug.Print " StillExecuting = " & _
.StillExecuting
Debug.Print " Transactions = " & .Transactions
Debug.Print " Updatable = " & .Updatable
End With
Next conLoop
dbsNorthwind.Close
conPubs.Close
conPubs2.Close
wrkJet.Close
wrkODBC.Close
End Sub
Quote:
> Cauz I've not done that before :) Here's what I've got:
> Server Name: sys1000.2110
> Server DSN: CeilSrv
> User Name: smith
> Password: xyz
> How would I set it up so that I could connect with that info?
> >Silly question :-)
> >Why not use the original source of the data rather than through a link
in
> >an Access database?
> >Terry
> >> I'm trying to get data from a linked access table using VBA in Outlook
> >> '97. Non-linked access tables work just fine. However, a linked
> >> table produces an Invalid Page Fault. Here's the code:
> >> set dbe=Application.CreateObject("DAO.DBEngine.35")
> >> set conDb=dbe.Workspaces(0).OpenDatabase("MyData.mdb",1)
> >> set rstCust=conDb.OpenRecordset("Select * from LinkedTable", 4)
> >> Is anybody else able to access linked tables?