VBA code to copy table structure and data from one access database to another

How would I write a do loop if I have many tables?  Is there a way for VBA to recognize the names of the tables without me having to type that in the code and automatically use that same same for the copied table in the new db?

you can also use this

dim extDb as string
extDb="c:\folderX\xDB.mdb"
docmd.TransferDatabase acExport, "Microsoft Access", extDB, acTable, _
        ["& td.name &"], ["& td.name &"], false

I did try that solution earlier, what I get is the same linked tables copied over.  So if I copy this db onto my flash drive and take it home, I cannot open the tables because the link to the SQL tables is not there.  Is there a way that I can perhaps establish a connection to the sql tables and then do a data dump of each table into a new access table offline?

The "Description" section under each Table's properties states the following connection:

ODBC;DSN=QA;APP=Microsoft® Access;WSID=GENE;DATABASE=QA;Network=DBMSSOCN;Address=Webby1,1433;TABLE=mytablename

and I have about 100 of these........

did you try the codes on http:#25310689 ?

Yes.  It is only copying the linked format, not creating the static access format tables........I have a picture attached of my the tables in my destination database.  As you can see, the table were copied or transferred over as linked SQL tables:(

right now I have to manually create 100 or so separate select queries that will output all the fields of the table.   And then copy the data output from these select queries to my destination database.  I have attached a picture of the macro.

i just tried the codes, copying a linked SQL table and the table was created to the external DB.

how did you use the codes?

ok, partial success..................

I chose one table and it copied over as a static access table and not a linked SQl table.  However, I had to physically type the name of the table.  How can I automate the logic process below where vba can take over selecting the names of the tables instead of me having to type them?  Thanks.

For Each tbl In db.TableDefs
   If Left(tbl.Name, 4) <> "msys" Then
       fPrg.AppendMessage "Exporting table " & tbl.Name
       'DoCmd.TransferDatabase acExport, "Microsoft Access", qaPath, acTable, tbl.Name, tbl.Name, False, True
        db.Execute "select 90F0069.* into 90F0069 in 'C:\Documents and Settings\sanjayg\Desktop\qa.mdb' from 90F0069"
       'fPrg.UpdateTime
   End If
Next

                                              

1:
2:
3:
4:
5:
6:
7:
8:

the codes i posted is very clear..

db.execute "select ["& td.name &"].* into ["& td.name &"] in 'c:\folderX\xDb.mdb'  from ["& td.name &"]"


why do you have to type the table names?

This is the code that I used for the parameters defined:

For Each tbl In db.TableDefs
  If Left(tbl.Name, 4) <> "msys" Then
      fPrg.AppendMessage "Exporting table " & tbl.Name
      'DoCmd.TransferDatabase acExport, "Microsoft Access", qaPath, acTable, tbl.Name, tbl.Name, False, True
      'fPrg.UpdateTime
  End If
Next

I will try.....

This is what I am trying........verbatim except for the destination address.

db.Execute "select [" & td.Name & "].* into [" & td.Name & "] in 'C:\Documents and Settings\sanjayg\Desktop\qa.mdb'  from [" & td.Name & "]"

ok, I get a complie error where "td" is not defined.

the define it as i have define it in my post at http:#25310689

Here is my complete code with variables

Sub exportdbobjects2()
Dim db As dao.Database, qdf As dao.QueryDef, tbl As dao.TableDef, qaPath As String, j As Integer, fPrg As Form, rpt, mdl, frm, mac
On Error GoTo ProcErr
qaPath = "C:\Documents and Settings\sanjayg\Desktop\qa.mdb"
Set db = CurrentDb
Set fPrg = New Form_zfrmProgress
'fPrg.Visible = True
' you can set the caption if you like:
fPrg.Caption = "Exporting database objects..."
 
db.Execute "select [" & td.Name & "].* into [" & td.Name & "] in 'C:\Documents and Settings\sanjayg\Desktop\qa.mdb'  from [" & td.Name & "]"
 
fPrg.StopTimer
 
'MsgBox "Finished Exporting Objects."
 
ProcExit:
   On Error Resume Next
   DoCmd.Close acForm, fPrg.Name
   Exit Sub
ProcErr:
   MsgBox Err.Description
   Resume ProcExit
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

sxxgupta,

all you need to do is copy the codes i posted at http:#25310689

Ok, I did, thank you.  I now get a "Object variable or With block variable not set" error.

post the codes *you* are using