Access VBA - DoCmd. Run SQL Creating Tables with field types containing expressions

Question

I am trying to create a SQL query that will create a table.

However, one of the fields will contain an expression. This is what my expression looks like.

Docmd. Run SQL "CREATE MyTable ([ID] PRIMARY KEY, [PNO] DOUBLE, [DD] DATE, [OP] TEXT(255), [UNIQUEKEY] FUNCTION([PNO]&[DD]&[OP]))"

However, I am getting a syntax error which I am assuming that VBA does not recognize the FUNCTION as a data type. The UNIQUEKEY is supposed to be a concatenation of three fields as you all can probably tell. Any idea what I should do?

What is the correct field type for an expression in access VBA?

Thanks!

Answer 1

MS Access doesn't support keys as functions but the multiple fields in unique key are allowed:

VBA code:

DoCmd.RunSQL "CREATE TABLE MyTable([ID] INT PRIMARY KEY, " & _
              "[PNO] DOUBLE, [DD] DATE, [OP] TEXT(255), " & _
              "CONSTRAINT unique_index_name UNIQUE ([PNO],[DD],[OP]) )"

SQL code:

CREATE TABLE MyTable(
   [ID] INT PRIMARY KEY, 
   [PNO] DOUBLE,
   [DD] DATE, 
   [OP] TEXT(255), 
   CONSTRAINT unique_index_name UNIQUE ([PNO],[DD],[OP]) 
 );