Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

RE: Creating a Sequential Record ID number with a make-table

Discussion in 'Information Technology' started by samwardill, Nov 2, 2005.

  1. samwardill

    Expand Collapse

    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)?

    "Tom Wickerath" wrote:

    > 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
    > ___________________________________________
    > "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
  2. Loading...

    Similar Threads Forum Date
    Canada Ottawa weighs creating ambassador for religious freedom Breaking News Oct 4, 2011
    India SGPC Accused of Creating a Row Breaking News Jan 20, 2011
    Pacific International Gathering of Young Sikhs Focuses on Creating Good Citizens Breaking News Jan 8, 2011
    Are We Creating New Devtas? Sikh Sikhi Sikhism Aug 21, 2009
    Sikh News Sharif warns against creating instability in Punjab (Express India) Breaking News Sep 19, 2008

  3. peregenem@jetemail.net

    Expand Collapse

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

    [MS Access;DATABASE=C:\MyOtherDB.mdb;].MyTable
    data_col NVARCHAR(20) NOT NULL)
    [MS Access;DATABASE=C:\MyOtherDB.mdb;].MyTable
    SELECT lname AS data_col
    FROM Employees

    [Shame Tom Wickerath never returned to help "come up with a
    non-contentious phrase..."]
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     

Share This Page