Solved

DAO vs Access 2000

Posted on 2000-04-22
29
312 Views
Last Modified: 2010-05-02
I usually use a .mdb file created with Access 97.
In VB, I use DAO 3.51.
Now, I must use an Access 2000 .mdb, so I must switch to DAO 3.6 in VB to use this new format.
How can I search a recordset in this new DAO library?
rs.findfirst doesn't seem to work anymore.  Any suggestions?
Thanks!
0
Comment
Question by:Mustadio
  • 12
  • 6
  • 6
  • +4
29 Comments
 
LVL 6

Expert Comment

by:Marine
ID: 2739966
i don't know but it worked for me.
rs.FindFirst "Name='" & txtName & "'"
0
 
LVL 6

Expert Comment

by:Marine
ID: 2739968
ofcourse check NoMatch property if it found any records or not.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2740019
I ran into some trouble after conversion of my application to A2000.
I used a concatenated key that contained an integer. After conversion the "string" key after the integer wasn't concatenated anymore. I had to explicitly convert the integer to a string to get it working again.
Advice:
Check the value of the field you are trying to find. Is it still like under A97 ?!
0
 

Author Comment

by:Mustadio
ID: 2740030
Adjusted points from 40 to 50
0
 

Author Comment

by:Mustadio
ID: 2740031
Yes, it is...
I have declared a variable like this:
dim rs as recordset
Now, the rs is a RECORDSET from the DAO 3.6 library.
When I write rs.findfirst, it says: "Object does not support this property or method" (or something similar)...
So, if the FINDFIRST doesn't exist anymore, how can I FIND through my recordset?
THANKS!
0
 
LVL 6

Expert Comment

by:Marine
ID: 2740047
show me how you use FindFirst in your code give me these few lines of code . I'd like to see what the problem you having.
0
 
LVL 9

Expert Comment

by:Ruchi
ID: 2740097
You need to declare a form-level Recordset object variable like this:
Dim rs As DAO.Recordset

See http://support.microsoft.com/support/kb/articles/q238/4/01.asp
0
 
LVL 6

Expert Comment

by:Marine
ID: 2740119
it's not required to be a form level. What do you say that it can't be declared localy ?
0
 

Author Comment

by:Mustadio
ID: 2740275
I AM able to connect to the database, using 3.6 library.
I AM able to set my recordset, and read it with movefirst, movelast and movenext.
What I am unable to to is findfirst and findnext.
When I write:
rs.findfirst "Table = 2"
it says that rs does not support this property or method...
(guess the property or method IS FINDFIRST..?)
how am I supposed to FIND items in my rs if not using FINDFIRST?
0
 

Author Comment

by:Mustadio
ID: 2740281
Adjusted points from 50 to 60
0
 
LVL 9

Expert Comment

by:Ruchi
ID: 2740314
Marine, I'm sorry for messing this up. Don't need to declare a form level record obejct.

Here is a way to search your recordset/table using DAO:

Dim db As Database
Dim rs As DAO.Recordset

Set db = DBEngine.OpenDatabase("Nwind.mdb")

Set rs = db.OpenRecordset("Select * from Customers")

rs.FindFirst "CustomerID = 'BOTTM'"

If rs.NoMatch = True Then
MsgBox "No Match!"
Else
MsgBox rs.Fields("CustomerID").Value
End If

db.Close
0
 
LVL 9

Expert Comment

by:Ruchi
ID: 2740322
Mustadio, Where do you put the declaration Dim rs as Recordset?
0
 

Author Comment

by:Mustadio
ID: 2740407
First of all, I stated that my problem IS that FINDFIRST DOESNT WORK...
My rs as recordset (or DAO.recordset) is global to the main form.
0
 
LVL 9

Expert Comment

by:Ruchi
ID: 2740418
Post your whole code here....
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Expert Comment

by:tmcnally
ID: 2740511
I seem to recall in Access 2K if creating a new database the object model is by default the ADO recordset not DAO. ADO doesn't have the findfirst method -- uses Find (Criteria, SkipRows, SearchDirection, Start) instead.  If you converted an Access97 to Access 2000 mdb, then it would inherit the DAO object model from 97.  Did you convert from 97 to 2000 or just create a new database, hence a new ADODB object model?
0
 

Author Comment

by:Mustadio
ID: 2740991
I have created a whole new database directly from Access 2000.
BUT, I included in VB the DAO object library.  Are you saying the Access 2000 database format MUST be access by ADO method?
0
 
LVL 6

Expert Comment

by:Marine
ID: 2740993
NO NO. You can use any access method that is available to you.
0
 

Author Comment

by:Mustadio
ID: 2740998
Adjusted points from 60 to 70
0
 

Author Comment

by:Mustadio
ID: 2740999
Listend guys... I have build many programs that work just fine using DAO 3.51 library.
So, stop trying to help me to understand how a recordset work, please.  I just want to know what is different now with Access 2000 and 3.6 library that would make the .findfirst method to no longer exist...
0
 
LVL 6

Expert Comment

by:Marine
ID: 2741003
And i already explained that there is no difference in Recordset. I've gave you a snipped of the code that works for me. The other person gave you same code as well.
0
 

Author Comment

by:Mustadio
ID: 2741007
Ok, so you clearly see now that, my problem, you never had.
No need to show me your code, it will without a doubt be exactly the same as mine.  So the problem seems to be somewhere else. Thanks for your help, Marine.
Can anybody help me?
0
 
LVL 9

Expert Comment

by:Ruchi
ID: 2741195
Try to press F2 to bring up the VB Object Browser. Click on the Recordset oject and you can see a LIST of ALL the methods and properties of the Recordset object. Does that help you?
0
 

Author Comment

by:Mustadio
ID: 2741446
Ruchi, that's the first thing I've done.  I can clearly see findfirst and findnext, but, when I use one of 'em, the compilator say: "Object does not support this property or method", whatever I write after.
It does the same thing if I write
rs.findfirst
rs.findfirst ""
rs.findfirst "Street = 'TEST'"
rs.findfirst "Street = '" & txtStreet & "'"
:(
0
 

Expert Comment

by:mdmurphy
ID: 2742534
Mustadio,
There appears to be some confusion within your references and/or component configuration. The new data access method ADO (Active Data Objects) does not have a 'FindFirst' or 'FindNext' method. And as you have previously stated (and we all know) DAO does have these methods. Just for testing purposes, use the 'MoveFirst', then the 'Find' methods, and see if you are somehow configured for ADO, then you can take it from there--at that point it's a configuration issue.
Hope this helps.
MDMurphy
0
 
LVL 9

Expert Comment

by:Ruchi
ID: 2754270
Mustadio, really?
Did you install MDAC 2.1? If not, try to download at MS Site http://www.microsoft.com/data/download_21242023.htm
0
 

Author Comment

by:Mustadio
ID: 2835196
Doesn't work.  Find property doesn't exist here.
0
 

Accepted Solution

by:
Mustadio earned 0 total points
ID: 2835204
I had to use Movefirst and Movenext to seek through the whole recordset... what a shame! :P
0
 
LVL 5

Expert Comment

by:ianB
ID: 2853663
Community Support has reduced points from 70 to 0
0
 
LVL 5

Expert Comment

by:ianB
ID: 2853665
Comment accepted as answer
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now