Removing Memo fields for row level locking

[David Portwood]

I think it was one of Tom Wickerath's suggestions to separate Memo fields
from main tables because tables with Memo fields don't allow row level
locking. Instead, put the Memo field into a separate table linked 1:1 to the
main table. I do want row level locking for my multiuser app. How best to
implement this?

My first thought was to base my form on a query linking the main table and
the memo table. This way I get all fields on the form including the Memo
field. However, if the tables are linked 1:1, does this allow row level
locking? I would think that restrictions on either of the tables in a 1:1
relationship would affect the other table the same way. Or is this wrong?

Another possibility is to use an unbound field and in the AfterInsert method
of the main form open a recordset to update the Memo table. Actually, I
would keep the recordset open while the form was open. I would populate the
field in the Current method of the main form.

Which is the best way to go and why?

 

[Guest]

Hi David,

Yup, that was me who made the suggestion. It is listed in my multiuser
applications paper, here:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

Note that this suggestion applies to JET databases only.

My first thought was to base my form on a query linking the main table and
the memo table. This way I get all fields on the form including the Memo
field. However, if the tables are linked 1:1, does this allow row level
locking?

The main table should be fine. I'm not so sure about the linked table. You
could use a subform that has the appropriate Link Master Field / Link Child
Field settings. That way, when you click into the subform, you are committing
any changes to the main form and vice-versa.

Were you able to get the speed issue worked out (improved) that you reported
last week, after splitting your database?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[David Portwood]

My first thought was to base my form on a query linking the main table

The main table should be fine. I'm not so sure about the linked table. You
could use a subform that has the appropriate Link Master Field / Link
Child
Field settings. That way, when you click into the subform, you are
committing
any changes to the main form and vice-versa.

I hadn't thought of using a table linked 1:1 in a subform control. I'll give
it a try.

Were you able to get the speed issue worked out (improved) that you
reported
last week, after splitting your database?

Yes, when I ran your code to change the SubDatasheet property to "[None]"
for each table and also unchecked Table Auto Name Correct, the performance
improved markedly. I'm already using a persistent connection. I would like
to also see a ranking of the suggestions.

I can't bring myself to worry about what level of nesting my app folders are
at and how long my filenames are. If I have to start thinking about things
like this, I'm going to wonder if there isn't a better tool than Access for
developing multiuser apps.

As it stands, the performance is just fine, however, I haven't tested it
with more than one user as yet. So if there is a row level locking problem
it wouldn't have reared its head yet.

Memo fields are a good catchall for various kinds of notes and comments. I'd
like to see more analysis of just how risky it is to use them and what the
consequences might be without the separation in a multiuser environment.

It seems like someone could write a book about all the mods required to
successfully deploy an Access multiuser app. I find this very strange. I
think the main reason a person uses Access (versus Excel, for instance) is
exactly because Access allows simultaneous multiusers, so this number of
workarounds (none of which are mentioned in any Access textbook I've looked
at) to achieve that goal seems a bit "shlocky" to me. Does Bill know about
this?