UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Appending From Access, Any Versions    
 
   
Everettc4
post Sep 25 2019, 05:06 PM
Post#1



Posts: 212
Joined: 12-June 06
From: Oregon


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
Go to the top of the page
 
Jeff B.
post Sep 25 2019, 05:15 PM
Post#2


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


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

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Everettc4
post Sep 25 2019, 05:32 PM
Post#3



Posts: 212
Joined: 12-June 06
From: Oregon


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?
Go to the top of the page
 
MadPiet
post Oct 1 2019, 08:01 PM
Post#4



Posts: 3,367
Joined: 27-February 09



It could be a permissions issue. Maybe they don't have the rights to update that table?
Go to the top of the page
 
Jeff B.
post Oct 2 2019, 08:46 AM
Post#5


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


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.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 08:13 AM