RE: Creating a Sequential Record ID number with a make-table
AT @comcastdotnet Tom Wickerath
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
___________________________________________
"M KING" wrote:
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
Joseph Meehan
> 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
While as noted you can use autonumber to do part of the job, Tom
explained it much better than I could, but I do suggest that if you are
expecting to keep these numbers in any kind of contiguous order, don't use
autonumbers as they tend to get out of order rather easily and start
skipping numbers etc.
There are ways of adding the numbers via code and I would guess at least
one of Tom's references will show you how.
--
Joseph Meehan
26 + 6 = 1 It's Irish Math
onedaywhen
Tom Wickerath wrote:
> 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.
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.
--
AT @comcastdotnet Tom Wickerath
> 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:
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.
Microsoft Jet databases ignore the Clustered property because the Microsoft
Jet database engine doesn't support clustered indexes.
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
_______________________________________
onedaywhen
Tom Wickerath wrote:
> > I think you have your analogies in a twist <g>.
> > The more usual term, I believe, for 'bucket of fish' is a 'heap'
>
> lack of order present in the bucket o' fish analogy.
My understanding of a 'bucket o' fish' is that the bucket is filled
with water and the fish are alive, active and swimming around in the
bucket. Each time I look into the bucket to view a specific set of
fish, they are never to be found in the same place relative to all the
other fish because they are in constant flux. I don't think this
analogy is correct for rows in a Jet table. The reality is more like
dead fish in a bucket containing no liquid; to my mind's eye these fish
are in a heap. They remain in the same order I put them in (date/time
order) until I reorganize them into a specific order (compacting, PK
exists).
How about a test? Try this:
CREATE TABLE no_PK (col1 INTEGER NOT NULL);
INSERT INTO no_PK VALUES (3);
INSERT INTO no_PK VALUES (2);
INSERT INTO no_PK VALUES (1);
SELECT col1 FROM no_PK;
SELECT col1 FROM no_PK;
SELECT col1 FROM no_PK;
...
If this was your bucket, the omission of an ORDER BY should result in
the fish jumping in a different order fairly frequently. However, they
are always pulled out in the same order, suggesting the bucket is more
like mine.
> 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.
Let's look at the text in question:
Quote: "Compacting the database now results in the indices being stored
in a clustered-index format. While the clustered index isn't maintained
until the next compact, performance is still improved ... The new
clustered-key compact method is based on the primary key of the table.
New data entered will be in time order."
I think I can be forgiven for coming away with the idea that concept of
a clustered index does exist for Jet <g>. I've also read another KB
article which may be closer to the truth:
ACC2000: Defragment and Compact Database to Improve Performance
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769
Quote: "A disk defragmenter will place all files, including the
database file into contiguous clusters on a hard disk ... If a primary
key exists in the table, compacting re-stores table records into their
Primary Key order. This provides the equivalent of Non-maintained
Clustered Indexes"
The way I think of it is that Jet tables *do* support the concept of a
clustered index but there is no choice in the matter i.e. it is always
on the PK and it is due to this lack of user choice that the official
line, reflected in MS OLE DB provider properties, is that clustered
indexes are 'not supported' (this feature makes choosing the PK more of
a performance issue for Jet than it is for other more capable DBMSs but
that's a whole new thread <g>). You may focus on the lack of 'support'
but that does not mean they do not exist.
Can we please compromise on 'Jet tables support the equivalent of
clustered indexes' and come up with a non-contentious phrase to refer
to this concept e.g. 'physical order on disk'?
Jamie.
--
samwardill
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)?
pere...@jetemail.net
samwardill wrote:
> 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..."]