Persistent connection question

[Montez659]

I am testing a split version of my db (for this will be the final product) but I am noticing a much slower response rate. I was doing some research on persistent connections and I had a question.

Does having a persistent connection prevent more than one user from inputing a new record at the same time? Meaning, if one user has the form open (which would mean the connection is open) would this prevent anyone else from using the form on their distributed FE to add or update a record?

Thanks in advance!

 

[boblarson]

Don't open the connection using a normal form. Just open a form bound to a table, such as a lookup table - one with lookup values). Then you don't have to worry about any collisions with a record that might be open.

And, also make sure you set the SubDatasheets for all tables from Auto to NONE, or if you are using a subdatasheet with a particular table, make sure to specify which one that table is related to so Access doesn't have to figure it out (which slows things down as well).

 

[Montez659]

Bob, thanks for the reply. I am learning about this as I go along so a few more questions:

I created a separate form that would open and remain hidden on startup. There is VBA code from http://www.tek-tips.com/viewthread.cfm?qid=1524118 that I found that would run in On Load and On Close. What are the differences of doing the VBA as opposed to what you said, and which one would work better?

Secondly, I do have subdatasheets for some of my forms. How do I set this property? Is it in the Form properties, and if so is there a different name for it? I haven't been able to find it.

I have Access 2007. Thanks for your help!

Edit: Also, does your method skirt the fact that in the code I linked to the BE db location is hardcoded? That could be something that could be a problem.

 

[boblarson]

First - You can use the code method but I just found the form method is, in my mind, simpler to deal with. No code necessary (the autoexec macro opens the form and as hidden and when the database frontend closes the form closes too)

Second, personally I don't use subdatasheets as it is just like opening a table and you don't have events to control data validation and such. So if I am going to use a table I have a form for it. But if you must use them just open the table which is the form's record source and go to View > Properties and you should see a dialog which shows SUBDATASHEET as an entry and [AUTO] next to it. Change [AUTO] to the actual table which should be opened when you click on the little square in the form.

And last, my form method uses a linked table which doesn't require it know anything about its location. If you have to relink the tables then you don't have to go making any changes as the form will still work.

 

[Montez659]

I may have been confused about what you meant by subdatasheets. I think they are sub Forms in my main form that display as a datasheet.

I will give a stab at what you are saying because I think that it might be the right choice in my situation. I will let you know if I have any questions and how it turns out.

 

[Montez659]

Everything seems to be working fine by your method. Not much of an improvement in speed, but I think that it is due to server traffic. There are a few other options I have. Appreciate the help!