• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

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?
0
Elroy Taulton
Asked:
Elroy Taulton
  • 4
  • 3
2 Solutions
 
Jeffrey CoachmanCommented:
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 CoachmanCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Elroy TaultonDevelopment ManagerAuthor Commented:
yes.

I have a UniqueID field in my SQL table.  It is type uniueidentifier and a primary key.
0
 
Jeffrey CoachmanCommented:
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
 
Jeffrey CoachmanCommented:
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now