Importing a table without knowing its name? (1 Viewer)

totempohl · Aug 13, 2013

I figured out how to import a table using VBA like so:

Code:

Private Sub Command0_Click()
    Dim dr As String
    dr = Dir("F:\SomeFolder\*.mdb", vbDirectory)
    DoCmd.TransferDatabase acImport, "Microsoft Access", "F:\SomeFolder\" & dr, acTable, "Table", "Table2"
    
End Sub

This was just a test to see if I could get an import to work. My problem is that I have a lot of .mdb/.accdb files with similar tables that I need to add into one larger database and these similar tables could have different names in different folders. For example, a file named db1.mdb has a table named tbl. Another file named db2.mdb has a table with all of the same fields, but its called tabX. Furthermore, db2.mdb could even contain a second table that I need to import.

Sorry if that explanation was unnecessarily long. I'm only trying to be clear.

My question is:

Is there a way to simply import all the tables from an access database without knowing any table names using VBA?

Thank you for your time. Any help is greatly appreciated.

TJPoorman · Aug 13, 2013

I'm not a hundred percent sure on the importable function, but I would imagine you could loop through all the tables in a database and import them based on the name. The following code is something I use to get all the table names for a particular database. Perhaps you could adapt this to do what you're looking for.

Code:

Private Sub GetDatabaseTables(strDatabase As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim objAccess As Object
Dim strConnect As String
 
Set objAccess = CreateObject("Access.Application")
DoCmd.SetWarnings False
 
With objAccess
    .UserControl = False
    .OpenCurrentDatabase strFrontEndPath & strDatabase & ".accde"
   Set db = .CurrentDb
    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) = "msys" Then
            'skip these tables
        ElseIf Nz(tdf.Connect, "") = "" Then
            'these are local tables.  record them with the tag ***Local***
            DoCmd.RunSQL "INSERT INTO tblDatabaseTables ([Database], [TableName], [Connection]) " & _
                        "VALUES ('" & strDatabase & "', '" & tdf.Name & "', '***Local***')"
        ElseIf Left(tdf.Connect, 9) <> ";DATABASE" Then
            'these are not standard database connections so record the whole connect string
            DoCmd.RunSQL "INSERT INTO tblDatabaseTables ([Database], [TableName], [Connection]) " & _
                        "VALUES ('" & strDatabase & "', '" & tdf.Name & "', '" & tdf.Connect & "')"
        Else
            'these are standard database connections so record only the connected database
            strConnect = Mid(tdf.Connect, InStrRev(tdf.Connect, "\") + 1)
            DoCmd.RunSQL "INSERT INTO tblDatabaseTables ([Database], [TableName], [Connection]) " & _
                        "VALUES ('" & strDatabase & "', '" & tdf.Name & "', '" & strConnect & "')"
        End If
    Next tdf
    .CloseCurrentDatabase
End With
 
DoCmd.SetWarnings True
Set objAccess = Nothing
End Sub

totempohl · Aug 13, 2013

Thank you so much for your reply, TJPoorMan. I definitely needed another perspective on this and I think I can make your solution work for what I need. I won't be able to try this out until tomorrow, but thank you once again for your help.

boblarson · Aug 14, 2013

There is some simpler code you could use - using DoCmd.TransferDatabase. This is a simple example which assumes you only have local tables in that database. But you can modify it to bypass other tables besides system if you need to.

Code:

Function GetDatabaseTables(strFileAndPath)
       Dim tdf As DAO.TableDef
       Dim db As DAO.Database
 
      Set db = OpenDatabase(strFileAndPath)
 
      For Each tdf In db.TableDefs
          If Left(tdf.Name, 4) <> "Msys" Then
             DoCmd.TransferDatabase acImport, "Microsoft Access", strFileAndPath, acTable, tdf.Name, tdf.Name
 
          End If
     Next
       MsgBox "done"
End Function

gemma-the-husky · Aug 14, 2013

to do what you are doing manually you need this amendment to your own code

Code:

Private Sub Command0_Click()
    Dim dr As String
    dim tb as string
 dim tb2 as string
 
dr = use_some_dialog_to_locate_the_file_you_want_to_upload
tb = use_some_code_to_determine_the_tablename_to_load
tb2 = use_some_code_to_determine_the_tablename_to_import_to
 
    DoCmd.TransferDatabase acImport, "Microsoft Access",  dr, acTable, tb,  tb2    
End Sub

so it's a matter of taste, and testing to find the best way of capturing the filenames, and source and destination table names

totempohl · Aug 14, 2013

Thank you all for replying to my post so quickly. After figuring out how to search a folder and its subfolders for .mdb and .accdb files, I got around to actually importing tables from them. I ended up using a solution looking almost exactly like boblarson's and it works perfectly so far. Thanks again. I'm really glad I joined this site.

Importing a table without knowing its name? (1 Viewer)

totempohl

New member

TJPoorman

Registered User.

totempohl

New member

boblarson

Smeghead

gemma-the-husky

Super Moderator

totempohl

New member

Similar threads

Users who are viewing this thread