Solved

Alter Identity SQL with VBA

Posted on 2010-09-24
22
776 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
  • 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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 33

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 33

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 33

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now