Solved

DAO vs Access 2000

Posted on 2000-04-22
29
316 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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
 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

770 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