Use of Access Queries, Best Approach (1 Viewer)

[Steve R.]

1. Access Queries: I tend to use access queries as the datasource for all my forms. I have started to use SQL statements to define the record source. This seems superior; however I have had a couple of lingering questions. I have often wondered if the Access queries are always active or if they are only active when called upon by an active form. If they are always active, I would assume that there would be a performance drag. Since my datadases are small, I haven't noticed anything.

2.Adding New Records: As noted in #1, I tend to have an access query that is uses all the relevant tables as the datasource and to open the form with acFormAdd. I am beginning to think that it would be better for all the controls on the form to be unbound and then to add the new record through code:

Dim dbstemp As DAO.Database
Dim rsttemp As DAO.Recordset
Dim authortemp As String
Set dbstemp = CurrentDb
authortemp = "select * from authortemp"
Set rsttemp = dbstemp.OpenRecordset(authortemp, dbOpenDynaset)
rsttemp.AddNew
rsttemp![LAST] = [LASTX]
rsttemp![FIRST] = [FIRSTX]
rsttemp.Update
rsttemp.close

The reason that I am considering the above approach is that I have, what is now a single user database that I intend to make multiuser (Yes the database was split.) and this may minimize the database "locked" message that I have been periodically getting on an erratic basis when testing it as a multiuser database.

 

[gemma-the-husky]

i very rarely use record locking on multi user databases (access actually defaults to optimistic record locking, which is not quite the same as no record locking). In small environments you are generally unlikely to get two users writing to the same record at the same time, and even if you do, Access WILL warn you.

what are you doing to get frequent locked records? In theory any number of data readers can access the same record without problems, its just multiple writers (updaters) that produce a problem. If you lock records for readers, this may be causing problems unnecessarily.

I would base forms on queries or tables, rather than unbound unless you have very good reason to have unbound forms. I tend to use unbound forms to accept parameter inputs, and for perhaps say things like new order entries but its a lot easier to use bound forms if possible.

 

[Steve R.]

In theory, none of my records should be locked. However, I do believe that some of the ways that I am handling an open dataset may inadvertently lock a group of records. As a matter of fact, I found some old code today that opens a recordset, but does nothing. So I deleted those lines. Its amazing how you can stumble upon some old code and wonder how you could have written that.

I will continue poking away at it.

 

[gemma-the-husky]

just looking at your code again, I still dont see why you are getting frequent locked messages. You are only opening the authortemp table briefly to add a new item

Incidentally, You dont really need to read in all the existing authors.

You could just open the table directly with

Set rsttemp = dbstemp.OpenRecordset("authortemp")

here authortemp is the table, not your string

and this will work fine

 

[Steve R.]

Good point. That is the value of this forum as a learning experience.