File sharing lock count exceeded.

[oleBucky]

With the help of this forum's users, I created a small trial db (one table with about 350 records and another of less than 10) for viewing scanned images. The db links to images stored outside the db by use of two text fields per record each with the path to an image.



I added one text box on a previously fabulously working form (thanks Rural Guy) to show one ancillary text field of data and after setting up the text box, I received the following error when I went to run the form.

File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

Microsoft support article ID 815281 gave two options as workarounds. One involved altering my computer's registry (I'm a bit nervous about that; I am amateaurish with computers beyond application software.) and the other involves the temporary use of the Immediate Window in the VB editor.




There is lots of traffic on the web about this problem. After reviewing some of this traffic, most of which is over my programming head, my lack of sophistication leaves me with three initial questions:

1. Can anyone provide insight into the two possible workarounds from 815281 or other options that may be available and their relative utility and potential pitfalls?
2. What causes a file sharing lock?
3. In the hopes of streamlining my relatively small db which could grow rapidly, how can I get around this problem with db design?

A bit of information which may shed some light, my db switchbox is VERY slow to open when I start my db as are my key forms when first opened. If I close a form, then reopen the same form, the second time and thereafter the form opens quickly.

[RuralGuy]

It would seem the issue arose with the addition of that last TextBox. What is the ControlSource of the TextBox? Will this db be used on another machine?

[oleBucky]

The control source for the text box was the second column of a combo box on the form. I used the expression builder to enter the following: =HomeTeamCombo.Column(2)

For the near term, the db will be on this one machine only. In the longer term and when I have more time to learn, I will try posting it to my website knowing full well that's a whole different kettle of fish.

PS: How do I correct my type-o on the thread title?

[RuralGuy]

Column(2) is the 3rd column if it makes any difference and why complete the TextBox in the AfterUpdate event of the ComboBox?

[oleBucky]

I understand the 2 as the third column thing. Hidden column and all. I've used it several times on this form successfully.

I use the AfterUpdate event because if the HomeTeamCombo is changed (the home team is changed), the home field is also changed. I guess I left that tidbit out. Sorry.

[oleBucky]

BTW, I looked at the Similar Threads offerings, in particular the "johnmerlino" query thread. I tried all the suggested corrective measures without success. Although I did not do them in the order suggested... if that makes a difference.

[RuralGuy]

You have slowed down the form loading because the TextBox must wait for the ComboBox to complete its lookup before it can load. It sounds to me like you should really solve the slowness issue in the RecordSource query with a Join.

[oleBucky]

On this form, aside from the problem text box, I have 40 other text boxes with similar ControlSources. Can I assume this contributes to the slowness? I do not know what a Join is. I will research a Join so I can understand what it is, what it does and how it is implemented and give it a shot. Thanks.

[RuralGuy]

On this form, aside from the problem text box, I have 40 other text boxes with similar ControlSources. Can I assume this contributes to the slowness?

Absolutely! They are great controls but they each have their own RowSource that needs to load.

[oleBucky]

It seems I've been using Joins without knowing their name. I have two tables, tblHLBTeams and tblPlayers. The tables have an inner Join with one record on tblHLBTeams represented many times on tblPlayers. I removed the Lookup on tblPlayers.

Here goes... The form that generated the error in question has two combo boxes each selecting a team from tblHLBTeams; one home and one visitor. The row source for this combo box is an SQL statement calling the tblHLBTeam record ID and the associated team name then ordering the team names alphabetically. Each team (Home and Visitor) has 9 player combo boxes and one pitcher combo box. Each team's player and pitcher combo boxes relies on one query (one for home and one for visitors) to draw the teams players from tblPlayers filtered by the appropriate team combo box selection. I call these queries VisitorRosterQ and HomeRosterQ. Each of these roster queries also includes the player's year and MLB team. The MLB (Major League Baseball) team is independant of the team on tblHLBTeams. The player and pitcher combo boxes include 4 columns: PlayerID (hidden), Player, Year and Team. The forty text boxes mentioned earlier use the type of column control source mentioned above for each player. Phew!

With that description behind, how might I use additional Joins to speed-up my db. After reviewing some of you other recommendations on other threads, might it help if I replace the queries with SQL on the Player combo boxes? FYI, the db is currently just under 195 MB with just over 350 records.

[RuralGuy]

To answer one of your questions, static queries are precompiled and more efficienf (faster) than dynamic queries.

[oleBucky]

At the risk of showing my ignorance, is the SQL statement the dynamic query?

[RuralGuy]

At the risk of showing my ignorance, is the SQL statement the dynamic query?

You've got it.

[oleBucky]

Speedin' it up! I took two images out of the Switchboard and the db now opens much faster... very much faster. When I have the time, I will retry the addition of the fateful text box and see if they (the images) are somehow related to the file sharing lock problem.

[RuralGuy]

Cool! I'll stay tuned.