How many external tables can Access handle?

Question

Are there any logical/structural limitations in Access that would prevent me from having 90 different sources of data linking into a database?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
Cc: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: How many external tables can Access handle?
Date: 9 June 1998 1:54

Paul,

If you don't plan to use union query(-ies) to process all your ~280 attachments (12x8x3) then you should be OK. I tested something like your case in MS Access 97 and as I see it can handle ~ 40 attachments of MS Excel spreadsheets in one union query - so I think you can "up-channel" your source files up-to your "Mother-Ship" and use the same, as you call it, Command application on upper level(s) but you need to write some code which consolidates (append queries) your spreadsheets into three MS Access native tables (Requirements, Projects and Inventory) with Fields and Commands Codes/Descriptor fields...

Here is a fast&dirty code I used to model your case in MS Access 97 - attach 288 MS Excel spreadsheets  (it uses template tmp.xls stored in the same directory as test.mdb - use it only in an empty .MDB - it deletes all table-/querydefs in codedb()! )

Public Function a_test()
    Dim dbs As Database
    Dim strDBPath As String
    Dim i As Integer
    Dim j As Integer
    
    delAll
    
    Set dbs = CodeDb()
    
    strDBPath = dbs.Name
    
    For i = Len(strDBPath) To 1 Step -1
       If Mid(strDBPath, i, 1) = "\" Then
          strDBPath = Left(strDBPath, i)
          Exit For
       End If
    Next
    
   
    On Error Resume Next
    For i = 1 To 12
       MkDir strDBPath & "command" & Format(i, "00")
       
       For j = 1 To 8
          
          Dim tdf As TableDef
          Dim strConnect As String
          Dim strXlsFilePath As String
          Dim strTblName As String
          Dim strWSName As String
          
          strXlsFilePath = strDBPath & "command" & Format(i, "00") & "\" & "field" & Format(j, "00") & "_tmp.xls"
          FileCopy strDBPath & "tmp.xls", strXlsFilePath
          
          
          strConnect = "Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & strXlsFilePath
          
          strWSName = "Requirements"
          strTblName = "Command" & Format(i, "00") & "_Field" & Format(j, "00") & "_" & strWSName
          Set tdf = dbs.CreateTableDef(strTblName)
          tdf.Connect = strConnect
          tdf.SourceTableName = strWSName & "$"
          dbs.TableDefs.Append tdf
          
          strWSName = "Inventory"
          strTblName = "Command" & Format(i, "00") & "_Field" & Format(j, "00") & "_" & strWSName
          Set tdf = dbs.CreateTableDef(strTblName)
          tdf.Connect = strConnect
          tdf.SourceTableName = strWSName & "$"
          dbs.TableDefs.Append tdf

          strWSName = "Projects"
          strTblName = "Command" & Format(i, "00") & "_Field" & Format(j, "00") & "_" & strWSName
          Set tdf = dbs.CreateTableDef(strTblName)
          tdf.Connect = strConnect
          tdf.SourceTableName = strWSName & "$"
          dbs.TableDefs.Append tdf
          
          DoEvents
          dbs.TableDefs.Refresh
       Next
    Next
End Function

Public Function delAll()
    Dim dbs As Database
    Dim tdf As TableDef
    Dim qdf As QueryDef
    
    Set dbs = CodeDb()
    
tdf_Cycle:
    For Each tdf In dbs.TableDefs
       If Left(tdf.Name, 4) <> "Msys" Then
         dbs.TableDefs.Delete tdf.Name
         dbs.TableDefs.Refresh
         GoTo tdf_Cycle
       End If
    Next
    
qdf_Cycle:
    For Each qdf In dbs.QueryDefs
       If Left(qdf.Name, 4) <> "Msys" Then
         dbs.QueryDefs.Delete qdf.Name
         dbs.QueryDefs.Refresh
         GoTo qdf_Cycle
       End If
    Next
End Function

And here is a fast&dirty code which generates union query:

Public Function GenQry()
    Dim dbs As Database
    Dim tdf As TableDef
    Dim qdf As QueryDef
    Dim strSql As String
    Dim i As Integer
    
    Set dbs = CodeDb()
    strSql = ""
    
    i = 0
    For Each tdf In dbs.TableDefs
       
       If Left(tdf.Name, 7) = "Command" Then
        If Right(tdf.Name, 8) = "Projects" Then
        strSql = strSql & "select ProdItemId from [" & tdf.Name & "] union " & vbCrLf
        i = i + 1
        If i > 40 Then Exit For
        End If
       End If
    Next
    
    strSql = Left(strSql, Len(strSql) - 8)
    
    Set qdf = dbs.CreateQueryDef("quniAll", strSql)

End Function

HTH,
Shamil