Update or append to another database

[kdoran]

I am trying to use a query to update or append (no luck with either) from one table to a table in another database.

I have a form for data entry which puts info in a few tables when equipment in the field is worked on.

On another form I have a text box where you type in the date which will run a query on the form from the LastCalibrated column in tblFieldTAg, which will pull up the tblFieldTag data in a subform on the screen from that date to present.

What I would like to do have a button run either an update or append query to dump to another database.

Basically both database have a table like this.

tblFieldTag

FieldTagID
Manufacturer
ModelNumber
LastCalibrated
Input
Output


I have been trying for a couple of hours with no luck before posting. In the update query I used the criteria for the FieldTagID from the subform and tried to have it update all the columns for the rows that displayed, but recieved an error like null key value cannot overwrite?

I hope this is enough info.

thanks in advance,

Kelly

 

[cghoga]

Kelly,

Try creating an ODBC connection to the table in the other database, so that you have a direct link to the table, then run your queries within your current app without having to open/reference another mdb.

To do so, right click in Table Design View
Choose Link Tables...
Make sure the Files of Type dropdown is set to .mdb
Navigate to the location where the other mdb is stored Click Link
you may have to click OK.

you should now be able to see the "linked" table.

if the table is a duplicate, you may want to centralize this data, and create a GUI for the users(frontend, backend). this way you don't have to update multiple locations and the data is always current for your users.

just a thought.

hope this helps you.




just a thought.

 

[kdoran]

The reason for doing this is the user is out in the field with his laptop without a network connection to the backend DB. If he tries to open the DB it cannot find the backend DB. I was thinking linking the tables to a backend on the laptop and he could run this query to update the main backend DB.

Will this work with what you are saying?

Kelly

 

[kdoran]

Here is error message I get

Cant append all the reocrd in the append query.

Db set 0 fields to Null due to a type conversion failure, and it didn't add 1 records to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations.

help,

Kelly

 

[kdoran]

Here is error message I get

Cant append all the reocrd in the append query.

Db set 0 fields to Null due to a type conversion failure, and it didn't add 3 records to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations.

help,

Kelly