Re: Set rs = db.OpenRecordset("tabFeriadosFixos", dbOpenTable)

  • Thread starter Thread starter Dirk Goldgar
  • Start date Start date
D

Dirk Goldgar

Frank Dulk said:
in the code I do lower the line in prominence of the mistake with
linked tables, because? how to solve?

-------------

Dim intDia Integer
Dim intMes Integer
Dim db Database
Dim rs Recordset

intDia = Day(umaData)
intMes = Month(umaData)

Set db = CurrentDb
Set rs = db.OpenRecordset (tabFeriadosFixos ", dbOpenTable)

rs.Index = PrimaryKey "
rs.Seek " =", intDia, intMes
If rs.NoMatch Then
E_FeriadoFixo = False
Else
E_FeriadoFixo = True
End If

rs.Close
Click to expand...

You can't open a table-type recordset (as with dbOpenTable) directly on
a linked table, and thus can't use the Seek method. You have two
alternatives:

1. Open a dynaset instead, and use the FindFirst method instead of Seek.

2. Open a database object on the back-end database where the table is
actually stored, then open a table-type recordset from that database
object -- so you are not using the linked table at all -- and use the
Seek method on that recordset. The following KnowledgeBase article
shows how to do it:

http://support.microsoft.com/default.aspx?kbid=210266

However, although either of the above methods can be used to locate the
record you want, this may not be the best way to do what your posted
code seems to be doing. If the sole purpose of your recordset is to
determine whether a record exists in the table for the specified day and
month, then it would probably be faster just to use either DLookup on
the linked table, with a criteria string that specifies the day and
month, or to open a recordset on an SQL statement that attempts to
extract just the matching record(s). In other words, you might write
something like this:

E_FeriadoFixo = _
Not IsNull(DLookup( _
"CampoDia", _
"tabFeriadosFixos", _
"CampoDia=" & intDia & _
" AND CampoMes=" & intMes))

or like this:

Set db = CurrentDb
Set rs = db.OpenRecordset ( _
"SELECT CampoDia FROM tabFeriadosFixos " & _
"WHERE CampoDia=" & intDia & _
" AND CampoMes=" & intMes)

E_FeriadoFixo = Not Rs.EOF

rs.Close
 
Thank you

Dirk Goldgar said:
You can't open a table-type recordset (as with dbOpenTable) directly on
a linked table, and thus can't use the Seek method. You have two
alternatives:

1. Open a dynaset instead, and use the FindFirst method instead of Seek.

2. Open a database object on the back-end database where the table is
actually stored, then open a table-type recordset from that database
object -- so you are not using the linked table at all -- and use the
Seek method on that recordset. The following KnowledgeBase article
shows how to do it:

http://support.microsoft.com/default.aspx?kbid=210266

However, although either of the above methods can be used to locate the
record you want, this may not be the best way to do what your posted
code seems to be doing. If the sole purpose of your recordset is to
determine whether a record exists in the table for the specified day and
month, then it would probably be faster just to use either DLookup on
the linked table, with a criteria string that specifies the day and
month, or to open a recordset on an SQL statement that attempts to
extract just the matching record(s). In other words, you might write
something like this:

E_FeriadoFixo = _
Not IsNull(DLookup( _
"CampoDia", _
"tabFeriadosFixos", _
"CampoDia=" & intDia & _
" AND CampoMes=" & intMes))

or like this:

Set db = CurrentDb
Set rs = db.OpenRecordset ( _
"SELECT CampoDia FROM tabFeriadosFixos " & _
"WHERE CampoDia=" & intDia & _
" AND CampoMes=" & intMes)

E_FeriadoFixo = Not Rs.EOF

rs.Close

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
did I place the code below rotating but very slow ta!!! does have as getting
better?


Set db = CurrentDb() Set rs = db.OpenRecordset("SELECT Dia FROM
tabFeriadosFixos " & "WHERE Dia=" & intDia & " AND Mês=" & intMes)
E_FeriadoFixo = Not rs.EOF
rs.Close
 
Frank Dulk said:
did I place the code below rotating but very slow ta!!! does have as
getting better?


Set db = CurrentDb() Set rs = db.OpenRecordset("SELECT Dia FROM
tabFeriadosFixos " & "WHERE Dia=" & intDia & " AND Mês=" & intMes)
E_FeriadoFixo = Not rs.EOF
rs.Close
Click to expand...

I don't know why it should be so slow. Are the fields [Dia] and [Mês]
indexed? For best results in this sort of query they should be.

How slow was it? Was it slower than the code you were using before?
Try the FindFirst approach I suggested and see if it's just as slow.
 
I placed rs.findfirst but of the a mistake like this

" The Argument is not optional ""

looks at the code

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT Dia FROM tabFeriadosFixos " & "WHERE Dia="
& intDia & " AND Mês=" & intMes)
rs.FindFirst
E_FeriadoFixo = Not rs.EOF
rs.Close


Dirk Goldgar said:
Frank Dulk said:
did I place the code below rotating but very slow ta!!! does have as
getting better?


Set db = CurrentDb() Set rs = db.OpenRecordset("SELECT Dia FROM
tabFeriadosFixos " & "WHERE Dia=" & intDia & " AND Mês=" & intMes)
E_FeriadoFixo = Not rs.EOF
rs.Close
Click to expand...

I don't know why it should be so slow. Are the fields [Dia] and [Mês]
indexed? For best results in this sort of query they should be.

How slow was it? Was it slower than the code you were using before?
Try the FindFirst approach I suggested and see if it's just as slow.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Frank Dulk said:
I placed rs.findfirst but of the a mistake like this

" The Argument is not optional ""

looks at the code

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT Dia FROM tabFeriadosFixos " &
"WHERE Dia=" & intDia & " AND Mês=" & intMes)
rs.FindFirst
E_FeriadoFixo = Not rs.EOF
rs.Close
Click to expand...

No, that was not what I meant. By "try the FindFirst approach", I meant
adapting your original code to open a dynaset-type recordset on the
table and use FindFirst, rather than Seek, like this:

Set db = CurrentDb
Set rs = db.OpenRecordset ("tabFeriadosFixos", dbOpenDynaset)

rs.FindFirst "Dia=" & intDia & " AND Mês=" & intMes

E_FeriadoFixo = Not rs.NoMatch

rs.Close

I don't know whether that would be faster or not, but it would be worth
a try.
 
Back
Top