Create Table with Random Key

[doulostheou]

I am looking to create a table via VBA (need to create the table in many different backend databases). I am using the following code, which almost does what I need it to do:

    db.Execute "CREATE TABLE tblSessions (SessionID Counter CONSTRAINT MyFieldConstraint PRIMARY KEY, YearID long, CampusID long, StartDate date, EndDate date, CreatedBy long, DateCreated date, ModifiedBy long, DateModified date);"

The problem is that because of the way this database works, I have many different field users working off independent copies and syncing with a backend. The normal incrementing primary key created lots of issues for me, so I switched it to Random and have been good to go since. I know there is still some room for duplication, and if there is a better solution to avoid dup keys in independent copies I would be happy to hear it.

But back to the issue at hand, Is there any way to create the table through SQL (similar to what is above) with a Random primary key instead of using Increment or Counter. I have tried playing with the syntax myself to no avail and my searches for an answer have come up empty.

If you cannot do it through pure SQL, I would have to believe there is a way in VBA to change the New Values property of the primary key to Random after it is created. However, I have no idea how to go about this.

Any help or push in the right direction would be greatly appreciated. Thanks!

 

[LPurvis]

You're suspicion is correct - you can't use Jet DDL alone to create a random autonumer.
You can add another line of code though.. use DAO's OM to provide the setting.

db.Execute "CREATE TABLE tblSessions (SessionID Counter CONSTRAINT MyFieldConstraint PRIMARY KEY, YearID long, CampusID long, StartDate date, EndDate date, CreatedBy long, DateCreated date, ModifiedBy long, DateModified date);"
db("tblSessions")("SessionID").DefaultValue = "GenUniqueID()"

It's not the same syntax for all the RDBMS you might be using - but them's the breaks. ;-)

Cheers.

 

[doulostheou]

Thank you. The extra line of code worked perfectly (I am using native Microsoft Access tables) on my first attempt. However, I noticed some oddities.

I actually have three tables I am looking to add, so I used the following:

    db.Execute "CREATE TABLE tblSessions (SessionID INTEGER CONSTRAINT MyFieldConstraint PRIMARY KEY, YearID long, CampusID long, StartDate date, EndDate date, CreatedBy long, DateCreated date, ModifiedBy long, DateModified date);"
    db("tblSessions")("SessionID").DefaultValue = "GenUniqueID()"
    db.Execute "CREATE TABLE tblTracking (TrackID Counter CONSTRAINT MyFieldConstraint PRIMARY KEY, Description text(50), Annual yesno, CreatedBy long, DateCreated date, ModifiedBy long, DateModified date);"
    db("tblTracking")("TrackID").DefaultValue = "GenUniqueID()"
    db.Execute "CREATE TABLE mapSessionTracking (MapID Counter CONSTRAINT MyFieldConstraint PRIMARY KEY, SessionID long, TrackID long, EmployeeID long, Completed date, Month byte, CreatedBy long, DateCreated date, ModifiedBy long, DateModified date)"
    db("mapSessionTracking")("MapID").DefaultValue = "GenUniqueID()"

But I got three different results with each table. With the first table, everything worked correctly but it shows "#Name?" for new records (in the table view) as opposed to the normal "(New)". The user does not ever see this field, so I was not overly concerned about this.

Oddly enough, the second table also works correctly but shows "(New)" for new records. This is obviously the way Access tables normally work, so no problems again.

The third table gets created without a problem, but when the line of code to set the DefaultValue runs, I get a run-time error 3265: Item not found in this collection. I have verified that mapSessionTracking did get created correctly and that there is a field named MapID.

So I have 3 different tables with 3 different results. Odd.

 

[doulostheou]

Okay. I fixed the problem with the third table using the following:

    db.Execute "CREATE TABLE tblSessions (SessionID INTEGER CONSTRAINT MyFieldConstraint PRIMARY KEY, YearID long, CampusID long, StartDate date, EndDate date, CreatedBy long, DateCreated date, ModifiedBy long, DateModified date);"
    db.Execute "CREATE TABLE tblTracking (TrackID Counter CONSTRAINT MyFieldConstraint PRIMARY KEY, Description text(50), Annual yesno, CreatedBy long, DateCreated date, ModifiedBy long, DateModified date);"
    db.Execute "CREATE TABLE mapSessionTracking (MapID Counter CONSTRAINT MyFieldConstraint PRIMARY KEY, SessionID long, TrackID long, EmployeeID long, Completed date, Month byte, CreatedBy long, DateCreated date, ModifiedBy long, DateModified date)"
    Set db = Nothing
    Set db = DBEngine.Workspaces(0).OpenDatabase(strPath)
    db("tblTracking")("TrackID").DefaultValue = "GenUniqueID()"
    db("tblSessions")("SessionID").DefaultValue = "GenUniqueID()"
    db("mapSessionTracking")("MapID").DefaultValue = "GenUniqueID()"

Establishing a clean relationship with the database appears to have fixed the error. tblTracking still shows #Name? for new records and the other two tables both show (New). I still find this odd, but I wouldn't anticipate that it would be a problem.