Announcement

Collapse
No announcement yet.

[SQL Tools] Reset MS Access 2003 auto-number

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [SQL Tools] Reset MS Access 2003 auto-number

    Based on http://stackoverflow.com/questions/9...mber-on-import I am using
    Code:
        dosql = "ALTER TABLE CUSTCARDHIST ALTER COLUMN ID COUNTER(1,1)"
        SQL_Stmt %SQL_STMT_IMMEDIATE, dosql
        CheckForSQLerrors
    which results in a ODBC error. (And, yes, the table is empty first.)

    How else can this be accomplished?
    Erich Schulman (KT4VOL/KTN4CA)
    Go Big Orange

  • #2
    Worked for me.

    What are the error codes please?

    Comment


    • #3
      See attachment.
      Attached Files
      Erich Schulman (KT4VOL/KTN4CA)
      Go Big Orange

      Comment


      • #4
        >which results in a ODBC error.

        Technically, it's an error message from SQL Tools, not from the ODBC subsystem, although I would suspect the only reason you have an SQL Tools' message is because SQL Tools got an error from the ODBC subsystem in response to some call.

        Not that it directly helps you - except it does direct you to the correct source for support.

        MCM
        Michael Mattias
        Tal Systems (retired)
        Port Washington WI USA
        [email protected]
        http://www.talsystems.com

        Comment


        • #5
          To reset the autonumber you have to compact/repair the database after emptying the table
          > And, yes, the table is empty first.)

          But did you compact the DB?

          MCM
          Michael Mattias
          Tal Systems (retired)
          Port Washington WI USA
          [email protected]
          http://www.talsystems.com

          Comment


          • #6
            Originally posted by Michael Mattias View Post

            But did you compact the DB?

            MCM
            No. It is my understanding that compacting it would reset the auto-number, but I have not found any way to effect that via SQL. The program is to run daily at 5am, so compacting manually is not a viable option.
            Erich Schulman (KT4VOL/KTN4CA)
            Go Big Orange

            Comment


            • #7
              Originally posted by Erich Schulman View Post
              No. It is my understanding that compacting it would reset the auto-number, but I have not found any way to effect that via SQL. The program is to run daily at 5am, so compacting manually is not a viable option.
              As an alternative, try Shelling to MS Access with the database name and the /compact switch - or set up a scheduled task to do the same from a batch file.

              Comment


              • #8
                >>>> To reset the autonumber you have to compact/repair the database after emptying the table

                >>>And, yes, the table is empty first.)

                >>But did you compact the DB?

                > No.

                I hope Cliff Nichols reads this thread. He has his own thread in which he laments how his users don't read his documentation.

                Cliff, you are NOT alone!!!

                MCM

                PS: I know there is a programmatic way to compact a Jet Database, using the ODBC interface directly. Let me fire up Bob Scott's "disk utility" to see if I can find that command. No, I don't know how that would by done thru Sql Tools, but I figure if I can find the direct command someone will be able to figure it out.
                Michael Mattias
                Tal Systems (retired)
                Port Washington WI USA
                [email protected]
                http://www.talsystems.com

                Comment


                • #9
                  How about code which works?
                  Code:
                  #IF 0
                  ****************************************************************************************
                  * ' JCOMPACT.BAS
                    Compact a Jet Database for PB/CC 3.0
                    Working 11/19/02, subject to problems with embedded spaces in input or output paths (*.mdb file names).
                    Also need better way to get the driver name. Can I get the driver name associated with a DSN? YES I CAN!
                    I will need to connect, but that's OK.
                    ' Get driver name associated with a DSN.. I can call SqlDataSources and compare..
                    ' must be a better way.. maybe by connecting
                  ****************************************************************************************
                  #ENDIF
                  
                  
                  DECLARE FUNCTION CompactJetDb (BYVAL hWndParent AS LONG, szDriver AS ASCIIZ, szDBIn AS ASCIIZ, szDbOut AS ASCIIZ ) AS LONG
                  ' ABSOLUTELY UNABLE TO FIGURE OUT HOW TO USE ANY DB NAMES WITH EMBEDDED SPACES.
                  ' when it's the input database, GetShortPathName works, but I cannot
                  ' figure out how to do a destination with embedded space in one of the
                  ' subdirectories.
                  
                  FUNCTION DoTheCompact () AS LONG
                  
                      LOCAL szDriver AS ASCIIZ * %MAX_PATH
                      LOCAL szDbIn   AS ASCIIZ * %MAX_PATH
                      LOCAL szDbOut  AS ASCIIZ * %MAX_PATH
                      LOCAL hWnd AS LONG, Stat AS LONG
                      ' FOR SqlInstallerError:
                      LOCAL szMessage AS ASCIIZ * 4096, iErrorRec AS LONG
                      LOCAL cbMsgMax AS LONG, MsgLength AS LONG, ErrorCode AS LONG
                  
                      szDriver   = "Microsoft Access Driver (*.mdb)"
                      szdbIn     = "C:\Software_Development\Testdata\ppps\Database\ppps_cp.mdb"
                      ' DBout may be the same as DBin; however, if different, it may not exist.
                      szDbOut    = "C:\Software_Development\Testdata\ppps\Database\ppps_cp.mdb"
                  
                      hWnd = GetDeskTopWindow         ' has no effect for compact operation
                      PRINT "Compact Database Names (passed):"
                      PRINT "In = " & szDbIn
                      PRINT "Out="  & szDbOut
                  
                      Stat = CompactJetDb (hWnd, szDriver, szDbIn, szDBOut)
                  
                      IF ISTRUE Stat THEN
                          PRINT "COMPACT SUCCESS"
                      ELSE
                          PRINT "Compact Failed"
                          iErrorRec = 1         ' this works, but why I do not know.
                          cbMsgMax  = SIZEOF(szMessage)
                          Stat =SqlInstallerError (iErrorRec, ErrorCode, szMessage, cbMsgMax, MsgLength)
                          IF Stat = %SQL_SUCCESS THEN      ' we retrieved the error info
                              PRINT "ErrorCode=" & STR$(ErrorCode)
                              PRINT szMessage
                              ' just about every error is error 11, "Installer, Translator or Config Failed" Really useful. Not.
                          ELSE
                              PRINT "Can't get SQLInstallerError (" & LTRIM$(STR$(Stat)) & ")"
                          END IF
                      END IF
                  
                  END FUNCTION
                  
                  ' =============================================================
                  '  COMPACT A JET DATABASE
                  '  THERE MUST BE SOME WAY TO HANDLE EMBEDDED SPACES
                  '  IN THE INPUT AND OUTPUT DATABASE NAMES.
                  '  Syntax of szAttrbutes:  "COMPACT DB=<source_file> <dest_file> [sort option]"
                  '  Strange but true: ODBC_ADD_DSN is used, not ODBC_CONFIG_DSN
                  ' ==============================================================
                  
                  $COMPACT_DB  = "COMPACT_DB"
                  FUNCTION CompactJetDb (BYVAL hWndParent AS LONG, szDriver AS ASCIIZ, szDBIn AS ASCIIZ, szDbOut AS ASCIIZ ) AS LONG
                  
                      LOCAL szAttributes AS ASCIIZ * 4096
                  
                      szAttributes = $COMPACT_DB & "=" & szDbIn & $SPC & szDbOut
                      FUNCTION = SqlConfigDataSource (hWndParent, %ODBC_ADD_DSN, szDriver, szAttributes)
                  
                  END FUNCTION
                  It's a little old so I included my comments.

                  MCM
                  Michael Mattias
                  Tal Systems (retired)
                  Port Washington WI USA
                  [email protected]
                  http://www.talsystems.com

                  Comment


                  • #10
                    You know, I just looked at that code. You can just call those functions directly... SQL Tools does not have to be a part of the solution at all.
                    Michael Mattias
                    Tal Systems (retired)
                    Port Washington WI USA
                    [email protected]
                    http://www.talsystems.com

                    Comment


                    • #11
                      Hi Erich --

                      I don't have any useful feedback for you about your ALTER TABLE statement; that's strictly an Access/ODBC syntax thing, and it may be dependent on the version of Access that you are using, the Access/ODBC driver version, and other factors.

                      If that statement is generating an "Invalid field data type" error message -- and NOT generating an error for Christopher -- I'd suspect that you and he are using different types of counters, but it's hard to know for sure.

                      > I have not found any way to effect [Access database compaction] via SQL.

                      If you are using SQL Tools Pro, check out the SQL_DataSourceModify function with the COMPACT_DB option. Simply search for "compact" in the SQL Tools help file.

                      -- Eric Pearson, Perfect Sync, Inc.
                      "Not my circus, not my monkeys."

                      Comment


                      • #12
                        I hope Cliff Nichols reads this thread. He has his own thread in which he laments how his users don't read his documentation.
                        If I already knew how to compact the database without doing so manually, I would have implemented same, the auto-numbering would be reset, and I would not have had any need to start a thread. But pathetic me was unaware it could be done, let alone how. And so what that I made an abortive attempt to work out my own solution before coming here.

                        I guess I somehow missed
                        Thou shalt memorize MSDN and SDK help files in their entirety so that when thou art under time constraints thou wilt already have thy answer.
                        If He can know every word, then obviously I can too.
                        Erich Schulman (KT4VOL/KTN4CA)
                        Go Big Orange

                        Comment


                        • #13
                          Give me a break.

                          I ASSUMED your problem was changing the sequence number, not how to get there.

                          Your own posting of the problem says, "ffirst you empty the table, then you compact the database." You said you had emptied the table. I just checked to see if you had also compacted the database.

                          If what you really wanted was, "How can I compact a Jet (Access) database programmatically?" I would have thought you would have asked for same.

                          And you know what you would have gotten had you asked that way? You would have got post # 9 - working code - minus the grief.

                          You earned what you got.

                          MCM
                          Michael Mattias
                          Tal Systems (retired)
                          Port Washington WI USA
                          [email protected]
                          http://www.talsystems.com

                          Comment


                          • #14
                            Erich und Eric, apologies: I tested on Access 2000 - not Access 2003. I misread the numerals. I can test on A2003 in a couple of days time.

                            One would think that there would be no difference in the AutoNumber between these two versions of Access, but looking through the MS KB suggests otherwise.

                            To test, I created a new database with a new table that had two fields:
                            - 1. Data type: AutoNumber; Field Size: Long Integer; New values: Increment; Primary key: yes.
                            - 2. Just a text field.

                            The PC I tested this on uses MS XP Pro. The column was not a foreign key to another table.

                            I then added some rows, which incremented the auto count. All these were then deleted. Adding a new row displayed a number in the first column equivalent to the number of rows previously added, plus one. I then deleted that new row, leaving no rows in the table.

                            I then ran the code you posted to clear the counter, then added a new row. The number displayed in the first column was 1, showing that the code worked as desired.


                            So can you check please that the column (Access field) you are testing is an AutoNumber - Long Integer specifically. This has been known to be set to Text before, but appears to be fixed in Office 2003 SP1.

                            The error you get (ODBC HY000) states that the column is the wrong data type - as per the above bug.

                            Another cause of an invalid AutoNumber in Access 2003 is a corrupted database. Running Compact and Repair should fix this. But see KB articles 291162 - "AutoNumber field duplicates previous values after you compact and repair a database", and 287756 - "AutoNumber field is not reset after you compact an Access database".

                            KB article 884185 hints at other issues with the AutoNumber: "You may receive an error message when you try to insert a new record in a table that contains an Autonumber field in Access 2007 or in Access 2003".

                            The above-mentioned KB article 287756 provides an ADO solution to programmatically reset the seed value of an AutoNumber column, which might be better in this instance to use rather than ODBC.

                            And for a SQL Tools code example to compact an Access database, see http://www.powerbasic.com/support/fo...ML/003670.html.

                            Comment


                            • #15
                              Ok, here is the column: http://img200.imageshack.us/img200/1...pboard01yx.png

                              This box has Office 2007 only installed. I don't know which version was used to create the database, though it was surely >=2003.

                              The JET driver used (on my box) is 4.00.6305.00 under XP32 Pro SP3.
                              Last edited by Erich Schulman; 7 Sep 2009, 08:56 PM. Reason: add URL
                              Erich Schulman (KT4VOL/KTN4CA)
                              Go Big Orange

                              Comment


                              • #16
                                above-mentioned KB article 287756 provides an ADO solution to programmatically reset the seed value of an AutoNumber column, which might be better in this instance to use rather than ODBC.
                                I looked at that link, and that is some real straightforward code.

                                It also told me of the existence of the ADOX library, which has some properties and methods which, while you can get there from here using ADO, make the trip a lot shorter.
                                Michael Mattias
                                Tal Systems (retired)
                                Port Washington WI USA
                                [email protected]
                                http://www.talsystems.com

                                Comment


                                • #17
                                  I also forgot to ask...

                                  Why would one want to reset an AUTONUMBER column in the first place?

                                  Seems to me, if you've decided AUTONUMBER is the correct column type, then whatever AUTONUMBER is assigned ought to be plenty good enough.

                                  Enquiring Minds want to know...
                                  Michael Mattias
                                  Tal Systems (retired)
                                  Port Washington WI USA
                                  [email protected]
                                  http://www.talsystems.com

                                  Comment


                                  • #18
                                    Originally posted by Michael Mattias View Post
                                    I also forgot to ask...

                                    Why would one want to reset an AUTONUMBER column in the first place?

                                    Seems to me, if you've decided AUTONUMBER is the correct column type, then whatever AUTONUMBER is assigned ought to be plenty good enough.

                                    Enquiring Minds want to know...
                                    Because the customer is always right

                                    Every day at 5am, my program will empty out two tables (only one uses autonumbers) then re-populate them using TSV files from another program.

                                    It would be easy for my program to determine which rows need to be deleted, delete them, determine which records to update/insert, and update/insert as needed. There is really no need for 70-75K total SQL statements running every day, but this is what they want.

                                    The autonumber column is merely a surrogate key and should probably not even be there. But I was not involved in normalizing the database, and I can't change the schema.

                                    And, personally, the less I deal with Access the better. I wish they were using SQL Server 200x Express (or PostgreSQL or MySQL), especially with what they're doing.
                                    Erich Schulman (KT4VOL/KTN4CA)
                                    Go Big Orange

                                    Comment


                                    • #19
                                      every day at 5am, my program will empty out two tables (only one uses autonumbers) then re-populate them using tsv files from another program.
                                      Code:
                                      DROP TABLE
                                      
                                      (RE-) CREATE TABLE..
                                      
                                      (re)-populate table

                                      ????
                                      Michael Mattias
                                      Tal Systems (retired)
                                      Port Washington WI USA
                                      [email protected]
                                      http://www.talsystems.com

                                      Comment


                                      • #20
                                        I've found one can delete the table contents and rewrite/reuse the autonumber values in Access without having to reseed or drop the table (which has issues if referential integrity is used).

                                        Just give the autonumber field a value in the SQL statement.
                                        The value does not have to have been used before and does not need to be next incrementally or sequentially ordered. In fact it can be the same value.

                                        This is vb code but I wonder if SQLTools "SQL_Stmt" could be used with same result.

                                        Code:
                                        Private Sub DoAppendAutoNumber()
                                        Dim db As DAO.Database
                                        Dim strSQL As String
                                        Dim lAutoNumb As Long
                                        
                                        Set db = DAO.OpenDatabase("TheDatabase")
                                        
                                        db.Execute "DELETE * FROM Table1"
                                        
                                        'go forwards in steps
                                        For lAutoNumb = 1 To 10 Step 3
                                        
                                            strSQL = "INSERT INTO Table1 ( a )SELECT " & lAutoNumb
                                            db.Execute strSQL
                                            
                                        Next lAutoNumb
                                        
                                        'go backwards in steps
                                        For lAutoNumb = 10 To 1 Step -5
                                        
                                            strSQL = "INSERT INTO Table1 ( a )SELECT " & lAutoNumb
                                            db.Execute strSQL
                                            
                                        Next lAutoNumb
                                        
                                        'write same value
                                        For lAutoNumb = 21 To 30
                                        
                                            strSQL = "INSERT INTO Table1 ( a )SELECT 20000"
                                            db.Execute strSQL
                                            
                                        Next lAutoNumb
                                        
                                        db.Close
                                        Set db = Nothing
                                        
                                        End Sub
                                        Last edited by matt humphreys; 8 Sep 2009, 05:35 PM.

                                        Comment

                                        Working...
                                        X