ADO recordset returns no records, identical SQL string returns when pasted in query

[todd.tharp]

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:
<blockquote><pre>
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
</pre></blockquote>


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?

 

[Guest]

What is g.PRJID?
If g is a table, at the very least you need a JOIN in your SQL...
Your SQL doesn't make any sense as it stands. If you copy the SQL from the
query that works (in SQL view) and paste it into your procedure it may work.

In any case, presuming your project has all tables linked or local, I would
code like this:

Public Sub loadAdtNfo()
Call clrAdtNfo
Dim xTyp, strRS As String
Dim rs As Recordset

' -- You need to correct SQL of strRS below --

strRS = "SELECT acctSetup.* FROM acctSetup WHERE acctSetup.PRJID="
& g.PRJID

Set rs = CurrentDb.OpenRecordset(strRS)
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

End Sub

Alternatively, instead of using strRS, you could just create the query that
you know works and use it to create the recordset like this:
Set rs = CurrentDb.OpenRecordset("MyQuery")

You may also want to consider adding some error handling...

Using Domain Aggregate functions is not good for large data sources. They
will look at the whole domain data regardless of supplied criteria and so
will get slower as your tables grow.

There are a couple of ways to speed this up. You can use queries to filter
your data and use them as the domain criteria of your DSum(), DCount() etc.
Another option is to create your own replacement functions that use
recordsets. If you go to mdbmakers.com you will find some of these
replacement functions JLookup(), JCount(), JSum() etc. There may be others on
the MVP sites too.

Steve

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:
<blockquote><pre>
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
</pre></blockquote>


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?

 

[Synapsys]

What is g.PRJID?

g is a custom type I defined, PRJID is one of the items in the type.

Here's where I declare it:

Public Type PrjNfo ' Container for project specific values
PRJID As Long
PRPID As Long
SPNID As Long
SPPID As Long
INVID As Long
PACNo As Long
ProjNo As Long
AcctNo As String
dtBeg As Date
dtEnd As Date
BudTotal As Currency
TotalCalc As Currency ' Award Totals
NOApos As Currency ' Increase totals
NOAneg As Currency ' Decrease totals
ADVtotal As Currency ' Total Advanced
ADRstat As Currency ' Advance Release Balance
CSR As Currency ' Cost Sharing Required
CSM As Currency ' Cost Sharing Met
End Type
Public g As PrjNfo

If g is a table, at the very least you need a JOIN in your SQL...
Your SQL doesn't make any sense as it stands. If you copy the SQL from the
query that works (in SQL view) and paste it into your procedure it may work.

The SQL does make sense because I was able to paste it into a query
window and get records. The problem is that the exact same SQL string
is returning ZERO records via ADODB recordset.

Alternatively, instead of using strRS, you could just create the query that
you know works and use it to create the recordset like this:
Set rs = CurrentDb.OpenRecordset("MyQuery")

You may also want to consider adding some error handling...

Right now, I just want to get the sub to work. Again the SQL returns
records when pasted into a Query SQL window, but doesn't work when
opening a recordset via VBA.

Using Domain Aggregate functions is not good for large data sources. They
will look at the whole domain data regardless of supplied criteria and so
will get slower as your tables grow.

tell me about it. that is why I'm optimizing. The database has
finally reached a level where legacy dlookups are slowing it down.

There are a couple of ways to speed this up. You can use queries to filter
your data and use them as the domain criteria of your DSum(), DCount() etc.
Another option is to create your own replacement functions that use
recordsets. If you go to mdbmakers.com you will find some of these
replacement functions JLookup(), JCount(), JSum() etc. There may be others on
the MVP sites too.

Steve

Thanks for the sites. I'll check them out.

regards,

Todd

 

[Dirk Goldgar]

In

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:
<blockquote><pre>
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
</pre></blockquote>


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.

Check the value of g.PRJID at the point where you're using it to build
the SQL string. Maybe it hasn't been set yet. Also, make sure your
public variable g is defined in a standard module, not in a form module
(or other class module) that would require an extra reference to reach.
Do you have Option Explicit set in all these modules?

 

[Synapsys]

In

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:
<blockquote><pre>
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
</pre></blockquote>

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.

Check the value of g.PRJID at the point where you're using it to build
the SQL string. Maybe it hasn't been set yet. Also, make sure your
public variable g is defined in a standard module, not in a form module
(or other class module) that would require an extra reference to reach.
Do you have Option Explicit set in all these modules?

Dirk,

Yes, I've even removed the g.prjid and put the value straight in.
Same problem. And yes, it is in a standard module, option explicit.

thanks,

todd

 

[Guest]

Ah! Hadn't thought of a custom type...
I was going to suggest using a Function to get the value but I noticed in
another post that you are having problems even entering values directly?

Have you tried adding a semi-colon to the end of the strRS string?


Steve

 

[David W. Fenton]

Yes, I've even removed the g.prjid and put the value straight in.
Same problem. And yes, it is in a standard module, option
explicit.

Is there some reason why you're using ADO instead of DAO? It's very
easy to convert the code to DAO by changing about 3 lines of your
code. I'd test it and see if it works.

And I wouldn't use ADO.

 

[Synapsys]

Steve,

Yeah, I've used a semicolon. In fact, I made strRS because I thought
maybe the concatenation within the rs.open statement might be the
problem.

T

 

[Synapsys]

David,

I switched to ADO because I plan on moving the backend to SQL server
with ODBC and Jet has some problems on that score. ADO is more
flexible. I then plan to migrate stuff to a new table structure and am
just getting ahead of the curve with any new functions / subs I
write. Though, I may just do that so I can move on. I just hate
leaving mysteries like this unresolved. If I haven't figured it out
this weekend, I'm going to take your advice.

Last night as I was falling asleep, it occurred to me to try this
without the WHERE clause, which I just did (strRS = "SELECT * FROM
acctSetup") and got the same result - no records. This is the
equivalent of opening the query acctSetup, which returns many records.

This raises the question as to how Access/VBA deals with aggregate
queries in recordsets. Anyone know? Here's the SQL string of the
query:

SELECT tblOptions.Type, tblOptions.ID1 AS PRJID,
Last(tblOptions.dtFlag) AS Updt, Last(tblOptions.ID2) AS PROID FROM
tblOptions WHERE (((tblOptions.Type) Like ""AS*"")) GROUP BY
tblOptions.Type, tblOptions.ID1; (by the way, I substituted this
string in the rs.open statement, adding the WHERE qualifier for
g.PRJID, still no results.

The tblOptions table is a utility table. There are 7 types beginning
with "AS". These types are used to mark milestones and the user,
project (PRJID) and date are recorded. I have to aggregate because
these milestones are periodically reviewed, so a long term project can
have multiples of the same "AS"(AcctSetup) type.

Does anyone know if aggregate queries aren't handled well by ADO?

T

 

[Dirk Goldgar]

In

David,

I switched to ADO because I plan on moving the backend to SQL server
with ODBC and Jet has some problems on that score. ADO is more
flexible. I then plan to migrate stuff to a new table structure and am
just getting ahead of the curve with any new functions / subs I
write. Though, I may just do that so I can move on. I just hate
leaving mysteries like this unresolved. If I haven't figured it out
this weekend, I'm going to take your advice.

Last night as I was falling asleep, it occurred to me to try this
without the WHERE clause, which I just did (strRS = "SELECT * FROM
acctSetup") and got the same result - no records. This is the
equivalent of opening the query acctSetup, which returns many records.

This raises the question as to how Access/VBA deals with aggregate
queries in recordsets. Anyone know? Here's the SQL string of the
query:

SELECT tblOptions.Type, tblOptions.ID1 AS PRJID,
Last(tblOptions.dtFlag) AS Updt, Last(tblOptions.ID2) AS PROID FROM
tblOptions WHERE (((tblOptions.Type) Like ""AS*"")) GROUP BY
tblOptions.Type, tblOptions.ID1;

(by the way, I substituted this
string in the rs.open statement, adding the WHERE qualifier for
g.PRJID, still no results.

There's your problem right there! I didn't realize that acctSetup was a
query, and therefore might use the Like operator.

In ADO, the wild-card character is '%', not '*'. So use

WHERE (((tblOptions.Type) Like ""AS%""))

 

[Synapsys]

In









There's your problem right there! I didn't realize that acctSetup was a
query, and therefore might use the Like operator.

In ADO, the wild-card character is '%', not '*'. So use

WHERE (((tblOptions.Type) Like ""AS%""))

Ta da!

Dirk, you win the prize!

thank you so much!

 

[Synapsys]

Is there some reason why you're using ADO instead of DAO? It's very
easy to convert the code to DAO by changing about 3 lines of your
code. I'd test it and see if it works.

And I wouldn't use ADO.

I'm curious as to why you wouldn't use ADO - is there a reason other
than DAO being tailored to Jet?

regards,

todd

 

[David W. Fenton]

I switched to ADO because I plan on moving the backend to SQL
server with ODBC and Jet has some problems on that score.

Huh? When Access uses ODBC connections, Jet processes it. ADO is not
going to do anything for you unless you use it to connect directly
to the SQL Server, without linked tables or ODBC. That puts quite a
few limitations on what you can do in Access.

ADO is more
flexible.

Name 3 ways in which ADO is more flexible than DAO, please.

I then plan to migrate stuff to a new table structure and am
just getting ahead of the curve with any new functions / subs I
write. Though, I may just do that so I can move on. I just hate
leaving mysteries like this unresolved. If I haven't figured it
out this weekend, I'm going to take your advice.

It would not be just my advice, but Microsoft's, as well.

Classic ADO is a dead technology -- deader now than DAO, in fact,
which is back in full-fledged development with A2K7 (though it's got
a different library name now).

[]

Does anyone know if aggregate queries aren't handled well by ADO?

Again, I would just say: who cares? Switch to DAO and be done with
it. Trying to avoid Jet is a fool's errand -- it doesn't have any
more problems that the alternatives, it's just a different set of
problems for each data access technology. I would stay with the
non-dead method, i.e., DAO.

 

[David W. Fenton]

I'm curious as to why you wouldn't use ADO - is there a reason
other than DAO being tailored to Jet?

Because ADO is dead.

Because it has bad design flaws (it guesses too much).

Because it is slower than DAO with Jet data.

Because it offers only about 3 features that I don't already have
with DAO.

 

[David W. Fenton]

Ta da!

Dirk, you win the prize!

thank you so much!

This is a problem you would not have had with DAO.

 

[Jamie Collins]

Name 3 ways in which ADO is more flexible than DAO, please.

What do you mean by "more flexible"?

ADO has a flatter object hierarchy e.g. I can create a recordset
without explicitly creating a connection object. With DAO you have to
explicitly create the objects and in the correct order. AFAIK this is
what is commonly meant by ADO being 'flexible'.

ADO has better teardown behaviour e.g. I can let an open recordset
variable go out of scope and be confident it will be closed along with
its Connection. With DAO, you have to explicitly call each Close
method and set your variables to Nothing, and in the correct order
too, for fear of memory leaks.

ADO allows me to connect, execute and fetch asynchronously for an
Access/Jet source (yes, ADO has events) e.g. while executing a SQL
procedure that takes a long time execute I can show a marquee progress
meter and allow the user to get on with other UI operations. With DAO,
your equivalent code will be synchronous and while executing a SQL
procedure that takes a long time execute your users will wonder if the
hourglass cursor icon means you application has crashed.

It would not be just my advice, but Microsoft's, as well.

Citation required.

Classic ADO is a dead technology -- deader now than DAO, in fact,
which is back in full-fledged development with A2K7 (though it's got
a different library name now).

Why then did Microsoft invest in an ACE 12.0 (A2K7) OLE DB provider?
Why does "full-fledged" DAO still not include support for features
introduced in the Jet 4.0 era e.g. CHECK constraints?

While I'm tickled by your "deader than DAO" comment (I don't think it
will catch on like "the dodo" one did <g>) and I know you were stung
by the JRO thing, the truth is that until the Jet 4.0 holes are
plugged in DAO, there will be a need for ADO. Also consider than ADO
is more feature rich: hierarchical recordsets, INFORMATION SCHEMA as
recordsets, asynchronous execution and fetching with events,
disconnected recordsets, save recordsets to disk for later update of
source, XML output -- and that's just recordsets <g>.

Jamie.

--

 

[Jamie Collins]

Huh?

See:

Microsoft Office 2000/Visual Basic Programmer's Guide
Choosing ADO or DAO for Working with Access Databases

http://msdn2.microsoft.com/en-us/library/aa164825(office.10).aspx

"If you are creating new data access components, you should consider
using ADO for its advanced features, simplified object model, and
support for multiple data sources. In particular, ADO is a good choice
if you are developing an Access database solution that will later be
upgraded to SQL Server"

The above is not archived content. I could post links to other MSDN
articles with similar recommendations for ADO over DAO. Sure, they are
dated but we've not had much in the way of more recent advice from
Microsoft: a few blog entries, I understand, however note the same
blogs said we wouldn't get an ACE 12.0 OLE DB provider which we later
did get.

If you know of more recent Microsoft articles with contrary advice,
please post a link to them for the OP's benefit.

Jamie.

--

 

[Jamie Collins]

Because ADO is dead.

Citation required. And chose carefully e.g. this one says that the Jet
engine itself is 'dead':

MDAC 2.8 Overview
Deprecated Components

http://msdn.microsoft.com/library/d...us/mdacsdk/htm/mdac_deprecated_components.asp

"Microsoft has deprecated the Microsoft Jet Engine, and plans no new
releases or service packs for this component."

Because it has bad design flaws (it guesses too much).

Examples please, because I think you may have misunderstood something

Because it is slower than DAO with Jet data.

The OP said, "other than DAO being tailored to Jet". Also consider
that for the vast majority of SQL queries/statements executed against
a Jet source there will be no noticeable difference in performance.

Because it offers only about 3 features that I don't already have
with DAO.

Clearly wrong, unless you meant additional or improved 1) properties,
2) methods and 3) events <g>.

Jamie.

--

 

[kerrywano]

I would be curious to see how people feel about DAO now.

Anyways, I am so glad I found this post. I'm working on a very old classic ASP site with a mini DB in Access as the backend.

In Access, I had a query that returned employee records. This query returned records to my VBScript. They needed a seperate section for specific types of employees, so I added a LIKE clause to the query in Access. Boom! No records. Remove the LIKE clause, returns records.

I racked my brain trying to figure this one out before running into this (old) post. I took the LIKE clause out, so the query will still run in Access, and moved it to the SQL text in VBScript with proper SQL '%'.

 

[bmwjohnno]

Hi Dirk, I replicated the problem in some VBA (ADO) code I use all the time for returning disconnected recordsets. The guilty party was, you guessed it, part of the query contains the "AS" word (WHERE ((AREG.Text_12) Like "*AS*Lid*") AND ((Eng_Pits_Audit.INSP_DATE) Is Not Null)) Removing that enabled the ADO code to run (I filtered by ID instead)! By the way I call the query directly "SELECT * FROM [Audit Data Pits]".

Thanks for the hint (unfortunately derived from Synapsis problem) I was going bezerk!