| | |
|
|
|
backend db |
|---|
| message from ted medin on 19 May 2004 |
1. openrecordset( ... , table ) no longer works with linked tables
2. Keys no longer work like: rstMember.Index = "sheet1mem#" or
rstMember.Index = "primarykey"
So whats the solution? TIA
|
| Sandra Daigle replied to ted medin on 19 May 2004 |
Hi Ted,
Tabletype recordsets can not be used on linked tables. Add to that the fact
that Index and Seek are only available with table-type Recordset objects.
There are two options - you can either open the backend
database directly, then open a table-type recordset and use the Index
property and seek method or open a recordset on the linked table and use one
of the Find methods (FindFirst, FindLast, FindNext, FindPrevious).
To open a tabletype recordset on the backend database use the Opendatabase
method to get a reference directly to the back end database (instead of
using currentdb()). Then open the recordset using the reference to the
backend database. Then, be sure to close the db - something you wouldn't
normally do with a db object that was created from currentdb().
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = OpenDatabase("C:/mydata/mydb.mdb")
Set rst = db.OpenRecordset("tblMyTable", dbOpenTable)
With rst
' do what you need to do here
.Close
End With
db.Close
Set rst = Nothing
Set db = Nothing
|
| ted medin replied to Sandra Daigle on 19 May 2004 |
Ok thanks for all the good info. As I understand it if I use something like
your example then I will not have to change my seeks & opentable type in the
open recordset. Thanks that will save a lot of recoding.
"Sandra Daigle" <Invalid@KeepYourSpam.org> wrote in message
news:exZcSsZPEHA.2876@TK2MSFTNGP09.phx.gbl...
|
| ted medin replied to Sandra Daigle on 19 May 2004 |
Ok thanks again for your input. I believe it showed me an error that we have
had for months here. Have a vba pgm that did a 'docmd form ... '. It failed
regularly with the linked tables. Occured to me that i should close the db
before doing the docmd siince i was generating a table for the form in the
proceedure. Put the close & all the = nothing before executing the docmd &
everything began to run correctly. My question to you is: is the close
sufficient or do we also need the = nothing for the objects. Never have got
a hold of ms object programing :-(. TIA
|
| Sandra Daigle replied to ted medin on 20 May 2004 |
Hi Ted,
Glad to have helped you with the original problem and indirectly with this
one.
Regarding the use "'Set obj=Nothing" . . . I always recommend doing this. In
*theory* Access/VBA will clean up behind itself and destroy any object
variable which refers to an object that has gone out of scope. This usually
is the case but there are times when the garbage collection/cleanup does not
occur and this can lead to odd behavior (Access will not Close) or memory
leaks which degrade performance. The generally accepted rule of thumb is . .
. "If you open it, close it. If you create it, destroy it".
|
| Fred Boer replied to Sandra Daigle on 20 May 2004 |
.....If you break it, you buy it.. If you spill it, you clean it up.. If you
lift the lid, you close the lid.... (Learned the last one after getting
married!<g>)
Cheers!
Fred
|
| ted medin replied to Fred Boer on 20 May 2004 |
Imagine getting help & entertained :-).
|
| Sandra Daigle replied to Fred Boer on 20 May 2004 |
LOL - leave it to Fred to make it entertaining!
|
|
Archived message: backend db (Microsoft Access Error Message)
