Can VBA DAO access linked tables? 
Author Message
 Can VBA DAO access linked tables?

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?



Wed, 09 Aug 2000 03:00:00 GMT  
 Can VBA DAO access linked tables?

Silly question :-)

Why not use the original source of the data rather than through a link in
an Access database?

Terry



Quote:
> 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?



Wed, 09 Aug 2000 03:00:00 GMT  
 Can VBA DAO access linked tables?

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?



Quote:
>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?



Thu, 10 Aug 2000 03:00:00 GMT  
 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?



Thu, 10 Aug 2000 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Strange ODBC errors using DAO to access linked tables

2. VB using DAO to execute Access query containing a linked table

3. Linking Access tables vs. DAO

4. DAO - Creating tables with VBA in Access

5. Linked table in Access 97 using VBA code ????

6. VBA: linking a table from a secured access-db

7. Access 2000 VBA/SQL - cannot create pseudo-index on linked SQL Server table

8. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

9. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

10. Problems using VBA to link or DAO to import Excel file into Access97

11. Linking Excel As Tables Using DAO

12. Linked tables and DAO

 

 
Powered by phpBB® Forum Software