• VBA and Backend DB Access

    • This topic has 6 replies, 3 voices, and was last updated 13 years ago.
    Author
    Topic
    #483403

    Hi Y’all,

    I’m having a bit of a problem with Access (2003) and VBA. I’d like to gain access to the TableDefs object in a linked database and can’t figure how to to it.

    If I use CurrentDb.TableDefs(1).Name I get the name of the Linked table but
    CurrentDb.TableDefs(1).DateCreated returns the current date (I relink the table everytime the db is opened {don’t ask}). If I open the .mdb file with the linked tables the same command returns the correct date. Likewise .RecordCount, .LastUpdated, .Updateable.

    I can tell if the table is linked by use of the .Connect property which returns a connection string, I think, i.e., ;Database=G:pathfilename.ext or blank if the table is not linked. But how do I directly access the linked DB file to get the values I need? :cheers:

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!
    Computer Specs

    Viewing 2 reply threads
    Author
    Replies
    • #1333694

      Hi RG,

      You can use ADO to access any other DB from Access itself. You just need to open the connection using the connection string:

      Code:
      Dim con As ADODB.Connection
      
      
      Dim connString As String
      
      
      con.Open (connString)
      
      ... 
      
      con.Close
      

      Once you get the connection, you can use with ADOX to access specific tables:

      Code:
      Dim con As ADODB.Connection
      Dim cat As ADOX.Catalog
      Dim tbl As ADOX.Table
      
      
      Dim connString As String
      
      
      Set cat = New ADOX.Catalog
      
      
      con.Open connString
      
      
      cat.ActiveConnection = con
      
      
      Set tbl = cat.Tables("tableName")
       (...)
      
      
      con.Close
      

      Hope this gets you on track. To use ADO, you will need to set a reference to a Microsoft ActiveX Data Objects Library.

    • #1333700

      Rui,

      Thanks!

      I have a couple of follow on questions.
      1. Can ADO be used with Late Binding?
      2. Is ADO available with both Access 2003 & 07/10?
      3. Can this method be used on an already open back-end DB?
      :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1333711

      You’re welcome.

      I have never used ADO with late binding. Actually, I don’t really use late binding, but it does seem possible. Here is an example: http://bytes.com/topic/access/answers/210255-late-binding

      ADO is available with all Access versions. There are several version of the library, for each version of Access / Office. Whenever I need to access an Access database programmatically, I use ADO instead of DAO. Always did it, I guess. ADO can be used for all data sources, from Access to SQL Server to Excel and other data sources. ADO was succeeded by ADO.NET, a similar API but available only for the .net framework.

      The method can be used with any DB. Usually I just use CurrentProject.Connection, to obtain the connection to the current DB, instead of using a connection string. I suppose you’d have problems only if the database was opened exclusively, but I suppose it won’t be.

      • #1333716

        Alternatively

        Dim db as database
        Set db = DBEngine(0).OpenDatabase(“C:PathYourBackend.mdb”)
        Debug.Print db.TableDefs(“tblYourTable”).DateCreated

        • #1333722

          Alternatively

          Dim db as database
          Set db = DBEngine(0).OpenDatabase(“C:PathYourBackend.mdb”)
          Debug.Print db.TableDefs(“tblYourTable”).DateCreated

          Cronk,

          Thanks this works great and I’ll stick with it since I’m already using DAO for everything else and that should be more efficient. Interesting that ADO & DAO have different properties, i.e. DAO doesn’t have the .Keys.Count available but does have others that ADO does not. You’d thing MS could be more consistent. :cheers:

          Code:
          Sub TestDAOEarly()
          
             Dim conDB As Database
             Dim zConStr  As String
             Dim vProp As Variant
             
             zConStr = CurrentDb.TableDefs("Owners").Connect
             zConStr = Right(zConStr, Len(zConStr) - 10)
             Set conDB = DBEngine(0).OpenDatabase(zConStr)
             
            Debug.Print "Early Binding"
            With conDB.TableDefs("Owners")
                Debug.Print "Created:    " & .DateCreated
                Debug.Print "Updated:    " & .LastUpdated
                Debug.Print "Fields:     " & .Fields.Count
                Debug.Print "Indexes:    " & .Indexes.Count
                Debug.Print "Updateable: " & .Updatable
                Debug.Print "Records:    " & .RecordCount
            End With
             
             Set conDB = Nothing
          
          End Sub

          [noparse]Early Binding
          Created: 11/11/05 4:31:46
          Updated: 11/18/10 2:02:44
          Fields: 11
          Indexes: 2
          Updateable: True
          Records: 476
          [/noparse] :cheers:

          May the Forces of good computing be with you!

          RG

          PowerShell & VBA Rule!
          Computer Specs

      • #1333718

        Rui,

        Ok I got it to work with both early and late binding.

        Code:
        Sub TestADOEarly()
        '*** Early Binding Example ***
          Dim Cat      As ADOX.Catalog
          Dim tbl      As ADOX.Table
          Dim zConStr  As String
          Dim zBaseCon As String
          
        '  zConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydatabase.mdb;User Id=admin;Password=;"
          zBaseCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
          zConStr = CurrentDb.TableDefs("Owners").Connect
          zConStr = Right(zConStr, Len(zConStr) - 10)
          zConStr = zBaseCon & zConStr & ";User Id=admin;Password=;"
          Set Cat = New ADOX.Catalog
          Cat.ActiveConnection = zConStr
        
          Set tbl = Cat.Tables("Owners")
         
          Debug.Print "Early Binding"
          Debug.Print "Created: " & tbl.DateCreated
          Debug.Print "Updated: " & tbl.DateModified
          Debug.Print "Indexes: " & tbl.Indexes.Count
          Debug.Print "Keys:    " & tbl.Keys.Count
          
          Set tbl = Nothing
          Set Cat = Nothing
          
        End Sub
        
        Sub TestADOLate()
         '*** Late Binding Example ***
          Dim Cat      As Object
          Dim tbl      As Object
          Dim zConStr  As String
          Dim zBaseCon As String
          
        '  zConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydatabase.mdb;User Id=admin;Password=;"
          zBaseCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
          zConStr = CurrentDb.TableDefs("Owners").Connect
          zConStr = Right(zConStr, Len(zConStr) - 10)
          zConStr = zBaseCon & zConStr & ";User Id=admin;Password=;"
          
          Set Cat = CreateObject("ADOX.Catalog")
          Cat.ActiveConnection = zConStr
        
          Set tbl = CreateObject("ADOX.Table")
          Set tbl = Cat.Tables("Owners")
         
          Debug.Print "Late Binding"
          Debug.Print "Created: " & tbl.DateCreated
          Debug.Print "Updated: " & tbl.DateModified
          Debug.Print "Indexes: " & tbl.Indexes.Count
          Debug.Print "Keys:    " & tbl.Keys.Count
        
          Set tbl = Nothing
          Set Cat = Nothing
        
        End Sub

        [noparse]Early Binding
        Created: 11/11/05 4:31:46
        Updated: 11/18/10 2:02:44
        Indexes: 2
        Keys: 1

        Late Binding
        Created: 11/11/05 4:31:46
        Updated: 11/18/10 2:02:44
        Indexes: 2
        Keys: 1
        [/noparse]

        Unfortunately, ADOX does not offer all the properties that DAO does, i.e., RecordCount, Fields.count, etc. but it does give the biggies that I want for my documentation program. Thanks again! :cheers:

        Cronk,

        I’ll give your suggestion a go also and report back. :cheers:

        May the Forces of good computing be with you!

        RG

        PowerShell & VBA Rule!
        Computer Specs

    Viewing 2 reply threads
    Reply To: VBA and Backend DB Access

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: