Is there a Known Bug for this Specific Circumstance in Access 2003?

Users have been getting "There isn't enough memory to perform this operation" error messages in an Access 2003 .mdb database.  I've found some corrupt objects and replaced them, but they're still getting this error.  I finally pinpointed a set of circumstances that causes this.  If the following 2 things are done in this order, the error occurs ...  
1.) The "DoCmd.FindRecord" command is used on any form (no matter how complex or simple the form is)
2.) The user finds a record on a specific form that is bound to a query that is based on a SQL Server linked table that has 40 fields, one of which is a varchar(MAX) field, and that has criteria such that the query outputs 233 records.

Does anyone know if this is a known bug?  How can I find out if this is a known bug and if there is a hot fix for this?  Does anyone have any suggestions about a workaround?
LVL 1
Declan_BasileITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Jeffrey CoachmanMIS LiasonCommented:
I don't know of and specific bug for that particular set of circumstances, ...but here are some suggestions:
1.
I've found some corrupt objects and replaced them,
If you have a few, ...you probably have more (that just don't exhibit any symptoms yet)
So try creating a new blank database and importing all the objects.

2. Is the DB "Split"?, ...if not, then it needs to be.
http://www.techrepublic.com/blog/10-things/10-plus-reasons-to-split-an-access-database/

3. Try using code like this to find a record:
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[YourID] = " & Str(Nz(Me![YourIDControlOnYourForm], 0))

JeffCoachman
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
+1 on Jeff's comments as there is no direct answer to your questions.

A2003 is long over and done with as far as Microsoft is concerned and even if you found a critical bug at this point, it would not be fixed.

 The only other thing I would add is adding:

 dbEngine.SetOption dbMaxLocksPerFile, 300000

To your code at start up and see if that helps.   This increases the max lock limit (default is only like 10,000) and may solve the problem.

 And of course, make sure your up to date.  The last build for A2003 is 11.0.8204.  That is achieved by applying SP3 and then https://support.microsoft.com/en-us/kb/945674

Jim.
Jeffrey CoachmanMIS LiasonCommented:
sorry about the typo,...
...the code should be:

    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[eID] = " & Str(Nz(Me![Combo14], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
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.

Declan_BasileITAuthor Commented:
I didn't have the hotfix installed, but it didn't fix the problem after I installed it.  The maxlocks setting didn't help either.  I already imported all objects into a blank database.  Also, the program is already split with most of the data in linked SQL Server tables.  Changing the code to use Recordset Clone instead of FindRecord also didn't prevent this problem from happening.  Any other ideas?  I don't think that the program can handle finding a record in a form bound to a recordset with so many fields (one of which is a varchar(Max) field AND so many records.  Should I convert the form to an unbound form?  I've never had to do that.  What's involved in doing that?  Would it even help?
Jeffrey CoachmanMIS LiasonCommented:
Other than guessing, ..I am not sure...

If you made the tables local, does it work?
If not, then post a sample of this database (with the local table) for us to evaluate.
Declan_BasileITAuthor Commented:
I created a make table query of the original query, ran it to create one local table, then opened the form with the local table as the recordsource and I could find records like lightening without any problem whatsoever.  

I also found that the problem still happens when the recordsource is a query with only one linked SQL Server table.  This table has almost 700,000 records in it.  I set criteria on only one field which makes the query output 233 records.  I only specify about 30 of the table's 40 fields to be output in the query, and I don't include the varchar(max) field in the query.  Also, the test form only has two controls, namely one bound to the field to search and another bound to a random field to display.  Also, both the criteria field and the field I'm searching have indexes.  Could there be a problem with this SQL Server table?  I don't normally run re-indexing on the SQL Server database.  It's probably been over a year.  I'll try that next.
Declan_BasileITAuthor Commented:
No such luck.  I ran the following script in SQL Server to reindex the troublesome table

GO
DBCC DBREINDEX ('dbo.QuoteItems');
GO
 
and I still get the problem, but only after FindRecord is used somewhere in the program on some other screen first (and I did try to use RecordsetClone in place of FindRecord and still got the "Not enough memory" error.  Again, could there be a problem with the "QuoteItems" SQL Server table?  Do SQL Server tables ever become corrupt?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Only other thing I can think of:

1. Drop to a command prompt.
2. Type "SET" followed by a return.

 In the resulting display, look for TEMP and/or TMP environment variables.   make sure both point to a valid drive/directory and it has plenty of free diskspace (2GB or more).

 Also in the directories they point to, delete all *.tmp files.

 Now try the operation again.

<<Do SQL Server tables ever become corrupt? >> 

  They can, but not like JET based tables; It's extremely rare and always related to a hardware/software failure on the server.

 I think what's happening is that simply your consuming some internal resource in Access due to the size of the tables.

 How are you connecting to the SQL Tables?  Via linked tables (ODBC), using DAO, or ADO?

Jim.
Declan_BasileITAuthor Commented:
This is my code to connect to create linked SQL Server tables.

    Set tDef = DB.CreateTableDef(strTableName)
    If strEnvironment = "Local" Then
        tDef.Connect = "ODBC;Driver={SQL Server};Server=" & strDevelopmentServerName & ";DATABASE=ALTEK;Uid=" & strUser & ";Pwd=" & strPwd & ";"
    Else
        tDef.Connect = "ODBC;Driver={SQL Server};Server=" & strActiveServerName & ";DATABASE=ALTEK;Uid=" & strUser & ";Pwd=" & strPwd & ";"
    End If
       
    tDef.Attributes = dbAttachSavePWD
    tDef.SourceTableName = "dbo." & strTableName
    DB.TableDefs.Append tDef
Declan_BasileITAuthor Commented:
I converted enough of the program to demonstrate the problem to Access 2013 so I can open an incident with Microsoft about it.  I believe they don't support Access 2003 anymore.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I believe they don't support Access 2003 anymore.>>

 That is correct.  Extended support for 2003 ended 4/1/14 and main support ended many years ago.

Jim.
Declan_BasileITAuthor Commented:
Just to let you know, a Microsoft support person and I were able to replicate the problem in a very simple Access 2013 .accdb database.  We saved the SQL Server table as a local table and I ftp'd the database to him.  He was then able to upload the table into his SQL Server, create a link to it and experience the same problem on his computer.  Now I'm waiting for Microsoft people to analyze it and get back to me.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Good deal.   Keep us updated please!

Jim.
Declan_BasileITAuthor Commented:
I've kept this question opened so I can post the results of my opened incident with Microsoft.  This is what I heard back from them:

"It seems to a bug  but we need more Information to Confirm. We are working to find the Workaround so that we can free Access Virtual memory."
Declan_BasileITAuthor Commented:
FYI.  I've opened an incident with Microsoft on Monday, May 4th and FTP'd a sample database in Access 2013 .accdb format that demonstrates the problem on that day.  I've worked with them for several hours since then and still don't have a hot fix or workaround from them.  Now they're asking me to spend more time on this with them, but I believe they have everything they need to troubleshoot this issue without my involvement, and I think that the case should have been closed by now.  Does anyone have any contacts at Microsoft that could help escalate this issue to a higher level of support or have any advice about who I can call at Microsoft that could help get this issue resolved?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<and I think that the case should have been closed by now. >>

 Nothing moves that fast when you report a bug to a vendor, especially if it ends up truly to be a bug.   You may wait months to see a fix, and a few weeks at the very least for a work a round if one can be made.

 As far as the time, tell them you have given them everything they need, and since they said they could reproduce the problem, then you don't need to spend any more time on it unless they want you to test a work a round or fix.

Jim.
Declan_BasileITAuthor Commented:
Ok, thanks Jim.  I did already told them to figure this out without my involvement.  Now that I know what to expect (that it'll take a few weeks for a workaround) I'll do more work on my own to try to figure out a workaround.  I can't wait on them any longer.  Do they typically reimburse the cost of the incident if it's found to be a bug in Access?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< Do they typically reimburse the cost of the incident if it's found to be a bug in Access? >>

 Yes, it should be a No Charge incident if it is a bug.

Jim.
Declan_BasileITAuthor Commented:
I keep receiving messages requesting my attention because I haven't replied in three days, but I probably won't have anything to report until people from Microsoft report back to me.  This is incident (which I was advised is normal - thank you Jim, that's good to know) is taking them a long time to resolve.  Should I keep this post open or close it and report back once it's resolved?
Declan_BasileITAuthor Commented:
I deleted the index on a varchar(10) field of the SQL Server table (the same field that was being searched on in the Access form) and it fixed the problem.  I found this out on my own after several hours of research.

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
Declan_BasileITAuthor Commented:
I've assigned my post as the best solution because it's what fixed the problem but also assigned points.  Thank you Jeffery for the original comments and thank you Jim for hanging in there with me and advising me about how Microsoft support works.
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.