stanl
03-02-2008, 10:01 AM
[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
UPDATE tasks SET tasks.payments=payments.payments from tasks,payments WHERE tasks.Service_Code=payments.Service_Code
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. :dunno Stan
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
UPDATE tasks SET tasks.payments=payments.payments from tasks,payments WHERE tasks.Service_Code=payments.Service_Code
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. :dunno Stan