Generate numbers 1 to 1000000 in MS Access using SQL

I am looking for a simple clean method to obtain the sequence {1, 2, 3, 4, 5, 6, 7,…,1000000} in MS Access SQL. I thought of creating a table with a column that numbers from 1 to 100000 however, this is inefficient.

Is there a way of generating numbers 1 to 10000000 in MS Access using SQL?

I tried the GENERATE_SERIES() function but MS Access SQL does not support this function.

id | number
------------
1. | 1
2. | 2
3. | 3
4. | 4
5. | 5
6. | 6
7. | 7 
8. | 8

Answer

Yes, and it is not painfull – use a Cartesian query.

First, create a small query returning 10 records:

SELECT 
    DISTINCT Abs([id] Mod 10) AS N
FROM 
    MSysObjects;

Save it as Ten.

Then run this simple query:

SELECT 
    [Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000 AS Id
FROM 
    Ten AS Ten_0, 
    Ten AS Ten_1, 
    Ten AS Ten_2, 
    Ten AS Ten_3, 
    Ten AS Ten_4, 
    Ten AS Ten_5

which, in two seconds, will return Id from 0 to 999999.