MS Access 2007 VBA: crash on Set rst = qdf.OpenRecordset , , dbForwardOnly

Hi All,

Access is crashing on server machines when running the line "Set rst = qdf.OpenRecordset , , dbForwardOnly". This is a full crash, and the exception given is:

Problem signature:
  Problem Event Name:      APPCRASH
  Application Name:      MSACCESS.EXE
  Application Version:      12.0.6606.1000
  Application Timestamp:      4e27ab6b
  Fault Module Name:      MSVCR80.dll
  Fault Module Version:      8.0.50727.4940
  Fault Module Timestamp:      4ca2b271
  Exception Code:      c0000005
  Exception Offset:      000172d7
  OS Version:      6.1.7601.2.1.0.1296.17
  Locale ID:      2057

Additional information about the problem:
  LCID:      1033
  Brand:      Office12Crash
  skulcid:      1033

Read our privacy statement online:
  http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

If the online privacy statement is not available, please read our privacy statement offline:
  C:\Windows\system32\en-US\erofflps.txt

It's a 'regular' query, has been working for ages and I cannot replicate the crash when using my local testbed, only happens on production. This I suppose could mean to do with locking, or operating systems (64 Win Svr 2008 R2).

Does anyone have experience of this/have any ideas? I have tried decompiling and recompiling/compacting etc.

Many thanks!
Katerina
katerina-pAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

katerina-pAuthor Commented:
Additionally:

OK, does not crash on the old server either (Win 2003).
0
katerina-pAuthor Commented:
Old server has the following files which the new doesn't. Is this the problem?

\Windows\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.762_x-ww_6b128700\msvcr80.dll

..\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd\msvcr80.dll
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
msvcr80 is the "shared C runtime component":

http://support.microsoft.com/kb/326922

Looks like it's installed on both machines, just in different locations. You could look into the server's Installed Programs to see if the C Runtime is installed, but I'm betting it is. If this were the trouble, however, I'd suspect that many more apps would be crashing on that machine. C is used for many programs, and a corrupt C library or runtime would wreak a lot more havoc.

What does the app do? Generally you don't run Access apps on servers, so I'm curious why the need to have that app on a server machine.

Is this a compiled file (.accde/.mde)?

Are you running Access in the full Retail build, or are you using the Access Runtime? If the Runtime, are you running the 2007 or 2010 runtime?

Have you tried reinstalling/repairing Office/Access on the server?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

datAdrenalineCommented:
Are you fully patched with respect to Access 2007? ... ie. All service packs and such.
0
Jim P.Commented:
Can we see the appropriate chunk of code including the variable declaration?

And on the affected machines go to the VBA window and open the Tools --> References and see if any are marked as MISSING.
0
katerina-pAuthor Commented:
Thanks guys.

It's an accdb. The code is nothing special -
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim iSite as Integer
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryUpdER03")
          fAddParamQDF qdf, "bLMS", False
          fAddParamQDF qdf, "iSite", iSite
          fOpenRecordset , rst, , dbOpenSnapshot, , , , qdf

Open in new window

I have the add params and open rst in subs with error handling, but other than that they do nothing special:
Public Sub fAddParamQDF(ByRef qdf As DAO.QueryDef, ByVal ParamName As String, ByVal ParamVal)
...
qdf.Parameters(ParamName) = ParamVal
...
End Sub

Open in new window

Public Function fOpenRecordset(Optional ByRef dbs As DAO.Database, Optional ByRef rst As DAO.Recordset, Optional ByVal sRST As String, Optional ByVal sOpenType As _
    Variant, Optional ByVal sOptions As Variant, Optional ByVal sLockEdit As Variant, Optional bHandleErrors As Boolean = _
    False, Optional ByRef qdf As DAO.QueryDef) As Boolean


....


  Set rst = qdf.OpenRecordset(sOpenType, sOptions, sLockEdit)


...
End Function

Open in new window

The accdb manages APIs into online retail platforms, and links back to about 30 Access backends with tthe data in. However this query is just a simple select query (despite 'qryApp...' name). No references are broken. I'm just checking for non-essential updates to see if it changes it.

Many thannks
0
Jim P.Commented:
I've always done it more this way:
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim SQL as String
Dim iSite as Integer
Set dbs = CurrentDb

SQL = "SELECT <fieldlist> FROM <TblName> WHERE iSite = " & iSite " " & _
         "and bLMS=  False"

Set rst = dbs.OpenRecordset(SQL)

Open in new window


But the big one I wanted to see was if the "DAO." qualifier was there.

Have you tried doing a compile on the offending machines?
0
katerina-pAuthor Commented:
Yep, decompiled, compiled, compacted & repaired, updated both Office and Windows, restarted servers, etc. No luck!
0
katerina-pAuthor Commented:
The query opens fine if I copy the SQL and open in a Query object manually. However it crashes Access when using set rst = dbs.openrecordset. What would cause this?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The query opens fine if I copy the SQL and open in a Query object manually. However it crashes Access when using set rst = dbs.openrecordset. What would cause this? >>

 You need to pin this down a bit more; take a copy of a DB where it does run and they try it on this new server (not sure if you've actually done this or not).

  If it runs, then it was VBA project corruption.  If not, then it's something in the environment of the new server and if it's just this query, I would suspect your tripping over a bug based on the specific syntax of that SQL statement.

  Also you said you cut and paste the SQL; did you literially do that?  I mean execute and let the SQL statement build and then copy paste right from the debug window?   If not, try that as well and make sure the statement executes.

Jim.
0
katerina-pAuthor Commented:
Thanks Jim - I opened the other Q as a more Windows OS approach to the dll referenced in the crash rather than just an Access-specific question - apologies if I wasn't supposed to.

I'm running through all sorts of test and will update when I've pinned it down. I was hoping the exception notes in the OP and the .dll referenced would mean something to someone.
0
Michael PfisterCommented:
It looks like Access stumbles over an updated MSVCR80.DLL.

On your old server it uses the one that comes with Access 2007 without SP1. It seems MS has updated this file several times due to vulnerabilities.

The MSVCR80.DLL version 8.0.50727.4940 seems to cause issues for multiple products

Try installing this hotfix

http://support.microsoft.com/kb/2538242/en-us

which should update msvcr80.dll to 8.0.50727.6195

I'm not sure that is the latest version.
0
Michael PfisterCommented:
This should be the correct download link

http://www.microsoft.com/en-us/download/details.aspx?id=26347
0
katerina-pAuthor Commented:
Found a workaround / isolated to the following.

The query, amongst other tables, contained a single table twice, i.e., a self-referencing table. This table, tblCategoryRef Aliased as tblChildCat and tblParentCat, has primaryK nesc_id. It also has a non-required field called nesc_ParentCat which points back to nesc_id.

I've isolated the self-referencing tables to a separate query:
SELECT tblD.*, tblChildCat.*, tblParentCat.* FROM (tblCategoryRef AS tblChildCat LEFT JOIN tblCategoryRef AS tblParentCat ON tblChildCat.nesc_ParentCat = tblParentCat.nesc_ID) INNER JOIN tblD ON tblChildCat.nesc_ID = tblD.d_ESCR;

Open in new window

This now runs fine. The structure has not been changed to the query which will not run:
SELECT [...]
FROM (tblCategoryRef AS ChildCat LEFT JOIN tblCategoryRef AS ParentCat ON ChildCat.nesc_ParentCat = ParentCat.nesc_ID) INNER JOIN (qryIsbnData INNER JOIN tblD ON qryIsbnData.isbn_dewey = tblD.[dewey_#]) ON ChildCat.nesc_ID = tblD.dewey_ESCF;

Open in new window

For information, 'qryIsbnData' as referenced in that query is simply
SELECT tblIsbnDataA.*, tblIsbnDataB.isbn_desc
FROM tblIsbnDataA INNER JOIN tblIsbnDataB ON tblIsbnDataA.isbn_isbn = tblIsbnDataB.isbn_isbn;

Open in new window


tblIsbnDataB.isbn_desc is a memo field. Removing tblISBNDataB from the query also allows it to complete fully.


So the two workarounds are: 1/ remove the table with [this particular] memo field from the query, 2/ send the self-referencing table out to another query and pull in, along with the memo field in question, to a higher-level query.

Any ideas what's going on? Note that for the original query to crash, the memo field doesn't even have to be included in the SELECT, the table merely has to be joined in the same query as the self-referential tables (and that only self-references once - child to parent). I've checked and there's no circular references.

Thanks all.
K.
0
katerina-pAuthor Commented:
mpfister, thanks for your help. I've downloaded the update, however the crash still occurs (have now realised it's on multiple queries):


Problem signature:
  Problem Event Name:      APPCRASH
  Application Name:      MSACCESS.EXE
  Application Version:      12.0.6606.1000
  Application Timestamp:      4e27ab6b
  Fault Module Name:      MSVCR80.dll
  Fault Module Version:      8.0.50727.6195
  Fault Module Timestamp:      4dcddbf3
  Exception Code:      c0000005
  Exception Offset:      000172d7
  OS Version:      6.1.7601.2.1.0.1296.17
  Locale ID:      2057

Additional information about the problem:
  LCID:      1033
  Brand:      Office12Crash
  skulcid:      1033

Read our privacy statement online:
  http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

If the online privacy statement is not available, please read our privacy statement offline:
  C:\Windows\system32\en-US\erofflps.txt
0
Jim P.Commented:
Memo fields have always had problems in Access.

ISBN sounds like you're dealing with books.  Is there anyway you could create a 250 character text field and take the first 250 characters and then have a "Read More ..." button that would pull the rest up in a popup box, or something similar?

You haven't really indicated how the results are being used.
0
katerina-pAuthor Commented:
jimpen, thanks for your comment. Yes, I have been 'aware' of issues with memo fields. In this particular case, I have indeed done a workaround for the query which avoids the memo field being pulled in. However, I've now got the crash on another query. Memo fields are used all over the place in this system, although the fields typically only require a few thousands characters. So I really need to find out what's causing these crashes rather than just doing workarounds for them, all, because the queries are 'good' in that they work on my production machine but not the server.
0
Jim P.Commented:
If you change the query to

SELECT tblIsbnDataA.FldName1, tblIsbnDataA.FldName2, 
     tblIsbnDataA.FldName3, [...], tblIsbnDataB.isbn_desc
FROM tblIsbnDataA INNER JOIN tblIsbnDataB ON tblIsbnDataA.isbn_isbn = tblIsbnDataB.isbn_isbn;

Open in new window


Or in other words, use the actual field names, do you get a failure still?

And is it possible one of the tables was altered that the total columns are over 255 columns now? Or the total fields count in the query over 255?
0
katerina-pAuthor Commented:
Will try changing the query tomorrow.

A single table certainly doesn't have over 255 columns, nor a single query I don't believe. Again, I'll check tomorrow - however all of these queries work fine on my dev machine so I don't think I could have exceeded 255 anywhere.
0
katerina-pAuthor Commented:
No luck on either of those suggestions I'm afraid.
0
Jim P.Commented:
So I really need to find out what's causing these crashes rather than just doing workarounds for them, all, because the queries are 'good' in that they work on my production machine but not the server.

I was re-reading the thread and caught this. I hope you meant test machine but not production?

Is the old 2003 server 32 or 64 bit?

Is the test machine 64 bit?

Is it a web page accessing it? What happens if you reset IIS?
0
katerina-pAuthor Commented:
I was re-reading the thread and caught this. I hope you meant test machine but not production?

Yes sorry, I meant development/test machine!

No problems on:
DevMachine: 64 bit Win 8
SVR3: 32 bit Win Svr 2003 SP2
SVR2: 32 bit Win Svr 2003 R2 SP2

Crashes on:
SVR1: 64 bit Win Svr 2008 R2

Happens whether is accdb or accde


Along with the windows error message shown in #39481275, I get a restart button - clicking this results in a subsequent windows msgbox error stating
"The instruction at 0x73a672d7 referenced memory at 0x00000000. The memory could not be written. Click OK to terminate program."
although Access does indeed restart OK.

Example of query which crash (I note it's the same table as in #39480055)

DELETE tblSTA.sta_d_id, tblSTA.*
FROM tblIsbnDataA INNER JOIN tblSTA ON tblIsbnDataA.isbn_13dbl=tblSTA.sta_isbn13
WHERE (((tblSTA.sta_d_id)<>[isbn_d_id]));

Open in new window


Now, if I execute this manually (click on the object in object pane, hit enter) it runs fine and executes. Additionally, Design View and SQL view are fine. However, the crash occurs on DataSheet View and from VBA (1)



(1)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
dbs.Execute "qryDelIncorrectAssigned"
Set dbs = Nothing

Open in new window

1.jpg
0
katerina-pAuthor Commented:
Jim, to follow up on your post:

You need to pin this down a bit more; take a copy of a DB where it does run and they try it on this new server (not sure if you've actually done this or not).

  If it runs, then it was VBA project corruption.  If not, then it's something in the environment of the new server and if it's just this query, I would suspect your tripping over a bug based on the specific syntax of that SQL statement.

  Also you said you cut and paste the SQL; did you literially do that?  I mean execute and let the SQL statement build and then copy paste right from the debug window?   If not, try that as well and make sure the statement executes.

Jim.

Have done all your suggestions, and so I think we've left with the environment of the new server being the culprit.
0
katerina-pAuthor Commented:
Not only does
DELETE tblSTA.sta_d_id, tblSTA.*
FROM tblIsbnDataA INNER JOIN tblSTA ON tblIsbnDataA.isbn_13dbl=tblSTA.sta_isbn13
WHERE (((tblSTA.sta_d_id)<>[isbn_d_id]))

Open in new window

; cause the crash, so does
SELECT tblSTA.sta_d_id, tblSTA.*
FROM tblIsbnDataA INNER JOIN tblSTA ON tblIsbnDataA.isbn_13dbl=tblSTA.sta_isbn13
WHERE (((tblSTA.sta_d_id)<>[isbn_d_id]));

Open in new window

0
katerina-pAuthor Commented:
Table of test results
\bin\ has the front ends, \db\ has the data stores.

OK, so this points to there being some 'corruption' in one of either \db\BEorig1.accdb or \db\BEorig2.accdb. I put corruption in scare quotes because the other servers have the exact same front ends, and link to the exact same backends, and they haven't been crashing.
The 'corrupted' back ends were created on the old server, and subsequently migrated to the new server. The non-crashing back ends I created directly on the new server.
0
Jim P.Commented:
I ran into a similar issue a few months back. I was developing an Acc2003 DB on my Win7 64 bit machine and then would hand it off to the users to uses on a Win2K3 32 bit servers. It would crash.

But if I did a recompile on the servers then it would work. So I did the dev on my machine, copied it to a 2k3 server and then passed it on.
0
katerina-pAuthor Commented:
Still having the same issue; the new database(s) are failing after 1-3 days'. Once replaced it/they seem to work again fine, until they don't...
0
Jim P.Commented:
Are you doing a recompile on the production server and using that?
0
katerina-pAuthor Commented:
Hi jimpen,

Yes I have.

K.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Still having the same issue; the new database(s) are failing after 1-3 days'. Once replaced it/they seem to work again fine, until they don't...
Then something is amiss on the server, either in the Office or Windows installation. Unfortunately I don't know of any way to trace down the actual culprit, and you couldn't really do much even if you did find it - system files have such a long dependency chain that you can't really just pluck one component out and expect it to work properly.

Any chance of a reinstall of Office on the machine?

Is the server fully patched and updated, with all relevant service packs?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
As a suggestion, try removing the forward only.  That may change the operation enough so that it doesn't crash.

I'm with LSM though; problem appears to be the server.  I'd make sure it's fully patched and updated, would re-install Office, and then if still an error, it's time to get it to Microsoft.

Jim.
0
katerina-pAuthor Commented:
LMS, Jim,

OK thanks - will try the full reinstalls (etc, etc...) as and when possible.

K.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Try removing the forward only first.  While that will give you a heavier cursor, it may avoid the bug.

 It's an easy change and also would have the least impact.

Jim.
0
datAdrenalineCommented:
I rarely use dbOpenForwardOnly as I find that the option slows things down -- despite the documentation and logic that indicate its the "fastest" type of recordset.  I use dbOpenSnapshot if I need the recordset to be static, but typically leave the argument blank and let DAO decide what it can do.  DAO trys for dbTableDirect first, but that only works for local (to the DAO database object calling the .OpenRecordset method), then falls back to dbOpenDynaset.  dbOpenDynaset has the ability to open "faster" (maybe buffered, or "streaming", or "lazy load" is a better descriptor).  Also, you may find more success in trying to force the database engine to employ Rushmore optimization by forcing at least 2 index scans, even if its the same index.  For example criteria like "WHERE pkId > 0 Or pkId <=0".  I know that criteria will pass all records, but it forces Rushmore technology to be employed.

I have no past experience to suggest what I did, but my thought is to force the supporting DLLs down a different execution branch in the hopes of avoiding the crash.

Other thoughts ... when running an Action Query, try using a DoCmd method (.RunSQL or .OpenQuery).  If you have a Query object with parameters, then modify the .SQL property of the Query object with the literal values of the parameters before you call the .RunSQL or .OpenQuery methods.

---

When we look at the history of the thread it does seem that DAO and the OS have an incompatibility.  Remember that the Access UI is not bound by the constraints of DAO and as such you have success through the Access UI, but not through DAO.

Just to make sure, are you indeed fully patched with respect to the Office Access Database Engine?  --- BUT --- one thing to note,

** I have not found any reference that indicates Access 2007 will run on Windows 2008 Server **

With that in mind, you should consider an upgrade to Access 2010, which lists Windows Server 2008 as a compatible OS.  You can D/L the Runtime version of Access 2010 and 'clean' install it on your Win 2008 server to run your app.

If that is not possible, maybe just D/L the Access 2010 Database Engine (32 bit, since Access 2007 is 32 bit) then you would get an updated version of DAO for use by VBA ... but that seems like a lot of trickery that may not work.

So ... in the end, it is my suggestion to upgrade to Office 2010, which is documented to run on Windows Server 2008.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
katerina-pAuthor Commented:
"So ... in the end, it is my suggestion to upgrade to Office 2010, which is documented to run on Windows Server 2008. "

Solved most of the problems.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.