Link to home
Start Free TrialLog in
Avatar of Sanjay
SanjayFlag for United States of America

asked on

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

Right now I have a database that contains liked SQL tables.  If I want to analyze data "offline", I simply just cannot copy the database as another .mdb file because the tables are copied as linked SQL tables.  Could someone please help me in developing VBA code that will copy the table structure and data to another .mdb file?  The tables being copied should have the same name.  Right now I have some code bits for exporting queries, macros, etc to an offline database defined by the variable qaPath (see code below).  I want to follow the same logic of copying the table structure and data of these linked SQL tables that I can insert into the code below.  Right now I am using a macro (called 'z copy tables') to peform this task, where essentially I am using artificially created queries to output the data of these linked tables and then copying these queries outputs as data inputs to my offline tables.  I think this is extremely inefficient especially if new linked SQL tables are created that I have to keep track of it.  I believe VBA is the way to go.  I have attached an example of one line of the macro below:

Transfer Type: Export
Database Type: Microsoft Access
Database Name: C:\Documents and Settings\sanjayg\Desktop\qa.mdb
Object Type: Table
Source: copy Test  ------------------------The name of the table is Test and I have created query 'copy Test' to output table Test data
Destination: Test --------------------------The new name of the table is the same as the name of the linked table
Structure Only: No

I would have to do this about 100 times because there are about that many tables.  This means I would have to create 100 queries and then fill in 100 line items in the macro to copy the table structure and data and export the table object, its structure, and data to "qaPath".

Please help.
Sub exportdbobjects1()
Dim db As DAO.Database, qdf As DAO.QueryDef, qaPath As String, j As Integer, rpt, mdl, frm, mac
On Error GoTo ProcErr
qaPath = "C:\Documents and Settings\sanjayg\Desktop\qa.mdb"
Set db = CurrentDb
 
 For Each qdf In db.QueryDefs
   If Left(qdf.Name, 1) <> "~" Then
       DoCmd.TransferDatabase acExport, "Microsoft Access", qaPath, acQuery, qdf.Name, qdf.Name
   End If
Next
 
For j = 0 To db.Containers("Reports").Documents.Count - 1
    rpt = db.Containers("Reports").Documents(j).Name
    DoCmd.TransferDatabase acExport, "Microsoft Access", qaPath, acReport, rpt, rpt
Next
 
For j = 0 To db.Containers("Modules").Documents.Count - 1
    mdl = db.Containers("Modules").Documents(j).Name
    DoCmd.TransferDatabase acExport, "Microsoft Access", qaPath, acModule, mdl, mdl
Next
 
For j = 0 To db.Containers("Scripts").Documents.Count - 1
    mac = db.Containers("Scripts").Documents(j).Name
    DoCmd.TransferDatabase acExport, "Microsoft Access", qaPath, acMacro, mac, mac
Next
 
For j = 0 To db.Containers("Forms").Documents.Count - 1
    frm = db.Containers("Forms").Documents(j).Name
    DoCmd.TransferDatabase acExport, "Microsoft Access", qaPath, acForm, frm, frm
Next
 
DoCmd.RunMacro "z copy tables"
 
MsgBox "Finished Exporting Objects."
 
ProcExit:
   On Error Resume Next
   Exit Sub
ProcErr:
   MsgBox Err.Description
   Resume ProcExit
End Sub

Open in new window

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

to copy a table to another .mdb, create a make table query, something like this



currentdb.execute "select T1.* into TX in 'c:\folderX\xDb.mdb' from T1"
Avatar of Sanjay

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of Sanjay

ASKER

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?
Avatar of Sanjay

ASKER

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 ?
Avatar of Sanjay

ASKER

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:(
test.jpg
Avatar of Sanjay

ASKER

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.
macro.jpg
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?
Avatar of Sanjay

ASKER

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

Open in new window

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?
Avatar of Sanjay

ASKER

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
Avatar of Sanjay

ASKER

I will try.....
Avatar of Sanjay

ASKER

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 & "]"
Avatar of Sanjay

ASKER

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 
Avatar of Sanjay

ASKER

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

Open in new window

sxxgupta,

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

ASKER

Ok, I did, thank you.  I now get a "Object variable or With block variable not set" error.
post the codes *you* are using
Avatar of Sanjay

ASKER

The solution works!!!!!!!!!!Thank you Capricorn.  Obviously I was not listening or reading very well and taking your advice literally:(  But thank you for hanging in there.  I know that it can be frustrating working with a novice:)