S
Synapsys
I've got a baffling problem with a particular ADO recordset in a
database I've been developing for the last 6-7 years or so.
Here's the setup:
I'm in the process of optimizing a very large MDB frontend. Compacted
it runs 29mb, connecting to a data backend which runs at ~80mb
compacted. There are 16 users who use their own local copy of the
frontend MDE to access the networked backend. As the data has
increased, we've begun to see perfomance issues when records are
changed and when moving through various controls on the form.
Many controls on the database aren't bound to the underlying table,
but instead use domain lookups (Dlast and Dsum and Dlookup mostly).
I've created several custom types with elements to store some of this
info in memory and reduce the amount of table lookups.
I have an autoexec macro which runs some VBA code which loads these
custom types with user info (full name, ID, etc) and office info
(phone, fax, address, etc), then loads the main form. The current
event on this form then loads more custom types with record specific
lookup information.
So here's the problem: One of the load functions is returning no
records (EOF), but if I take the identical SQL string used to open the
recordset and paste it into a blank Access query window, I return
records! I've copied this code from an identical function which
doesn't return EOF, so I'm relative certain I've got the syntax for
opening an ADODB recordset entered correctly.
Here's the subroutine:
______________________________________________________________
Public Sub loadAdtNfo()
Call clrAdtNfo
Dim xTyp As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
Dim strRS As String
strRS = "SELECT acctSetup.* FROM acctSetup WHERE acctSetup.PRJID="
& g.PRJID
rs.Open strRS, cn
If rs.EOF Then: GoTo ExitOut
rs.MoveFirst
Do Until rs.EOF
xTyp = Mid(rs("Type"), 3)
Select Case xTyp
[[SNIP]]
End Select
rs.MoveNext
Loop
ExitOut:
rs.Close
Set cn = Nothing
End Sub
________________________________________________________________
So has any one seen this problem and know why this is happening?
Understand that I've done all the tried and true debug methods I know
and the crazy thing is that the exact SQL string returning no records
in the above subroutine, returns records when run in a query.
Any ideas?
database I've been developing for the last 6-7 years or so.
Here's the setup:
I'm in the process of optimizing a very large MDB frontend. Compacted
it runs 29mb, connecting to a data backend which runs at ~80mb
compacted. There are 16 users who use their own local copy of the
frontend MDE to access the networked backend. As the data has
increased, we've begun to see perfomance issues when records are
changed and when moving through various controls on the form.
Many controls on the database aren't bound to the underlying table,
but instead use domain lookups (Dlast and Dsum and Dlookup mostly).
I've created several custom types with elements to store some of this
info in memory and reduce the amount of table lookups.
I have an autoexec macro which runs some VBA code which loads these
custom types with user info (full name, ID, etc) and office info
(phone, fax, address, etc), then loads the main form. The current
event on this form then loads more custom types with record specific
lookup information.
So here's the problem: One of the load functions is returning no
records (EOF), but if I take the identical SQL string used to open the
recordset and paste it into a blank Access query window, I return
records! I've copied this code from an identical function which
doesn't return EOF, so I'm relative certain I've got the syntax for
opening an ADODB recordset entered correctly.
Here's the subroutine:
______________________________________________________________
Public Sub loadAdtNfo()
Call clrAdtNfo
Dim xTyp As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
Dim strRS As String
strRS = "SELECT acctSetup.* FROM acctSetup WHERE acctSetup.PRJID="
& g.PRJID
rs.Open strRS, cn
If rs.EOF Then: GoTo ExitOut
rs.MoveFirst
Do Until rs.EOF
xTyp = Mid(rs("Type"), 3)
Select Case xTyp
[[SNIP]]
End Select
rs.MoveNext
Loop
ExitOut:
rs.Close
Set cn = Nothing
End Sub
________________________________________________________________
So has any one seen this problem and know why this is happening?
Understand that I've done all the tried and true debug methods I know
and the crazy thing is that the exact SQL string returning no records
in the above subroutine, returns records when run in a query.
Any ideas?
