Im not that familiar with MS Access SQL commands. Can anyone tell me what is wrong with my create statement below?
Create Table Extract( [WeekID] TEXT(10) , [RetailerID] INT , [ItemCode] INT , [SaleType] TEXT(1) , [Multiple] INT , [Store] INT , [DateCollected] DATETIME , [Price] DECIMAL(9,4) )
There are two main problems with this statement.
Firstly, Extract is a reserved word in Access SQL. That means you have to surround it by [] brackets.
Secondly, the DECIMAL data type is only supported in Access SQL when executing SQL in ANSI mode. You can see how to switch your database to ANSI mode in this answer, or you can execute your query using the following line in the immediate window:
CurrentProject.Connection.Execute "Create Table [Extract]( [WeekID] TEXT(10) , [RetailerID] INT , [ItemCode] INT , [SaleType] TEXT(1) , [Multiple] INT , [Store] INT , [DateCollected] DATETIME , [Price] DECIMAL(9,4) )"
Furthermore, I recommend using NVARCHAR over TEXT in queries. There's no functional difference as long as you specify a field size smaller than 255, but TEXT usually refers to the Long Text data type, a harder to work with data type that can take very long strings.
