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

Additionally:

OK, does not crash on the old server either (Win 2003).

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

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?

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

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.

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

Select allOpen 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

Select allOpen 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

Select allOpen 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

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)

Select allOpen 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?

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

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?

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

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.

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.

This should be the correct download link

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

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;

Select allOpen 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;

Select allOpen 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;

Select allOpen 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.

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

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.

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.

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;

Select allOpen 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?

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.

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

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?

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]));

Select allOpen 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

Select allOpen in new window

1.jpg

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.

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]))

Select allOpen 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]));

Select allOpen in new window

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

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.

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

Are you doing a recompile on the production server and using that?

Hi jimpen,

Yes I have.

K.

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?

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.

LMS, Jim,

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

K.

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.

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