Solved: Copy Remote Tables

[DarkSprout]

Can Anybody please help, I need to copy loads of Tables via code, from one database to another, with a third controling the operation
what I'm doing is looping through the TableDef:

What I Have So Far

sql="SELECT * INTO SourceTable IN OPENROWSET" & _
"(Provider=Microsoft.Jet.OLEDB.4.0;DataSource=Path\SourceDatabase.mdb) FROM " & _
"SourceTable IN OPENROWSET(Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
"Source=Path\DestinationDatabase.mdb);"
DoCmd.RunSQL sql


PLEASE HELP!

But I get the error:
Query input must contain at least one table or query.

[CreganTur]

At first blush it looks like the problem is with the syntax of your SQL. I'm guessing that SourceTable and OPENROWSET are variable names that represent the names of the actual tables you are working with.

When you add variables into a SQL string in VBA you have to wrap the variables with '" "' and concatenate them using &.

So the first part of your code should be:
sql="SELECT * INTO '" & SourceTable & "' IN '" & OPENROWSET"' & _
That might be why it says your query doesn't contain a table.

HTH

[DarkSprout]

I Think (OpenRowSet): 'OPENROWSET(Provider=Microsoft.Jet.OLEDB.4.0;DataSource=Path\SourceDatabase .mdb)'
is the conection string, if it isn't please somebody correct me!

[OBP]

Why use that method when Access VBA provides the Transfer database method?
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:\My Documents\NWSales.mdb", acReport, "NW Sales for April", _
"Corporate Sales for April"

you just need to obtain the .Name from the Table Defs an dput it in the Transfer database don't you?

[DarkSprout]

With DoCmd.TransferDatabase the Source:"Corporate Sales for April"
has to be local, this is to be a third controling two others
:C copies table from A into B ...

[CreganTur]

I Think (OpenRowSet): 'OPENROWSET(Provider=Microsoft.Jet.OLEDB.4.0;DataSource=Path\SourceDatabase .mdb)'
is the conection string, if it isn't please somebody correct me!

I think you might be right on that. Try it and see :dunno

If that still doesn't work then I've got an alternate method you could try.

[DarkSprout]

OK, what is the other method, I', burnt out on all of the others

[Tommy]

I believe the Path is not defined and the "\SourceDatabase.mdb" may be in correct so far as qualifing it, course this may be a typo also :)

DataSource=Path\SourceDatabase.mdb
'should be
DataSource='" & Path & "\SourceDatabase.mdb'

I haven't tested this........

[CreganTur]

OK, what is the other method, I', burnt out on all of the others

Sorry man. The metod I was referring to is something I use to pull in data from Excel spreadsheets, but so far I can't adapt it to work with Access.

I do think Tommy was right, though- your path (since it was a variable) wasn't listed properly.

This might work(be sure to reformat it after you C&P):

sql="SELECT * INTO '" & SourceTable & "' IN OPENROWSET" & _
"(Provider=Microsoft.Jet.OLEDB.4.0;DataSource='" & Path & "'\SourceDatabase.mdb) FROM " & _
'" & SourceTable & "' IN OPENROWSET(Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
"Source='" & Path & "'\DestinationDatabase.mdb);"
DoCmd.RunSQL sql

Now I'm assuming that SourceTable is also a variable, along with Path.

I'm not very knowledgable with ADO, but I hope this solves your problem.

[DarkSprout]

Done It!


Private Function BuildSQL(SourceMDB As String, DestMDB As String, TableName As String) As String
'// =DarkSprout= June08
'// ToUse: sql = BuildSQL(PathAndDBName, PathAndDBName, TableName)
'// DoCmd.RunSQL sql
BuildSQL = "SELECT * INTO " & TableName & " IN " & Chr(34) & DestMDB & Chr(34) & " " & _
"FROM " & TableName & " IN " & Chr(34) & SourceMDB & Chr(34) & ";"
End Function