DAO or ADO in an MDE data base?

[Paul Copeland]

Hi,
I am using Access2000 and have developed a data base which successfully uses
DAO code in some of the forms to update tables.
Recently I converted the data base to a split MDE data base and now get
errors in the DAO methods.
IE:
Dim Rst As DAO.Recordset
Set Rst = CurrentDb.OpenRecordset("TableName")
Rst.Index = "IndexName"

The code halts at the last line above with the error message "Operation not
supported by this type of object"

Should the DAO code work on an MDE data base or should I be using ADO code.

Paul Copeland

[Trevor Best]

On Tue, 16 Sep 2003 22:05:46 GMT in comp.databases.ms-access, "Paul
Copeland" <pa***@ngbrown.com.au> wrote:

Hi,
I am using Access2000 and have developed a data base which successfully uses
DAO code in some of the forms to update tables.
Recently I converted the data base to a split MDE data base and now get
errors in the DAO methods.
IE:
Dim Rst As DAO.Recordset
Set Rst = CurrentDb.OpenRecordset("TableName")
Rst.Index = "IndexName"

The code halts at the last line above with the error message "Operation not
supported by this type of object"

Should the DAO code work on an MDE data base or should I be using ADO code.

ADO would not support that even on the local tables. The DAO method
you're using only works for tables in the database that you're working
in, not attached tables, you need to open the backend database first,
e.g.

Dim db As database, rs as recordset

set db = dbengine(0).opendatabase("c:\foo\bar\mydata.mdb")
set rs=db.openrecordset("TableName")
rs.index="IndexName"
....
rs.close
set rs=nothing
db.close
set db=nothing

Remember this is a database object you will have to close since you
opened it.

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)

[Paul Copeland]

Thanks Trevor,
Are you saying that the DAO code as I have used is restricted to local
database applications i.e. .mdb files as the code in my posting does work on
my .mdb file without error.
If I wish to split my database so I can have a number of users accessing
the one database file on a server do I need to modify my code as per your
example. If this is the case how do I get around the fixed reference
opendatabase("c:\foo\bar\mydata.mdb") in your "set db" statement as this
means the location of the database files are hard coded in VB? Not very
flexible.
Paul Copeland

"Trevor Best" <bouncer@localhost> wrote in message
news:s2********************************@4ax.com...

On Tue, 16 Sep 2003 22:05:46 GMT in comp.databases.ms-access, "Paul
Copeland" <pa***@ngbrown.com.au> wrote:

Hi,
I am using Access2000 and have developed a data base which successfully usesDAO code in some of the forms to update tables.
Recently I converted the data base to a split MDE data base and now get
errors in the DAO methods.
IE:
Dim Rst As DAO.Recordset
Set Rst = CurrentDb.OpenRecordset("TableName")
Rst.Index = "IndexName"

The code halts at the last line above with the error message "Operation notsupported by this type of object"

Should the DAO code work on an MDE data base or should I be using ADO

code.
ADO would not support that even on the local tables. The DAO method
you're using only works for tables in the database that you're working
in, not attached tables, you need to open the backend database first,
e.g.

Dim db As database, rs as recordset

set db = dbengine(0).opendatabase("c:\foo\bar\mydata.mdb")
set rs=db.openrecordset("TableName")
rs.index="IndexName"
...
rs.close
set rs=nothing
db.close
set db=nothing

Remember this is a database object you will have to close since you
opened it.

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)

[Albert D. Kallal]

No, the problem is that use of the seek command,a nd the index command is a
very old legacy command. It is a throwback to the old dbase/dos days.

You can use dao freely, but use of seek on linked tables requires some
special coding. There is a work around for this problem here:

http://www.mvps.org/access/tables/tbl0006.htm
Note that seek does not work on sql server, or in fact for odbc connections
to any other database. It should be avoided. However, it has VERY high
performance, so don't be too quick to not use it!
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

[Trevor Best]

On Wed, 17 Sep 2003 01:56:35 GMT in comp.databases.ms-access, "Paul
Copeland" <pa***@ngbrown.com.au> wrote:

Thanks Trevor,
Are you saying that the DAO code as I have used is restricted to local
database applications i.e. .mdb files as the code in my posting does work on
my .mdb file without error.
If I wish to split my database so I can have a number of users accessing
the one database file on a server do I need to modify my code as per your
example. If this is the case how do I get around the fixed reference
opendatabase("c:\foo\bar\mydata.mdb") in your "set db" statement as this
means the location of the database files are hard coded in VB? Not very
flexible.

It need not be a fixed reference but a variable, you can even get the
path from the .Connect property of the table, useful if you attach
tables from different back-ends simultaneously. (you'll need to strip
off the ";DATABASE=" bit from the front.

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)