Passing <uniqueidentifier> from MS Access 2007 to SQL Database Table

I am creating a Access Form (2007) that will be utilize to update and create new records that are stored in a backend SQL Server 2008 Table.  One of my fields in this table is a UniqueID.  From SQL, I can use newid() to create this unique value.  How can do this in the actual Access Form OR what are my options for this?
Elroy TaultonDevelopment ManagerAsked:
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.

Jeffrey CoachmanMIS LiasonCommented:
There are many ways...

One is to use a function that simply adds 1 to the highest number

Function NextNumber() as long
    NextNumber=Dmax("YourIDField","YourTable")+1
end function.

This may not be perfect for a multi-user environment...

So lets see what other experts post...

JeffCoachman
0
Elroy TaultonDevelopment ManagerAuthor Commented:
Thanks Jeff, but you are correct.  This is a multi-user environment and this method may cause problems.  Are there other options for doing this?
0
Jeffrey CoachmanMIS LiasonCommented:
If I am understanding your question...

The act of creating the record in Access should trigger the SQL to generate the next number.
Do you have an "Identity" (auto-number) field in this SQL table?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Elroy TaultonDevelopment ManagerAuthor Commented:
yes.

I have a UniqueID field in my SQL table.  It is type uniueidentifier and a primary key.
0
Jeffrey CoachmanMIS LiasonCommented:
So are you saying if you add a record in Access, the SQL autonumber is not created?
0
mbizupCommented:
Iin addition to the property settings you mentioned, you should set this up as an identity column.

"Is Identity" should be set to Yes, so that your column autonumbers.  See this for details (under the identity specification heading):
http://msdn.microsoft.com/en-us/library/ms177173.aspx

If you have not already done so, your settings should look like this:
Ident.PNG
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
Jeffrey CoachmanMIS LiasonCommented:
Thanks for chiming in mbizup...
As you know SQL is not my strong point...

;-)

Jeff
0
Elroy TaultonDevelopment ManagerAuthor Commented:
Thanks for the help on this one.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.