VBA Code to copy tables in other databases 2

[wemeier]

I'm trying to find a way, using VBA, to copy a table from one external Access database to another external database. In other words, there are three Access databases involved (source, destination and code).

Most of the copy commands are methods of DoCmd and only work when the database that is running the code is either the source of the table or the destination of the table.

I can create a MakeTable query that goes from one external database to another external database, but it doesn't copy the indexes -- just the data.

I'd appreciate any advice.

Cruising the Information Superhighway at the speed of light
[sub] (your mileage may vary)[/sub]

 

[PHV]

Copy the table from the source external DB locally, then copy the local stuff to the target external DB, then delete the local copy.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Your right about not being able to do it directly but you can automate access from within Access.

I got a start to this but didn't finish it. This is a place to hang your hat anyways... I've not used docmd.transferdatabase but it looks like what you are looking for.

Sub ExportDb(strDBpath As String)
    
    Dim App As Application
    Set App = CreateObject("Access.application")
    'App.Visible = True
    
    App.OpenCurrentDatabase strDBpath, False
    With App
'         .docmd.transferdatabase
         .Quit
    End With
    Set App = Nothing
End Sub

If you round out the code, please post it back to help others.

 

[wemeier]

PHV,

Thanks for the tip. However, while doing what you say will work, it will bloat my applications database. I'm trying to avoid that.

Cruising the Information Superhighway at the speed of light
[sub] (your mileage may vary)[/sub]

 

[PWise]

how about

Function RemoteCopyTable(SourceTable,SourceDB,DestTable,DestDb)
currentdb.execute "SELECT " & SourceTable & ".* INTO " & DestTable & ".*  IN '" & SourceDB & "'
FROM Students IN '" & DestDB & ";
"
End Function

 

[wemeier]

pwise,

Thanks, but that doesn't copy the indexes. The tables that I want to copy are heavily indexed.

Cruising the Information Superhighway at the speed of light
[sub] (your mileage may vary)[/sub]

 

[MazeWorX]

it will bloat my applications database. I'm trying to avoid that"

How about Compact on close to clean up the bloat?

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[Southernx50]

Have a look at

http://support.microsoft.com/kb/108147

 

[wemeier]

Thanks, Southernx50! That worked perfectly!

Cruising the Information Superhighway at the speed of light
[sub] (your mileage may vary)[/sub]