Query Syntax problem

I have a table called Draw. It has an Identity KeyField called DrawingID. One of the other fields is called Register. Somtimes I want to create a new record with a value in the Register field which is 1 higher than the previous maximum.

I can execute the following statement in the a Management Studio Query and get a value returned:

Set NoCount On
Insert into Draw (REGISTER) Select MAX(Register)+1 from Draw
Select Register from Draw where DrawingID=SCOPE_IDENTITY()

But when I try to open an ADODB recordset in MS Access with that same statement, the recordset will not open. I've opened recordsets previously containing compound statements. What is the problem with this one?
TownTalkAsked:
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.

DcpKingCommented:
Try making it a stored proc:

create procedure dbo.AddNewDrawRecord
as
Begin
    set nocount on
    insert into dbo.Draw(Register)
        select max(Register) + 1 from dbo.Draw
    select Register from Draw where DrawingID = scope_identity()
End
deightonprogCommented:
do you not need a semi-colon

Insert into Draw (REGISTER) Select MAX(Register)+1 from Draw;
Select Register from Draw where DrawingID=SCOPE_IDENTITY();
Michael CarrilloInformation Systems ManagerCommented:
Try:

Set NoCount On
Insert into Draw
Set REGISTER=(Select MAX(Register)+1 from Draw)
Select Register from Draw where DrawingID=SCOPE_IDENTITY()
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Kyle AbrahamsSenior .Net DeveloperCommented:
MAX(Register)+1 is the WRONG way to go about this.

Setup an identity of (1,1) on register, insert it without specifying and get the number back.

You can run into all sorts of issues (race time conditions) that will leave your program in an unstable state.


More on Identity:
http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx
TownTalkAuthor Commented:
@Ged325: This field only sometimes has data in it. I cannot make it an identity field.

@deighton: No it doesn't work with semicolons either

@macarillo1: Your suggestion doesn't work. It says: Incorrect syntax near the keyword 'Set'

@Depking: As a last resort i'll make it a stored procedure, but that reduces the readability of my MsAccess code.

Like I said in my original post, i've done these compound statements previously in MsAccess. It should be possible to make it work.
DcpKingCommented:
I wouldn't be so sure about being able to make it work - I've come across things in Access that you'd expect to work but which don't.

As for using sprocs, I personally am all in favour of them. I find that they don't diminish readability at all (and anyhow, the comments in the code will explain stuff. Errr. You are commenting your code, aren't you? [grin]).  In addition, using sprocs insulates your user-friendly but not very efficient Access front-end from your user-unfriendly but very efficient SQL Server back-end, so if you need to change things on the server you don't necessarily have to go chasing through all the code of all the Access projects that might use that part of the DB, to see if they need fixing.
 Using sprocs means that you can test them without having to use the Access front end, too, which can speed things up a lot.

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
TownTalkAuthor Commented:
Ok thanks. I'll use an sp.
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.