Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Copy Tables and Preserve Autonumbers? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have a 30 tables, 15 tables have autonumbers as a unique identifier.

    I want to leave the table/field structure verbatium however I want to change the field names by prefixing them with str, lng, dtm, etc based on the data type.

    So for each table I have an empty table with different table name and same file structure and I want to come up with an append query to append all the records from old table to new table making sure I preserve the autonumbers in all tables.

    I tested this with Northwind Categories

    INSERT INTO Categories_New ( lngCategoryID, strCategoryName, memDescription, olePicture )
    SELECT Categories.CategoryID, Categories.CategoryName, Categories.Description, Categories.Picture
    FROM Categories;

    And it seem to work, can I assume that this will work the same way with all my tables that have autonumbers?

    Note: CategoryID is autonumber and a primary key, some of my tables autonumber fields do not have a primary key.

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Copy Tables and Preserve Autonumbers? (a2k (9.0.6926) SP-3 Jet

    If you have already created 30 new tables with the field names you want to use, it should work.
    But you must also create the indexes (including the primary key) in the new tables, and create relationships between the new tables. Append queries will not transfer the indexes and relationships.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: How to Copy Tables and Preserve Autonumbers? (a2k (9.0.6926) SP-3 Jet

    (I'm back!!! Thanks, Hans, for the notice)

    The only other thing you need to worry about is that sometimes Access has a habit of not properly updating its Last Autonumber Used (not real name) when you append records that already have a value in the autonumber field to a table that has an autonumber in it. You can manually go to each table and start making an entry in a new record to see what # Access will use next. Or, just download my LiquormanUtilities (see my site below) and have it automatically check and reset the autonumbers.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Copy Tables and Preserve Autonumbers? (a2k (9.0.6926) SP-3 Jet

    There is a school of thought which does not agree with prefixing field names with data type identifiers.

    See <post#=185655>post 185655</post#> for a fuller discussion.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Copy Tables and Preserve Autonumbers? (a2k (9.0.6926) SP-3 Jet

    Hi Steve

    Thanks for that!

    Like every other programmer getting started I wrestled with each issue in your document including access/naming conventions and settled on the three character prefix.

    Where was MSGoldenGoldenRules.doc when I needed it?

    Woody’s Forum Idea:

    MSGoldenGoldenRules.doc should be in MS Office Starpost for Access

    How about getting all the Access Starpost into MS Office Starpost for Access?

    This should be a collection of all the Starpost. This would be beneficial the exitsing members and more importantly to new members.

    My first five projects were conversions from existing oracle systems with solid database design and field data type definitions so Charlotte’s valid concerns were not an issue.

    The prefix has helped me in a lot of areas including coding techniques, see following code

    Bottom line, it gets down to personal preference, just be consistent.

    I tried the link at:

    Re: Tips'n'Tricks handout (A2K)
    <post:=515,889>post 515,889</post:> re: 195,604 from SteveH

    http://img.woodyswatch.com/w3tfiles/4-5158...GoldenRules.doc

    And received:

    The page cannot be found

    Can you check this out?

    Thanks, John

    <pre>‘Lookup by user selected Field (cboLookupSequence) and user typed Criteria (strLookupCriteria) Routine
    Dim strOperator As String
    If Left(cboLookupSequence, 3) = "dtm" Then
    strOperator = "="
    If strLookupCriteria = "*" Then
    strLookupCriteria = DMin(cboLookupSequence, strTable)
    strOperator = ">="
    End If
    strIDSQL = strIDSQL & _
    " FROM " & strTable & _
    " WHERE " & cboLookupSequence & _
    " " & strOperator & "#" & strLookupCriteria & "# " & _
    " ORDER BY " & strSort
    Else
    If Left(cboLookupSequence, 3) = "lng" Or Left(cboLookupSequence, 3) = "int" Then
    strOperator = "="
    If strLookupCriteria = "*" Then
    strLookupCriteria = DMin(cboLookupSequence, strTable)
    strOperator = ">="
    End If
    strIDSQL = strIDSQL & _
    " FROM " & strTable & _
    " WHERE " & cboLookupSequence & _
    " " & strOperator & " " & strLookupCriteria & " " & _
    " ORDER BY " & strSort
    Else ‘str
    strIDSQL = strIDSQL & _
    " FROM " & strTable & _
    " WHERE " & cboLookupSequence & _
    " LIKE " & Chr(34) & strLookupCriteria & "*" & Chr(34) & _
    " ORDER BY " & strSort
    End If
    End If
    </pre>


  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Copy Tables and Preserve Autonumbers? (a2k (9.0.6926) SP-3 Jet

    Unfortunately, a lot of attachments were lost when the old Lounge server crashed in August.

    Perhaps Steve can post a reply to that thread with the latest version of his handout. I'll be happy to make it a Star Post.

  7. #7
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Copy Tables and Preserve Autonumbers? (a2k (9.0.6926) SP-3 Jet

    Uploaded both files.

    Enjoy.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Copy Tables and Preserve Autonumbers? (a2k (9.0.6926) SP-3 Jet

    Thanks! I've always found them very useful. I'll star the "Golden Rules" one now.

  9. #9
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Copy Tables and Preserve Autonumbers? (a2k (9.0.6926) SP-3 Jet

    Ta very much! Two starred posts now - I'll treat myself to two beers tonight! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Copy Tables and Preserve Autonumbers? (a2k (9.0.6926) SP-3 Jet

    Congratulations! It's well deserved!

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: How to Copy Tables and Preserve Autonumbers? (a2k (9.0.6926) SP-3 Jet

    >>There is a school of thought which does not agree with prefixing field names with data type identifiers.<<
    Yeah, I'm not crazy about it, but to each his own. About the only time I use the prefixes is in a procedure when I've defined a variable. In tables, I never use them, figuring my FieldNames are usually enough to easily figure out the data type. After all, do fields such as "InvoiceDate", "City", or "FirstName" really need any qualification?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •