[X]My Assistant

[Everettc4]

Hi all, I'm having some real inconsistencies trying to append data from Access to SQL. Here's the workflow. One of our account managers writes a quote request in access and submits to estimating. One of our estimators runs a query that appends the data from access to SQL. ( we do this to take it out of the AM tables so they can't change things after its been submitted, and it runs much faster) It seems sometimes the data won't append due to key violations so I have to run it from my machine. I thought it was a permissions thing at first but after having it fail on me it seems like the table is being locked somehow. Any setting I should look at? I can provide the tables and queries if that will help? Let me know what I can share that can help solve this.

Thanks

We are running the new SQL server 2016
Microsoft SQL Server Management Studio 13.0.16000.28
Microsoft Analysis Services Client Tools 13.0.1700.441
Microsoft Data Access Components (MDAC) 10.0.14393.0
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 9.11.14393.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.14393

[Jeff B.]

Does this 'append' fail for everyone attempting it, or is it only failing on one "estimator's" machine?

[Everettc4]

It has always failed on both estimators machines but once I append from mine they do show up and they can enter and change data to complete the estimate. Most recently though one of the 2 append queries failed on mine machine. I went to SQL server and refreshed to quote db reopen my access database and was able to append, and that is our work around until I figure it out. seems like a setting on the server side?

[MadPiet]

It could be a permissions issue. Maybe they don't have the rights to update that table?

[Jeff B.]

I wondered about that (table permission) also, but if the OP enters the data, apparently those folks can then 'continue'/flesh out the rest of the record.