Copy data from one databse tabel to other database table

[K]

Hi all, I have about 70 databases in folder "C:\Records\Databases" and
I have one database called "Main" in folder "C\Documents\Data". I
need macro in database "Main" which should loop through all 70
databases in folder "C:\Records\Databases" and copy data from table
"T_Output" of each database and paste into table "T_Input" of database
"Main" in folder "C:\Documents\Data". Please can any friend can help
me on this.

 

[John Spencer]

I don't know of a way to do this with a macro. You could use a VBA routine to
do so. The following is a completely UNTESTED routine that you could add to
the Main database and try.

If you do try this I suggest you make a backup FIRST. It may not give you
what you want, it may partially work, or it may introduce other problems into
your data.

By the way I assumed (you did not say) that your databases are .mdb format.
If they are .accdb format then you will need to change .mdb in the following
code to .accdb

Public Sub sCopyData()
Dim strSQL As String
Dim strExecute As String
Dim dbAny As DAO.Database
Dim strPath As String
Dim strDBName As String

strPath = "C:\Records\Databases\"
Set dbAny = CurrentDb()
strSQL = "INSERT INTO T_Input (NameOfField1, Field2, NameofField3" & _
" SELECT NameOfField1, Field2, NameofField3 FROM T_Output" & _
" IN '" & strPath

strDBName = Dir(strPath)

While Len(strDBName) > 0
If strDBName Like "*.mdb" Then 'check to see if the file is an db
strExecute = strSQL & strDbName & "'"
dbAny.Execute strSQL, dbFailOnError
End If
strDBName = Dir(strPath)
Wend

End Sub

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County