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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

(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.

Reply | Quote

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

See post 185655 for a fuller discussion.

Reply | Quote

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 re: 195,604 from SteveH

http://img.woodyswatch.com/w3tfiles/4-5158…GoldenRules.doc%5B/url%5D

And received:

The page cannot be found

Can you check this out?

Thanks, John

‘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

Reply | Quote

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.

Reply | Quote

Uploaded both files.

Enjoy.

Reply | Quote

Thanks! I’ve always found them very useful. I’ll star the “Golden Rules” one now.

Reply | Quote

Ta very much! Two starred posts now – I’ll treat myself to two beers tonight!

Reply | Quote

Congratulations! It’s well deserved!

Reply | Quote

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

Reply | Quote