Transfer a table from one Db to another using VBA in a 3rd Db?

[Kahuna]

Should be a simple matter but I cant define a method.

Db 'A' is running VBA and has Db'B' Open and Db'C' Open. How could I
transfer a Table from Db'B' to Db'C'.

Help appreciated.

 

[Jon]

Should be a simple matter but I cant define a method.

Db 'A' is running VBA and has Db'B' Open and Db'C' Open. How could I
transfer a Table from Db'B' to Db'C'.

Help appreciated.

If you don't want to import it from one database into the current
database, and then export it again, you could use something like:

Sub sExportExternal(strDBFrom As String, strDBTo As String,
strTableName As String)
' Procedure to transfer a table from one external Access database to
another
' Accepts:
' strDBFrom - the name and path of the database that contains
the table to be exported from
' strDBTo - the name and path of the database that the table is
to be exported to
' strTableName - the name of the table that is to be exported
On Error GoTo E_Handle
Dim objAccess As New Access.Application
With objAccess
.OpenCurrentDatabase (strDBFrom)
.DoCmd.TransferDatabase acExport, "Microsoft Access", strDBTo,
acTable, strTableName, strTableName
.CloseCurrentDatabase
End With
sExit:
Exit Sub
E_Handle:
Select Case Err.Number
Case 3011 ' The table does not exist in the first database
MsgBox "'" & strTableName & "' does not exist in '" &
strDBFrom & "'", vbOKOnly, "Transfer cancelled"
Case 3044 ' The database that we are transferring the table to
does not exist
MsgBox "'" & strDBTo & "' does not exist.", vbOKOnly,
"Transfer cancelled"
Case 7866 ' The database that we are transferring the table
from does not exist
MsgBox "'" & strDBFrom & "' does not exist.", vbOKOnly,
"Transfer cancelled"
Case Else
MsgBox Err.Description, vbOKOnly + vbCritical, Err.Number
End Select
Resume sExit
End Sub

Jon

Access tips & tricks - http://www.applecore99.com
Microsoft Access webring -
http://a.webring.com/hub?ring=microsoftaccess

 

[Tim Ferguson]

Db 'A' is running VBA and has Db'B' Open and Db'C' Open. How could I
transfer a Table from Db'B' to Db'C'.

strSQL = "SELECT * FROM MyTable " & _
"INTO MyOtherTable IN H:\MyData\DatabaseC.mdb"

dbB.Execute strSQL, dbFailOnError


Hope that helps


Tim F

 

[Kahuna]

Tim thanks for that, but I tried that and its a partial answer. There seems
no way to replace the path and Db name with a variable which is really
necessary to automate this process of Table Transfer.

Unless you know a way?

Cheers

 

[Kahuna]

Jon, that looks interesting, Ill play with that tomorrow.

Cheers

 

[david epsom dot com dot au]

s1="[mydb2].[mytbl2]"
s2="[mydb3].[mytbl3]"

Currentdb.execute "select * from " & s1 & " into " & s2

(david)

 

[Kahuna]

Thanks David - pretty sure I tried that format but will play again ASAP.

Thanks all for the input.

--
Kahuna
------------

s1="[mydb2].[mytbl2]"
s2="[mydb3].[mytbl3]"

Currentdb.execute "select * from " & s1 & " into " & s2

(david)

 

[Tim Ferguson]

s1="[mydb2].[mytbl2]"
s2="[mydb3].[mytbl3]"

Currentdb.execute "select * from " & s1 & " into " & s2

Close: the s1 has to be the name of a table, and the s2 has to be the name
and path of the target:

s1 = "MyOldTable"

s2 = "MyNewTable IN d:\thatdata\theOther.mdb"

If you have already opened the second database, you can get its path:

s2 = "MyNewTable IN " & dbSecond.Name

Without looking, I can't remember if you need quotes around the path: you
certainly will if it contains illegal characters like spaces:

s2 = "MyNewTable IN """ & dbSecond.Name & """"

Then you can call the Execute command as above, preferably with a WHERE
clause. Don't forget to add the dbFailOnError parameter.

B Wishes



Tim F

 

[david epsom dot com dot au]

Got that wrong of course - I always mix up the order of the
FROM and INTO clause -- what I was trying (!) to demonstrate
was the use of the [file].

syntax, rather than IN


select *
into [c:\db2.mdb].[table2]
from [c:\db3.mdb].[table3]
;

(david)

s1="[mydb2].[mytbl2]"
s2="[mydb3].[mytbl3]"

Currentdb.execute "select * from " & s1 & " into " & s2

Close: the s1 has to be the name of a table, and the s2 has to be the name
and path of the target:

s1 = "MyOldTable"

s2 = "MyNewTable IN d:\thatdata\theOther.mdb"

If you have already opened the second database, you can get its path:

s2 = "MyNewTable IN " & dbSecond.Name

Without looking, I can't remember if you need quotes around the path: you
certainly will if it contains illegal characters like spaces:

s2 = "MyNewTable IN """ & dbSecond.Name & """"

Then you can call the Execute command as above, preferably with a WHERE
clause. Don't forget to add the dbFailOnError parameter.

B Wishes



Tim F

 

[david epsom dot com dot au]

You're right - it should be
SELECT * INTO [file1].[table1] FROM [file2].[table2]

or
INSERT INTO [file1].[table1] SELECT * FROM [file2].[table2]


-- I always get the order of the INSERT/SELECT/FROM/INTO
clauses mixed up.

(david)

Thanks David - pretty sure I tried that format but will play again ASAP.

Thanks all for the input.

--
Kahuna
------------

s1="[mydb2].[mytbl2]"
s2="[mydb3].[mytbl3]"

Currentdb.execute "select * from " & s1 & " into " & s2

(david)

Tim thanks for that, but I tried that and its a partial answer. There seems
no way to replace the path and Db name with a variable which is really
necessary to automate this process of Table Transfer.

Unless you know a way?

Cheers

--
Kahuna
------------


Db 'A' is running VBA and has Db'B' Open and Db'C' Open. How could I
transfer a Table from Db'B' to Db'C'.



strSQL = "SELECT * FROM MyTable " & _
"INTO MyOtherTable IN H:\MyData\DatabaseC.mdb"

dbB.Execute strSQL, dbFailOnError


Hope that helps


Tim F

 

[Tim Ferguson]

into [c:\db2.mdb].[table2]
from [c:\db3.mdb].[table3]

No again: this is not a recogisable way of referring to tables in remote
databases. Check the help files for the IN clause... ... and note that you
can only use one IN in the a single command too.


Tim F

 

[Marshall Barton]

into [c:\db2.mdb].[table2]
from [c:\db3.mdb].[table3]

No again: this is not a recogisable way of referring to tables in remote
databases. Check the help files for the IN clause... ... and note that you
can only use one IN in the a single command too.

Did you try David's approach Tim? Regardless of Help, that
syntax has been valid for a long time. Sorry, but I can't
find the URL for the Access SQL Language reference.

Aside from some naming and syntax restrictions, you can even
use a subquery inside the square brackets.

As for two IN clauses, this will also work:

SELECT *
INTO table2
IN "c:\db2.mdb"
FROM table3
IN "c:\db3.mdb"

Note that the IN clause and the square bracket syntax both
have an awesome degree of power to retrieve data from
ODBC(?) sources. For example, I've used this kind of query
on several occasions:

SELECT *
INTO table2
IN "c:\db2.mdb"
FROM [range3]
IN "" [Excel 5.0;HDR=NO;DATABASE=c:\wb3.xls]

 

[david epsom dot com dot au]

ODBC(?) sources. For example, I've used this kind of query

IN "" [Excel 5.0;HDR=NO;DATABASE=c:\wb3.xls]

Yes, and from ISAM sources too

IN "" [ODBC;DSN=FRED] (air code)

(david)

into [c:\db2.mdb].[table2]
from [c:\db3.mdb].[table3]

No again: this is not a recogisable way of referring to tables in remote
databases. Check the help files for the IN clause... ... and note that you
can only use one IN in the a single command too.

Did you try David's approach Tim? Regardless of Help, that
syntax has been valid for a long time. Sorry, but I can't
find the URL for the Access SQL Language reference.

Aside from some naming and syntax restrictions, you can even
use a subquery inside the square brackets.

As for two IN clauses, this will also work:

SELECT *
INTO table2
IN "c:\db2.mdb"
FROM table3
IN "c:\db3.mdb"

Note that the IN clause and the square bracket syntax both
have an awesome degree of power to retrieve data from
ODBC(?) sources. For example, I've used this kind of query
on several occasions:

SELECT *
INTO table2
IN "c:\db2.mdb"
FROM [range3]
IN "" [Excel 5.0;HDR=NO;DATABASE=c:\wb3.xls]

 

[david epsom dot com dot au]

Are you using ADO dynamic SQL? I haven't tested to
see if this syntax has been broken in that environment --
my suggestion was for use in an Access Query/AKA Querydef
/AKA view/AKA stored procedure.

(david)

into [c:\db2.mdb].[table2]
from [c:\db3.mdb].[table3]

No again: this is not a recogisable way of referring to tables in remote
databases. Check the help files for the IN clause... ... and note that you
can only use one IN in the a single command too.


Tim F