Copying a table to a new table in another Access database

[Guest]

From a form, I have a table of records that I am appending to an SQL Server
database, but I'd like to store those records in another local Access
database as a backup of what was sent before I append. In an onclick event,
I build the filename string (strTableName) and then build the SQL string:

strSQL = "SELECT tblSend.* INTO " & strTableName & " IN
'Q:\ICP\ICPBackups.mdb' FROM tmptblSend;"

But running that SQL string in a recordset open statement (the recordset,
rst1, and connecction, cnn, are properly intialized according to the ADO
examples)

rst1.Open stSQL, cnn, 1

results in error code 3001 - "arguments are of the wrong type, are out of
acceptable range, or are in conflict with one another"

Evidently my approach is wrong, but I don't see a solution in VB or Access
help, yet.
has anyone done something like this before?


Harry V

 

[theonesteve]

I don't have a solution for your issue as it stands, but I have another
method. I'd export the table as a comma-delimited text file with
DoCmd.RunCommand acTransferDatabase and something like the MDB name
concatenated with the current date for the .csv name. Then if you
needed to restore your tables from your backup you could just import
them from your .csv files. It would actually take up less disk space to
do this than to store your tables in a separate MDB file.

 

[Michel Walsh]

Hi,


Link the other remote table in the actual database, then run an Insert Into
SQL statement, since both tables are now known in this database. Finally,
remove the (link to the) table, if required.


Note that a SELECT INTO creates a table, and does not return record by
itself, so, rst.Open is not really appropriate. Should be a
Connection.Execute.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


Note that:


CurrentProject.Connection.Execute "SELECT * INTO tempT1 IN 'db2.mdb' FROM
table1"


worked fine for me. It creates the table tempT1, a copy of table1, in a
remote database, db2.mdb. It is even easier than explicitly creating the
link, as I proposed initially.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you gentlemen, this wraps up this portion of the project.

Happy Christmas.

(I wish this level of help were easily reached in the Help provided...)

 

[joel sitbon]

Friend,

joel-ange sitbon has invited you to join GreenZap and get $50 WebCash to
spend online. Sign up for a FREE GreenZap account and get $50 to spend at
hundreds of the world's premier merchants, many of whom are offering
incredible upfront discounts. Click on the link below to go to GreenZap and
signup! All thanks to joel-ange sitbon.

It's Zappening in the GreenZap Storez.
http://www.greenzap.com/joel1962

If you do not want to receive these emails in the future click the link
below:
http://www.greenzap.com/optout_invite.asp