Corruption of Access MDE files

[Ralph Fico]

I have been experiencing a strange corruption problem with an
application I developed using MS Access 2000 and deployed using an .MDE
file. The application is being used in a networked environment where
the .MDE file resides on a network server and is accessed by several
users using different versions of Access (2000, 2002 and 2003). The
problem is as follows: The program is designed to manage bar-coded
inventory at client sites, which receive text file updates from me when
we ship in new material. The updates are delimited files, which are
imported using VB code when a form button is clicked. The program
worked fine during initial testing, but now seems to seems to hang right
at the beginning of the update process. When I have the customer send
me the .MDE file for testing on my machine it hangs every time I try to
run it. I originally had the database set to compact on close, which I
thought might be causing the problem due to the multiple versions of
Access, and it appeared to work a about two weeks, but then started
again. Are there known issues with MDE file corruption when using
multiple versions of Access and could it be corrected by using the .MDB
version instead or is there a better solution?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Thilo Immel]

Ralph,

I have been experiencing a strange corruption problem with an
application I developed using MS Access 2000 and deployed using an .MDE
file. The application is being used in a networked environment where
the .MDE file resides on a network server and is accessed by several
users using different versions of Access (2000, 2002 and 2003). The
problem is as follows: The program is designed to manage bar-coded
inventory at client sites, which receive text file updates from me when
we ship in new material. The updates are delimited files, which are
imported using VB code when a form button is clicked. The program
worked fine during initial testing, but now seems to seems to hang right
at the beginning of the update process. When I have the customer send
me the .MDE file for testing on my machine it hangs every time I try to
run it. I originally had the database set to compact on close, which I
thought might be causing the problem due to the multiple versions of
Access, and it appeared to work a about two weeks, but then started
again. Are there known issues with MDE file corruption when using
multiple versions of Access and could it be corrected by using the .MDB
version instead or is there a better solution?

Why don't you give each user a local copy of the .mde file?

Regards!

Thilo Immel
The Access Druid www.atroplan.com

[Tony Toews]

Ralph Fico <ra********@peerlessaerospace.com> wrote:

I have been experiencing a strange corruption problem with an
application I developed using MS Access 2000 and deployed using an .MDE
file. The application is being used in a networked environment where
the .MDE file resides on a network server and is accessed by several
users using different versions of Access (2000, 2002 and 2003).

You want to give each user their own copy of hte FE MDE.

I specifically created the Auto FE Updater utility so that I could make changes to
the FE MDE as often as I wanted and be quite confident that the next time someone
went to run the app that it would pull in the latest version. For more info on the
errors or the Auto FE Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the FE on each PC up
to date.

In a Terminal Server or Citrix environment the Auto FE Updater now supports creating
a directory named after the user on a server. Given a choice put the FE on the
Citrix server to reduce network traffic and to avoid having to load objects over the
network which can be somewhat sluggish.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Ranger1]

Thanks for the splitting advice. I will try that and see if it resolves
the problem.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ranger1]

I tried splitting the database into a FE and BE as you suggested, but
now am getting the following errors (which I did not get before) in a
couple modules of my application, which I am not sure how to address.

Any ideas on how to fix this?

Error code: Runtime error 3219 "Invalid Operation"

Here is the code that debug points me to as the source of the error
(note the debug marker highlights on the line " Set rstOrdRec =
db.OpenRecordset("tblReorderItems", dbopentable)
")

Private Sub btnUpdateStock_Click()

Dim strPafPart As String 'Variable to hold peerless part
number from update stock form
Dim intOnhandQty As Integer 'Holds onhand qty from
tblMastLicTot
Dim intSafeStock As Integer 'Holds safety stock value from
tblSafetyStock
Dim db As DAO.Database
Dim rstOrdRec As DAO.Recordset

Set db = CurrentDb()
Set rstOrdRec = db.OpenRecordset("tblReorderItems", dbopentable)
DoCmd.OpenQuery "qappManLic" 'Append copy of Master License
to Used Table for each ManUpd checked record
DoCmd.OpenQuery "qdelManUpdLic" 'Delete Used License From Master
Table for each ManUpd checked record
DoCmd.DeleteObject acTable, "tblMastLicTot"
DoCmd.OpenQuery "qmakMastLicTot" 'Reruns master total table
creation for use in comparing items whic may be below SS levels

If IsNull(DLookup("[totqty]", "tblmastlictot",
"[pafpart]=cbopafpart")) Then
intOnhandQty = 0
Else
intOnhandQty = DLookup("[totqty]", "tblMastLicTot",
"[pafpart]=cboPafPart")
End If

intSafeStock = DLookup("[reorderpoint]", "tblSafetyStock",
"[pafpart]=cboPafPart")
'Run reorder checks to see if modifications trigger reorder
If intOnhandQty < intSafeStock Then
If IsNull(DLookup("[pafpart]", "tblReorderItems",
"[pafpart]=cbopafpart")) Then
If IsNull(DLookup("[pafpart]", "tblMastLicTot",
"[pafpart]=cbopafpart")) Then
With rstOrdRec
.AddNew
![reqdate] = Date
![PAFPART] = Me!cboPafPart
![curqty] = intOnhandQty
![maxqty] = DLookup("[MaxStockQty]",
"tblSafetyStock", "[pafpart]=cbopafpart")
![REORDERPOINT] = DLookup("[reorderpoint]",
"tblSafetyStock", "[pafpart]=cbopafpart")
![ordqty] = DLookup("[MaxStockQty]",
"tblSafetyStock", "[pafpart]=cbopafpart")
.Update
End With
Else
DoCmd.OpenQuery "qappReOrderMatchMan" 'Append items to
be reordered to reorder table
End If
Else
DoCmd.OpenQuery "qupdReorderChgMan" 'update existing
re-order record
End If
'Email send command rem's out in favor of batch send controlled
by remove stock form timmer event
'DoCmd.RunMacro "mcrSendReorderEmail"
End If

DoCmd.Requery "qlkpMastLicDet subform" 'Requery subform data
End Sub
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

You cannot use the "dbOpenTable" option on a linked table. Remove that
argument from the OpenRecordset method and it will use the default of
dbDynaSet, which works just fine with linked tables.

Larry Linson
Microsoft Access MVP

"Ranger1" <ra*****@devdex.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

I tried splitting the database into a FE and BE as you suggested, but
now am getting the following errors (which I did not get before) in a
couple modules of my application, which I am not sure how to address.

Any ideas on how to fix this?

Error code: Runtime error 3219 "Invalid Operation"

Here is the code that debug points me to as the source of the error
(note the debug marker highlights on the line " Set rstOrdRec =
db.OpenRecordset("tblReorderItems", dbopentable)
")

Private Sub btnUpdateStock_Click()

Dim strPafPart As String 'Variable to hold peerless part
number from update stock form
Dim intOnhandQty As Integer 'Holds onhand qty from
tblMastLicTot
Dim intSafeStock As Integer 'Holds safety stock value from
tblSafetyStock
Dim db As DAO.Database
Dim rstOrdRec As DAO.Recordset

Set db = CurrentDb()
Set rstOrdRec = db.OpenRecordset("tblReorderItems", dbopentable)
DoCmd.OpenQuery "qappManLic" 'Append copy of Master License
to Used Table for each ManUpd checked record
DoCmd.OpenQuery "qdelManUpdLic" 'Delete Used License From Master
Table for each ManUpd checked record
DoCmd.DeleteObject acTable, "tblMastLicTot"
DoCmd.OpenQuery "qmakMastLicTot" 'Reruns master total table
creation for use in comparing items whic may be below SS levels

If IsNull(DLookup("[totqty]", "tblmastlictot",
"[pafpart]=cbopafpart")) Then
intOnhandQty = 0
Else
intOnhandQty = DLookup("[totqty]", "tblMastLicTot",
"[pafpart]=cboPafPart")
End If

intSafeStock = DLookup("[reorderpoint]", "tblSafetyStock",
"[pafpart]=cboPafPart")
'Run reorder checks to see if modifications trigger reorder
If intOnhandQty < intSafeStock Then
If IsNull(DLookup("[pafpart]", "tblReorderItems",
"[pafpart]=cbopafpart")) Then
If IsNull(DLookup("[pafpart]", "tblMastLicTot",
"[pafpart]=cbopafpart")) Then
With rstOrdRec
.AddNew
![reqdate] = Date
![PAFPART] = Me!cboPafPart
![curqty] = intOnhandQty
![maxqty] = DLookup("[MaxStockQty]",
"tblSafetyStock", "[pafpart]=cbopafpart")
![REORDERPOINT] = DLookup("[reorderpoint]",
"tblSafetyStock", "[pafpart]=cbopafpart")
![ordqty] = DLookup("[MaxStockQty]",
"tblSafetyStock", "[pafpart]=cbopafpart")
.Update
End With
Else
DoCmd.OpenQuery "qappReOrderMatchMan" 'Append items to
be reordered to reorder table
End If
Else
DoCmd.OpenQuery "qupdReorderChgMan" 'update existing
re-order record
End If
'Email send command rem's out in favor of batch send controlled
by remove stock form timmer event
'DoCmd.RunMacro "mcrSendReorderEmail"
End If

DoCmd.Requery "qlkpMastLicDet subform" 'Requery subform data
End Sub
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

"Larry Linson" wrote

You cannot use the "dbOpenTable"
option on a linked table. Remove that
argument from the OpenRecordset method
and it will use the default of dbDynaSet,
which works just fine with linked tables.

OOPS... for "dbDynaSet" in the original, read "dbOpenDynaSet".

Larry Linson
Microsoft Access MVP

[Ranger1]

Thanks. Removing the db.Opentable reference worked fine. I also got a
tip from someone that I should conver the DAO references to ADODB, which
it turns out seemed to solve the problem as well. Thanks again to
everyone who responded.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

"Ranger1" <ra*****@devdex.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

Thanks. Removing the db.Opentable reference worked fine. I also got a
tip from someone that I should conver the DAO references to ADODB, which
it turns out seemed to solve the problem as well. Thanks again to
everyone who responded.

Moving from the familiar DAO, which is the native language of the Jet
database engine, to ADO, which is intended primarily for access to server
databases (and, overall, is not "better" than DAO) seems a drastic solution
to just not realizing that you can't use the table-type recordset in linked
tables.

You don't, by the way, have to convert to ADO -- instead of opening the
linked table, you can open the tables database directly and still use
OpenTable, but that is not as simple as just using the linked tabledef as a
dynaset. And, unless you are dealing with extraordinary volumes of data,
you'll not notice the performance difference sitting in front of the
monitor.

Larry Linson
Microsoft Access MVP