Exceeding 2 Gb MDB file size (and a bit of TGIF humour)

Tony — Fri, 08 May 2009 23:14:50 GMT

Exceeding the 2 Gb MDB file size gives you an exceedingly misleading error message:

In Jet 4.0 one message that is received when the 2 Gb file size is exceeded is invalid argument. This is, of course, utterly confusing and can mean other things such as corruption.

In A2007/ACE (I was running in an ACCDB) I got the following message

3049 Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.

running the following code. (Watch for line wrap.)

Const FillText As String = "ASdlkjSLDJKADSLJADSLJ ASDLJK ALSDJ LASJKD
LJASLDJK ALSJKD LAJSD lJKA SDLJK ASDLj ASDLj ALSDJ LJ " & _
    "ASdlkjSLDJKADSLJADSLJ ASDLJK ALSDJ LASJKD LJASLDJK ALSJKD LAJSD
lJKA SDLJK ASDLj ASDLj ALSDJ LJ " & _
    " AS:LK:ASDK A:D :AKSD :ASDK:AKSD :AKS D adfasdf asd fasdf asdf"
Sub Filltable()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Table1")
tagTop:
    rs.AddNew
    rs!field1 = FillText
    rs.Update
    DoEvents
    GoTo tagTop
End Sub

Now this code runs for several hours so be patient. I only ran it inside Access 2007 and ACCDBs. I'll run it in Access 2003 just to double check that message. (Out for steak, Caesar salad and mugs of red wine with friends.)

Now note that you don't want an autonumber key on the table. Just the text field. I suspect by the time you hit five million records the overflow areas of the index will get real, real slow. But I don't feel like testing that.

Humour

Bill Mosca, fellow Access MVP, had the following to say about my FillText string: Here's a site for greeking text...in case you need to fill a longer string without monkey-typing it yourself.
http://www.duckisland.com/GreekMachine.asp

Of course another alternative is Lorem Ipsum. I used a paragraph from that in a reply to someone who stated "Of course, we're going to have to argue that one for a while."

I sure hope there is a final exam on this course

Tony — Mon, 27 Apr 2009 22:02:22 GMT

Project title: Need 15 Queries in Access

"I need someone who can make 15 queries in Access 2007 and have to be done in sql view (this is a must). I want this for school home work and need them fast, as soon as possible. I will provide you with the tables and the queries I need, they are attached so you can tell me if you can do it or not. Payment will be made in full after project is completed. I will not consider anyone without any reviews. Bid only if you can do this project, I need it fast so don't make me waste my time. I wil... "

And if you click through the link Place my bid for this project! you will see the replies. Wow.

Tony's Microsoft Access Blog : Humour, Access

Exceeding 2 Gb MDB file size (and a bit of TGIF humour)

Humour

I sure hope there is a final exam on this course