Update Query Question

[AliM]

Hi

Wonder if anyone can help me? Is there any way of using an update query to update a table that's not within the database the query runs from? We're already using an append query to send the data across, but it is amended and updated regularly wich of course the append wont reflect. I'm not allowed to link the tables either!


Any suggestions?

 

[gblack]

hmm?

There's not really enough info here. How big a table are we talking about? How many people use it at a time?


If you can't link to the database how do you intend to make changes from another database?

Why can't you Import the table in question into your Database...use your update query to make the changes, then export it back to the other database?

Or send your changes to the Database where the table resides via a smaller table and overwrite the table there.

It sounds odd to me that you can't link, but you can update and append... what kind of rights are we talking about here?

Later,
Gary

 

[AliM]

Yep, what I'm trying to do sounds mad I know! I work for the NHS and the reason I cant link the tables is because of audit trails and patient confidentiality clauses. Makes things very difficult as I'm also not allowed to have any of the other departments database objects in my database. Hence I'm stuck, fed up and at the end of my tether!

It's only a small system with about 12 users in total. I do have full admin rights to the other database for what that's worth. The data that is needed to be put into both tables is generated through one form - that can be within either database - any way it can be split from there instead of using the query?

Thanks
Alison

 

[gblack]

What kind of rights do you have to the other database?

 

[AliM]

I have full rights to the other database although can't really change anything in there!

 

[gblack]

So what do you want? I am unclear...

It sounds like you want to update a table you aren't allowed to touch, but you have rights to if you were.

If that is the case from my perspective you either can or can't do this.

If you can, and you simply want to get around the fact you can't have a consistant like to the "other" database...well then there are a number of ways to update the data.

Since you have rights to both, I would suggest working in the "other" database to do the updates...then hop out quickly. Always save the table you are working on so if you should screw up...you have a quick backup...or save the .mdb file for that matter (but your probably not allowed).

How does the database that you can play with relate to the "other" database in terms of this form?

Do they both link to a table with the same name?

Does "your" database get updated by the "other" Database?

Whats the relation?

 

[AliM]

Well, to start at the beginning, I collect certain information that another department wants too. We already had a database in place that we use for a number of reasons and the other dept have come along and forced their system onto us. The idea, and the reason why I have full rights to their system, was that they wanted us to input the information directly into their system. For one reason or another it's been decided that the information should be split between the two systems in order to satisfy both departments.

The only way I've managed to achieve this without double data entry (another condition imposed on me!) was to use the append query method and just to append the data from our table into theirs. This wont work the other way around as there are a couple of different fields in our table. The form that I've mentioned supplies the main body of the information that we require and all of the info for the other dept involved. They're fine with the append query being used but the problem is the records being appended are updated regularly and on occasion amended.

 

[gblack]

AH IC!

OK so here's how you do it. create a make table query that has all the fields they need. Lets say the tables is called "Updates". Call the make table query MakeUpdates (or whatever).

Run the make table query. I am assuming Updates has a primary key (I hope)

Go to the "other" database. Import "Updates" or link to "Updates" if this is an ongoing thing (you should be allowed to link the other way, as linked tables only work one way as far as I know.

Now create a query. In desgn mode choose the "Updates" table and whatever their table is called...lets call it "Pain".

(side note: remember to Save "Pain" and call it "pain+the date")

Link "Pain" and "updates" by the primary key. Go to the query buttin on the menu bar and choose update query.

goto the "Pain" table and choose all of the fields.

Now...in the criteria section of each field you'll want to type in the the "Updates" table name and the corresponding field name.

So for field number 1...lets call it F1...you'll want to type in:
[Updates].[F1]...in the update to section.
In the update to section for field 2 you'll type [Updates].[F2]
and so on, till you have all the fields covered. Be sure to use the brackets.

Then you'll hit the exlamation point to run it just like an append query. You probably will want to check it first just to

didn't know if you need this much detail...but I thought better to have too much than too little.

 

[AliM]

Thanks for that Gary. I shall attempt it tomorrow and will report back the results.

Like the pain bit btw - very apt!