MS Access Multiuser - table-type recordsets

Author

table-type recordsets

jodyblau@gmail.com

2006-02-08, 3:24 am

I am about to try creating my first multi-user database, and I have
purchased the Access 2002 develooper's handbook.

In the developer's handbook, it mentions that, when using a multi-user
database, you "can't use table-type recordsets or the Seek method
directly on linked tables..."

It doesn't, however, explain what is meant by table-type recordsets.
I'm certain this is a basic question, but could someone point out to me
a sample usage of what would be considered an attempt to use a
"table-type recordset" ?

I just need to know how to recognize what code I will need to alter in
my single-user database in order for the multi-user thing to work.

Thank you,

Jody Blau

Brendan Reynolds

2006-02-08, 7:24 am

Consider the following code ...

Public Sub TableTypeRecordsets(
)

Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset

Set db = CurrentDb
Set rst1 = db.OpenRecordset("tblTest", dbOpenTable)
Debug.Print rst1.Type
rst1.Close
Set rst2 = db.OpenRecordset("tblTest")
Debug.Print rst2.Type
rst2.Close
Set rst3 = db.OpenRecordset("SELECT * FROM tblTest")
Debug.Print rst3.Type
rst3.Close

End Sub

The first recordset, rst1, is, obviously enough, a table-type recordset,
because we opened it using the dbOpenTable argument. But what about rst2 and
rst3? Well, rst2 will be a table-type recordset if the table 'tblTest' is a
local table, or a dynaset-type recordset if the table is a linked table,
because those are the defaults that DAO uses if you don't specify the
recordset type. The third recordset, rst3, will be a dynaset-type recordset,
because that is the default when the source is a SQL statement or query
rather than a table name.

Look for references to either the Index property of the Seek method of
recordsets in your code, both of which require a table-type recordset. Don't
just assume that every reference to 'Index' in the code is a reference to
the Index property of a recordset, though, because there may be references
to the Index object, which is not directly relevant to this problem.

Take a look at the help topic for the OpenRecordset method, it explains the
different recordset types.

--
Brendan Reynolds
Access MVP

<jodyblau@gmail.com> wrote in message
news:1139375250.670148.241830@g43g2000cwa.googlegroups.com...
>I am about to try creating my first multi-user database, and I have
> purchased the Access 2002 develooper's handbook.
>
> In the developer's handbook, it mentions that, when using a multi-user
> database, you "can't use table-type recordsets or the Seek method
> directly on linked tables..."
>
> It doesn't, however, explain what is meant by table-type recordsets.
> I'm certain this is a basic question, but could someone point out to me
> a sample usage of what would be considered an attempt to use a
> "table-type recordset" ?
>
> I just need to know how to recognize what code I will need to alter in
> my single-user database in order for the multi-user thing to work.
>
> Thank you,
>
> Jody Blau
>

jodyblau@gmail.com

2006-02-08, 8:26 pm

Ok, so here is a sample of code, the form of which I use often:

Dim rs2 As DAO.Recordset
Set rs2 = db.OpenRecordset("Select * from Misc_Settings where Name
= 'Law_Forms'", dbOpenDynaset)
rs2.MoveFirst

If I understand correctly, this is similar to the example you provided.

When I split the database with the database splitting utility,
everything appears to still work as intended.

So if the above mentioned code should not be used with a split
database, what are the consquences of doing so? How could I adjust the
above code to make it multi-user friendly?

Thank you,

Jody Blau

Brendan Reynolds

2006-02-08, 8:26 pm

That code doesn't use Index, doesn't use Seek, and doesn't use a table-type
recordset. There's no problem with that code.

--
Brendan Reynolds
Access MVP

<jodyblau@gmail.com> wrote in message
news:1139429147.885851.203250@g44g2000cwa.googlegroups.com...
> Ok, so here is a sample of code, the form of which I use often:
>
> Dim rs2 As DAO.Recordset
> Set rs2 = db.OpenRecordset("Select * from Misc_Settings where Name
> = 'Law_Forms'", dbOpenDynaset)
> rs2.MoveFirst
>
>
> If I understand correctly, this is similar to the example you provided.
>
> When I split the database with the database splitting utility,
> everything appears to still work as intended.
>
> So if the above mentioned code should not be used with a split
> database, what are the consquences of doing so? How could I adjust the
> above code to make it multi-user friendly?
>
> Thank you,
>
> Jody Blau
>

jodyblau@gmail.com

2006-02-08, 8:26 pm

Thank you for the help!

Jody Blau

Sponsored Links