Make MDE Error

[AC5FF]

I am currently trying to get my overused DB into a more manageable/safer FE/BE setup. While I finally figured out HOW to correctly split my DB so that it would work, I now would like to add in the security to keep people from 'poking' around with things they shouldn't. The DB was originally set up with this in mind, but anyone with any knowledge of Access can find the Navigation bar and go in to edit/delete/etc.. wherever they want. Making the MDE seemed like the easiest/best method to me...

But now when I try to 'Make MDE' (Access2007) I get the following error:

Microsoft Office Access was unable to create the .accde, .mde, or .ade file

This error is usually associated with compiling a large database into an MDE file.* Because of the method used to compile the database, a considerable number of TableID references are created for each table.* The Access database engine can only create a maximum of 2048 open TableIDs at one time.* Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Access database engine uses during the process of compiling a database as an MDE.* However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.

Yes, my DB is huge. I've not done a complete count, but I believe we've got well over 500 tables/queries/forms/macros/reports. Any suggestions on how I can get past this error and create the MDE?

Thx

 

[boblarson]

Go to the VBA Window and go to DEBUG > COMPILE and it should show you any errors which are keeping it from compiling.

 

[AC5FF]

Okay; doing this found an error right off the bat. Yet, this section of code 'appears' to be running correctly because it never errors out when I run it from within Access.

The error line is:
"If Me.Check8.Value = False Then"
highlighted on the 'check8'
(in the 'Check all SRUs in 2413 Not Yet Received' section)

Here's the whole sub:

Public Sub CheckSRU()
Dim sql As String
Dim D23Date As Date

D23Date = [Form_Config subform].[D23 Date]
[Form_MD5InitMapData].[D23 Date] = D23Date
[Form_MD5InitMapData].Refresh



'****************************************************************************************
'**** Check all SRUs in 2413 Not yet recieved
'****************************************************************************************
sql = "SELECT DISTINCTROW [2413].Team, [2413].[Submit Date], [2413].[Rec Date], [2413].Doc, D23.DOC, [2413].Found, D23.LOC, [2413].[Mark For], D23.MarkFor, [2413].NSN, D23.NSN, [2413].QTY, D23.QTY, [2413].Noun, D23.NOMENCLATURE, D23.STAIND, D23.CUR, D23.ISUDAY, D23.AWPDAY, D23.STADAY, D23.DELDAY, D23.EDD, D23.ERC "
sql = sql & "FROM 2413 LEFT JOIN D23 ON [2413].Doc = D23.DOC "
sql = sql & "WHERE ((([2413].Team)='" & [Form_MD5InitMapData]!Name & "') AND (([2413].[Rec Date]) Is Null));"

Me.RecordSource = sql
Do While Not Me.Recordset.EOF
With Me.Recordset

'Not in D23 or D19
If IsNull(.[d23.doc]) Then
If .[Found] Then
If Me.Check8.Value = False Then
WriteBounce .[2413.doc], "Appears to have been received. Please review status.", False, True
Else
WriteBounce .[2413.doc], "Appears to have been received. Updating 2413. Please review status.", False, True
.edit
.[Rec Date] = Date
.Update
End If
Else
If IIf(IsNull(.[Submit Date]), True, .[Submit Date] < D23Date) And Format(Date, "ddd") = "Wed" Then
'Monitor XB3s not tracked in D23 or D19
WriteBounce .[2413.doc], .[NOUN] & " has never been found in D23 or D19. Please review status.", False, True
End If
End If
Else

'********************************
'***** Is in D23 and/or D19 *****
'********************************
If Not .[Found] Then
.edit
.[Found] = True
.Update
End If

'***** Check EDD If Listed *****
If IsNull(.EDD) Then
Dtemp = Date + 1
Else
Dtemp = To_Date(.[EDD])
End If
If Dtemp + 30 < Date Then
WriteBounce .[2413.doc], "D23 lists EDD date:" & Dtemp & " Overdue by " & Date - To_Date(.[EDD]) & " Days. Please review.", False, True
End If

'Check Status
If Not IsNull(.[STAIND]) And .[STAIND] <> "CRT" Then
WriteBounce .[2413.doc], .[NOUN] & " requires a turn in to supply.", True, True
End If

If .[STAIND] = "ISU" Then
WriteBounce .[2413.doc], "Appears to have been received. Updating 2413. Please review status.", False, True
End If

'********Null Status*******
If IsNull(.[STAIND]) Then
If .[ERC] <> "XB3" And .[ERC] <> "XF3" Then
WriteBounce .[2413.doc], "D23 shows Blank Status. Change to appropriate status.", True, True
End If
End If



'******************************************
'*Check Maint Days **
'******************************************
If Not (.[Cur] = "AWI" Or .[Cur] = "AWM" Or .[Cur] = "AWF" Or .[Cur] = "INW" Or .[Cur] = "FWP") Or IsNull(.[Cur]) Then
'ASSUME $WP status
MaintTemp = 0
Else
'Assume AWF status
MaintTemp = IIf(.[STAIND] = "N/C" Or .[STAIND] = "CRT", 0, (Date - D23Date) + .[ISUDAY])
End If
If MaintTemp > 2 And MaintTemp < 45 Then WriteBounce .[d23.doc], .Nomenclature & " has " & MaintTemp & " Repair Days!", True, True
If 60 - MaintTemp < 15 Then 'Getting close to carcass
If MaintTemp < 60 Then
WriteBounce .[d23.doc], .Nomenclature & " is " & 60 - MaintTemp & " DAYS FROM CARCASS! *** NOTIFY " & Form_MD5InitMapData.[POC] & " ***", True, True
Else
WriteBounce .[d23.doc], .Nomenclature & " is " & MaintTemp - 60 & " DAYS CARCASSaasdlkfjhawe! *** NOTIFY " & Form_MD5InitMapData.[POC] & " ***", True, True
End If
End If


'Check NSN
If .[D23.NSN] <> .[2413.nsn] Then
.edit
.[2413.nsn] = .[D23.NSN]
.Update
WriteBounce .[2413.doc], .[NOUN] & " NSN mismatch. repaired with D19 NSN:" & .[D23.NSN] & ".", False, True
End If

'Check Markfor
If Not IsNull(.[Markfor]) And .[Markfor] <> .[Mark For] Then
.edit
.[Mark For] = .[Markfor]
.Update
WriteBounce .[2413.doc], .[NOUN] & " Mark For mismatch. Repaired with D19 Mark For: " & .[Markfor] & ".", False, True
End If

'Check QTY
If .[2413.QTY] <> .[D23.QTY] Then
.edit
.[2413.QTY] = .[D23.QTY]
.Update
WriteBounce .[2413.doc], .[NOUN] & " had wrong QTY: Updated with D23 QTY: " & .[D23.QTY] & ".", False, True
End If

'Check Location
If [Form_MD5InitMapData]!Location <> .[LOC] Then
WriteBounce .[2413.doc], .[NOUN] & " shows D23 location: " & .[LOC] & ".", False, True
End If

End If 'If IsNull(.[d23.doc]) Then
End With

DoEvents: Me.Recordset.MoveNext
Loop


'****************************************************************************************
'**** Check all D23/D19 Entries assigned area, not in 2413, or appear recvd
'****************************************************************************************
sql = "SELECT lru.Abbr, D23.LOC, D23.DOC, [2413].Doc, [2413].[Submit Date], [2413].[Mark For], D23.MarkFor, D23.QTY, [2413].QTY, D23.NSN, [2413].NSN, D23.NOMENCLATURE, [2413].Noun, [2413].Team, [2413].Found, [2413].[Rec Date], D23.STAIND, D23.CUR, D23.ISUDAY, D23.AWPDAY, D23.STADAY, D23.DELDAY, [2413].employee "
sql = sql & "FROM (D23 LEFT JOIN 2413 ON D23.DOC = [2413].Doc) LEFT JOIN lru ON D23.NSN = lru.NSN "
sql = sql & "WHERE (((lru.Abbr) Is Null) AND ((D23.LOC)='" & [Form_MD5InitMapData]!Location & "') AND (Not ([2413].[Rec Date]) Is Null)) OR (((lru.Abbr) Is Null) AND ((D23.LOC)='" & [Form_MD5InitMapData].Recordset.[Location] & "') AND (([2413].Doc) Is Null));"


Me.RecordSource = sql

Do While Not Me.Recordset.EOF

With Me.Recordset

'******************************************
'*Check Maint Days **
'******************************************
If Not (.[Cur] = "AWI" Or .[Cur] = "AWM" Or .[Cur] = "AWF" Or .[Cur] = "INW" Or .[Cur] = "FWP") Or IsNull(.[Cur]) Then
'ASSUME $WP status
MaintTemp = 0
Else
'Assume AWF status
MaintTemp = IIf(.[STAIND] = "N/C" Or .[STAIND] = "CRT", 0, (Date - D23Date) + .[ISUDAY])
End If
If MaintTemp > 2 And MaintTemp < 45 Then WriteBounce .[d23.doc], .Nomenclature & " has " & MaintTemp & " Repair Days!", True, True
If 60 - MaintTemp < 15 Then 'Getting close to carcass
If MaintTemp < 60 Then
WriteBounce .[d23.doc], .Nomenclature & " is " & 60 - MaintTemp & " DAYS FROM CARCASS! *** NOTIFY " & Form_MD5InitMapData.[POC] & " ***", True, True
Else
WriteBounce .[d23.doc], .Nomenclature & " is " & MaintTemp - 60 & " DAYS CARCASS! *** NOTIFY " & Form_MD5InitMapData.[POC] & " ***", True, True
End If
End If

If IsNull(.[2413.doc]) Then
'Needs added to 2413
[Form_MD5Parts SubForm].Recordset.AddNew
[Form_MD5Parts SubForm].Recordset.[Doc] = .[d23.doc]
[Form_MD5Parts SubForm].Recordset.[Submit Date] = Extract(.[d23.doc])
[Form_MD5Parts SubForm].Recordset.[Mark For] = .[Markfor]
[Form_MD5Parts SubForm].Recordset.[QTY] = .[D23.QTY]
[Form_MD5Parts SubForm].Recordset.[NSN] = .[D23.NSN]
[Form_MD5Parts SubForm].Recordset.[NOUN] = .[Nomenclature]
[Form_MD5Parts SubForm].Recordset.[Team] = [Form_MD5InitMapData]!Name
[Form_MD5Parts SubForm].Recordset.[Employee] = "Not Entered"
[Form_MD5Parts SubForm].Recordset.[Found] = True
[Form_MD5Parts SubForm].Recordset.Update
WriteBounce .[d23.doc], "Not in 2413. Adding.", False, True
Else
'Shows recieved in 2413, not so in D23/D19
If .[STAIND] <> "CRT" Then
WriteBounce .[d23.doc], .[NOUN] & " Shows received in 2413, still requires TIN to supply.", True, True
Else
If .[Rec Date] < D23Date Then WriteBounce .[d23.doc], .[NOUN] & " Shows received in 2413, still on order in D23/D19. Please review Status.", False, True
End If
End If
End With
DoEvents: Me.Recordset.MoveNext
Loop


End Sub

 

[AC5FF]

Wow, that ended up bigger than I thought...
I thought I'd post the section of VB to see if anyone had a clue why it would error there.
(I'm not much of a VB programmer yet... )

 

[boblarson]

Is this sub (Public Sub CheckSRU()) in the form's module or a standard module? If on a standard module then you would have to give the full form reference instead of ME. Also is Check8 still named that?

 

[AC5FF]

Bob; I think you're talking a little over my head here. Form Module or Standard Module; I'm not sure what you mean there.

If I had to guess from what I am seeing is that it is a Form Module. In my VBA window I have approximatly 55 'Objects'. Most are titled "Form_(formname)" and a few are titled "Report_(Report name)"

As for 'check8' still being called that... I'm not sure where it's pulling that from. I do know that this section of code is run when I press the "Bounce" button on my main form. But I'm not sure where to find where that "Check8" is coming from. The 'bounce' itself runs correctly - I do know that.

What I did notice, is that I have a section "Form_Bounce Subform" that contains this same code... every bit of it. But during the 'compile' it is erroring in "Form_Enterprise Collaboration" ... And then looking around I noticed that this same code is in several other places; but I have no clue as to why..... That just doesn't sound right to me..

 

[boblarson]

Form module - module which underlies a form (the module that opens up when you go to an event on a form or one of its controls

Standard module - the module you get if you go to the database window and click on MODULES and NEW.

If you can't find a Check8 on your form anywhere then it is probably code left over from when you started something and then renamed the control and didn't delete the existing code (which it won't do if you rename a control). So, make a copy of your database just as a precaution and then delete the part of the code that refers to Check8 and see what else pops up in compiling.

 

[AC5FF]

Update:
Since I'm working on a test copy anyway..
I went into these other "Form_XXX" areas that had the same code and deleted it all.
Re-run the compile and it didn't error out! I tested the buttons on my main form that were involved with areas I deleted code from and they 'appear' to be working. Going to have to do some more testing.

I then tried to 'Make MDE" again, and it worked!! Thanks Bob!
But, I was under the impression that a MDE file would not show you the Navigation Pane. When I run the MDE file I still have access to that ... Or, did I read something wrong ??

Thx Again!

 

[boblarson]

MDE files will let you have access to the database window UNLESS you use the RUNTIME.

You (and users) can also modify tables and queries in MDE files, but they can't modify forms, reports, macros or modules. So, if you are using MDE files with people that have full versions of Access you may want to investigate the removal of the F11 and Shift key for opening the database. There is code out there to do that (and I think it should be somewhere here on the forum).

 

[AC5FF]

I will look up removing F11/Shift options...

But, I thought MDE was using the Runtime?!? I.E. the MDE does not require Access to run - or what am I missing here?

 

[boblarson]

I will look up removing F11/Shift options...

But, I thought MDE was using the Runtime?!? I.E. the MDE does not require Access to run - or what am I missing here?

No, you can use the runtime to run MDE or MDB files (or ACCDB / ACCDE / ACCDR in 2007) but you can also run them in regular Access.

 

[boblarson]

But your users either need to have a full version of Access or the Runtime installed (the runtime doesn't automatically get installed - you or them need to install it).

 

[AC5FF]

Will have to read up on that one also.. Thx!