How come opening recordsets doesn't pay attention to criteria?

A

Access9001

Registered User.
Local time
Yesterday, 17:35
Joined
Feb 18, 2010
Messages
268
I have the following

dim rst as adodb.recordset
set rst = new adodb.recordset

rst.open "select * from officeTemp", currentproject.connection

But the problem is that rst now possesses a set that is, say, 50 entries long. If I open up "select * from officeTemp" manually in Access, it shows 42 records (and correctly excludes 8 records as denoted in the criteria of the query).

Why is this happening?
 
officeTemp is the name of the table isn't it? You should be using the query name (where the sql statement resides) instead and not the SQL statement, i.e.

rst.open "Query name", currentproject.connection
 
officeTemp is a query. Even when I write it rst.open "OfficeTemp," I get the same result.
 
select office.branch, office.zone from office where (office.branch not like "*South*") order by office.branch
 
Nothing wrong with the sql. Hmmm... have you tried DAO?
 
I don't know how to properly open DAO recordsets -- I was using it before but for some reason I ran into errors
 
For example,
Code: 
dim db as DAO.Database
dim rst as DAO.Recordset

set db = currentdb
set rst = db.openrecordset("SELECT ...", <other options here>)

Msgbox rst.RecordCount .... display a record count just to verify the number of records

Perhaps this short DAO tutorial will help:

http://allenbrowne.com/ser-29.html
 
I think that worked -- thank you very much.

Is there a limit to DAO recordsets in terms of size or processing? Sometimes the queries I process are quite large.
 
DAO is quite capable. You would have to test it out on your large dataset to see how it performs before considering any performance tuning.
 
Problem: Sometimes I run into the error "System resource exceeded" exporting SQL through this method even though I have plenty of system resources. What's going on?
 
You must log in or register to reply here.

Similar threads

B
Solved AuditChanges not capturing ID for NEW additions
Replies
7
Views
908
Pat Hartman
P
N
How to ensure the open recordset include also any new record entered
Replies
9
Views
588
nector
N
R
  • Question Question
Using vba to select a particular record in a combo box is not working properly
Replies
4
Views
452
cheekybuddha
cheekybuddha
A
Solved Loop recordset and print reports
Replies
5
Views
733
Pat Hartman
P
N
Solved Caching recordset in MS Access VBA
2
Replies
26
Views
2,483
tvanstiphout
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom