How to create a table in an MS-Access database using SQL?
Background:
I am developing an application that connects to a database in MS-Access.
The application I'm developing is aimed at different users; each of them owns their own database in Access, so I'm looking for a way to run a script similar to the following to create a table in a database in Access:
CREATE TABLE RewardCoupon
(
[ID] AUTOINCREMENT,
[OrderID] INTEGER,
[PaymentDate] DATETIME,
[CouponCode] TEXT,
[CouponType] TEXT,
[MemberNumber] TEXT
);
Executing the above code results in the following Message:
Query must have at least one destination field.
I'm looking first for a solution in script ; that is, I don't want to count on you having to develop some additional application.
For program distribution purposes1, it is necessary to have a script that facilitates the creation of the table in each database in Access that is required.
So my question is: How to create a table in an MS-Access database using SQL?
1 by this I mean that the person in charge of supporting the program has the necessary scripts to in this case execute said script and quickly create the table in the database in Access.
1 answers
From Comment
Have you selected "data definition" for the query type? – Aprendiendo.NET
Synthesis of the OP response: No.
From comment
So that's the problem, one step is missing: Create > Query design > Data definition. In the following link ► hold a example, step by step, to perform a "definition of data" – Aprendiendo.NET
The solution is correct; however, in the particular case of the OP, the database in Access is locked as can be seen in the following screenshot #1:
screenshot # 1:
To run the script correctly, you need:
- close the modal window "marked with red text" (according to the image).
- enable content (see screenshot #2):
screenshot # 2:
- after this change, you may need to close the database and Microsoft Access to save the changes.
- open the database again and perform the steps described in the link.