Running update queries to Remote linked DB

[Malcolmneill]

Can somebody help me with this please. I have an application running against A linked DB and it runs fine when both DB and application are on the same machine. I want to run a second client and the app runs fine against the linked DB EXCEPT update queries; none of them will run. Maybe I missed a setting somewhere, but if the remote client can see the DB and all forms, read queries etc run why not the updates?

 

[isladogs]

Do the linked tables you are trying to update have primary key fields?

 

[The_Doc_Man]

Colin, it's a valid question but only if the BE isn't native Access. You don't need a PK on a BE table to update it if it is native Access format.

 

[CJ_London]

I find this confusing

but if the remote client can see the DB and all forms, read queries etc run why not the updates?

Think the OP needs to clarify the setup - the BE should only have tables

 

[isladogs]

Hi Doc
Yes I'm aware of that but the OP didn't state what type of linked database was being used.

 

[Malcolmneill]

Hi,

I am not sure whether I am answering 1 question or 2, but all the tables have primary keys. As to "confused", I have the DB on 1 machine with a local copy of the application part on a separate PC. When I execute the app it all works except the Update queries, i.e. forms load read queries are fine but when it hits an update it stops with an error "Update query". The DB is set to "Shared", but still won't run updates.

 

[The_Doc_Man]

Can you take a screen shot of the error and post that? It MIGHT be instructive, might not. But at the moment I'm drawing a blank.

 

[isladogs]

Also can you post the query SQL & confirm whether the update queries work using a local copy of the table(s) concerned.

 

[Malcolmneill]

As I think I said earlier, forms load, queries all run OK, but Update Queries bring it to a halt. The error message is attached, but seems to say that the query type is incorrect i.e. Not an updateable query. However everything runs perfectly if the DB is local i.e. same machine with linked DB. It's across the network when this happens
Malcolm

 

[isladogs]

Update Queries bring it to a halt. The error message is attached, but seems to say that the query type is incorrect i.e. Not an updateable query.

OK thanks but I still need the query SQL if this is to progress further.
Also you haven't stated whether the database containing the linked tables is Access or SQL Server or Excel or ...something else

The error message about using an updateable query may be simple to solve or it may require a redesign

The first thing to try is to specify unique records in the query property sheet.
In SQL, that is equivalent to UPDATE DISTINCTROW .... instead of UPDATE ....

 

[The_Doc_Man]

OK, here is a quick read for you on the subject of queries that cannot be updated.

http://allenbrowne.com/ser-61.html

This is a simple bullet list. Read through it and see if perhaps one of these reasons apply. The first five or six reasons Allen lists are the most common but certainly the other reasons could also easily apply.

 

[Malcolmneill]

Thanks Doc Man, that link has given me some ideas as to where to look.

 

[deletedT]

OK, here is a quick read for you on the subject of queries that cannot be updated.

http://allenbrowne.com/ser-61.html

If any of those reasons apply the query doesn't run when both BE and FE are on the same machine too.

If a FE can not update a BE, the first thing I would check is the client right. If the client has no read/write permission for the shared directory (where the BE is saved), the update query stops.

 

[The_Doc_Man]

True, Tera. Also is in Allen's list, 4th from bottom.

 

[deletedT]

True, Tera. Also is in Allen's list, 4th from bottom.

Sorry. I missed that.