move tables to other db

[Bram de Groot]

I want to move tables into new databases and since there are quite a lot of tables in my database I want to automate this procedure in VB.

suppose I want to move table1 to database1 , table2 to database2, etc....

Can anyone help me out?

Thanks!

Bram de Groot

 

[dan-cat]

Something like this:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
strdbPath = "Put the full file path of your target db here"

For Each tdf In db.TableDefs

If Not tdf.Name Like "Msys*" Then

DoCmd.TransferDatabase acExport, "Microsoft Access", strdbPath, acTable, tdf.Name, tdf.Name
End If
Next

 

[Bram de Groot]

Works perfectly! Thanks!

Bram de Groot

 

[Tim L]

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef

    For Each tdf In db.TableDefs
        If Not tdf.Name Like "Msys*" Then
            strFileName = strDrivePath & "DLTPxpt.mdb"
            DoCmd.TransferDatabase acExport, "Microsoft Access", [COLOR=DarkRed]strFileName[/COLOR], acTable, tdf.Name, tdf.Name
        End If
    Next

I've used the above, with very little modification (which should not, I don't think, have a detrimental effect).

The problem is that when I run the code I get the following error message:

"Object variable or With Block variable not set"

I've used the code as part of a much larger routine and moved the two dim statements to the start of the code. The error occurs as soon as the "For" line is excuted.

I figure that there must be something else that I need to do with the db or tdf variables, but don't know what.

Tim

 

[modest]

Go into your database you want to open. Right click the white space in the database window. Select import... (choose the database you want to import from) and you can make multiple selections of tables, queries, forms, etc. and import them all at one time.

 

[Tim L]

Hi Modest,

Sorry, I suppose I should have made it clear that I really wanted to make use of the automated process.

I want users to be able to export multiple tables from a database and rather than create multiple tables and files with transferSpreadsheet I thought that it would be best to create a single file. I saw the routine given by dan-cat and thought that it would solve the problem.

BTW, I have checked and Microsoft DAO 3.6 is included in my dB, if anyone thought that might be part of the problem.

Tim

 

[Tim L]

Ok, knew deep down that it had to be something simple... needed the line

Set db = CurrentDb

Doh!

Tim