Copy table from open database to a closed database

  • Thread starter Thread starter RW
  • Start date Start date
R

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
 
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.
 
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.
 
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?
 
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.
 
Thanks!

boblarson said:
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.
Click to expand...
 
Back
Top