[SQL Tools] Reset MS Access 2003 auto-number

[Erich Schulman]

Based on http://stackoverflow.com/questions/9...mber-on-import I am using

    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?

[Christopher Carroll]

Worked for me.

What are the error codes please?

[Erich Schulman]

See attachment.

[Michael Mattias]

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

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

[Erich Schulman]

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.

[Stuart McLachlan]

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.

[Michael Mattias]

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

How about code which works?

#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]

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.

[Eric Pearson]

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.

[Erich Schulman]

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.

[Michael Mattias]

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

[Christopher Carroll]

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.

[Erich Schulman]

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.

[Michael Mattias]

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]

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

[Erich Schulman]

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.

[Michael Mattias]

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.

DROP TABLE

(RE-) CREATE TABLE..

(re)-populate table

????

[matt humphreys]

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.

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