| Author |
Topic |
|
mhnsql
Starting Member
11 Posts |
 Posted - 2004-09-20 : 14:38:16
|
| Can a cursor be created in MS Access? I have the ODBC driver installed by the office product on my XP Pro PC.The reason??The table I want to go through a table that has a record id (rec_id) for each record that is sequential and some #'s missing (deleted records). Also, some records has data that excludes the record, I dont want Every record. The execution, however, is v e r y s l o w:select rec_id from {table} where rec_id=(select min(rec_id) from {table} where rec_id> {my last record used} and fieldx<>"bla bla")ThanksPS "rec_id" is indexed |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-20 : 15:23:54
|
| why don't you show us the SQL statement you are trying to execute. In Access, like SQL Server, a cursor will almost always be slower than a regular SQL statement.(FYI -- to use a cursor in Access, you use DAO/ADO and VBA)- Jeff |
 |
|
|
mhnsql
Starting Member
11 Posts |
Posted - 2004-09-21 : 11:09:35
|
| I am attempting to use:DECLARE MyCursor CURSOR FOR SELECT * FROM MyTableAnd when I execute it, I get "Invalid SQL statement , expected 'DELETE','INSERT' ETC....I am assuming that a FETCH NEXT will be much faster than my original queryJeff,please explain using DAO/ADO and VBA. All I know is that I create DataSourceName and configure it to connect to an Access table.Mike |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-21 : 11:27:35
|
| Please, first, show us the SQL statement you are trying that is too slow. If you don't know how to use VB or do programming, then it is a large undertaking to do it well. As mentioned, a set-based SQL Statement will always outperform a cursor -- you should not need to use one.- Jeff |
|
|
|
mhnsql
Starting Member
11 Posts |
Posted - 2004-09-24 : 10:57:25
|
| REC_ID Is a numeric field that is sequential and indexed but skips some numbers. also , some records has data that excludes what I want (so I have an additional exclusion EG AND {fieldx}<>'something' but just with the statement below, it is very slow).SELECT * FROM {TABLE1} WHERE RE_ID=(SELECT MIN(REC_ID) FROM {TABLE1} WHERE REC_ID> 2)If I use the following, it is very fast:SELECT * FROM {TABLE1} WHERE REC_ID =2Again, I want to get the very Next record in the table with the very next highest REC_ID in the table higher than the one I just used. So If I just used rec_id OF 4 and REC_ID 5, 6 AND 7 do not exist, then I need to get 8 returned.So I am wondering if a cursor using FETCH NEXT would be much faster. (Or a better statement)Mike |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-24 : 11:30:09
|
again, please post the actual SQL statement you are trying to execute. I have no idea specifically what you are trying to accomplish.i'll give you an example:----------------------------I have a table of users, like this:UserID, Name1,Jeff2,Bob4,Jim6,EdAnd I would like a SELECT statement that will show me, for each userID, what the NEXT highest userID is in the table. Note that there are gaps in my table (i.e., no userID 5). For the last userID, please return NULL as the next highest user ID.-------------------------------and then I would say:------------------------------Thanks for the detailed information, that is very helpful and I can see exactly what your requirements are.Try:select A.*, (select min(ID) as NextID from yourtable B where B.ID > A.ID) as NextIDfrom yourtable AI hope this helps, good luck!--------------------------------------See how that works ?  - Jeff |
|
|
|
mhnsql
Starting Member
11 Posts |
Posted - 2004-09-24 : 12:54:35
|
| The statement is SELECT * FROM {table1} WHERE REC_ID=(SELECT MIN(REC_ID) FROM {table1} WHERE REC_ID > 2 AND STATE<>'HI' ) |
|
|
|
mhnsql
Starting Member
11 Posts |
Posted - 2004-09-24 : 13:03:07
|
| Jeff,I have only one table, however, I tried select *, (select min(REC_ID) as NextID from CLIENTDATA3 where REC_ID > 2) as NextIDand I get "reserved error, there is no message for this error" |
|
|
|
mhnsql
Starting Member
11 Posts |
Posted - 2004-09-24 : 13:08:22
|
| JeffI also tried your query exactly as you did and I encounter"the ms jet engine does not recognize A.* as a valid field name" |
|
|
|
mhnsql
Starting Member
11 Posts |
Posted - 2004-09-24 : 13:13:29
|
| Jeffselect A.*, (select min(rec_ID) as NextID from clientdata3 A,clientdata3 B where B.rec_ID > A.rec_ID) as NextIDresults in "The field rec_id could refer to more than one table listed in the from clause........." |
|
|
|
mhnsql
Starting Member
11 Posts |
Posted - 2004-09-24 : 13:15:56
|
| I am trying and trying, but I do not see how to do this other than specifing a record id HIGHER than the Minimum number existing in the table which is [MIN(REC_ID) WHERE REC_ID > {last one used}] |
|
|
|
|
Cursors in ms access??