Update query on external database

[Williams485]

Hello Access Forum,



I'm trying to write a query to update a field in an external database. I'm using the correct 'IN' parameters because I've used them on a different query but I'm not sure on the syntax for this one: I get a runtime 3144 error (syntax error in update statement).

UPDATE Contact2 IN 'g:\databases\extranet\aw test\' 'dBASE IV;' 
INNER JOIN IDLookup ON Contact2.UEXTRA04 = IDLookup.OldID 
SET Contact2.UEXTRA04 = [IDLookup].[NewID] 
WHERE [IDLookup].Campaign = 'AW TEST'

Please can you identify which bit of my query might be wrong?

Thanks in advance.

[ranman256]

instead, link the external db as a table, THEN run a normal update query.

[Williams485]

thanks for the response, Ranman.

Unfortunately, I'm not able to do that, because the query is dynamically created and loops through a number of databases. Do you know if there is a way to get this query to work?

[ranman256]

That makes no sense. If it is a table, you can link it.
Is it a table?

[Williams485]

Sorry, if I perhaps wasn't clear. I'm a relative beginner with Access and SQL. It is a table, yes. However, there are reasons that linking it is impractical (and an option I can only really consider as a last resort):

- I have around 30 tables that I must repeat the operation on, which would take too much time to set up.
- There are around 50 people using the tables (which form part of 50 databases at back end of our CRM) so I'm unable to get exclusive access in order to set up the links.
- Linking 30 external tables to my database would be too resource intensive.

Does that help clarify?

[ranman256]

The IN wont work unless its a table.
you could try ADO code, but that is a table.

[Williams485]

Okay. Bear with me. Contact2 is a table; an external table (which is where my IN clause is coming in). I've used the IN clause in the past to copy data from tables (using SELECT INTO queries) so I know the IN clause parameters (the path and db type) are correct. What I'm unsure of is the following:

- Whether it's possible to use the IN clause in an UPDATE query in the way I'm using it
- If it is possible, what is the proper syntax/structure/order the query must take.

I appreciate your patience...

[CJ_London]

this bit does not look right to me

'g:\databases\extranet\aw test\' 'dBASE IV;'

[Williams485]

I used the structure for this part based on the reference: https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

I'm confident that the references are correct because I've used it in other queries. Do you think the structure of the complete query is correct, Ajax?

[CJ_London]

I'm confident that the references are correct because I've used it in other queries.

can you post one of your other queries because the link you provided shows

…FROM Table IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];

not

IN 'g:\databases\extranet\aw test\' 'dBASE IV;'

[Williams485]

Sure. It's worth pointing out that I used the reference in the 3rd row (2nd column) of the 'Example' table: "C:\DBASE\DATA\SALES" "dBASE IV;". The following use of the IN clause worked within this SELECT-INTO query:

SELECT ondate, ontime, userid, srectype, actvcode, resultcode
INTO [AW TEST]
FROM ContHist IN 'g:\databases\extranet\aw test\' 'dBASE IV;' 
WHERE ondate BETWEEN #01/01/2015# AND #01/31/2015#

does this help?

[CJ_London]

I'm out of ideas, I don't have time to play around at the moment (not sure when). The only other thing I can suggest is that the target table must have a primary key for update queries

[Williams485]

No problem, Ajax. Thanks for your input.