Can I Append an Array to a table???

[emreut]

I need to copy the data in a table to another table and simultaneously enter a number under a particular field for all the records. I created a recordset. But I don't know how to enter data (the same number) to that particular column for every record and copy them to another table. Can I do it by using an array. If so, how can I copy an array to a table?
PLEASE HELP, I AM SO FRUSTRATED

Thanks,

 

[accesswatch]

There are number of ways of doing this. If this is a one off operation then I suggest:
1-Create an intermediate temporary table tblTemp with the same structure as the table you are copying from
2-Copy all your records to the temp table
3-Create a temporay update query to update the number field to the value you require
4-Append the temporary table records to your object table

If you intend to do this on a reguar basis then just make your intermediate table and query permanent. Create an additional Delete query to delete all the records from the intermediate table and then run them in this order
Delete Query (empty intermediate table)
Copy your records (You could create a query to do this if appropriate)
Run update query to update number
Run the Append query to add the reocrds to the object (ouput) table.
Once this is working O.K you could creat a module to automate the process
The advantage of this method is that it is simple, easy to understand and can be tested step by step until it works. The disadvantage is that if you are doing this on a regular basis and on a large number of records then you will need to compact your database more often to prevent 'bloat'

Hope this helps
Regards
Trevor from accesswatch.co.uk

 

[emreut]

I tried that but whenever I use action queries it pops up a confirmation meessage box to confirm the appended records, etc. I don't want the users to see that kind of stuff. Is ther anyway avoiding that?
Actually I solved the problem with by writing a module, but for future reference if I could avoid those messages I would have used your way.

Thanks for your help

 

[David Mack]

Turn off the system warnings with the following line of code preceding your append/update query:

DoCmd.SetWarnings False

Dave Mack
Saratoga, NY

 

[emreut]

That helps!

Thanks a lot

Emre

 

[accesswatch]

Just a small rider to David Mack's (100% correct) answer.
Don't forget to switch setwarnings back on after you have run your code:

DoCmd.SetWarnings True

Also add the
DoCmd.SetWarnings true
into your error handling routines

One common 'gotcha' with this type of operation is when one of the queries fails
thus creating an error condition. If your error code does not switch the setwarnings back on you might not be able to see any futher warnings. I have also had cases where the database has locked because an error condition has occured whilst setwarnings was switched off and I had not allowed for this in my error handling.
I believe (Please let me know if I am wrong) that Setwarnings is global so that once it is switched off it will remain in this state until you restart your database or you switch it back on in code
Hope this helps
Trevor from accesswatch.co.uk

 

[Travis]

Just a note on all of the good answers here. To avoid getting the warning message use:

Currentdb.Execute instead of the OpenQuery method.

Its faster and no messages.