The code below illustrates what I'm trying to do. TransferDatabase and CopyObject won't work because I can only import to or export from the database I have open. What I need is code that will iterate through a text file containing the list of mdb's, create a corresponding accdb, and copy all the objects from one to the other.
Code:
Public Sub UpgradeDatabase()
Dim app As Object
Dim dbOld As Database
Dim dbNew As Database
Dim tbl As TableDef
Dim fileName As String
Dim dbName As String
Set app = CreateObject("Access.Application")
app.Visible = False
app.UserControl = False
'mdbList.txt is a file with a list of mdb names and the path to it
Open "C:\temp\MS Access\mdbList.txt" For Input As 1
While Not EOF(1)
Line Input #1, fileName
'extract the name of the database from the path
dbName = Replace(Mid(fileName, InStrRev(fileName, "\") + 1), ".mdb", "")
'create a new accdb database with the same name
Set dbNew = app.DBEngine.CreateDatabase("C:\temp\MS Access\" & dbName & ".accdb")
dbNew.Close
Set dbNew = Nothing
Set dbOld = app.OpenCurrentDatabase(fileName)
'copy the objects from old to new
For Each tbl In dbOld.TableDefs
'this line does not compile, but essentially what I need to do
'also need to do a similar loop for each object type
dbOld.CopyObject "C:\temp\MS Access\" & dbName & ".accdb", , acTable, tbl.Name
Next
dbOld.Close
Set dbOld = Nothing
Wend
Close 1
app.Quit
Set app = Nothing
End Sub