Copy table from open database to a closed database

[RW]

I have a table that gets updated in one database. I don't want to link this
table to the other database. I'd rather use vb in the open database to copy
the table to another database. I guess I would also need to delete the table
in the other database before copying the new one there. Can this be done? If
so, how?

Thanks

 

[boblarson]

Here's the code slightly modified from my tool on my website:

Dim db As DAO.Database
Dim strSQL As String

On Error GoTo err_handler


' Opens the database
Set db = OpenDatabase(YourClosedDatabaseNameHere.mdb")

' DROPS THE TABLE
' sets the SQL String
strSQL = "DROP TABLE YourTableNameHere"
' executes the SQL
db.Execute strSQL
' transfers the new table over
DoCmd.TransferDatabase acExport, "Microsoft Access",
"YourClosedDbNameHere.mdb", acTable, "YourTableNameFrom", "YourTableNameTo"
MsgBox "The update process is complete", vbInformation, "Complete"

db.Close

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

 

[boblarson]

Oh, in my code I just posted, it has the line:

On Error GoTo err_handler

Either make sure to put in an error handler with that name, or remove that
line.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

 

[boblarson]

And, if you want, take a look at my Backend Auto Update Tool, which uses this
same code.

http://www.btabdevelopment.com/main/AccessTools/tabid/78/Default.aspx

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

 

[RW]

Thanks for the code. I do not see anything that will delete the table in the
closed database. The table will be there already. I just need to send an
updated table periodically. Won't transferring the table cause it to have a 1
after the table name if it already existed?

 

[boblarson]

THIS is the code in there that deletes the table first:

' Opens the database
Set db = OpenDatabase(YourClosedDatabaseNameHere.mdb")

' DROPS THE TABLE
' sets the SQL String
strSQL = "DROP TABLE YourTableNameHere"
' executes the SQL
db.Execute strSQL


When it says "DROPS THE TABLE" that is SQL talk for DELETES THE TABLE.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

 

[RW]

Thanks!

THIS is the code in there that deletes the table first:

' Opens the database
Set db = OpenDatabase(YourClosedDatabaseNameHere.mdb")

' DROPS THE TABLE
' sets the SQL String
strSQL = "DROP TABLE YourTableNameHere"
' executes the SQL
db.Execute strSQL


When it says "DROPS THE TABLE" that is SQL talk for DELETES THE TABLE.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.