Results 1 to 7 of 7
  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts

    VBA and Backend DB Access

    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:\path\filename.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?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.

  3. The Following User Says Thank You to ruirib For This Useful Post:

    RetiredGeek (2012-05-23)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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

    A
    DO 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.

  6. #5
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Alternatively

    Dim db as database
    Set db = DBEngine(0).OpenDatabase("C:\Path\YourBackend.mdb" )
    Debug.Print db.TableDefs("tblYourTable").DateCreated

  7. The Following User Says Thank You to Cronk For This Useful Post:

    RetiredGeek (2012-05-23)

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    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


    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!

    Cronk,

    I'll give your suggestion a go also and report back.
    Last edited by RetiredGeek; 2012-05-23 at 21:11.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Cronk View Post
    Alternatively

    Dim db as database
    Set db = DBEngine(0).OpenDatabase("C:\Path\YourBackend.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.
    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
    Early Binding
    Created: 11/11/05 4:31:46
    Updated: 11/18/10 2:02:44
    Fields: 11
    Indexes: 2
    Updateable: True
    Records: 476
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •