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