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
On 6/23/2011 6:30 AM, K wrote:
> 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.
>
|
Similar Threads
