CODEWITHSUNDEEP
excelvbams-access
Scotch
Scotch

Reputation: 3226

Appending records from one table to another (with different columns)

I'm looking for a way to add the records from one table to another effectively. The tables are similar, but different. Let's say I'm trying to append all of the data from Employee table B into Employee table A.

   Employee Table B
   ---------------
   EmpID (int), fname (text), lname(text)
   1            Bob           Dole

I want this data appended to

   Employee Table A
   empid (int)  fname(text)    lname(text)     DateOfBirth (DateTime)

as

   1             Bob           Dole              null / blank

I have to do this for around 30+ tables and am looking for a quick way to do it.

Rather than writing a INSERT INTO table_a(empid, fname, lname) SELECT empid, fname,lname FROM table_b, I would like to use some of Access's importing features to save time. I tried dumping each table to excel and appending to the necessary tables, but I got a Subscript out of range error. I also tried copy pasting the records to no avail.

Is there a tool that Access provides that will save me the trouble of writing an append query for each table?

Upvotes: 0

Views: 2250

Answers (1)

RichardC
RichardC

Reputation: 794

Provided the field names are the same in both tables (except for the ones missing) you could write some code to do it for you. Use the TableDefs (http://msdn.microsoft.com/en-us/library/office/bb220949(v=office.12).aspx) object to loop through tables and look for "_a" tables to append to and create the INSERT statement on-the-fly by querying the TableDef's .Fields collection.

For example something like this should work (un-tested, written by hand!):

Dim dbs As DAO.Database
Dim tdfLoop As TableDef
Dim strSql As String
Dim i as Integer
Dim strSourceTable as String
Dim strFieldList as String

Set dbs = CurrentDb

With dbs
  For Each tdfLoop In .TableDefs
     If Right(tdfLoop.Name, 2) = "_a" Then            
         strSourceTable = Mid(tdfLoop.Name, 1, Len(tdfLoop.Name)-2) & "_b"
         strSql = "INSERT INTO " & tdfLoop.Name & "("
         strFieldList = ""
         For i = 0 To tdfLoop.Fields.Count - 1
             strFieldList = strFieldList & tdfLoop.Fields(i).Name & ","
         Next i
         If strFieldList <> "" Then
             strFieldList = Mid(strFieldList, 1, Len(strFieldList) - 2)
         End If

         strSql = strSql & strFieldList & ") SELECT " & strFieldList & " FROM " & strSourceTable

         dbs.Execute(strSql)
     End If
  Next tdfLoop
End With

dbs.Close
Set dbs = Nothing

If the 'missing fields' do not have defaults defined in the table, then you could modify the above to return NULL values in their columns but I've assumed they have.

Upvotes: 1

Related Questions