Question VERY strange problem

[benkingery]

I have an Access front end linked to a SQL Server 2005 back end via ODBC. I have anywhere from 1 - 5 concurrent users in the DB at any given time.

I am having some users begin to complain that their Access file freezes every time another user is logged in and has a particular form open. They report that when said user closes the particular form, their Access file unfreezes and everything works just fine.

I told the users that the coincidental circumstance they've identified is akin to saying "The Yankees win every time I eat waffles". HOWEVER, I viewed it myself yesterday.

Now my mind is perplexed and I'm racking my brain trying to have any idea what is going on. I've verified that there are no irregularities with the SQL server during the process. I think it has something to do with the actual Access (.mdb) file.

Any ideas?

Thanks in advance.

 

[boblarson]

So does each person run a copy of the frontend file that is on their desktop machine? They aren't all opening the same frontend file from a network location are they?

 

[benkingery]

Yes. Every IS opening the same DB from a network location. They are all sharing the same access file on a public drive

 

[BecoNorte]

You should create a mde file and give a copy to each user

 

[benkingery]

Distributing that on my network and managing updates would be an absolute NIGHTMARE. Doesn't that kind of defeat the purpose of using Access a an engine for concurrent users?

 

[boblarson]

I'll answer the second question first:

Doesn't that kind of defeat the purpose of using Access a an engine for concurrent users?

No, because the DATA is where the concurrency lies. But using a single file frontend can increase the chance of corruption and, should it go corrupt, everyone can't do their work whereas if they each have a copy then they would not be affected if one of theirs corrupts. Only one person would need a new frontend copy and all others could continue their work without issue or even knowing something was wrong.

Now the next one:

Distributing that on my network and managing updates would be an absolute NIGHTMARE.

Actually, I have just the solution for you. My supervisor at Providence Health System said exactly the same thing you just did, because we had 200 users using the various databases and so maintaining them would be horrible. So I created a solution for them which is available for free on my website. It is an Auto Update Enabling tool.

How it works is - you first download the tool. Then following the instructions it makes your frontend able to update itself when necessary. All you do is make changes to your master copy (in a completely separate location), copy that version over to the "master location" and then change the version number in both the frontend and backend table (all done from the frontend). Once that is done the users will

1. Open their frontend on their machine.
2. It checks the version number in their frontend with that on the backend.
3. If it doesn't match, it tells the user that their frontend is out of date.
4. It then will close and
5. Delete itself
6. Copy a new copy down from the "master" location
7. and reopen automatically for the user.

so, once you deploy to a user, you should not have to worry about them again (unless they have a corrupt frontend and then you can just copy the frontend down for them).

See my link here (and be sure to download and read carefully the documentation and test on a COPY first before implementing).
http://www.btabdevelopment.com/ts/freetools


And don't download the secure version one unless you are using Microsoft Access User Level Security.

 

[benkingery]

Oh I like you Boblarson Always knew I did.

I'll give that a try next week. That also appears that it would be a good function for deploying changes and updates from a central location.

I'll follow up with you then.

 

[BecoNorte]

Boblarson you bit me to it! Nice process! Thanks for sharing.
I use a self-extract zip file (or .exe file) combined with a batch file that will copy the mde file to the user's computer and place a short cut on the desktop. I also use the version number to activate the .exe file.When the user opens their copy, if a new version is available, they receive a message about the new version. After then click OK I use the shell() function to run the .exe file.

 

[benkingery]

Boblarson:

Hey I downloaded your program. It has 3 parameters to input before you begin. One of the parameters is the Access Database Back end. I am using a SQL server and connecting through ODBC. Can I still use your solution?

Thanks

 

[boblarson]

Boblarson:

Hey I downloaded your program. It has 3 parameters to input before you begin. One of the parameters is the Access Database Back end. I am using a SQL server and connecting through ODBC. Can I still use your solution?

Thanks

I believe the code and all it places will work but the tool itself won't be able to because it uses a method to place a table that is inside of the tool into the backend. But give me a few and I'll create a customized version that will work with the caveat that you would need to create the table in the backend SQL Server database manually (or via a script which I might be able to include for you).

 

[benkingery]

Seriously, thank you!

 

[boblarson]

Okay, here you go. So, the modified instructions are

1. Run the script from SQL Management Studio to create the table in the SQL Server backend and populate the one record to be 1.0.1 to match the local table which the tool will place in your frontend.

2. Then link the SQL table tbl-version_fe_master in to your frontend.

3. Then open the Auto Update Enabling Tool

4. Select the frontend master location and the frontend file (the two remaining text boxes).

5. Click the Enable Auto Updating button.

6. Click the Display Start Form Code button and copy and paste that into the On Load event of the form which is your start up form (it needs to be the very first code that runs in that event if there is other code there).

And I think you'll be good to go. Let me know if you have any problems.

 

[benkingery]

Boblarson, I do have to say you're a genius. Sorry I'm just getting back to this right now. I went on an extended vacation. I just conducted the process with a test version of the DB and it works perfectly.

Thank you for all your help.

 

[boblarson]

I just conducted the process with a test version of the DB and it works perfectly.

Glad to hear. I'll be trying to get that version added to my website. I just had a problem doing so the other day.