Access Stability

[Perky1977]

We have a Help Desk system based in Access and it holds about 30,000-50,000 records. It seems to always be crashing, It runs through citrix, what i was wondering is how many records can access handle??

 

[MrsGorilla]

My personal experience has been not with the amount of records that Access can handle, but the size of the database. We had one database up here that got really huge (greater than 1 gig, sometimes close to 2) which would cause it to crash. I was having to compact and repair on a daily basis. At that point we began a migration to SQL, but the database was holding in excess of 2.5 million records.

 

[Perky1977]

It was originally written in Access 97. The back end is held on a server and each PC it's on has it's own referance files to it (I'm new at this so might state the obvious) It has been converted for use with Access 2000 on some PC's and is run in Access 97 on Citrix. At any one time there can be up to 15 users in the database using the 97 and 2000 version.

 

[BlueSpruce]

It was originally written in Access 97. The back end is held on a server and each PC it's on has it's own referance files to it (I'm new at this so might state the obvious) It has been converted for use with Access 2000 on some PC's and is run in Access 97 on Citrix. At any one time there can be up to 15 users in the database using the 97 and 2000 version.

We have 2,500+ workstations running Office365, Sharepoint Colab, some siloed departamental Access apps, ERP/EHR, PACS, and other tools accessed via Citrix Workspace. It was all stable until IT pushed out Win11 to all workstations, which are not all the same. So I'm theorising variety of NIC cards, incompatible workstations are culprits. They should replace all those desktops with identical thin clients, no workstation should store data locally, that should all go on the server and users work with "virtual desktop dumb terminals". That minimises issues, simplifies support.

 

[RonPaii]

1) Confirm that each Citrix user is running their own copy of the front end.
2) Confirm that the all front end databases including Citrix are on the same WIRED network as the shared back end database.
3) Confirm that all network cards are working correctly i.e. no drops.
4) Confirm that the Front Ends are not crashing.

 

[cheekybuddha]

22 year necro - good work!

 

[The_Doc_Man]

It was originally written in Access 97. The back end is held on a server and each PC it's on has it's own referance files to it (I'm new at this so might state the obvious) It has been converted for use with Access 2000 on some PC's and is run in Access 97 on Citrix. At any one time there can be up to 15 users in the database using the 97 and 2000 version.

As to limits, with the U.S. Navy I had a database that held approximately 600,000 primary data records at a time (with monthly archiving) plus 1500 servers being tracked. We had perhaps 35-40 users, of whom about a dozen at a time might be online.

The only thing you described that is somewhat of a "red flag" situation is mixed versions of Access, though I don't recall that Ac97 vs. Ac2k was a huge leap. As long as EVERYONE has a private front-end copy and the back-end ONLY contains data tables, you will probably be OK with mixed Access versions - but if one of my colleagues recalls a compatibility issue between those two versions, I will stand corrected.

Be sure that for the Citrix users, your server provides separate private folders for each user so that you can have separate front-end files for each person. This is important for Windows File Lock issues as well as "destructive interference" problems - the latter being due to "the left hand not knowing what the right hand is doing." If the FE was correctly split and in individual private folders, destructive interference should not be an issue.

 

[BlueSpruce]

22 year necro - good work!

Yeah, I searched for anything Access Citrix and this relic thread popped up. I saw Pat and MrsGorilla's (Rat Race Participant) replies lol

 

[BlueSpruce]

As to limits, with the U.S. Navy I had a database that held approximately 600,000 primary data records at a time (with monthly archiving) plus 1500 servers being tracked. We had perhaps 35-40 users, of whom about a dozen at a time might be online.

The only thing you described that is somewhat of a "red flag" situation is mixed versions of Access, though I don't recall that Ac97 vs. Ac2k was a huge leap. As long as EVERYONE has a private front-end copy and the back-end ONLY contains data tables, you will probably be OK with mixed Access versions - but if one of my colleagues recalls a compatibility issue between those two versions, I will stand corrected.

Be sure that for the Citrix users, your server provides separate private folders for each user so that you can have separate front-end files for each person. This is important for Windows File Lock issues as well as "destructive interference" problems - the latter being due to "the left hand not knowing what the right hand is doing." If the FE was correctly split and in individual private folders, destructive interference should not be an issue.

Yes, its all setup as you described. The issue here is with the Citrix Workspace software. All 2,500+ workstations cannot communicate with the server. IT has been working on this for several hours. They recently upgraded all workstations to Win11 Enterprise. Maybe there's issues with 11 and Citrix?

 

[The_Doc_Man]

With my U.S. Navy situation, we very rarely got involved with Citrix because we were a headquarters department, co-located with the servers in question. Therefore, my knowledge of it is second-hand at best. Win11 hadn't been released yet when I retired, so I have no feedback on the Citrix + Win11 combo.

HOWEVER... I asked Google+Gemini this question: Does the current version of Citrix have trouble with Windows 11?

It answered with a laundry list of Win11 vs. Citrix highly specific "gotcha" cases. So there may well be some fire in that smoke.

 

[BlueSpruce]

With my U.S. Navy situation, we very rarely got involved with Citrix because we were a headquarters department, co-located with the servers in question. Therefore, my knowledge of it is second-hand at best. Win11 hadn't been released yet when I retired, so I have no feedback on the Citrix + Win11 combo.

HOWEVER... I asked Google+Gemini this question: Does the current version of Citrix have trouble with Windows 11?

It answered with a laundry list of Win11 vs. Citrix highly specific "gotcha" cases. So there may well be some fire in that smoke.

I would think anybody in their right mind would thoroughly test Win11 with everything else before upgrading 2,500+ workstations.

 

[The_Doc_Man]

I would think anybody in their right mind would thoroughly test Win11 with everything else before upgrading 2,500+ workstations.

There you go again... using "government" (implied) and "in their right mind" in the same sentence.

 

[BlueSpruce]

There you go again... using "government" (implied) and "in their right mind" in the same sentence.

I guess you can categorize an organization with 19 hospitals as a government. I think some heads are going to roll if they don't fix this soon. Production has been at a standstill.

 

[BlueSpruce]

1) Confirm that each Citrix user is running their own copy of the front end.
2) Confirm that the all front end databases including Citrix are on the same WIRED network as the shared back end database.
3) Confirm that all network cards are working correctly i.e. no drops.
4) Confirm that the Front Ends are not crashing.

Google AI suggested that. Not all workstations are identical. That's most likely the problem. They should replace desktop workstations with identical thin client virtual desktops.

 

[The_Doc_Man]

Google AI suggested that. Not all workstations are identical. That's most likely the problem. They should replace desktop workstations with identical thin client virtual desktops.

Replace if and only if it is in the budget. The U.S. Navy did a tech refresh every 3 or 4 years for us, so we very often got new machines that were pre-loaded with Office and several other useful utilities like Reflections (a terminal emulator) and SnagIt. But not everybody has a Navy budget.

 

[BlueSpruce]

Replace if and only if it is in the budget. The U.S. Navy did a tech refresh every 3 or 4 years for us, so we very often got new machines that were pre-loaded with Office and several other useful utilities like Reflections (a terminal emulator) and SnagIt. But not everybody has a Navy budget.

This outfit has plenty of money and their mission is extremely critical. Thin clients are a lot cheaper than desktops, especially when you buy 2,500+ units. Downtime and support costs are minimised, security is increased.