Creating a Sequential Record ID number with a make-table

[M KING]

I am running a make-table query, but I want to have the first column of the
created table to be a record ID, filled with 1,2,3,4, etc. Is there an easy
way to do this? The tables I am querying have ID numbers, but after I do the
query they are not necessarily sequential or contiguous, i.e. 5134, 5137,
5140, etc.

Thank you

 

[Tom Wickerath]

Hello -

First, regarding your statement about the records being "not necessarily
sequential or contiguous", you should know that records stored in JET tables
are completely unordered. Think of a table of records like it is a bucket of
fish. You use queries with specified sorts to establish order.

You can always add an autonumber datatype manually, after the make-table
query has been run. Just make sure that the new values property for the
autonumber field is set to the default of increment (instead of random). If
you want to do this all in one operation, then my suggestion is to use a SQL
DDL (Data Definition Language) query to create the table first, and then use
an append query (instead of a make-table query) to append the records to your
new table.

Here are some links to KB articles to get you started on SQL DDL queries:

How To Common DDL SQL for the Microsoft Access Database Engine
http://support.microsoft.com/?id=180841

Create and drop tables and relationships using SQL DDL in Microsoft Access
http://support.microsoft.com/?id=291539

You can use VBA code to first run the SQL DDL query, followed by the append
query. You can probably also do this using macros, but I absolutely abhor the
use of *most* macros in an Access database (the only exceptions being
Autoexec and Autokeys macros). Here is a procedure that will create a table
with all available datatypes using SQL DDL:

'*****************Begin Code*****************
' Note: This code requires a reference set to the "Microsoft DAO 3.6 Object
Library"

Option Compare Database
Option Explicit

Sub CreateTableUsingSQLDDL()
On Error GoTo ProcError

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()

'Define a SQL DDL query that uses all available data types
strSQL = "CREATE TABLE tblTestAllTypes " _
& "(MyAutoNumber COUNTER, " _
& "MyText TEXT(50), " _
& "MyMemo MEMO, " _
& "MyByte BYTE, " _
& "MyInteger INTEGER, " _
& "MyLong LONG, " _
& "MySingle SINGLE, " _
& "MyDouble DOUBLE, " _
& "MyCurrency CURRENCY, " _
& "MyReplicaID GUID, " _
& "MyDateTime DATETIME, " _
& "MyYesNo YESNO, " _
& "MyOleObject LONGBINARY, " _
& "MyBinary BINARY(50))"

'Run the query
db.Execute strSQL, dbFailOnError

'Redefine the strSQL variable here for your append query
strSQL = "Insert appropriate SQL statement here"

'Run the query (Note: Commented out for now, since the new strSQL is invalid)
'db.Execute strSQL, dbFailOnError '<---Uncomment this line when you have a
valid SQL statement

Application.RefreshDatabaseWindow

ExitProc:
'Cleanup
On Error Resume Next
db.Close
Set db = Nothing
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure CreateTableUsingSQLDDL"
Resume ExitProc
End Sub

'*********************End Code***********************


If my answer has helped you, please sign in to Microsoft's Online Community
and mark my post as "Answered".

Tom
http://www.access.qbuilt.com/html/expert_contributors.html
___________________________________________

:

I am running a make-table query, but I want to have the first column of the
created table to be a record ID, filled with 1,2,3,4, etc. Is there an easy
way to do this? The tables I am querying have ID numbers, but after I do the
query they are not necessarily sequential or contiguous, i.e. 5134, 5137,
5140, etc.

Thank you

 

[Tom Wickerath]

Jamie,

I think you have your analogies in a twist <g>.
The more usual term, I believe, for 'bucket of fish' is a 'heap'
and refers to a table without a clustered index.

I prefer to communicate at a level more easily understood by *most* people.
I have no need to use geek speak (ie. clustered index) in my answers, unless
I take the time to clearly define the meaning. It's my analogy, and I can use
it however I like. I think most people can draw a pretty clear idea of the
lack of order present in the bucket o’ fish analogy.

You should know that for Jet tables the clustered index is determined
by the primary key column(s).

And you should know that JET does not use clustered indexes! Did you get
your information from the following KB article (specifically, the fourth
bullet)?

Regarding new features in Jet Version 3.0:
http://support.microsoft.com/default.aspx?id=137039

If so, you are reading into the statement information that isn’t really true
about Jet. Note that this bullet also contains the following statement:

"While the clustered index isn't maintained until the next compact..."

Remember that the leaf level of a clustered index contains the data row, not
the index’s key to the data, as in a non-clustered index. Instead, you
should be paying attention to the following Web pages:

http://msdn.microsoft.com/library/d...ado270/htm/admscclusteredpropertyexamplex.asp

Clustered Property Example (VB)

This example demonstrates the Clustered property of an Index. Note that
Microsoft Jet databases do not support clustered indexes, so this example
will return False for the Clustered property of all indexes in the Northwind
database.

http://msdn.microsoft.com/library/d...y/en-us/office97/html/output/F1/D2/S5A274.asp

Microsoft Jet databases ignore the Clustered property because the Microsoft
Jet database engine doesn't support clustered indexes.

http://msdn.microsoft.com/library/d...y/en-us/office97/html/output/F1/D2/S5A2B7.asp

The Clustered property is ignored for databases that use the Microsoft Jet
database engine, which doesn't support clustered indexes.

I must say that I'm rather surprised that no Access MVP's jumped on your
butt for making such a statement. They seem to be particularly vocal when
someone other than an MVP makes an erroneous assertion.

Tom
_______________________________________

:

I think you have your analogies in a twist <g>.

The more usual term, I believe, for 'bucket of fish' is a 'heap' and
refers to a table without a clustered index. The clustered index
determines the physical order i.e. how the data is organized on disk.

You should know that for Jet tables the clustered index is determined
by the primary key column(s). In absence of a PK, rows are physically
stored in date/time order. For a table with a PK, rows inserted
subsequent to the last compact will be stored in date/time order and
the physical order will only be re-established on the clustered index
when the file is compacted again.

Without an explicit ORDER BY, row order is not guaranteed but is highly
predictable, unlike the chaos of your aquarium.

Jamie.

 

[samwardill]

The SQL DDL hint is just what I need. I also want to sequentially number
records created by a query. If I delete the table and recreate each time I
run the query I always get sequential numbers (starting with 1) from the
autonumber - right?

I have just one more question: Do you (or anyone) know how do I generate the
table using 'CREATE TABLE' in a second database (my data and application are
in seperate databases)?

 

[peregenem]

Do you (or anyone) know how do I generate the
table using 'CREATE TABLE' in a second database

CREATE TABLE
[MS Access;DATABASE=C:\MyOtherDB.mdb;].MyTable
(row_ID INTEGER IDENTITY(1,1) NOT NULL,
data_col NVARCHAR(20) NOT NULL)
;
INSERT INTO
[MS Access;DATABASE=C:\MyOtherDB.mdb;].MyTable
(data_col)
SELECT lname AS data_col
FROM Employees
;

[Shame Tom Wickerath never returned to help "come up with a
non-contentious phrase..."]