Solved

Alter Identity SQL with VBA

Posted on 2010-09-24
22
864 Views
Last Modified: 2012-05-10
I am trying to alter a column field in a SQL table, originally setting Identity to No and then to Yes (as you would do in Enterprise Manager).  What is the query string?
0
Comment
Question by:simonwait
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 3
22 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 33756402
Hi Simon,

Why?

Are you wanting to insert values in an identity column? If so the better way to do so in SQL is this:

set identity_insert dbo.mytable on

insert dbo.mytable( col1, col2 ) values( val1, val2 ) -- must have a column list for this to work

set identity_insert dbo.mytable off

HTH
  David
0
 
LVL 1

Author Comment

by:simonwait
ID: 33756650
Im trying to update the tables with data from excel sheets.  (Sheet per table).  This data was originally taken from the Database and then modified.  The 1st column is an ID column which usually auto-increments but I want to force the numbering from excel otherwise it deletes the rows and then carries on from the last number.  These ID's are used for other things so cant change.  Below is the entire code
Sub SendData()
Dim DBConn As New ADODB.Connection
Dim RSConn As New Recordset
Dim SJConn As New Recordset
Dim thiscol As Integer


For sht = 1 To Worksheets.Count
Sheets(sht).Activate
If ActiveSheet.Name = "SQL Structure" Then
GoTo nextsheet
Else
End If
sheetname = ActiveSheet.Name
lastrow = Sheets(sheetname).UsedRange.Rows.Count
lastcol = Sheets(sheetname).UsedRange.Columns.Count
DBConn.Open ("Provider=SQLOLEDB.1;User ID=UID;Password=PSW;Initial Catalog=Newmans;Data Source=SERVER;")
With SJConn
strQuery = "DELETE FROM " & sheetname
.Open strQuery, DBConn, adOpenStatic, 2
End With


With RSConn
strQuery = "SELECT * FROM " & sheetname

thisline = 2
.Open strQuery, DBConn, adOpenStatic, 2
thisrow = 1

For thisrow = 2 To lastrow
.AddNew
thiscol = 1
For thiscol = 1 To lastcol
thiscollet = ColumnLetter(thiscol)
thisval = Range(thiscollet & "1").Value
On Error Resume Next
.Fields(thisval).Value = Range(thiscollet & thisrow).Value
On Error GoTo 0
Next thiscol
Next thisrow
End With
DBConn.Close
nextsheet:
Next sht
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:simonwait
ID: 33761505
So I have managed this with SET IDENTITY_INSERT ON but then obviously it can only set one at a time.  When I go to set it to OFF using the same code just saying OFF I get "The connection cannot be used to perfrom this operation.  It is either closed or out of context."


With ONConn
strQuery = "SET IDENTITY_INSERT " & sheetname & " ON"
.Open strQuery, DBConn, adOpenStatic, adLockOptimistic
End With

Open in new window

0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 1

Author Comment

by:simonwait
ID: 33761892
I have realised that this was because of where I placed the with block...after I closed the connection!  I now have the code below.  This steps through and the lines about SET IDENTITY_INSERT OFF go through but then when it goes to turn ON for the next table I get "IDENTITY_INSER is already ON for table 'Users' Cannot perform SET operation for table 'theatres'.

NB: 'users' is the first sheet/table with 'theatres' being the 2nd

Thanks in advance
Sub SendData()
Dim DBConn As New ADODB.Connection
Dim RSConn As New Recordset
Dim SJConn As New Recordset
Dim ONConn As New Recordset
Dim OFFConn As New Recordset
Dim thiscol As Integer

For sht = 1 To Worksheets.Count
Sheets(sht).Activate
If ActiveSheet.Name = "SQL Structure" Then
GoTo nextsheet
Else
End If

sheetname = ActiveSheet.Name
lastrow = Sheets(sheetname).UsedRange.Rows.Count
lastcol = Sheets(sheetname).UsedRange.Columns.Count
DBConn.Open ("Provider=SQLOLEDB.1;User ID=newmans;Password=newmans;Initial Catalog=Newmans;Data Source=ENTALIVE2008\ENTABOXOFFICE;")

With ONConn
strQuery = "SET IDENTITY_INSERT " & sheetname & " ON"
.Open strQuery, DBConn, adOpenStatic, 2
End With

With SJConn
strQuery = "DELETE FROM " & sheetname
.Open strQuery, DBConn, adOpenStatic, 2
End With

With RSConn
strQuery = "SELECT * FROM " & sheetname

thisline = 2
.Open strQuery, DBConn, adOpenStatic, 2
thisrow = 1

For thisrow = 2 To lastrow
.AddNew
thiscol = 1
For thiscol = 1 To lastcol
thiscollet = ColumnLetter(thiscol)
thisval = Range(thiscollet & "1").Value
On Error Resume Next
.Fields(thisval).Value = Range(thiscollet & thisrow).Value
On Error GoTo 0
Next thiscol
Next thisrow

With OFFConn
strQuery = "SET IDENTITY_INSERT " & sheetname & " OFF"
.Open strQuery, DBConn, adOpenStatic, 2
End With
End With
DBConn.Close
nextsheet:
Next sht
End Sub

Open in new window

0
 
LVL 35

Expert Comment

by:David Todd
ID: 33761950
Hi

Identity insert is on for a specific table, one at a time - which is the error you got.

I once had the issue where Identity_Insert was already on, and had to figure out which table it was on for to turn it off.

Regards
  David
0
 
LVL 1

Author Comment

by:simonwait
ID: 33762076
Thanks David but when I look at each table, properties in enterprise manager they all say identity yes? Is this not the same thing? Or each table has an I'd column which auto generates a number by default.  Cheers
0
 
LVL 35

Expert Comment

by:David Todd
ID: 33765319
Hi,

Many tables can have an identity column. That is

create table dbo.myNewTable(
  myNewTableID int identity( 1, 1 ) not null
  , SomeTextOrOther varchar( 200 ) not null
  , other columns
)

but only ONE table at a time can have set identity_insert sometablename on

Question: Do you really care about the exact value in the identity column? If not then ignore it.
That is
insert dbo.myNewTable( SomeTextOrOther ) values( 'Some text goes here' );

What we've done is specify which column(s) we supply values for, and let the identity column do its thing, which is starting from 1 autoincrement by one.

So back to the question you just posted, what you are seeing in Enterprise Manager is not the same thing.

Question: Which version of SQL are you using? You refer to Enterprise Manager which is a SQL 2000 product, yet have posted in SQL 2008. SQL 2005 & SQL 2008 use SQL Server Management Studio (SSMS)

HTH
  David
0
 
LVL 35

Expert Comment

by:David Todd
ID: 33765388
Hi,

Run this:
execute sp_MSforeachtable 'if OBJECTPROPERTY ( object_id( ''?'' ) , ''TableHasIdentity'' ) = 1 set identity_insert ? off'
;

What it does:
For each table in your database, it checks to see if there is an Identity column, and if there is sets the identity_insert off.

I would have hoped that if you were getting an error that indentity_insert was already on  the message would have identified the table concerned.

HTH
  David
0
 
LVL 1

Author Comment

by:simonwait
ID: 33765420
Sorry David I didnt realise I had put this in the wrong SQL version.  I am indeed using SQL 2000.  I do need to specify the values so cant ignore them.  

Essentially this project is downloading the values from the SQL database, I am then making changes and reuploading.  For instance I may delete a user (userid 4) from the users tables.  If I allow the table to do its thing then userid will be allocated to another user which I cant allow.

As for your last bit of code, I'm confused where this should go in my vba?  Is it to replace the line 21-24 bit?

Thanks so much for your help so far.  This is my last stumbling block before getting this working perfectly!
0
 
LVL 35

Expert Comment

by:David Todd
ID: 33765590
Hi,

The code above runs from Query Analyser - it appears that a table has indentity_insert on, and you need that turned off.

It is unlikely that with an identity column that the id's will be reused. If your tables have referential integrity, you won't be able to delete userID 4 untill all the rows that reference that have also been deleted.

Instead of a delete and re-insert, how about an update.

Let me reread your code ...

Regards
  David
0
 
LVL 35

Expert Comment

by:David Todd
ID: 33765606
Hi,

My suggestion is to use one recordset, rather than the four you have got at present. I think that what is happening is that recordsetA has identity_insert on, and then you are turning it off wiht recordsetB - which is perfectly fine, but doesn't alter the fact that recordsetA has identity_insert still on.

HTH
  David
0
 
LVL 1

Author Comment

by:simonwait
ID: 33765792
How would I do that?  I have tried several combinations for the sql.open string but they have various errors.  I was assuming that I I needed to split each task up.  Sorry but i'm floudering here!
0
 
LVL 35

Expert Comment

by:David Todd
ID: 33767408
Hi,

Try this code.

Note the indenting to show code levels and nesting and so on.

Let me know how it goes.

Else please attach your spreadsheet - less the connection details ...

Regards
  David
option explicit
Sub SendData()
	Dim DBConn As New ADODB.Connection
	Dim RS As New Recordset
	Dim thiscol As Integer
	Dim thisrow as Integer
	Dim LastRow as Integer
	Dim LastCol as Integer
	Dim SheetName as String
	Dim strQuery as String
	Dim sht as Integer
	
	For sht = 1 To Worksheets.Count
		Sheets(sht).Activate
		If ActiveSheet.Name != "SQL Structure" Then
	
			sheetname = ActiveSheet.Name
			lastrow = Sheets(sheetname).UsedRange.Rows.Count
			lastcol = Sheets(sheetname).UsedRange.Columns.Count
			DBConn.Open ("Provider=SQLOLEDB.1;User ID=;Password=;Initial Catalog=;Data Source=;")
			
			With RS
				strQuery = "DELETE FROM " & sheetname
				.Open strQuery, DBConn, adOpenStatic, 2
	
				strQuery = "SET IDENTITY_INSERT " & sheetname & " ON"
				.Open strQuery, DBConn, adOpenStatic, 2
	
				strQuery = "SELECT * FROM " & sheetname
			
				.Open strQuery, DBConn, adOpenStatic, 2
			
				For thisrow = 2 To lastrow
					.AddNew
					For thiscol = 1 To lastcol
						thiscollet = ColumnLetter(thiscol)
						thisval = Range(thiscollet & "1").Value
						On Error Resume Next
						.Fields(thisval).Value = Range(thiscollet & thisrow).Value
						On Error GoTo 0
					Next thiscol
				Next thisrow
	
				strQuery = "SET IDENTITY_INSERT " & sheetname & " OFF"
				.Open strQuery, DBConn, adOpenStatic, 2
			End With
			DBConn.Close
		End if
	Next sht
	
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:simonwait
ID: 33768177
Hi David.  Thank you so much for your help so far.  I tried your code but got an error saying the connection was already open.  I have slightly changed the code so that the connection string is at the top and not duplicated throughout the project.
SQL-reader.xlsm
0
 
LVL 1

Author Comment

by:simonwait
ID: 34029823
From what I can gather what I actually want to do cannot be done.  Suggest closing this quesiton
0
 
LVL 34

Expert Comment

by:Norie
ID: 34055391
Do you actually want to add new records or update existing records?
Maybe I've misread but, to me anyway, it sounds like you want to do the latter (update) but the code you are using is adding records.
0
 
LVL 1

Author Comment

by:simonwait
ID: 34090694
I wanted to do both really. More like a syncronise but with the excel sheet being the master
0
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 34094323
Well you can't really do both in one 'go' if you like.
The code you have only seems to be adding records.
If you want to update existing records you would need to use the Update method of the recordset, and you would have to locate the specific records you want to update.
You could probably combine the 2 but it might be easier to add new records then update, or vice versa.
Might even be easier to create 2 queries.
An INSERT query for the new records and an UPDATE query for the updates.
One thing I don't quite understand is the original question - why and where do you want to alter an identity column?
 
0
 
LVL 35

Expert Comment

by:David Todd
ID: 34096399
Hi,

Just a thought - if you want to do an insert and an update, think about using the new merge syntax for SQL 2008.

Regards
  David
0
 
LVL 1

Author Comment

by:simonwait
ID: 34104941
imnorie,
It was basically to force the numbering of what was in the excel side.  I kept getting errors where it would either give me a new set of ID numbers or fail because the fields were locked.

dtodd, thanks for the suggestion however this is on SQL 2000.  Think that has been lost somewhere and this now appears to be in the MS SQL 2008 zone.  Sorry
0
 
LVL 34

Expert Comment

by:Norie
ID: 34107714
Simon
Do you mean that for the new records you are creating an identity/primary key in Excel?
Do you know how/if that is generated in the actual table in the database?
ie when you add a new record to the table do you actually have to manually enter it or is it done automatically?
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question