none
Loop through multiple access databases in a folder a gather table definitions for all RRS feed

  • Question

  • Hi, I need help.

    I have 192 databases that I need to redact certain information in certain columns. Generally, I'm looking for field names like name, first name, last name, address, address2, shipping address, mailing address, and phone numbers. Not sure how to get this information without going into every every database and every local table inside the databases. Is there a way to get this information programmatically and just access the databases and table that I need to redact info in?

    Thank You.


    Tuandelia

    Saturday, March 8, 2014 7:55 PM

Answers

  • Use the ADOX Catalog library.  You'll need to add a reference to ADO Extentions.  This is a good link to start with:

    http://support.microsoft.com/kb/303814

    Below is a routine to fill a listbox with tables in a database.  tripDB is a ADO database connection. ADOX is usually used to create DBs/tables etc but you can use it to extract info.

       


    Sub InitDBList()
    
      Dim i As Integer
      Dim cat As New ADOX.Catalog
      
      Set cat.ActiveConnection = tripDB
      UtilForm.TableNameCB.Clear
      For i = 0 To cat.Tables.Count - 1
        If cat.Tables(i).Type = "TABLE" Then
          UtilForm.TableNameCB.AddItem cat.Tables(i).Name
        End If
      Next i
      
      If UtilForm.TableNameCB.ListCount >= 1 Then
        UtilForm.TableNameCB.ListIndex = 0
      Else
        UtilForm.TableNameCB.ListIndex = -1
      End If
    End Sub
    

    Sunday, March 9, 2014 3:56 PM

All replies

  • I'm sure there is, but I suggest you open at least 20 databases at random to see what the required table name and field names are for the information you want. Once you've seen the scope of the problem, I suspect you need to manually create a View in each database with the same name and column titles in every db that shows all the data you want to copy. Then all you need to do is:

    1. Open each db in code (easy enough)
    2. Copy all data from the new View and append it to a table in another db or copy to Excel (depending on amount of data).
    3. Close the db.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Sunday, March 9, 2014 7:30 AM
  • Thank you. How do I open each db through code to get the table definitions?

    Tuandelia

    Sunday, March 9, 2014 10:39 AM
  • Use the ADOX Catalog library.  You'll need to add a reference to ADO Extentions.  This is a good link to start with:

    http://support.microsoft.com/kb/303814

    Below is a routine to fill a listbox with tables in a database.  tripDB is a ADO database connection. ADOX is usually used to create DBs/tables etc but you can use it to extract info.

       


    Sub InitDBList()
    
      Dim i As Integer
      Dim cat As New ADOX.Catalog
      
      Set cat.ActiveConnection = tripDB
      UtilForm.TableNameCB.Clear
      For i = 0 To cat.Tables.Count - 1
        If cat.Tables(i).Type = "TABLE" Then
          UtilForm.TableNameCB.AddItem cat.Tables(i).Name
        End If
      Next i
      
      If UtilForm.TableNameCB.ListCount >= 1 Then
        UtilForm.TableNameCB.ListIndex = 0
      Else
        UtilForm.TableNameCB.ListIndex = -1
      End If
    End Sub
    

    Sunday, March 9, 2014 3:56 PM
  • Thank you very much. I'm up and running now.

    Tuandelia

    Sunday, March 9, 2014 7:53 PM