backend db

  • Thread starter Thread starter ted medin
  • Start date Start date
T

ted medin

Started switching to the split db & have a problem with vba code:
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
 
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
 
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 said:
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

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


ted said:
Started switching to the split db & have a problem with vba code:
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
Click to expand...
Click to expand...
 
Sandra Daigle said:
Hi Ted,
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
Click to expand...

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
 
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".
 
"If you open it, close it. If you create it, destroy it".

......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
 
Fred Boer said:
.....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>)
Click to expand...

Imagine getting help & entertained :-).
 
ãðéàì said:
ââââââââââââãããããããããããìùéìâéìùéâãùðàùðçëðìùòðéù ìãéâ áî
ìúêâéãùóíäòèðô/åèðóòùæäò÷ùù äðãùùäùùùäùìâðòéäìêâð ëùëìêçëéð ùçìðëâù
êìçéìóðäéçãùìê éìçð éçì éãâé éçìð éì çéçìêãéìêé â ìêçéã êìç éìêçãù éìç éêçãê
ñúí ñúí àðé ìà îáéï à
Sandra Daigle said:
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

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


ted said:
Started switching to the split db & have a problem with vba code:
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
Click to expand...
Click to expand...
Click to expand...
 
ââââââââââââãããããããããããìùéìâéìùéâãùðàùðçëðìùòðéù ìãéâ áî
ìúêâéãùóíäòèðô/åèðóòùæäò÷ùù äðãùùäùùùäùìâðòéäìêâ?ëùëìêçëé?ùçìðëâ?
êìçéìóðäéçãùìê éìçð éç?éãâé éçìð éì çéçìêãéìêé ?ìêçé?êì?éìêçãù éì?éêçã?
ñú?ñú?àð?ìà îáéï ?
Sandra Daigle said:
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

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


ted said:
Started switching to the split db & have a problem with vba code:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top