Ms Access 2003 Bug – Crash after Compact/Repair & Autonumber Resetting

Two little tidbits of information today. Firstly, a bug that occurs in Access 2003 after you try run the Compact/Repair function. This is a really annoying bug that seems to occur randomly in Access 2003. What happens is that for one reason or another your database corrupts, and when you try open it, Access tells you it must be repaired, you hit OK, then Access tells you that an error occured (abliet with very little detail other) and must be close. You close, try reopen, and you run into the same stumbling block – the database must be recovered (also, assume in this scenario, the decompile switch has not worked).

There is a good description of how this bug occurs on this Google group page.

Unfortunately, in this situation, I couldn’t recover from it using Ms Access 2003 alone (i.e. even importing all the objects into a new blank database would not work) and I lost patience, and simply loaded up the 2003 database in Access 2007 which fixed the corruption issue in about 2 seconds flat. If anyone could enlighten me as to another way to fix it, please comment. I hear, and read, that this is an unconfirmed bug in Access 2003.

After recovering the system, I reloaded it in Access 2003 and it worked fine, but I noticed some records had gone ‘bad’ in one of the main tables, I promptly fixed them, but then realised that the autonumber ‘counter’ I used (not a primary key, in case you ask) was seeding about 30,000 numbers above the previous record, so I sighed and shook my head and moved to tackle this next problem which was not as straight forward as I imagined.

Turns out resetting a autonumber usually means you have to rebuild the entire table from scratch and then relink all the relationships, if it happened to be a relational table. Mine happened to be one of the main tables in the database, and I wasn’t looking forward to checking that all the relational integrity was upheld because of one pesky autonumber field. Then I stumbled on this function from microsoft which did the trick and reset my autonumber field back to the correct number.

Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
‘You must pass the following variables to this function.
‘strTbl = Table containing autonumber field
‘strCol = Name of the autonumber field
‘lngSeed = Long integer value you want to use for next AutoNumber.

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

‘Set connection and catalog to current database.
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn

Set col = cat.Tables(strTbl).Columns(strCol)

col.Properties(“Seed”) = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties(“seed”) = lngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

End Function

As the MS link says, “make sure that the Microsoft ActiveX Data Objects 2.x and the Microsoft ADO Ext 2.x for DDL and Security Libraries check boxes are selected” otherwise the code won’t compile. Add that code to a vba module, then open up the debug window (ctrl-g) and type “debug.print ChangeSeed(“tableName”, “Autonumber Column name”, valueToStartSeed)” filling in the necessary arguments to run the function, and voila! Autonumber reset!

Ms Access – Cannot make MDE file error

Well, as usual, I published Access tips in response to problems that I come across, and get around, during my day-to-day Ms Access development.

As we know, Ms Access ain’t exactly the most stable product ever made and there are many bugs which often occur with badly explained non-sensical error messages.

Today, I kept getting an error that went something like “Cannot make MDE, ACCDE file”. This was strange. I could make the MDE about 5 minutes before I had made some minor edits to control names on a report. Why could it suddenley not make an MDE (with no real reason other than stating “Cannot make MDE”). For a few minutes, I was completely baffled.

However, as it turns out, the control name that I had innocently changed on the reports was actually used in some minor code that upon further inspection realised I didn’t need anymore. However, since Access doesn’t automatically change the VBA behind a control when you make changes to the controls properties, the new name was now inconsistent with the name used in the code…which means IT WOULDN’T COMPILE!

You see, when Access makes an MDE file, it compiles your entire database, and throws an error when it doesn’t compile (as it should) – but just doesn’t tell you specifically what the error is. So if you’re coming across the “cannot make MDE file” error, try recompiling the your Access project (and if you don’t know how to compile it, well simply jump into VBA mode and go to the DEBUG – COMPILE option on the toolbar).