Here is one way. It assumes that the table exists in the (source) MDB
containing this code but not in the (destination) MDB to which the table is
to be copied. If the table is already there, I would have to deal with that
contingency. In that case, though, it would be better to just automate a
table link & then run a query to append from the source to the destination,
unless you are changing the table structure. If that is what you really want,
post back, and I can post that code also.
I separated the code into the module header declarations & four separate
procedures because I extracted this from a module that I use when
distributing patches to existing DB's and need to make changes to back end
MDB's to which I do not have realtime access. The full module includes code
to add/remove fields to tables, add indexes, copy tables, create
relationships, etc.
In this case, the procedure (CopyTable) calls the other three, in turn.
Hopefully, I copied & pasted everything correctly here - if anything does not
work, post back, and I will post the entire module
'Declarations - in module header
Private tblName As String
Private tblName2 As String
Private db As DAO.Database
Private tdf As DAO.TableDef
Private Sub CopyTable()
'create local link to target MSysObjects as MSysObjectsTarget
tblName = "MSysObjectsTarget" 'local table name; do not change this line
tblName2 = "MSysObjects" 'source table name; do not change this line
TableLinkCreateCurrentDB 'refers to separate procedure below
'copy the table
tblName = "NameOfYourSourceTable" 'enter name of table to copy from
tblName2 = "NameOfYourDestinationTable" 'enter name of table to copy to
TableCopy 'refers to separate procedure below
'remove the link when done to avoid leaving it in the source MDB
tblName = "MSysObjectsTarget" 'local table name; do not change this line
TableLinkDeleteCurrentDB 'refers to separate procedure below
End Sub
Sub TableLinkCreateCurrentDB()
'Creates link to table in this patch file to allow local running of queries
'ensure variables have been provided
If IsNull(tblName) Then
MsgBox "The local table name must be specified.", vbCritical,
"Missing information"
Exit Sub
End If
If IsNull(tblName2) Then
MsgBox "The source table name must be specified.", vbCritical,
"Missing information"
Exit Sub
End If
'link table
Set db = CurrentDb
Set tdf = db.CreateTableDef(tblName)
tdf.Connect = ";DATABASE=" & FilePath & ""
tdf.SourceTableName = tblName2
db.TableDefs.Append tdf
End Sub
Sub TableCopy()
'copies new table from source to destination table
'ensure variables have been provided
If IsNull(tblName) Then
MsgBox "The table name must be specified.", vbCritical, "Missing
information"
End If
If DCount("[Name]", "[MSysObjects]", "[Name]='" & [tblName] & "'and Type
=1") = 0 Then
MsgBox "The local table was not found.", vbCritical, "Missing
information"
End If
'copy table
If DCount("[Name]", "[MSysObjectsTarget]", "[Name]='" & [tblName] &
"'and Type =1") = 0 Then 'run TableCopy only if table does not yet exist in
target database
DoCmd.SetWarnings False
DoCmd.CopyObject FilePath, tblName2, acTable, tblName
DoCmd.SetWarnings True
End If
End Sub
Sub TableLinkDeleteCurrentDB()
'Deletes link to local table in this patch file after local running of queries
If IsNull(tblName) Then
MsgBox "The table name must be specified.", vbCritical, "Missing
information"
End If
Set db = CurrentDb
db.TableDefs.Delete tblName
End Sub
Brad said:
I am trying to figure out the VBA code to import a table from one Access
database into a second Access database. Currently both the source and the
target table names are the same. I plan to run a query to delete all the
rows in the target table. I then would like to import the source table and
update the target table with the rows from the source table. I would really
appreciate a simple example.
Thanks,
Brad
