T-Sql fill in gaps in autoincrement field


I am using Microsoft.Jet.OLEDB.4.0. and I need to use SET IDENTITY_INSERT table_name ON in order to fill in gaps in autoincrement field but every time when I start executing my query VB reports error:

"Invalid SQL statment; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

I have tried with UPDATE tablename SET IDENTITY_INSERT ON but nothing

How can I solve this problem with SET statment?

Who is Participating?

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

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.

Anthony PerkinsCommented:
>> fill in gaps in autoincrement field <<
This is a very bad idea.  However if you insist:
As the name indicates SET IDENTITY_INSERT is intended for Inserts not Updates.  Something like this:

INSERT YourTableName (ID, Col1, Col2, ...) VALUES (4, @Value1, @Value2, ...)

damirselAuthor Commented:
Why do you think that this is very bad idea?
Anthony PerkinsCommented:
Because you will lock the table while the insert occurs and so block anyone else from adding a row until it is complete.  But you are right, if this is for maintaining your home recipes than it should not be a problem.  I am used to many users accessing the site simultaneously and I am not concerned about wasting a value, as there are another 2 billion to contend with :)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Anthony PerkinsCommented:
I should have mentioned that the other problem is that you first have to find the missing value, here is one way to do it:
Find gaps in sequence numbers.

This is an overhead that most shops cannot afford.

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
damirselAuthor Commented:
Yes my database is planed to be used only by one user so there is no conflicts, but thanks anyway for your response. Now I am heaving real trouble to SET IDENTITY i have tried like this but still the same error appears

Dim con As ADODB.Connection
Set con = New ADODB.Connection

con.ConnectionString = "Mode=12;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mybase.mdb;User ID=admin;Password=;Jet OLEDB:Database Password=mypass;"
con.Execute "SET IDENTITY_INSERT my_table ON INSERT INTO my_table(id,name) VALUES(3,'Jack') SET IDENTITY_INSERT my_table OFF"

Where am I going wrong ?
Anthony PerkinsCommented:
>>I am using Microsoft.Jet.OLEDB.4.0. and I need to use SET IDENTITY_INSERT table_name ON<<
My apologies.  You mentioned SET IDENTITY _INSERT and I assumed you were using MS SQL Server, since it appears you are using MS Access you are out of luck as that command (SET IDENTITY _INSERT) is not available in MS Access.
damirselAuthor Commented:
Is there some other way using Jet.OLEDB.4.0 to do this? (not neccesserely autonumber field)
Anthony PerkinsCommented:
If it is not an autonumber field, that of course you can insert any row with any ID you like.  If it is an autonumber row, you may need to resort to rebuilding the table every time.
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
Visual Basic Classic

From novice to tech pro — start learning today.