Results 1 to 8 of 8
  1. #1
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    67

    Creating an autonumber field with a with a Make Table Query

    I have a make table query that I would like to add an autonumber field to. I want to automate this, so I do not want to go into the table manually after creating it.

    I know you can add fields to a query that are placeholders for eventual updates, for example putting: NBR:0 in a query grid will create a field of the numerical (long integer I think) datatype.

    Is there anyway to do this for the autonumber field. Obviously in this case it wouldn't be a placeholder waiting to recieve data, I would like it to add data to the field: auto enumerate the records which are transferred to the new table.



    Thanks for any help!

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    well I think you're kind of stuck, because you can't change a number field to an autonumber field. the program simply won't let you. So you might have to create a new field on the table via code after you create the table with the sql statement. If this is run via a macro, you can run one action after the other.

    if you do happen to take this route, the code needs to be something to the effect of:

    Code:
    Dim tbl As DAO.TableDef
    Dim db As DAO.Database
    Set db = CurrentDb
    
    Set tbl = db.tabledefs("tablename")
    
    with tbl
       .Fields.Append .CreateField("field", dblong)
       .Fields("field").Attributes = .Fields("field").Attributes + dbAutoIncrField
    end with
    
    
    Set tbl = Nothing
    set db = nothing

  3. #3
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    in standard dbs one is not making permanent tables on the fly - since they are the core framework of a db. making and deleting the same temp table repeatedly is somewhat more common when one needs a scratch pad so to speak.

    a partial solution is to make it into multi steps. Step 1 is to make the table, as you know. Step 2 is to then append the records into an existing existing blank table that has an autonumber field existing already. the autonumbers will autogenerate when you append in records.

    where you go from there is not clear to me.... but you might want to rethink whether making tables on the fly is really the correct approach.

    hope this a little helpful.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by NTC View Post
    a partial solution is to make it into multi steps. Step 1 is to make the table, as you know. Step 2 is to then append the records into an existing existing blank table that has an autonumber field existing already. the autonumbers will autogenerate when you append in records.
    This is also on Allen Browne's site somewhere, but I don't think it is relevant to what's needed.

    I thought it was when I read it and I decided not to give my function for doing just this, but since you brought it up, yes, appending records to a one table field with an auto will fill them as like he/she obviously wants.

  5. #5
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    67
    Quote Originally Posted by NTC View Post
    in standard dbs one is not making permanent tables on the fly - since they are the core framework of a db. making and deleting the same temp table repeatedly is somewhat more common when one needs a scratch pad so to speak.

    a partial solution is to make it into multi steps. Step 1 is to make the table, as you know. Step 2 is to then append the records into an existing existing blank table that has an autonumber field existing already. the autonumbers will autogenerate when you append in records.

    where you go from there is not clear to me.... but you might want to rethink whether making tables on the fly is really the correct approach.

    hope this a little helpful.
    Had considered that solution and it does work; just wanted to know if the other way can be done. Thanks for the tip..

  6. #6
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    67
    Quote Originally Posted by ajetrumpet View Post
    well I think you're kind of stuck, because you can't change a number field to an autonumber field. the program simply won't let you. So you might have to create a new field on the table via code after you create the table with the sql statement. If this is run via a macro, you can run one action after the other.

    if you do happen to take this route, the code needs to be something to the effect of:

    Code:
    Dim tbl As DAO.TableDef
    Dim db As DAO.Database
    Set db = CurrentDb
     
    Set tbl = db.tabledefs("tablename")
     
    with tbl
       .Fields.Append .CreateField("field", dblong)
       .Fields("field").Attributes = .Fields("field").Attributes + dbAutoIncrField
    end with
     
     
    Set tbl = Nothing
    set db = nothing
    When I try this, I get an "Invalid Operation" error that points to the:

    .Fields("field").Attributes = .Fields("field").Attributes + dbAutoIncrField

    part of the code. The field appears to be appended per the code just before that as when I check the table, it shows. However, it fails when trying to adjust the field to autonumber..

    Any suggestions? Thanks..

  7. #7
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by orcinus View Post
    When I try this, I get an "Invalid Operation" error that points to the:

    .Fields("field").Attributes = .Fields("field").Attributes + dbAutoIncrField

    part of the code. The field appears to be appended per the code just before that as when I check the table, it shows. However, it fails when trying to adjust the field to autonumber..

    Any suggestions? Thanks..
    I got it!!! I learned something new today! wooohoo!

    apparently you can't assign the autonumber attribute to an existing field that's been appended to the table already, via code. So you have to use a variable to do the work and append it at the very last line. e.g. - change this:
    Code:
    Dim tbl As DAO.TableDef
    Dim db As DAO.Database
    Set db = CurrentDb
     
    Set tbl = db.tabledefs("tablename")
     
    with tbl
       .Fields.Append .CreateField("field", dblong)
       .Fields("field").Attributes = .Fields("field").Attributes + dbAutoIncrField
    end with
     
     
    Set tbl = Nothing
    set db = nothing
    to this:
    Code:
    Dim tbl As DAO.TableDef
    Dim db As DAO.Database
    dim fld as dao.field
    
    Set db = CurrentDb
     
    Set tbl = db.tabledefs("tablename")
     
    with tbl
       set fld = tbl.CreateField("field", dbLong)
       
          fld.Attributes = fld.Attributes + dbAutoIncrField
          .Fields.Append fld
    end with
     
    set fld = nothing
    Set tbl = Nothing
    set db = nothing
    You should be fine with that.

  8. #8
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    67
    Quote Originally Posted by ajetrumpet View Post
    I got it!!! I learned something new today! wooohoo!

    apparently you can't assign the autonumber attribute to an existing field that's been appended to the table already, via code. So you have to use a variable to do the work and append it at the very last line. e.g. - change this:
    Code:
    Dim tbl As DAO.TableDef
    Dim db As DAO.Database
    Set db = CurrentDb
     
    Set tbl = db.tabledefs("tablename")
     
    with tbl
       .Fields.Append .CreateField("field", dblong)
       .Fields("field").Attributes = .Fields("field").Attributes + dbAutoIncrField
    end with
     
     
    Set tbl = Nothing
    set db = nothing
    to this:
    Code:
    Dim tbl As DAO.TableDef
    Dim db As DAO.Database
    dim fld as dao.field
     
    Set db = CurrentDb
     
    Set tbl = db.tabledefs("tablename")
     
    with tbl
       set fld = tbl.CreateField("field", dbLong)
     
          fld.Attributes = fld.Attributes + dbAutoIncrField
          .Fields.Append fld
    end with
     
    set fld = nothing
    Set tbl = Nothing
    set db = nothing
    You should be fine with that.
    Yep, that works, thanks, much obliged!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-29-2010, 08:01 AM
  2. Creating Yes/No Field Using Make Table
    By orcinus in forum Queries
    Replies: 6
    Last Post: 08-19-2010, 11:09 PM
  3. How to make a custom autonumber?
    By AccessThis in forum Access
    Replies: 1
    Last Post: 07-20-2010, 06:49 PM
  4. Field changes in Make table query
    By asherbear in forum Queries
    Replies: 9
    Last Post: 05-29-2010, 01:35 PM
  5. Replies: 2
    Last Post: 04-20-2010, 12:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums