Subforms and Windows Server 2003 (1 Viewer)

[MikeAngelastro]

Hi,

We were using Terminal Services on a Windows 2000 Server machine to run an Access-based accounting system which I am customizing on a daily basis. After each change I made, I would compact and repair the MDB and then create an MDE. I would copy the MDE over to the server and there it would work as well as it did on my own machine (Windows XP).

We recently upgraded the server to Windows 2003 Server Enterprise edition. After the change we seem to be having weird problems on the server when the same program would work on my machine.

One problem was that in certain cases it would not print. Using the MDB equivalent of the MDE, I would find that the program did not know what to do with the "Me" designation when referring to a report. Creating an MDE on the server instead of my workstation seems to fix this problem. This problem has never occurred on my PC.

Another problem is that sometimes a sub form will not appear on a form when
the form is opened and the sub form has no record to show. Refreshing the form doesn't help. This problem does not appear to be helped by making the MDE on the server. I just noticed that when I open the parent table, Sales Orders, I am able to add records. But when I open the child table, Sales Order Line items, I am not able to add records. This is probably the reason. If the MDB resides on my workstation, I can add records to the Sales Order Line items table. If the MDB resides on the server, I can not add records to the Sales Order Line items table, even if I am looking at it on my workstation through the network. So it seems to be related to the machine that the database is residing on.

The inability to add records to a child table is pretty basic. Anyone else have these problems?

Thanks,

Mike

 

[Pat Hartman]

One continuing problem with Access is that if you run a database in an environment different from that in which it was developed, you frequently run into strange errors. These errors stem from the same problem and that is that some necessary library is either a different version or stored in a different directory path from the development system. This would explain why makin the .mde on the server solves the problem. The library references are to the server versions/locations. Sometimes getting past the references issue resolves all problems. Search the Microsoft kb for code solutions that may make the reference resolution possible in an .mde that was created on one system and used some place else.

 

[MikeAngelastro]

Thanks Pat.

I'll I will compare the paths of the references. Sounds very logical.

On the inability to add records to a table. It turns out that if someone has the sales order form open, no one can add anything directly to the table. However, I'm a little perplexed because we can have four people adding sales orders at the same time without a problem. But if I have a fifth person open a different form that is using the same table, that form for some reason will come up without the sub-form being visible. I don't understand why that would be different from the sales order form and therefore be locked out. Now the sub-form populates itself based on the results of updating a combo box, so the parent-child relationship doesn't exist here. I could change it so that there is only one form that requeries itself after the combo box updates, but is that really different from the current setup? Do you think this change would be worthwhile? Or do you think that it may be time to migrate the backend to SQL Server? Is the lockout policy of Access tool restrictive for what I need to do? The current backend is about 350MB compacted.

Thanks again Pat, you have always been very helpful through the years.

Mike

 

[Pat Hartman]

When you run into locking issues, there are a number of things you need to examine starting with each person's network permissions. Because of the way Jet implements security, users need more permissions to network shares than most network people expect so make sure that everyone can create new files in the server directory as well as update existing files. The next thing is to make sure that everyone is using their own copy of the front end. If they are using a shared copy, you need to be sure that they do not change any database objects. They may only modify data. Make sure that your own code is not changing objects behind the scenes.

You didn't say how many concurrent users you have. Officially Jet can support 255 but most people report a practical limit of 25 concurrent users.

 

[MikeAngelastro]

Thanks Pat. Apparently both sides, Sales and Shipping, can create new files in the server directory. It appears that whichever group gets there first keeps the other one out. The two are just doing two different kinds of actions. The Sales group (5 people) adds records to the sales order line item table as they are creating sales orders with quantity ordered and quantity back ordered. The shipping group (1 person) updates quantity shipped which in turn updates the back order quantity accordingly. So they are not even working with the same sales orders. By the time the shippers get the sales orders for fulfillment the sales folks are finished with them. It appears that it is a contest between two forms: the sales order and the fulfillment form.

The most concurrent users for the entire accounting program are 15. In addition, even though we are using terminal services, all users have their own copy of the front end.

 

[MikeAngelastro]

Solved!!!

Hi,

It's amazing how often some of the knotiest problems can be solved very simply. When this happens to you, you feel so stupid; but at least you're glad that you have finally solved the problem. I fixed this by changing the Record Locks property from "All Records" to "Edited Records". This is a form property that I have never paid any attention to, but no more. Apparently, "All Records" is the default value for the property. So I need to beware of it.

It appears that when you are involved in a multiuser application - even when you are using Terminal Services - its value may be a show stopper.

What helped was doing a forum search on "Record Lock". Thanks for all of your help on this one.

Mike

 

[Pat Hartman]

I'm glad you wrote back with your resolution. "All Records" is not the default though. The default is "No Locks". You can set the default option for your database in Tools/Options/Advanced but you will have to manually modify (or at least check) all current forms/queries/tables.

 

[MikeAngelastro]

Thanks Pat,

I must have clicked that property without knowing it. I have a bad habit of indiscriminately clicking the mouse when I am frustrated when debugging.

Mike