Access 2000 Front-End needs constant Repair 4

[TommieB]

Scenario: Large Access 2000 application running on a server at a customers site. Data is stored in SQL 2000 on the server. A client machine and the server machine use the application at the same time. No problem with speed or functionality. SQL Server data is backed up from the application at close each night.

Problem: The user states that he gets an error message to repair the database after running the backup (this would happen before the access application is closed). Which he does. Then a second message is generated stating that "The database is in an unexpected state." He can not reopen the application unless I remove the old copy and replace it with a backup copy kept on the server for this purpose. I could understand data corruption but why would an .mde become corrupted on a SQL backup? Or is this only masking the real problem? Could the user be doing something that would cause this to happen, like having the application open on the client while running the backup from the server or maybe a network card or hub is dropping the connection for a split second? Or am I causing this with some code that I am not aware of.

There are only 3 real possibilities: user error, hardware problems, or coding errors. I can run the backup in my office 200 times in a day and not have any problems. I can even run the backup while a client machine has the application open and I can not reproduce the error. I just don't see any code that would cause this problem.

The user is upset and I am frustrated. Does anyone have any suggestions?

 

[VicM]

TommieB:

A little confused here.

This may not directly answer your question, but if you are running an FE and BE with the BE in a SQL database, why are you doing daily backups of the FE? The SQL database should be automatically backed up each night by the client's IT group as a matter of course.

The only time the front end will change is when you recode, add new forms/reports/queries, etc. Then you would ship the client an updated copy. So a static backup copy of the FE on the client's machine, or other medium, is all that is needed in case of some unforseen event.

Additionally, you speak of an .mde file. Once the database is converted to an .mde there should be no need to compact/repair; and neither to backup for the same reasons above. I don't have first hand knowledge, but I would think that Access would not allow an attempt at compact/repair on an .mde file.

You also state that you cannot reproduce the error at your site. Are you using the exact configuration that is at the client's site? Server software version and installation, etc.?

Just some food for thought,

Vic

 

[TommieB]

No, you misunderstand. I do not do daily backups of the FE. The backups are of the SQL Database. The backups of the SQL database are initiated through code in the FE. The user clicks a button, the database is backed up. Simple. Since this business has no IT group, it is left to the person assigned to make sure it happens.

I keep a static backup copy of the FE in a folder for the purpose of replacing the copy in use if necessary. However, it is annoying to the user to have to call me and tell me his system is down so I can replace the copy of the FE.

Actually, Access does allow a compact/repair on an .mde and somewhere in my reading I got the idea that it was desired to do so. Maybe I misunderstood somewhere in my reading that there were some instances where the .mde actually did grow (I will go back and check this again). Not to mention the fact that if you did get a corruption of some type, compact and repair will fix it in most cases. But, since C&R is only scheduled to be run 1 time per week, I really don't see the harm.

There is a possibility that there is a slight deviation in the configuration of my system vs. theirs. Since my system is used strictly for development and usually has the latest and greatest and their system is not maintained by anyone that would be able to install the SPs, etc. I will be looking at this tomorrow which is the soonest that I can dial-in without interrupting their work. I hope that I actually find a difference in the configurations. Maybe the Jet SPs are different.

 

[MikeLoon]

I have seen that error when I accidently connected through a 97 workgroup.
Also, MDE files are version specific so if someone has a 2002 version you will probably get a similar error.

 

[vbajock]

Couple of things -

I don't know if it applies to you, because you didn't mention if the FE was on a network or local drive, but
I encountered a very similiar bug. If your clients are running the IPX protocol or dual IPX/TCPIP protocols and IPX is running on the server the FE is stored on, there is a bug in 95,97, and 2000 that causes the database to corrupt when IPX is used to write the database to an NTFS (and possibly a FAT, FAT32) disk. Fix is to go 100% TCP/IP or restrict clients to TCP/IP, or the fix may be in the service pack for all I know. Server was an NT 4.

If the FE is on the local drive, why don't you just set the FE to compact on close? It might take a few seconds longer for the user but if it saves you from having to rebuild the tables...

 

[TommieB]

MikeLoon, thanks for the suggestion but these machines are definitely the same version.

vbajock, The FE is on a network drive. I have not checked to see what protocols the client and server machines were set up with, but I will dial-in and check that today. I actually had originally set the application to compact on close but the powers that be did not want it to compact everytime it was closed just certain days so.....Luckily the tables are only linked tables, the actual data is on SQL which is having no problems. I am also going to check to make sure that both machines are running the same versions/service packs for everything.

Thanks guys for your suggestions. I appreciate the help.

 

[markphsd]

Hey,
Just so you know, an mde can become corrupt. It also can be compacted and repaired. It can grow, so at some point it will either need to be compacted and repaired, or replaced.
I've come across a similiar problem to yours, not running sql, but running a access 2000 back end and a 2000 fe. I think I solved it by making sure that my code was good and clean, and making sure the latest service pack was installed on the all of the client machines. What was happening was some machines were corrupting the fe, while others would run it fine. Updating the service pack seemed to solve the problem. Anyways, just a few thoughts. Mark P.

I write code in cuneiform, what about you?

 

[TommieB]

markphsd - Thanks for the information. I have watched my .mde (which contains nothing but forms, modules, queries, and reports) grow. You would expect it to be like any other .exe and not grow after being compiled, but they grow a lot if you do a relink of the tables and as an experiment I ran the program, opened a couple of forms and closed the app and it had gotten larger.

Anyway.....I think my problem is going to be found in versions and service pack differences. Of course, some of the code is awful (I took over where someone else left off and their coding skills/habits were pretty messy and amateurish), but at this point I can't rewrite it all.

I am going to check versions/service packs today and update anything that does not match my machine. Will let you know how it goes.

 

[TommieB]

Just finished checking both of the customers machines.

I found no differences between the version/service packs running of the 2. The only difference is that the server runs SQL Server, but the client does not. The Win 2000 SP are the same, as well as, Office 2000, Jet 4.0, and MDAC.

I also checked the protocol and it looks to be TCP/IP exclusively.

I guess I am back to square one. I am leaning toward the client machine being the problem. My initial thought was a network hardware problem, but I am not sure how I can monitor if there are times when packets are being dropped or whatever else might be happening. Of course, I still fail to see how that could cause a corruption problem on the FE where no data is stored.

 

[vbajock]

I believe just opening and closing the mdb causes system objects to be changed and re-saved. Something must change because just opening and closing one changes the system file date of the mdb to the current date. I don't know if this is true of an MDE as well. Are you forced to put the FE on a network drive ? Putting on the clients local drive sure cuts down on problems.

 

[TommieB]

vbajock, it sems to be true of the .mde as well as the .mdb. Why I don't know.

I don't have to put it on a network drive, I chose to probably because that is what I would have done with a VB app. We also discussed the need to maintain a number of machines when this product is finally sold retail. It is much easier to update the app on 1 machine than it is on 10 machines or 100 machines. I keep telling them that we need to think about rewriting it before we consider more than 10 users, but they won't listen to me.

I am going to see how the application runs over the weekend. They only seem to have problems on Saturday, which is their busiest day. I need to know exactly what is happening and when and I can't seem to get any information from them.

If they have the same problem, I will probably put a copy of the app on the client machine and see what happens.

 

[vbajock]

You don't want to design your app where you would need to make changes to 100 machines when a change is needed - you create a set up program that is stored in a central place, like server, or a web site, and when the user runs it, it writes everything the user needs to his local drive. Updates and changes are made to the setup progam, not to the users C drive, and when you make a change you notify the users a new version or patch is available and they run it so it updates their code. I don't know anybody running a big multi client front end from the server - you get too many bugs and you have to write a ton of code to handle locks and temp tables that you don't need to on a local front end. Been down that road - ain't goin back!

 

[TommieB]

What you say makes sense. I am used to working in a different environment from what I am in now. I used to work for DOD/AAFES as a VB/SQL client/server programmer and most of the applications ran from an application server with the data on a data server.

Maybe that is just the way they did it, but old habits are hard to break.

 

[vbajock]

I am not saying this applies to every app in the world, but I am saying that Access is so cumbersome and complicated it is just not a really great choice for an app server type front end. It really is designed to be a local drive/network drive split between user interface and data. Microsoft is assuming that everyone doing an Access thing must have a big fat PC because Access is a bloated bug infested pig, so what else would the user have just to fly the thing? BTW- Isn't about 40% of the DOD still running XT's and 486's ? You got no choice BUT an app server in that kind of enviro! The general wants the big new computers in the tanks- who gives an RA about the clerks! I kind of miss enviros like that - where you gotta code lean so you learn to be efficient with every last line of code. Access, is fat, fat fat. Have fun!

 

[TommieB]

You are absolutely right. I was trying not to imply anything about Access and it's problems. Didn't want to make anyone mad.

Working with Access has been a painful experience at times. It makes some things really easy, but it makes a lot of things almost impossible. I get really tired of having to find ways to work around things that Access does not want to relinguish control of.

As for the DOD/AAFES, way out in the boonies there were still some old dogs running and you would have to take them into account, but during my time there they were making a big push to get most areas upgraded. You are right about the generals though--they always wanted the newer, bigger, better, faster stuff and usually got it.

I had to pull data from old mainframes using cobol, as well as pull data from SQL servers and Oracle dbs. It was interesting work because there was always something new to learn. Didn't know COBOL but needed something done, no problem....here's a book, figure it out. I liked that.

Thanks for the tip on Access though. I guess I should rethink my position on where to put the application. I know that Access is a resource hog, but I just couldn't give up the old ways. Ya know?

 

[scking]

Yes and assembler code is smaller. I don't buy some of the comments flying around. Many new applications that provide significant improvements grow in size as they mature. Every application development system is different. They therefore have areas where they excel and areas where they do not. Access does an excellent job when the application is designed correctly. It was never meant to be, or replace, commercial client/server type applications like SQL Server or Oracle. However, I don't want to spend the money required to purchase multi-user licenses for SQL Server or Oracle for an application that can be easily satisfied with Access.

I spent enough time in the military to know that many of these problems are because of a unit's ability to justify, plan purchases, or make good economic decisions. When you get down to some of the problems it is related to the unit personnel being unfamiliar or unwilling to learn how to use the tools they have. I work with the military as my primary customer on a daily basis.

There are limitations to Access that drive all of us a little batty but I don't believe I've ever found a limitation that, with a little research and professional discussion, couldn't be resolved. Given, of course, that Access was properly selected in the application design.
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[TommieB]

scking, I am not sure exactly what point you were making.

I got the distinct impression that you were telling me that as an Access programmer, I must suck.

If that was the case, thank you for your input, I am sure that you are right, and I will go now and flog myself 100 times for being a programmer trying to learn to work in a different environment.

I have no problem with the military, if that is your problem. Programming for AAFES was one of my most rewarding experiences and if I had not had to move, I would still be there. I loved the work environment.

As for Access in a development environment, it is a great database. For small applications the development environment works. For larger applications and mission critical applications, I think there are better choices, but I must work with the hand that was dealt me.

I would like your input on the problems that I am having. If you have a suggestion as to what could be the problem and how I can fix it, please let me know.

 

[scking]

TommyB,

Whoa! I would never would never comment about a programmer like that. We all start from the same beginnings. However, I will say that experience in Access would allow EVERYONE, including me, to create smarter, better applications.

If your LAN stays up overnight could you program SQL Server to automatically perform the backup and avoid the problem with the Access front end?

Otherwise, what code performs the backup in Access? If we could see the construction possibly we could help lead to the answer.

If the user has the database open and possibly a table locked, what would happen if SQL Server tries to access this same table and create a backup?
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[TommieB]

Sorry scking, I must have misunderstood what you were saying.

I agree that experience makes for better applications, but after a year of doing some pretty complicated stuff in Access, I have to say that if I could have chosen any other development environment to work in, I would have. VB, C/C++, Java, anything, even something that I had to learn first would have been preferrable.

Anyway, actually I do have SQL server backing up the data automatically, but they also have the option to do the backup right now. I really don't think that the backup is the true problem. Users very seldom tell you exactly what they did, because they usually did something that they think they weren't supposed to do, so they lie and say they didn't do anything.

I have thought about the database being open, however, it is not the SQL db that is becoming corrupt. What is actually corrupt is the application front-end and no data resides there.

I am still looking for something in the code, but I just don't think so. I would copy the backup code here, but it is pretty involved.

 

[scking]

You didn't metion but have you set a breakpoint in the code and walked through each line of code? Sometimes that highlights the problem and sometimes it give clues or shows where the code may all of a sudden causes a problem.
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------