Access ADP problem

Be sure that your machines are fully up to date in regard to Office and Windows. Also not a bad idea to create a new .adp file and import all items to that file.

Hi thanks for replying, will try that import, just wondering why do you think something is wrong with the application, since most of the time all forms work fine, besides that this is only happening to few pc's the rest are all fine?
re updates, they have office 2003 sp3 and windows xp pro sp3, is there something else you suggest to upgrade?

The error "There isn't enough memory blah blah" generally means either (a) issues with the database or (b) issues with the OS or Office. It's rarely caused by an actual memory issue.

besides that this is only happening to few pc's the rest are all fine?

Given this new bit of info, I'd say that updating Windows/Office would be the FIRST thing I'd do. If the db works fine on one machine but not the other, then in general it's NOT the database, but rather the machine that is suspect.

I didn't say you should "upgrade" - I said you should UPDATE Office or Windows. In other words, be sure that Windows Update has been run, and all relevant hot fixes/patches have been installed.

Got you, will work on that and keep you posted, thanks.

Ok, i checked the auto windows updates and it was on, the only thing i realized is the office service pack was SP2, so i updated to SP3, however the user still got the above error after that, please let me know what else can be done to resolve this issue, thanks.

If it were me, I'd do a manual Windows Update session on the trouble machines, just to be sure all updates are current. Windows update is known to be troublesome in the automated mode.

Next, I'd try to do some maintenance on the database. Create a new, blank database and import everything into that new database, and then deploy that db to your users.

I assume each user has their own copy of the ADP file? That is, you've got a COPY of the .adp file on the user's desktop, and they work with that copy (and not with shared copy)?

These sorts of issues can also be caused by corrupt User Profiles, and even by corrupt print drivers. To test that out, go to a user machine that's having troubles and log in as a different user. If the problems go away, then you may have issues with that user's profile, and you'll have to recreate it.

Hi, sorry for the delay, it's just that while still experimenting your first suggestion about importing all objects into a new database, I was waiting this problem to occur again in the current file, then will test with this new file and see if this happens there too, however the last 2 days it didn't happen yet (because I am waiting for it to happen of course...)

Regarding you question, yes each user has their local copy of the front end.

Thanks for the suggestions, Will keep you posted.

Ben.

Hi, so far I have tried those suggestions above, and the error is still happening, however we got meanwhile some clue when this is most likely to happen, basically we have a popup screen with a list box control containing a loaded recordset, when the application starts, its setup to update that recordset, now if the user wants to refresh it in middle the day, there is a button which performs that, now usually after doing it is when the problem occurs...
Again, its not always happening and also not by all pc's.
Please let me know if this bring us any closer to the solution.
Thanks again,
Ben

Can you show the code that is run when the button is clicked?

Hi, just to keep you updated, its not that simple (for me) to get that portion of code out of the module, as it uses a function that calls another function inside a class module...I was really thinking of copying the entire module, however didn't get permission from the manager for that, will see maybe I still manage to arrange something..

Does the application use any non-standard controls or libraries?

Can you list the References you have in the database? Those are shown by clicking Tools - References (from the VBA Editor).

See attached.
Dlls.doc

Those are all common libraries, although not all of them are "standard" (meaning they're added automatically by Access, or are regularly used by Access, like ADO or Excel). I don't think that' s your issue.

Still, the error you mention almost always points to either (a) problems with the database in the form of corruption or (b) problems with the installation of Windows, Access/Office, or one of it's dependent libraries. If you could narrow this down to a specific set of circumstances - like this occurs on Bob's machine when he clicks ButtonA and runs Report C - then we'd have a better chance of resolving it. As it stands now, the only think you can do is try to catch this when it happens and investigate exactly what happened immediately before it occurs.

A Repair/Reinstall of Office sometimes resolves these matters.

Hi, to summarize what we already did in order to fix this issue:
1- uninstalled & reinstalled office completely
2- installed all service packs available for that version (office 2003) and windows.
3- as you suggested, made sure the windows update are manually done.
4- tried narrow this down to a specific set of circumstances...with no success
(Basically the only things those users have in common are OS windowsXP Pro and Office 2003).
I am still about to try that one more suggestion of recreating the database (actually I already started a while ago, just got stuck due to certain technical difficulties).
Just wondering, would it help if I attach a screenshot of the error or maybe a screenshot of the task manager, at the time this happens?
Thanks again for your responses,
Ben
MemmoryComplaints.txt

Has the problem always existed, or has it submerged as you increase either data or application users?

Regards Marten

Windows XP, Office 2003 (& this is only happening to few pc's the rest are all fine)
Are all these workstations the exact same hardware and memory configurations?
Are there any correlations between the hardware/RAM configurations and the occurrence of this problem?

Has anyone done a disk defrag on these problem machines?

How much virtual memory is available on the machines with problems -vs- those machines without problems?

Just got another complaint on memory issue. User was trying to refresh employee screen. had 14 screens open in access at the time. Closed 7 of them. tried refreshing again and it worked fine.

If they are closing screens and the problem is resolved then available memory would seem to be relevant.

Do these few pc's have something else loaded on them consuming memory ( AV? java?) MS Project? ) that is not present on the good machines?



Has the database simply grown and grown and now those screens are a memory problem for the most stressed machines.?

How much RAM do these clients have?

I would also consider the probability of a GDI issue. This tool should help.

Hi,

as you are using an ADP and not MDB you should not access an SQL Server version which is of a higher version than your Access version. That means: A2003 ADPs should not connect to any higher version than SQL Server 2000, A2007 ADP up to SQL Server 2005, A2010 ADP up to SQL Server 2008. You CAN try to connect to higher versions but always with the risk that something doesn't work. I cannot say if this is the case here but you maybe should test if your users can work if you use A2007 Runtime instead (if they don't need the A2003 full version on their computers, it's better not to install two versions on the same machine). If they only have A2003 runtime you can simply exchange that with A2007 runtime (but you should test your application with this runtime in deep as there are sometimes little differences in this versions, although A2007 can execute A2003 files without problems).

Cheers,

Christian

Thanks to all for your responses, will reply in order they were posted..
Netminder: thanks for arranging all this.

 martenrune: I dont recall having this issue only lately, however few changes may have happen since, first office was changed from 2000 to 2003, pc and os may also have changed, and the way of them using the app also may have changed, meaning that its possible they are opening currently more screens at a time..

PortletPaul: Nothing that i know of is unique in those pc's..however i may try to defrag one of them to see if that fixes..Re db growt, are you aware of any limitation? Are few thousand records considered allot?

strivoli: See attached for system configuration of one of those pc's

strivoli2: why should this tool be better then the task manager (which didn't show any high memory usage at that moment..?)

Bitsqueezer: I happen to have a long debate with our manager re upgrade office version, however is concerned of the big cost such a upgrade could pose..
Doc6.doc

Hi,

in case of a runtime you do not need to invest anything because the A2007 runtime can be downloaded and installed on any PC without cost. You only need one full version for the developer, and that's no longer a special developer edition.

Cheers,

Christian

A few thousand records shouldn't be a concern for a SQL Server. Although are there any blobs, do you fetch all thousands at oncr and then processing it? Have you checked the SQL for blockings or deadlocks?

Regards Marten

The RAM is OK.
The GDI is just one of the resources used by processes. The memory reported by Task Manager is another resource. BTW, there are many kind of memories (private, commited, ...) involved, too. Memory itself shouldn't be a problem since you have over 3GB of RAM, but GDI could be a problem and that's why I suggest you to use that tool.

You posted a Screen Shot of the System Configuration of one of the PCs. Could you post the video card make and model and the video card driver version?

Does the Windows Application or System Log report any warning/error entries when the problem occurs?

Bitsqueezer: We have sort of a large app, it took months to convert it from just access to sql/adp platform, and therefore the manager is concerned it should not pull again in another major expense..Besides that he just finished purchasing nice quantity of 03 licenses..And do you know what are limitations of runtime we can expect (not only to access, but to entire office package)?

martenrune: Most of our forms are Continuous Forms, based on table/query that have entire recordset, however they open with a filter of “id=0” and its upon of the users to decide what data they need..Do you think its something wrong with that approach?
Re sql blockage, I did not really check it, as usually when those things happen, it affects more then one user, and I get notified immediately.

by: strivoli: Will test this tool and let you know,
See attached what I found re video info (not really so familiar on that).
Where do I get Windows Application or System log files error?

PortletPaul: did system defragment last night, error continues to happen


Att all: just for clarification, I’m attaching the error message we are talking about.
Doc4.doc
Doc3.doc

Hi,

what's the code in the DblClick event which is shown in the error message?

Regarding O2003 and A2007 Runtime I would expect there would not be a problem because Access is not a real Office product, it was always a kind of a stand-alone application. But I cannot say that in general because everyone has his own special environment so the only thing to say for sure is what any responsible IT department would do before a roll out of a new technology: Simply try it. This can be done without any licence cost, simply use a standard PC of your environment, fresh installation, install any software your users usually all uses and then install the A2007 runtime - and then test if everything works as expected. If that's the case you can simply roll out the runtime to the users.

But don't forget what I said above: You should also be sure that all the users does not have an A2003 full version on their computer (which they want to keep on using) because in this case I would expect problems with a parallel A2007 installation. I have A2007 and A2010 (full versions) on my computer, that's no problem, but if I want to switch between them there is always some minutes (!) of automatic mini installation between - OK for me, but not acceptable for the users and also I don't think that A2003 can do that in the same way.

Another simple solution is to use a Terminal Server (but would need extra costs of investment) where you can install any version you want without any impact on the user's machine - moreover you are completely independent of the user's personal settings or configuration or memory (or even the OS version) because they only work with what the server has installed - and all garantueed to have the same (moreover you get the additional advantage of better performance especially at slow networks).

Cheers,

Christian

OK. The video card is usually a Nvidia, ATI or Intel. You posted the video capture. I kindly need the video card.
The have a look at the Windows Logs, run "eventvwr".

Hi, thanks to all for your replies.

Bitsqueezer: I spoke to my manager, at the moment he is not interested to do any of those kind of changes (which evolves things that can affect the entire app) unless we bump in on some errors that will affect all or most of the users in real bad way..The same thing applies to the terminal server suggestion.
Re The code on double click, it’s simply to open additional forms.

strivoli: As stated above, I am not familiar on how to obtain this info, (unless you can guide me), was told that it’s an integrated video card...
that eventvwr, is also i will have to try next time this happens & let u know.

OK. Eventvwr: you don't need to wait next time it happens. The eventvwr shows the Windows Application and System Logs (and others). The Logs are what they are, logs. As a result you can see if there are any warnings/errors last time the problem occurred.

Hi,

if possible, maybe you can upload the ADP file here. To make it anonymous you should delete the server connection information and maybe additional information inside the code which is personal information (like server names, passwords in connection strings, name of the company and so on). As an ADP cannot have local tables and it only uses SQL Server resources there would be no data affected, but maybe we can see in your code what could be the problem. What you call "is simply opening some forms" can be anywhere else in the code: When you open a form, it's events are fired, they start several subs, maybe they are using several standard module subs and so on. If the problem occurs anyhwere here you would get the error as if the problem were in the DblClick event sub itself.

If that's not possible you must do it on your own (but of course you must do that on the machine of the user if the problem never occurs on yours). I would start to disable all OpenForm commands in this DblClick event sub and only remain one of them intact (the first) and then try it again. Then enable one by one and test until you can reproduce the error.
I don't believe that the problem really has anything to do with the main memory as around 3 GB should be more than enough even if you have a lot other applications started at the same time. Normally Windows moves them to the page file if they are not active so if you also have enough space on your harddisk in the partition where you have your pagefile (I recommend to use a static pagefile of a defined size) then this should be no problem.
It's more likely that the error message has a completely other reason, would not be the first time in Access error messages where the shown error reason has nothing to do with the real reason.

Access has some severe bugs (even in A2007 which I mainly use) according filters. So for example if you have a continous form and you load 100.000 records (guessed value, can be more or less depending on the number of fields and so on) into it and set a filter in the Load event it can occur that Access crashes completely. The internal filters cannot handle a big amount of data in some scenarios. Using the ServerFilter property (only available in ADPs) is always the better way but here you must be sure that the filter string has no syntax error (T-SQL syntax), otherwise you get strange display results and you can mostly only use the Taskmanager to quit Access.
I also had problems if special printer drivers are simply installed on the machine (not that they were used in Access, simply the fact that they exist on the machine) which caused strange crashes of ADPs - took me month to find that out. So maybe you should compare the printer driver list with machines where no error occurs.
I also strongly recommend to use the SQL Server Profiler which is a tool you can start from the SQL Server Management Studio. This shows you any traffic between the server and the clients and can be filtered for example to a specific database (if you have more than yours on the same server) and to usernames, client machine names, application names and so on. So in the end you can filter the output to exactly the communication between the problematic machine(s) and the SQL Server (and don't need to be run on these machines, you can do it from the developer machine). Then try to test your application until the error occurs and find out if there is any error displayed in the SQL Server Profiler output because not all errors SQL Server produces are reported correctly by Access. You will also learn a lot about what's going on in the background between SQL Server and your application.

Another thing is that Access uses "Lazy Loading" by default. That means: The first visible page of records in a form will be available as fast as possible and all the other records will be loaded slowly in the background (which is the reason why "RecordCount" only works safe after "MoveLast" and why you get the count in the navigator after some time). If you load more than one form at the same time which all uses a lot of records maybe there is some reason here that certain data cannot be loaded at that time. You can prohibit this behaviour by using a "MoveLast" / "MoveFirst" at the beginning (in the Load event) to force Access to load all records fast. The disadvantage is only that the user must wait some seconds longer until the form opens.

If everything doesn't work I recommend to write an error log in VBA to get the exact point in time when the error occurs and run the process monitor tool from Sysinternals (free to download and use) which can save all the running processes in a log file (fast growing, so should have space on the harddisk...). Then you can compare if there is maybe some other application running at the same time which is the reason for this error. This method I used to find out the printer driver problem.

Cheers,

Christian

Hi, just updating the status.

strivoli: just tested eventvwr, nothing specific shows up there. (didn't get to the GDI yet..)

Bitsqueezer: Thanks for your nice writeup..So far as you suggested I first looked into sql, at the activity monitor, see attached what I saw for that user at the time the problem occurred, let me know if this helps.

Att all: Will keep you posted as I proceed with your suggestions.
memory.doc

Hi,

what I meant was the SQL Server Profiler, not the Activity Monitor (but this one is of course also a good point to see what's going on).

But why do you cast IDs (which are usually long integer = 32Bit values) as nvarchar? I can not think of any reason why this would make sense.

Cheers,

Christian

Hi Bitsqueezer,
1-re posting the entire app, I don’t have managers approval for that, however will attach the code that is most likely causing it, at least for one of the users, as described in the beginning of this thread, we have a popup form that contains all the records from a particular table, used for lookup purposes, and when they want to refresh the data is when this fun starts..some times immediately and in some after a while.
2-what is your opinion about by: PortletPaul point “If they are closing screens and the problem is resolved then available memory would seem to be relevant.” ?
3-re your sql question, parts of the applications code was inherited from earlier programmers, I would only change if this is really necessary at the moment.
Thanks again for your replies.
Ben
Refresh.txt

Hi,

your code is using a lot of calls to an external object named "SQLConversion" - could also be a form, I'm not really sure.

It's a lot code and difficult to say but also a lot code about filtering the data. So the question here would be: How many records will be loaded with this form? As I wrote above, the Access filters have bugs in ADPs so I would recommend to try to rewrite the filter code part and assign the filter string to the ServerFilter property as test.
Another thing is that you start the loading in Form_Open - should be no big problem but normally you should start anything which loads records in Form_Load, that's why that has this name. Form_Open is better to check if the form should open or not (Cancel=True).

In your refresh code you start the complete procedure "LoadRecordset" again and then, in the Find Sub it is also called again. Moreover you use "Recordset.Clone" which creates a complete copy of the whole recordset - why? If you want to use a recordset where you can use Find without moving the visible record bookmark you should use "RecordsetClone" (without the dot) instead. This is a background instance of the form's recordset which is always available. "Clone" is a method of recordsets to create a complete new recordset so in the end you have the data in memory a second time which is not needed in most cases.

I think this form does a lot of loading and maybe some of it is not necessary, but difficult to say without seeing the form and the dependencies and of course the code which is called outside the code. But in general I would say: Here is a lot of potential to create better performance and less problems.

I cannot say more about PortletPauls opinion other than: It seems to be a logic statement with no other information as you gave before. But "memory" can be more than one thing. It's not only the main memory, there are other pools which can ran out of space. For example, you have only very low memory for the registry, you have virtual memory, temporary files, and of course stack space. I would not simply believe Microsoft error messages word by word, you must always think a little bit about the errors and what could be the reason on your own.

SQL question: If there is no real need of using such a conversion you should try to create a view without the conversion (only the ID field) and try the new view in your frontend to see if that makes a difference or produces problems. I don't think that this is the case, but that's the simplest way to find that out without creating an impact on the running system or other user's frontends.

Cheers,

Christian

Hi Bitsqueezer,
Just wanted to give u some updates, today I tested again with sysinternals running, and you will see attached what it showed at the time the problem occurred, also attached what it looked at the sql (so far still using activity monitor...will take some time till all your suggestions will be tested, but hope finally to get there too, if we don’t find the source of problem sooner), FYI- I killed one of the suspended commands, and was able to open one more form, however got attached error message immediately after, also related to memory..

strivoli:
Attached a screenshot of the GDI at the time the problem occurred.
Memory-7-15-13.doc
sysinternals-7-15-13.doc
GDI-7-15-13.doc
sql-7-15-13.doc

Thank you. The DOC with sysinternals Screen Shot reports no digital signature on Access (see 1st PIC in the DOC). Could you kindly check if Access is from MS or not? Or, open another Office program such as Work or Excel and post a sysinternals Screen Shot.
Thank you.

BTW: Private Bytes and Working Set are (very) high.

Hi Bitsqueezer,
Trying your latest and getting the attached error in middle of running procRCLoadAllFromText, also tried skipping that form and error kept happening to each of the following forms,
FYI- my developing access version is 2000, let me know if that can be the issue, thanks.

strivoli:
See attached (if you question is if this was genuine copy from MS, I cant answer, as i am only the developer...however if you're think this may be causing this issue I will discuss it with the manager).
ProcRCLoadAllFormsFromText.doc
sysinternals-7-17-13.doc

Hi,

WHY in the world do you develop an A2003 ADP with A2000???
ADPs are strongly bound to the underlying SQL Server version and A2000 has only the knowledge about SQL Server 2000, maybe lower! - there were HEAVY changes between SQL Server 2000 and 2005 so I strongly recommend to upgrade your development environment!

Working with SQL Server 2005 or 2008 is almost critical enough with A2003 ADP - but the changes between SQL Server 2000 and 2005 are very significant so maybe all your problems comes from your wrong development environment. ADPs are not the same as MDBs/ACCDBs, where you can simply change the ODBC driver to work with a newer server, but ADPs are programmed to directly work with the SQL Server version up to the version when Access was released.

The error message: Seems to be some system or hidden form in your database which the code seems to has problems with. You should find out which form that is, maybe this is one of your problems. If you can't do that with the code you must create a new blank ADP database and import the needed objects manually with the import wizard. It's not exactly the same as the code can do for you, but better than nothing. You can recreate the ADP using the code with the new ADP (but you will not get the options or references set if you did not adjust that manually in this file before recreating it).

Cheers,

Christian

Hi Bitsqueezer,
A little bit of our history first...When I came in, the it was a database designed in 97/2000 MDB only, for both FE and BE as well...Then at some point the Buss decided its time to upgrade to SQL Server BE, (We bought in a consultant for the conversion, as at that time, I was unfamiliar with SQL, he is sort of responsible for the triggers functions and procedures written in the BE...therefore I cant really answer on all your questions re those..sorry for that), from the options and combination of options we had, it was decided that Access 2000 would work with sql 2005, I know one of the things we considered was upgrading to 2003 runtime version, however we ran into some trouble at that point..later in time some pc's were already upgraded to 2003, however I feel that it has significant drawbacks comparing to users still using 2000, just giving you an example:
1- when having conditional formatting on a continuous form with more then 2/3 hundred records, 2000 will display and navigate them with no problems, while in 2003 it takes forever..
As a result, only the newer pc's have 2003 running, while a big portion still uses 2000, and this is why I am forced to do the development in 2000, otherwise I would have to convert it backwards to 200 every time I want to make a new release in ADE...
Re your code to recreate the application, I really loved the idea, as it could be implemented immediately, without having to change code etc, and for a maybe bases... so maybe you can help get this thru,
Now what you're saying that something is wrong with that particular form, I would not think so, as the problem kept occurring for almost all following forms and different error messages appeared for the modules,
Would it help if I do:
1-attach all different of those messages?
2- convert it first to 2003 and then run it?
3- is it possible that the code is not able to handle system objects starting with "~"?
4- could it be that one problem triggers the next, so when solving the first would take care of the rest?

Hi,

really crazy situation, I don't know how someone can decide such environment in a business area. The only thing you would need would be ONE A2007 or A2010 full version for development and then all users could use the new runtime. Investment is really low.

The code: I can say that it runs without problem in my A2007 ADP environment (where the ADP file format is the same as A2003 ADP). I don't know why you have objects in your list which can't be recognized by the code, it should not convert system objects, moreover I don't understand the name of the form in your error message, doesn't sound like a system object.

You should try the suggested way first: Create a new ADP manually, import all objects here, insert the Recreate sub and try it here, just to see if it works.

Conversions between different Access versions can be problematic, each version leaves some information which the other don't need and so on. So maybe you've done that so often that your file is corrupt.

I cannot believe that an A2003 ADP should be slower than A2000 ADP, I guess it's in the kind of programming, same as with your memory problem. But as you are not be able to upload it I'm afraid I cannot help you here.

Cheers,

Christian

Hi Bitsqueezer,
Created new adp, imported all objects without an issue.
Also reset all references etc..
Now when running your code procRecreate, everything went fine, except for the attached, let me know if that can be ignored?
From preliminary testing, everything seems to be working...if you okay the above, will have the users test for the memory issue.
Thanks.
p.s. as you mentioned, file got really smaller (almost 50% of the original size), just wondering how come the ade created from that newer adp file, is bigger in size than from the adp…
With-objAccess.doc

Hi,

this loop tries to read out all options you made in the Access options window and set them equal in the new ADP. The names in the error messages are names of the options. To set options in VBA you must use their name to access them with GetOption. Here you can find a list for A2003:

Set Options from Visual Basic [Access 2003 VBA Language Reference]

As you are working with different Access versions I would guess that the old one has options which the new one does not have and vice-versa (you'll find their names in the Microsoft reference). So I would say: Yes, you can safely ignore this but you need to correct the mentioned options manually in the new file.

Don't forget to compile the VBA code after recreating it, the file will grow after compilation around 20% (but that's needed if you want to create an ADE).

I don't know why the file size is bigger than before, I would guess that one possible reason is the mixture of Access versions you use and another that there seemed to be some "digital trash" in the file before which maybe is the reason why parts of your application was not correctly compiled. But that are only guesses, I think only Microsoft could answer that in detail.

But as the recreation now works I would say you have a clean ADP file now.

Cheers,

Christian

Hi Bitsqueezer,
    As mentioned, I was able to recreate the database successfully, however the user was still able to reproduce the memory error (see attached), now basically we ruled out the possibility of corruption, correct?
Giving the fact that this only happens to WinXP pc's, which direction do you recommend we go?
1-Add memory
2-upgrade windows
3-force them to open less screens at the same time
4-change syntax of sql
5-change logic of code
Thanks
(P.S. At this point, you definitely deserve your points, wish I could assign 5000+ to this matter...)

Hi,

yes, the corruption of the database should now be not the problem anymore.

1 - no.
2 - no.
3 - not really a solution and I guess you will not be able to ensure that...:-)
4 - maybe.
5 - maybe.
6 - maybe you should add: Change the design of your database.

But as I have no information about your design, SQL or code I cannot tell you anymore about the possible problems here.

Thanks, I'm glad if I could help you. Maybe you can upload some demo with only the needed forms, the needed code and some anonymous test data then I maybe could help you further.

Cheers,

Christian