User Error: How Do I Append Tables in Different Databases

[rutkus]

I had two databases on our server, one for the users and a second for me to work on updating the user database. They were named differently and I (so did everyone else) thought it was apparent which was for which. BUT, today I find out someones been entering their invoices into my database. SO I need to be able to get about twenty records and move them from one database to another.

What im thinking is copy the table from my database to the user and run an append query, my problem is that i have alot of "sample" entries and things of that nature. I would much rather manually update the user database, but i dont think i can copy and paste records, can i??

any help would be really appreciated. I dont know much about append queries.

Thanks,
Omar

 

[organizer]

If the append query can be set up so it only selects the records you want to put into the regular database then your method will work (move the table into your original database, then create an append query - then delete the table you moved in). If they are invoices, I would think you would be able to do this using invoice numbers or something.

To find out for sure that you will be copying the right records, you can move back and forth between "design" and "datasheet" view in the query and see what you are getting. Don't hit the "!" to run the query until you are sure it is pulling up only the records you want. Until you hit that, nothing has actually moved.

-cynthia

 

[organizer]

I should also mention, if you are not familiar with append queries, if you don't complete your project in one sitting, make sure when you go to work on it again you only open it in "Design" view, or it will run (it will ask you before it actually does so, however, so just say "no" if it does and then open in "design" view). Once it's open, you can continue to move between "design" and "datasheet" with no problem as you fine tune it.

-cynthia

 

[Guest_imported]

You could try this if you want to append from one DB to another DB

Create an append query in the database that contains the records that you want to move to the other database.
Go into SQL mode and create an SQL something like this.

INSERT INTO [Invoice_tbl] IN 'C:\Path_To_Your_DB\Your_DB.mdb'
SELECT Field1,field2,field3
FROM Your_Table
Where (Enter your selectin criteria here);

Cheers
Andrew

 

[rutkus]

Thanks for all your suggestions, what I did was move my table into the user database and setup an append query that was based on any invoice numbers that werent in the user database. I spent most of yesterday deleting those test invoices that i had created. Everything seems like its in order, EXCEPT that i get a key violation when i run the query.

I use vendorID as the key to the tables and these new records have IDs that are already in use by existing invoices.

Heres my SQL statement:

INSERT INTO Invoices ( Vendor, Invoice, Requisition, InvoiceDate, ReceivedDate, ApprovedDate, InvoiceAmount, ApprovedAmount, Status, Reason, [General Comments], [Processing Comments] )
SELECT Invoices2.Vendor, Invoices2.Invoice, Invoices2.Requisition, Invoices2.InvoiceDate, Invoices2.ReceivedDate, Invoices2.ApprovedDate, Invoices2.InvoiceAmount, Invoices2.ApprovedAmount, Invoices2.Status, Invoices2.Reason, Invoices2.[General Comments], Invoices2.[Processing Comments]
FROM Invoices INNER JOIN Invoices2 ON Invoices.InvoiceID = Invoices2.InvoiceID
WHERE (((Invoices2.Invoice)<>[Invoices]![Invoice])
ORDER BY Invoices2.Vendor, Invoices2.Invoice;

Im guessing the problem is in the INNER JOIN line (since thats the only one that deals with invoiceID. Im not to sure what thats saying and I dont wanna do anything thatll screw up the database. If anyone has encountered this or otherwise would know how to deal with it, please help.

Thank You
Omar