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

Quick Tip – Conditional Formatting on Ms Access Reports

This is one little annoying problem that there is not enough obvious documentation for within Ms Access 2007. While it might seem obvious enough, at the time, it wasn’t to me (so who is to blame, my lack of knowledge, the software interface, or the help documentation?).

I was developing a report in Ms Access and setting all these conditional formatting expressions and values. When I was running the reports, the formatting conditions were seemingly not working. I ran my mouse over some of the textboxes, and noticed that the background colour was changing as per my formatting rules when I clicked in the text boxes.

I was slightly stuck, I couldn’t figure out why they weren’t working as I had specified. Then all of a sudden it dawned on me – you have to set the textbox control’s background to SOLID rather than TRANSPARENT. I did so, and viola! All was right in the world.

This was a tip brought to you from the seemingly-obvious-yet-not-so-obvious department.

Access 2007 After One Hour – Initial Highlights

I’ve been using Access 2007 for about an hour now, quickly developing a small database to store information on what periodicals I’ve been reading for my thesis, and when I have accessed them. Quick review – it is fairly impressive! Highlights are:

  • The Ribbon Gui common to all Office 2007 products is used very well in Access 2007
  • Graphical Calendar Lookup for Date Fields.
  • Split View Forms – Graphical Form at top, Datasheet at the bottom.
  • Ability to standardised formatting of controls on a form, meaning left, top, width and height values are consistent for groups of form controls! This means one can ‘pretty’ up a form much faster and easier.
  • Combo Box Lookup Wizards in Table Design.

Hopefully it won’t take too much time away from me actually *doing* work!

Access 2003 – Importing Queries results in bad query aliasing.

This is a quick little tip.

I came across a minor annoyance the other day when I was importing Access saved queries from another database and using link tables. If you import your queries before you link your tables, then try run the queries with no tables link – Access will complain that it can’t find the tables, then litter your queries that use those linked tables with aliases like “expr1:” “expr2:” and so on. This means that if you use a lot of saved queries you’re going to waste valuable time fixing them all up.

The trick is very simple and, when you think about it, does make sense – link your tables before you import anything.

Ms Access – The Upgrade

I’d never thought I’d ever say something so geeky but I must comment that I don’t think I’ve ever been as excited about downloading a product as I am right now. And right now I’m downloading Ms Access 2007. I’m taking advantage of the http://www.itsnotcheating.com.au offer, and buying the new version of Office 2007 Ultimate for $75. That is a bargain.

I’m presently doing a small bit of consulting work, developing in Access 2003, and have stumbled into a small problem. I have never worked with the Runtime version of Access, and now I’m being requested to deploy my new database using it (client doesn’t necessarily have Ms Access installed on all users computers’). I have come to realise that in order to properly license Runtime Access applications, I will have to splurge out something in the line of $1,000 AUD.

Lucky, MS have decided to be a bit more kind to developers in the new version, and will offer Runtime licenses free of charge! Unfortunately, the Runtime Access kit isn’t available yet – but should be soon!

Well, it’s official. As of later tonight, I will be happily coding away in Access 2007.

Geek!