locked
Create Autonumber GUID using SQL Statment RRS feed

  • Question

  • Hi Everyone,

    I have an Access 2013 database. In it, I use SQL statements heavily in VBA to process transactions, modify system date, process updates etc. I am at a point where I would like to create a table with an SQL Statement. Sounds easy. Do it all the time. But I want the ID field and PK to be an AutoNumber ReplicationID (or GUID).

    It's easy enough to create an AutoNumber using the AUTOINCREMENT datatype in SQL and it's easy enough to create a ReplicationID field using the GUID data type. BUT ... I can't create an AutoNumber Replication ID field.

    I have a function called GetGUID(). There are examples that I have found where using a simple function like that as the default value for the field should work but access keeps telling me that the function is not found or not valid.

    The SQL statement that creates an AutoNumber (Long) looks like this:

    CREATE TABLE myTable
    (
    ID AUTOINCREMENT PRIMARY KEY UNIQUE,
    Field1 VARCHAR(255) NOT NULL,
    Field2 GUID NOT NULL,
    SomeDate DATE,
    SomeTime TIME,
    Field3 VARCHAR(255),
    );

    If anyone can help make this script create an AutoNumber GUID, I would appreciate it.

    P.S. I can create this using DAO Table Defs, but that doesn't fit with the DQL Statement based update process that I need it to.

    Cheers


    Stephen, RedGalaxy Darwin, Australia

    Saturday, February 7, 2015 6:01 AM

Answers

  • You cannot use your own function GetGUID() in the default property of a field in a table. Only built-in functions are allowed, and GenGUID() is the built-in Access function to generate GUIDs.

    The reason why you are gettting the #Error is explained in Alex Dybenko's Access Blog

    Unfortunately, it is not possible to modify the Attributes property after the table has been created. But normally, such a GUID field is hidden from the user. If it doesn't bother you, just leave it as is. Otherwise, you will have to create an exact copy of the table using DAO (and possibly ADOX) methods.

    Matthias Kläy, Kläy Computing AG

    • Proposed as answer by L.Hl Friday, February 13, 2015 2:14 AM
    • Marked as answer by L.Hl Monday, March 9, 2015 7:47 AM
    Tuesday, February 10, 2015 1:28 PM

All replies

  • You have to use ADO to create such a field:

    CurrentProject.Connection.Execute "CREATE TABLE myTable " & _
      " (Id Guid Primary Key DEFAULT GenGUID(), " & _
      " Field1 Text(50) Not Null)"

    Matthias Kläy, Kläy Computing AG


    • Edited by mklaey Saturday, February 7, 2015 10:51 AM
    Saturday, February 7, 2015 10:49 AM
  • Hey Matthias,

    I have tried this method. it is essentially the same as CurrentDB.Execute(SQL). The issue I have with this method is that when the SQL executes, it generates an error saying Unknown function 'GetGUID' in validation experession or default value on 'myTable.Id'.

    My function is called GetGUID() not GenGUID() but the function works elsewhere in code.

    Cheers,


    Stephen, RedGalaxy Darwin, Australia

    Tuesday, February 10, 2015 3:46 AM
  • Hi Matthias,

    I tried this again exactly as you had written it. It did build a table, but when a record is created in the table, the Id is #Name? until the record is dirty. Is that what you would expect to see normally?

    Cheers


    Stephen, RedGalaxy Darwin, Australia

    Tuesday, February 10, 2015 3:52 AM
  • You cannot use your own function GetGUID() in the default property of a field in a table. Only built-in functions are allowed, and GenGUID() is the built-in Access function to generate GUIDs.

    The reason why you are gettting the #Error is explained in Alex Dybenko's Access Blog

    Unfortunately, it is not possible to modify the Attributes property after the table has been created. But normally, such a GUID field is hidden from the user. If it doesn't bother you, just leave it as is. Otherwise, you will have to create an exact copy of the table using DAO (and possibly ADOX) methods.

    Matthias Kläy, Kläy Computing AG

    • Proposed as answer by L.Hl Friday, February 13, 2015 2:14 AM
    • Marked as answer by L.Hl Monday, March 9, 2015 7:47 AM
    Tuesday, February 10, 2015 1:28 PM