Avatar of katerina-p
katerina-p asked on

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
Microsoft AccessVisual Basic ClassicWindows Server 2008

Avatar of undefined
Last Comment
katerina-p

8/22/2022 - Mon
ASKER
katerina-p

Additionally:

OK, does not crash on the old server either (Win 2003).
ASKER
katerina-p

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
Scott McDaniel (EE MVE )

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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
datAdrenaline

Are you fully patched with respect to Access 2007? ... ie. All service packs and such.
Jim P.

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.
ASKER
katerina-p

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim P.

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?
ASKER
katerina-p

Yep, decompiled, compiled, compacted & repaired, updated both Office and Windows, restarted servers, etc. No luck!
ASKER
katerina-p

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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jim Dettman (EE MVE)

<<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.
ASKER
katerina-p

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.
Michael Pfister

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Michael Pfister

This should be the correct download link

http://www.microsoft.com/en-us/download/details.aspx?id=26347
ASKER
katerina-p

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.
ASKER
katerina-p

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jim P.

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.
ASKER
katerina-p

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.
Jim P.

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
katerina-p

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.
ASKER
katerina-p

No luck on either of those suggestions I'm afraid.
Jim P.

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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
katerina-p

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
ASKER
katerina-p

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.
ASKER
katerina-p

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
katerina-p

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.
Jim P.

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.
ASKER
katerina-p

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...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jim P.

Are you doing a recompile on the production server and using that?
ASKER
katerina-p

Hi jimpen,

Yes I have.

K.
Scott McDaniel (EE MVE )

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

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.
ASKER
katerina-p

LMS, Jim,

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

K.
Jim Dettman (EE MVE)

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
datAdrenaline

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
katerina-p

"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.