How do you export sql tables into another db as access tables (1 Viewer)

[Jayce72]

I have an access database connected to a sql server. How can I export/copy the sql tables into another database as Access tables? Not as sql tables!

I could write make table qrys and run them as a macro, but it'll take ages to write 250 qrys to do this.

Any help please!!!!

 

[Jayce72]

PS...I placed this problem in this forum as I believe it can be done in vb!!

 

[gemma-the-husky]

all of them?

use a maketable query

use the tabledef object.
in a tabledef,
the tabledef.name property is the name of the table
the tabledef.connect property determines whether the table is linked, or not

so something like this pseudocode will separate linked tables from local tables, and process all your tables.

dim tdf as tabledef
dim counts as long
 
    counts = 0
    for each tdf in currentdb.tabledefs
        if tdf.connect<>"" then
            'you need to sort this line out!
            docmd.openquery "maketable query for table tdf.name"
            counts= counts + 1
         end if
     next

     msgbox(counts & " tables created")

 

[Jayce72]

Hi thanks
will give it a go

 

[Jayce72]

In my make table qry I have to build, do I have to add the 1st table in the database. Not really sure how the make table qry will look like. As your code will run this qry for every table, so not sure on the tables etc in the qry

Thanks for your help

 

[JANR]

Something like this inside your loop:

Currentdb.Execute " SELECT [" & tdf.Name & "].* INTO [" & tdf.Name & "] IN 'C:\Path and name to database'" & _
                  " FROM [" & tdf.Name & "];", dbFailOnError

Not tested!

JR

 

[Jayce72]

Something like this inside your loop:

Currentdb.Execute " SELECT [" & tdf.Name & "].* INTO [" & tdf.Name & "] IN 'C:\Path and name to database'" & _
                  " FROM [" & tdf.Name & "];", dbFailOnError

Not tested!

JR

Fantastic.....Top drawer
Thank you very much...it worked perfectly
Jason