Search notes:

MS Access: SQL

Executing SQL Statements

DML and DDL statements (aka action queries) with currentDb.execute "…".

SQL dialect

In most cases, the SQL dialect that is used in Access follows the SQL 89, level 1 standard.

However, its possible to «upgrade» the used dialect to SQL-92 compatiblity mode: File -> Options -> Object Designers -> SQL Server Compatible Syntax (ANSI 92)

Create a new table with a select statement

The equivalent of other databases' create table NewTable as select * from ... is

select *
into   NewTable
from   ...

Referencing tables in other formats

Tables in another Access file

Tables in another access database can be referenced with [access-path].[tablename] which allows, for example to insert into a table in another access (accdb) file:

insert into
[X:\path\to\backend.accdb].[TheTableName]
(x, y, z)
values
(1, 42, 99)

Data in Excel

Similarly, a worksheet can be referenced as a table with [Excel 8.0;HDR=yes;DATABASE=\path\to\xlsx].[Worksheet Name$].

Of course, if the data has no header, you will set HDR=no.

Note the trailing $ after the sheet name. If there were a named range in the excel sheet, the name of the range can be stated after the $. If the entire sheet has to be used, the $ still needs to be present.

  insert into someTable
  select * from [Excel 8.0;HDR=yes;DATABASE=x:\path\to\the.xlsx].[worksheet name$]

CSV files

insert into someTabel
select *
from [TEXT;HDR=yes;DATABASE=x:\path\to\Directory\Containing\CSV].[fileName.csv]

Changing data type of column

Change datatype of col in table tab to number:

alter table  tab alter column col number;

Add constraints to a table

alter table tab add constraint tab_pk primary key (col_1 [, col_2 … ])

Date related

Creating a date:

dateSerial(year, month, day)

No CASE WHEN … expression

There is no case when … then expression in Access-SQL.

Instead, iif should be used:

iif (condition, expr-if-true, expr-if-false)

If more than one conditional needs to be tested, the switch(…) function can be used:

switch(
   condition-1, value-if-true-1
 , condition-2, value-if-true-2
   …
 , condition-n, value-if-true-n
)

LIKE operator

Access doesn't use the percent sign (%) as wild character in a like expression. Instead, it uses an asterisk (star): *.

# matches a digit (0-9)

And there are characters lists that resemble those of regular expressions: [a-f] and [!a-f] (the latter meaning a character that is not in the range a through f.

TODO: Apparently, this behaviour can be changed if a database is set to ANSI-92 standard.

Selecting records with GUIDs

A textual representation of a GUID can be converted into «real» GUID with guidFromString("{…}").

So, a record that is identified by a GUID can be selected like so:

select *
from
   tab
where
   id = guidFromString('{A7CC3DA6-ABFB-E911-81A1-005056938A20}')

Action queries

Apparently, Access refers to DML statements (and also DDL statements?) as action queries. There seem to be three ways to execute such action queries:

TODO

The transform statement allows crosstab queries (= Pivot queries?)

The parameters declaration.

distinct is not supported.

No limit clause.

The imp (implies) and eqv (equivalence) operators.

fix as equivalent to Oracle's trunc(date).

select count(distinct col) from tab not possible.