Solved: Access-SQL Server Update Query

[stanl]

[or lessons learned].

In the process of migrating 2-3 million rows from Access to SQL Server in multiple related tables. One issue is that several of the lookup tables are dynamically updated, and updates must be applied to the related tables.

First: don't be fooled by the seemingly easy method of linking your SQL tables to Access then using the QBE UI to extract SQL. In my situation I had a lookup table [Payments] and a related Table [Tasks] related by a key [Service_Code]. The payment field in [Payments] dynamically changes and requires updating previous payments in [Tasks]... akin to booking anticpated revenue then actualizing it.

The Access GUI will write SQL code with INNER or LEFT joins [depending upon your preferences] but the query cannot be executed against the linked tables, resulting in an "Unupdateable Query" error.

For SQL server the syntax would be something like

[vba]
UPDATE tasks SET tasks.payments=payments.payments from tasks,payments WHERE tasks.Service_Code=payments.Service_Code
[/vba]

This, of course I set up via an ADODB.Command Object so I could record the RecordsAffected....

All set to make this more an FYI, instructional post.... but when I run the query I receive a "Provider Mismatch" error.

As usual, I had a Plan B.... a simple 2 recordset iteration which solves the problem.

But I would be curious to hear from others with experience performing similar UPDATES in SQL Server. Stan

[XLGibbs]

The reason for the un-updateable query is not the linked tables to a SQL server issue, but rather..when establishing the link, the appropriate primary keys in the source tables were not identified.

For an update query to work in Access, primary keys on linked tables have to be identified on the linked tables. More specifically, this issue arises when a local Access table is being updated FROM a linked SQL server table. If trying to update a local table FROM the SQL linked table, the SQL table must have a primary key identified. The inner join is not enough for Access (I guess).

Updating the actual linked table ON the SQL server is another issue, and provided the user had the proper pemissions, this could be done as well, but would likewise require identification of the primary key columns.

The Access GUI cannot update the linked table directly unless the connection properties allow it (generally bad practice)..however, update statements can be sent to the server via VBA and ADODB connections easily enough.

You don't do iterate through records when connecting via ADODB to do an update on SQL Server...

[stanl]

You don't do iterate through records when connecting via ADODB to do an update on SQL Server...

You do if you want to get paid. And actually ADO script code works faster and has more flexibility than Access pass-through queries. I might also refer you to the UPDATE section here.


http://www.cryer.co.uk/brian/sql/sql_crib_sheet.htm

where syntax gotchas are real.

[XLGibbs]

don't disagree with you, but SQL is set based language by design.

Row by row processing is not ALWAYS necessary...but hey, what do I know (not much)

[stanl]

don't disagree with you, but SQL is set based language by design.

Row by row processing is not ALWAYS necessary...but hey, what do I know (not much)

I'm not disagreeable IMHO. Like I said, I inherited the design. Normally, I would think that if you wanted to know the name of the Tech that did the work you would simply relate a Tech ID between a workorder and employee tables. The introduction of a third table in the relationship has (in the past) caused issues with duplicates, viz. a Tech does not get paid for a job, or the wrong Tech gets paid. The 'row by row' processing is merely the result of a discreet SELECT within a tight date/time range, performed as a disconnected recordset without unnecessary overhead on the Server.

[XLGibbs]

It isn't uncommon to have to use a "bridge" table between two others when there may exist 1:many relationships across tables. Derived tables are frequently used to limit a recordset via the query in that fashion.

I know all too much about inheriting bad mojo!

[stanl]

It isn't uncommon to have to use a "bridge" table between two others when there may exist 1:many relationships across tables.

Not really a bridge in my case. The 'middle' table is used to differentiate between the 'state' and the status of a workorder.

Status: ASSIGNED ACCEPTED CANCELLED CLOSED

State: en-route, on-hold... etc

The Tech Name is associated with the Status which is a discreet record. However, to capture the Name and associate it with the workorder, one must associate the workorder with its state... certain 'business' rules seem to allow multiple states...

At which point I had to ask... "How can you be in 2 places at once when you're NOT anywhere at all??????"

[XLGibbs]

That is one of those where they stare at your blankly thinking it is your programming skills that are the problem.

It isn't the data, or anything else. It is the business rules in place that make that kind of thing challenging.

We have similar issues with workstates and locations and status codes in our application system. It is just plain nasty.

That query actually isn't so bad. I can point you to some real ugliness if you want to see what REALLY nasty looks like.

Have fun!

[stanl]

Yeah, I'll mark it solved, although we both no different