Updating SQL Server table from Access VBA

[MacroAlan]

I have an Access database that was getting close to the 2gig limit so I converted the primary tables to SQL Server.

My query for pulling 195K rows into SQL table is working.

Before I begin producing reports, I need to update a number of fields on that master table.

Everything I do, I am getting a 3073 error; Operation must use an updatable query

The following query is constructed from a Recordset to give me the component parts.

UPDATE dbo_tbl_Sales_Data_Pass SET dbo_tbl_Sales_Data_Pass.[Lead Time Category] = '000-013' 
WHERE (((dbo_tbl_Sales_Data_Pass.[Num of Days Order to Request])>=0 
And (dbo_tbl_Sales_Data_Pass.[Num of Days Order to Request])<=13));
UPDATE dbo_tbl_Sales_Data_Pass 
SET dbo_tbl_Sales_Data_Pass.[Lead Time Category] = '000-013' 
WHERE (((dbo_tbl_Sales_Data_Pass.[Num of Days Order to Request])>=0 
And (dbo_tbl_Sales_Data_Pass.[Num of Days Order to Request])<=13));

Do I need to pass this thru a DAO.Connection? Is my syntax wrong?

Do I need to qualify the table with dbo.TableName?

 

[Joe4]

Why is the query code repeated twice?
Is that the way you really have it, or is that a typo?

 

[MacroAlan]

Typo.

 

[Joe4]

Similar issues that people have reported:
Runtime Error 3073 - UtterAccess Forums
Operation must use an updatable query. (Error 3073)

Something that you may want to check. Can you manually update a field by opening on the of linked tables in Access?
If not, you may have a permissions issue, and might need to check with an administrator (DBA) of your security team.

 

[xenou]

What do you mean by a sql query that is constructed from a recordset? You might need to give more of the code. Also your sql doesn't give a databasename in the sql so you'd have to be sure you are connected to the right database on the server.

 

[Joe4]

Also note that one efficient way to update data on a SQL Server from Access is to use a Pass-Through Query.
Here are a few links on the topic:
https://support.microsoft.com/en-us/help/303968/how-to-create-an-sql-pass-through-query-in-access
Process SQL on a database server by using a pass-through query - Access