<div>
Just for kicks and giggles, explain in detail why you are attempting a SET IDENTITY_INSERT ON statement, as this is not always best practice.
</div>


SQL Developer / Web Development / Business Analysis

Rog D

<div>
Basically I am refreshing a SQL relational database from MS Access.<br />
<br />
The Constraints have be set to NO CHECK while I load so I can load the PK fields in the tables.<br />
<br />
I run the SET IDENTITY_INSERT ON to allow this.<br />
<br />
Once I do load the tables, I then set the SET IDENTITY_INSERT OFF; on the table.<br />
<br />
I repeat this process for each table.<br />
<br />
I have about 30 tables.
</div>


<div>
Just noting that &nbsp;SET IDENTITY_INSERT ON / OFF is 'per session' and can only be in effect for on Table per session.<br />
<br />
<a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017" rel="ugc">https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017</a>
</div>


<div>
The simplest answer is that it was intentionally left to work that way. Microsoft wants us to license SQL Server... so they didn't put much effort into optimizing getting data out of Access and into SQL Server from the MS Access perspective/direction.<br />
<br />
Your best bet will be to go the other direction; use SSIS to load the data with the Access DB as your source.<br />
<br />
The alternative would be to write a simple script to run a bulk insert on each table. Please note that this option will give you the most flexibility.<br />
<br />
Good Luck!
</div>


<div>
I have more information to now share.<br />
<br />
I have found that under Options --&gt; &nbsp;Client Settings --&gt; Advanced Section<br />
<br />
Default <br />
&nbsp; &nbsp; &nbsp;Checked Open Database by Using record-level locking &nbsp;(I unchecked)<br />
<br />
&nbsp; &nbsp; ODBC Refresh Interval (SEC) was default to 1499 &nbsp; (I changed to 10)<br />
<br />
&nbsp; &nbsp; Refresh Interval (sec) 60 Default &nbsp; (Changed to 10).<br />
<br />
Now this didn't exactly resolve the issue. &nbsp;<br />
<br />
But now I can run SSMS SET IDENTITY_INSERT TableName ON;<br />
Open access run query <br />
close access<br />
run SSMS SET IDENTITY_INSERT TableName OFF;<br />
<br />
Repeat and it works every time.<br />
<br />
HOWERVER: &nbsp;I tried to wait and it doesn't seem to work without closing access and opening it back up.<br />
<br />
I did the open close before changing the Advanced Items, but no luck. &nbsp;So the advanced changes did have some affect.
</div>


<div>
&quot;close access&quot; ?? Aren't you running this process from a code module in Access?<br />
<br />
I posted code that works. &nbsp;It is not air code.<br />
<br />
Please post your entire procedure.
</div>


<div>
Sorry,<br />
<br />
I didn't write any code as this was supposed to be a run one or two times and be done and in SQL.<br />
<br />
I will utilize your code and give it a try. &nbsp;I do however think the session thing vs MS Access having it's own session may have something to do with this since they don't share the same session. &nbsp;Maybe after sometime X is when Access realizes the Idnetiy is on and allows the insert. &nbsp;Who knows.
</div>


<div>
You included Access in the topics list. &nbsp;Since the data is SQL Server, the only reason to include Access is because you want to run the code there.<br />
<br />
We can't help if you don't post your code. &nbsp;All we are doing is guessing.
</div>


<div>
<i>&quot;I will utilize your code and give it a try. &nbsp;I do however think the session thing vs MS Access having it's own session may have something to do with this since they don't share the same session&quot;</i><br />
It does ... I've already been down this road. &nbsp;I will post how I did this later tonight.
</div>


<div>
Thanks to all that participated as this information surely can be used by future exchange users.
</div>


<div>
Only you get to assign points. &nbsp;You don't delegate the task.
</div>


<div>
<div class="content wysiwyg-content">
MS Access append query to SQL Server table. &nbsp;SQL Table is blank but does have identity column. <br />
<br />
When I run the SET IDENTITY_INSERT [Table] ON;<br />
<br />
I try to run the Append Query in MS Access, but get a Key Violation. &nbsp;<br />
<br />
If I wait X minutes then run the same Append Query again, it adds the records.<br />
<br />
I am wanting to speed this process up as I have many tables to work on.<br />
<br />
What is the best way to do this?<br />
<br />
SQL Server 2012 R2<br />
<br />
Thanks,
</div>
</div>


MS Access append query to SQL Server table.  SQL Table is blank but does have identity column. 

When I run the SET IDENTITY_INSERT [Table] ON;

I try to run the Append Query in MS Access, but get a Key Violation.  

If I wait X minutes then run the same Append Query again, it adds the records.

I am wanting to speed this process up as I have many tables to work on.

What is the best way to do this?

SQL Server 2012 R2

Thanks,

Appending SQL Tables from MS Access SET IDENTITY_INSERT takes a long time why?

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.