Error 3049: Cannot open database when using AddNew with a memo field

Question

Occasionally, the assignment to the CNOTE memo field (rsGLXfer!CNOTE = Me!CNOTE) throws the 3049 error. Repair and compact fixes the problem for a while (1 day or so). Here's the code (pretty plain stuff!):

Private Sub OKSaveRecord_Click()

    Dim db As DAO.Database

    Dim rsGLXfer As DAO.Recordset

    Set db = CurrentDb()

    Set rsGLXfer = db.OpenRecordset("TransferGL")

    If DLookup("Cmonth", "qry_MM_YY") = Me.MM And DLookup("cyear", "qry_MM_YY") = Me.YY Then

        MsgBox "You cannot enter a deposit using a closed month Date."

        Me.DDATE.SetFocus

    End If

    If DLookup("Dataset", "tblDataset") = "PriorYear" Then

        Exit Sub

    End If

On Error GoTo Err_OKSaveRecord_Click

If Me.YAMOUNT = 0 Then

    Exit Sub

Else

    rsGLXfer.AddNew

    rsGLXfer!FromGLAcct = Me.FromGLAcct

    rsGLXfer!ToGLAcct = Me.ToGLAcct

    rsGLXfer!YAMOUNT = Me.YAMOUNT

    rsGLXfer!DDATE = Me.DDATE

    rsGLXfer!CDOCUMENT = "Deposit"

    rsGLXfer!CNOTE = Me!CNOTE    <-- right here is where it (sometimes) "breaks" whether Me!CNote is null or not.

    rsGLXfer!IsClosed = False

    rsGLXfer!DateAdded = Date

    rsGLXfer!NSCHOOLID = 0

    rsGLXfer!Random = 0

    rsGLXfer!VoidDate = Null

    If DLookup("NNextGLXfer", "ActPref") = 0 Or IsNull(DLookup("NNextGLXfer", "ActPref")) Or DLookup("NNextGLXfer", "ActPref") = "" Then

        rsGLXfer!GLXferNum = 1

    Else

        rsGLXfer!GLXferNum = DLookup("NNextGLXfer", "ActPref")

    End If

    rsGLXfer.Update  

End if

End Sub

The error seems to be specific to one customer's data file (using split app and data files) running the Access 2007 runtime. We can replicate the error using their data (usually, but not always) from our development and test machines. Any ideas what's causing this?

Thanks

DRL

Answer 1

Doug,

Hmmm, permenant... as close as you can get without upsizing the backend.  I will say that I have not experienced corruption in a looooooonnnnnggggg time now.  (I can't even remember when but I do break out my memo fields IF I use them at all.  Normally, (Text, 255) is more notes then most people will read let alone type.

I would run a query that determines just how much text is in the Memo Fields and if it's a small number of older posts AND it's not critical, adjust the field(s).

One of those links shows *likely* causes of corruption... so as long as you avoid those you should be fine!

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

Answer 2

Gina,

Will the new back-end be a permanent fix? I'll put some thought into that...

But the key words may be "if I have to use memo fields." The memo field I'm fighting was totally unnecessary, the users only enter a short comment. So, as you say, some Friday night work may be in order. We do have code that updates the back-end data model so perhaps I can throw some code in there and replace the memo field with a nice friendly text field.

Thanks for your help.

Doug

Answer 3

Doug,

Ummm, soon you will have no choice, so as much as it pains you I suggest you come up with a plan to create a new backend.  (Perhaps a Friday night thing?)  As you mentioned it's happening more frequently now...

I know this may not be an option but I will mention anyway (just in case you can)... typically, if I hae to use Memo fields for notes I put them in a table all by themselves linked back to the Parent table.  Why?  Because if anything is going to get corrupt it's going to be the Memo field and I'd rathwer lose that then everything.  (And with daily back ups of the Server, at most I'll lose a day.)

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

Answer 4

Thanks Gina,

The backup-> backup -->new database is not really an option: we have thousands of customers scattered all over the US!

This problem is now appearing in more than one customer file, so I really need to resolve this. I think the memo field is the culprit and it may be partially because the users use the # key a lot.

The application gives users the ability to do a repair and compact, which does fix this for a short time, but they aren't happy doing it every day.

Doug

Answer 5

DRL,

Sounds like the begining of corruption...

http://www.granite.ab.ca/access/corruption/symptoms.htm.  I would make a

back-up, make another back-up and then first try importing everything into a

new database.  Also, read this article...

http://www.granite.ab.ca/access/corruption/retrievedata.htm.  And after all

of that I would finish off with this article...

http://allenbrowne.com/ser-25.html and see if any of those circumstances

apply to you.

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.