Error #8107 when running an EXPORT to an SQL server

Please look at the code below.
You will see that I am deleting a table on an SQL sever and then uploading a 'fresh' copy of said table.
It runs fine up until the second DoCmd.RunSavedImportExport command.

What I am I missing between the first table being deleted and the second?

Runtime Error: 3146
[Microsoft][ODBC SQL Server Driver][SQL Server] IDENTITY_INSERT is already ON for table 'DA_DB.dbo.cause'. Cannot perform SET operation for table 'docs'. (#8701)
Public Function ExportTablesToSQL() As Boolean
Const DA_SQL1 = "ODBC;Description=DA SQL Tables;DRIVER=SQL Server;SERVER=DA1\dev1;APP=2007 Microsoft Office system;DATABASE=DA_DB;"
Const DA_SQL11 = "UID=user;PWD=passme"
Const DA_SQL2 = ";Network=DBMSSOCN;"
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
 
conn.ConnectionString = DA_SQL1 & DA_SQL11 & DA_SQL2
'Debug.Print conn.ConnectionString
conn.Open
 
Dim SQL As String, sTable As String
 
sTable = "codes"
SQL = "DROP TABLE " & sTable & ";": conn.Execute SQL
DoCmd.RunSavedImportExport "Export-DA1\dev1_" & sTable
Debug.Print sTable & " uploaded to SQL server."
 
sTable = "docs"
SQL = "DROP TABLE " & sTable & ";": conn.Execute SQL
DoCmd.RunSavedImportExport "Export-DA1\dev1_" & sTable ' <---Error #8107 here!
Debug.Print sTable & " uploaded to SQL server."
 
End Function

Open in new window

AviationAceSole-ProprietorAsked:
Who is Participating?
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 P.Commented:
Haven't done Access 2007 yet -- but the SQL side I know.

The issue is that the table has an Identity (autonumber) column on it. To import into that table you have a few options  --

1.  Change the table to take off the Identity field in the SQL Server side and just
       have an autonumber on the Access side.

2.  Change the import to something like making sure you don't pick up the Identity
   -------------------------------------
       Insert into DA_DB.dbo.docs (field1, field2....)
        SELECT field1, field2...
   -------------------------------------

3. Whichever SP or app that is running before this modify it to have do
     SET IDENTITY_INSERT DA_DB.dbo.cause ON
          do stuff
     SET IDENTITY_INSERT DA_DB.dbo.cause OFF

Then modify the RunSavedImportExport "Export-DA1\dev1_" & sTable ' procedure to have a

     SET IDENTITY_INSERT DA_DB.dbo.docs ON
          Insert into DA_DB.dbo.docs (IdentFLD, field1, field2....)
           SELECT IdentFLD, field1, field2...

     SET IDENTITY_INSERT DA_DB.dbo.docs OFF
0
AviationAceSole-ProprietorAuthor Commented:
I implemented the SET IDENTITY_INSERT  and that did not help at all.  Do you have another suggestion?  (I hope)
Public Function ExportTablesToSQL() As Boolean
Const DA_SQL1 = "ODBC;Description=DA SQL Tables;DRIVER=SQL Server;SERVER=DA1\dev1;APP=2007 Microsoft Office system;DATABASE=DA_DB;"
Const DA_SQL11 = "UID=user;PWD=passme"
Const DA_SQL2 = ";Network=DBMSSOCN;"
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
 
conn.ConnectionString = DA_SQL1 & DA_SQL11 & DA_SQL2
'Debug.Print conn.ConnectionString
conn.Open
 
Dim SQL As String, sTable As String
 
sTable = "codes"
SQL = "SET IDENTITY_INSERT DA_DB.dbo." & sTable & " ON": conn.Execute SQL
SQL = "DROP TABLE " & sTable & ";": conn.Execute SQL
DoCmd.RunSavedImportExport "Export-DA1\dev1_" & sTable
SQL = "SET IDENTITY_INSERT DA_DB.dbo." & sTable & " OFF": conn.Execute SQL
Debug.Print sTable & " uploaded to SQL server."
 
sTable = "docs"
SQL = "SET IDENTITY_INSERT DA_DB.dbo." & sTable & " ON": conn.Execute SQL
SQL = "DROP TABLE " & sTable & ";": conn.Execute SQL
DoCmd.RunSavedImportExport "Export-DA1\dev1_" & sTable ' <---Error #8107 here!
SQL = "SET IDENTITY_INSERT DA_DB.dbo." & sTable & " OFF": conn.Execute SQL
Debug.Print sTable & " uploaded to SQL server."
 
End Function

Open in new window

0
Jim P.Commented:
I'm working on an answer: http://www.experts-exchange.com/Q_24430973.html ;-)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AviationAceSole-ProprietorAuthor Commented:

Maybe a brute force method is in order here.  If I were to disconnect from the server each time I try to delete a table maybe that would do it.  What do you think?
0
AviationAceSole-ProprietorAuthor Commented:
What's IDENTITY_INSERT good for anyway?
0
Jim P.Commented:
The identity insert allows you to fill in the data in a columns that are Identity (AutoNumber) columns.

For example you have the customer number auto generate as you add them to the database. Someone deletes customer 15 by mistake, so you need to re-add them as 15 not a new number you would do something like below. If you don't turn on the IDENTITY_INSERT SQL will choke and say you can't insert values to the CustNum identity column.
SET IDENTITY_INSERT tblCustomers ON;
GO
INSERT INTO tblCustomers (CustNum, CustName, blah, blah)
VALUES (15, 'ABC Corp.', 'blahh', 'blah');
GO
SET IDENTITY_INSERT tblCustomers OFF;
GO

Open in new window

0

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
AviationAceSole-ProprietorAuthor Commented:
I ended up using the Microsoft SQL Server Migration Assistant for Access.  It took care of a lot of details I didn't expect.
0
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
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
Microsoft Access

From novice to tech pro — start learning today.