ship said:
We can put the database files anywhere you like!
At present they are have manually been renamed with the download date
burnt into the name, and look something like this:
"Web_Download_20090109.mdb"
Occassionally, where we have had to do two downloads per day they have
been renamed accordingly to look something like this:
"Web_Download_20090109-1.mdb"
"Web_Download_20090109-2.mdb"
From a business perspective this is an extremely simple thing we are
trying to do.
i.e. Take all the data in the .mdb files in THIS directory (all of which
have an IDENTICAL structure) and aggregate it into a single .mbd in THAT
directory.
Are you sure that noone has written anything that will do it ? (!)
Here's an example routine that I just put together for an import form having
text boxes to define a source folder and an archive folder (to which the
source databases will be moved after the import, preventing repeated imports
of the same data), and a command button to trigger the import:
'----- start of example code -----
Private Sub cmdImport_Click()
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strSourceFolder As String
Dim strArchiveFolder As String
Dim strSourceDatabase As String
Dim strTargetDatabase As String
Dim strTable As String
Dim strSQLTemplate As String
Dim strSQL As String
Dim lngDBCount As Long
Dim lngTableCount As Long
Dim lngRecordCount As Long
strSQLTemplate = "INSERT INTO [%d%].[%t%] SELECT * FROM [%t%];"
' Pick up folder paths from text boxes on form.
strSourceFolder = Me.txtSourceFolder
strArchiveFolder = Me.txtArchiveFolder
strSourceDatabase = Dir(strSourceFolder & "Web_Download_*.mdb")
strTargetDatabase = CurrentDb.Name
If Len(strSourceDatabase) = 0 Then
MsgBox "No databases were found to import."
Exit Sub
End If
Do While Len(strSourceDatabase) > 0
Set db = Application.DBEngine.OpenDatabase( _
strSourceFolder & strSourceDatabase, , True)
lngDBCount = lngDBCount + 1
' Import all the tables from the source database
' that match the (user) tables in this database.
For Each tdf In db.TableDefs
' Only process non-system tables.
If Left$(tdf.Name, 4) <> "MSys" Then
strTable = tdf.Name
' Construct SQL of append query.
strSQL = _
Replace( _
Replace(strSQLTemplate, "%d%", strTargetDatabase), _
"%t%", strTable)
' Execute the append query.
db.Execute strSQL, dbFailOnError
' Increment counts
lngRecordCount = lngRecordCount + db.RecordsAffected
lngTableCount = lngTableCount + 1
End If
NEXT_TABLE:
Next tdf
db.Close
' Move file from source folder to archive folder.
Name strSourceFolder & strSourceDatabase _
As strArchiveFolder & strSourceDatabase
' Get name of next database file in folder.
strSourceDatabase = Dir()
Loop
Set db = Nothing
MsgBox _
"Imported " & lngRecordCount & " records from " & _
lngTableCount & " tables in " & _
lngDBCount & " databases.", _
vbInformation, _
"Import Complete"
Exit_Point:
On Error Resume Next
If Not db Is Nothing Then
db.Close
Set db = Nothing
End If
Exit Sub
Err_Handler:
If Err.Number = 3192 Then
' Source table not found in target. Ignore it.
Resume NEXT_TABLE
End If
MsgBox _
Err.Description & vbCr & vbCr & _
"Database: " & strSourceDatabase & vbCr & _
"Table: " & strTable, _
vbExclamation, _
"Error " & Err.Number
Resume Exit_Point
End Sub
'----- end of example code -----