This should get you started. You'll need to run the TransferDatabase for each table you want in your new mdb file.
Option Compare Database
Option Explicit
Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function USERNAME() As String
Dim strTemp As String, strUserName As String
'Create a buffer
strUserName = String(100, Chr$(0))
'Get the username
GetUserName strUserName, 100
'strip the rest of the buffer
strUserName = Left$(strUserName, InStr(strUserName, Chr$(0)) - 1)
USERNAME = strUserName
End Function
Public Sub CreateExport()
On Error GoTo CreateExport_err
Dim myFile As Variant, strFileName As String
'This is where you want the file to go.
strFileName = "C:\TEST\" & USERNAME() & "3.mdb"
'Checks to see if the file exists since TransferDatabase requires an existing .mdb
myFile = Dir(strFileName)
If Len(myFile) <= 0 Then
'File doesn't exist, create using DAO
Dim wspDefault As Workspace, dbs As Database
Set wspDefault = DBEngine.Workspaces(0)
Set dbs = wspDefault.CreateDatabase(strFileName, dbLangGeneral)
dbs.Close
wspDefault.Close
End If
' test was the name of my input table, test2 was the name of my output table
DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "test", "test2"
CreateExport_exit:
Exit Sub
CreateExport_err:
MsgBox "Error: " & Err.Description
GoTo CreateExport_exit
End Sub
I hope this helps... it was a quick and dirty sample just to test the concept...
Take care,
Jeff