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

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,
LVL 8
Rog DSQL Developer / Web Development / Business AnalysisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
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.
Rog DSQL Developer / Web Development / Business AnalysisAuthor Commented:
Basically I am refreshing a SQL relational database from MS Access.

The Constraints have be set to NO CHECK while I load so I can load the PK fields in the tables.

I run the SET IDENTITY_INSERT ON to allow this.

Once I do load the tables, I then set the SET IDENTITY_INSERT OFF; on the table.

I repeat this process for each table.

I have about 30 tables.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Just noting that  SET IDENTITY_INSERT ON / OFF is 'per session' and can only be in effect for on Table per session.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

PatHartmanCommented:
This is one of the more annoying "features" of SQL Server.  However, I don't have any problem with it.  Here's the code I use
        'when tables are linked SQL, the identity insert must be set to on
        'to allow rows with existing autonumbers to be inserted and then off at the end.
        'We do this so that the test data always retains its original autonumbers.
        Set db = CurrentDb()
        Set qd = db.CreateQueryDef("")
            qd.Connect = "ODBC;" & strConnect
            qd.ReturnsRecords = False
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblAuditParms" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_110_AppendAuditParms"
            qd.SQL = "SET IDENTITY_INSERT " & "tblAuditParms" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblListValues" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_130_AppendListValues"
            qd.SQL = "SET IDENTITY_INSERT " & "tblListValues" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblDocuments" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_140_AppendDocuments"
            qd.SQL = "SET IDENTITY_INSERT " & "tblDocuments" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblMembers" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_210_AppendMembers"
            qd.SQL = "SET IDENTITY_INSERT " & "tblMembers" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblDependents" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_220_AppendDependents"
            qd.SQL = "SET IDENTITY_INSERT " & "tblDependents" & " OFF"
            qd.Execute
            
            qd.Close
            db.Close

Open in new window

Kelvin McDanielSr. DeveloperCommented:
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.

Your best bet will be to go the other direction; use SSIS to load the data with the Access DB as your source.

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.

Good Luck!
Rog DSQL Developer / Web Development / Business AnalysisAuthor Commented:
I have more information to now share.

I have found that under Options -->  Client Settings --> Advanced Section

Default
     Checked Open Database by Using record-level locking  (I unchecked)

    ODBC Refresh Interval (SEC) was default to 1499   (I changed to 10)

    Refresh Interval (sec) 60 Default   (Changed to 10).

Now this didn't exactly resolve the issue.  

But now I can run SSMS SET IDENTITY_INSERT TableName ON;
Open access run query
close access
run SSMS SET IDENTITY_INSERT TableName OFF;

Repeat and it works every time.

HOWERVER:  I tried to wait and it doesn't seem to work without closing access and opening it back up.

I did the open close before changing the Advanced Items, but no luck.  So the advanced changes did have some affect.
PatHartmanCommented:
"close access" ?? Aren't you running this process from a code module in Access?

I posted code that works.  It is not air code.

Please post your entire procedure.
Rog DSQL Developer / Web Development / Business AnalysisAuthor Commented:
Sorry,

I didn't write any code as this was supposed to be a run one or two times and be done and in SQL.

I will utilize your code and give it a try.  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.  Maybe after sometime X is when Access realizes the Idnetiy is on and allows the insert.  Who knows.
PatHartmanCommented:
You included Access in the topics list.  Since the data is SQL Server, the only reason to include Access is because you want to run the code there.

We can't help if you don't post your code.  All we are doing is guessing.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"I will utilize your code and give it a try.  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"
It does ... I've already been down this road.  I will post how I did this later tonight.
LJGCommented:
Just a couple of notes of what works for me.  Three different things worked.

1) PatHartman's code above (Give Pat the points) plus add refresh Link of the table (See my code below that is basically Pat's code)
2) Close the database / Open it back up / Run a pass through query with SET IDENTITY_INSERT YourTblName ON / Append records
3) Wait 10 minutes or so from last append.

Please give Pat the points if this helps - I basically used his code.

Hope this helps

Public Function Export_to_SQL()

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim Tbl_df As TableDef


Dim strTableName As String: strTableName = "tbl_Menu_Items"
Dim strQry_Set_Identity As String: strQry_Set_Identity = "sql_05_qry_Set_Identity_Insert"
Dim strQry_Append_Records As String: strQry_Append_Records = "sql_65_qry_tbl_Menu_Items_11_Append"


'Set Query's SQL
   Set db = CurrentDb
   Set qdf = db.QueryDefs(strQry_Set_Identity)
   Set Tbl_df = db.TableDefs(strTableName)
   qdf.SQL = "SET IDENTITY_INSERT " & strTableName & " ON"
   qdf.Execute
   Debug.Print "Id ON"
   
    DoCmd.SetWarnings False
    DoCmd.OpenQuery strQry_Append_Records
    
    
    DoCmd.SetWarnings True
   
   Debug.Print "Qry Ran"
   
   qdf.SQL = "SET IDENTITY_INSERT " & strTableName & " OFF"
   qdf.Execute
   
   Tbl_df.RefreshLink
   
   Set db = Nothing
   Set qdf = Nothing
   Set Tbl_df = Nothing
   
   Debug.Print "Finish"
 
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rog DSQL Developer / Web Development / Business AnalysisAuthor Commented:
Thanks to all that participated as this information surely can be used by future exchange users.
PatHartmanCommented:
Only you get to assign points.  You don't delegate the task.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.