Welcome to SPN

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

Sign Up Now!

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

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

  1. samwardill

    samwardill
    Expand Collapse
    Guest

    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

    peregenem@jetemail.net
    Expand Collapse
    Guest

    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..."]
     

Share This Page