[RESOLVED] I NEED HELP!!! DAO Recordset Problem

[avidichard]

I am getting really MAD now. It's been Hours that I am working on this and searching on google for anything but No answer!!!

Can someone please tell me how to Do a search using Dao Recordset like we do in VB6? Here is the code I have in a module

Public WS as dao.Workspace
Public DB as dao.Database
Public RS as dao.Recordset

Public Sub SearchInDatabase(ByVal sSearch as string)

  Dim sSQL As String
  ...
  DB = WS.OpenDatabase("{MDB_FilePath}")
  sSQL = "SELECT * FROM {Table} WHERE {FieldName} LIKE '*{Value}*'"
  RS = DB.OpenRecordset(sSQL)
  Do While Not RS.EOF
    ...
  Loop
  ...
End Sub

Here is my problem. The databse is opened correctly but when it comes to the line RS = DB.OpenRecordset it tells me that I need to create a New instance of the object RS. HOW DO WE DO THAT!!!!

I'm sorry for the capitals but after hours of search and head scratching I gave up and posted here!

Please, someone help me! AND NO, I don't want to use ADO or any other. I want to know how to use DAO like VB6!

Thank you in advance for your help!

[dglienna]

How about this?

Public RecSet as dao.Recordset,RS as dao.Recordset
RS= NEW RecSet

Also, put this in the VB6 FORUM next time, if that's what it is.

DAO is very old. Why work with it at all? ADO is preferred

[avidichard]

I'm using VB.NET and I want an answer for VB.NET. Why would I put this in VB6 forums???

I'll give feed back after trying your suggestion. Thank's for your answer.

[dglienna]

I don't know why you don't use ADO, as there is support built in.

Dim RS = New dao.Recordset

VB.Net lets you assign during a DIM, so you can use that, if it's even allowed

[HanneSThEGreaT]

I have to agree with David here. Dao is very old, and support for it is dying in .NET.

ADO.NET is the preferred way to handle databases in .NET

[DataMiser]

Agreed DAO has been dead for years. I don't think I have used this in at least 10 years now.
Use ADO instead.

DAO btw is not VB6 either it is VB5 and older. VB6 uses ADO. You will also find that getting the proper run time files to get DAO to work on the end users machine can be rather difficult now.

[avidichard]

Neither of the answers work. I know there is almost no support for DAO but still, that's what I'm using right now. I'll maybe plan updating my code when I have double the time I just spent programming with DAO but for now ALL my code works with DAO and I don't really want to rewrite it completely.

So none of the answers wroked up to now. Any other solution seriously, I really need to make that work. Thank you!
I posted the sceenshot of the error raised by VB.NET.

[HanneSThEGreaT]

OK, common sense tells me not to do what I'm about to, but yeah, we are here to try and help and guide wherever possible.

Because our guidence and advice didn't seem to "help" you much, let's do a test. Upload your project in zip format here, and we could perhaps have a look at it, and take it from there...

[avidichard]

For security issues I connot post my project but I have good news. I figured out what the problem was so I'm going to post the solution here for those that still use DAO. I really thank everyone for trying to help me even though I'm way behind with my DAO.

The initial problem was not my RS = DB.OpenRecordset even though VB pointed the error there. NO, the error was on the DB = WS.OpenDatabase.

I removed Public WS As dao.WorkSpace and replaced it with Public EN as dao.DBEngine.

Now My code looks like this when I open my database.

Public sub OpenDatabase(ByVal DBPath As String)
  EN = New dao.DBEngine
  DB = EN.OpenDatabase(DBPath)
End Sub

Now I can Use RS = DB.OpenRecordset any time without creating a new instance of whatever. Here is the sample code:

Public Sub SearchDatabase(ByVal sSearch As String)
  ...
  sSQL = "SELECT * FROM {TableName} WHERE {FieldName} LIKE '*{Value}*'"
  RS = DB.OpenRecordset(sSQL)
  Do While Not RS.EOF
    LstResults.Items.Add(RS.Fields("{FieldName}").Value)
    RS.MoveNext()
  Loop
  ...
End Sub

Again thanks for your time and support. I love CodeGuru and those that help me out. I hope this will help others out if needed. And for my further programs, I'll work with ADO since it seems that not many people like it anymore and almost no support is given.

Again thanks a lot!

[HanneSThEGreaT]

Thanks for sharing! I'm glad you have sorted it out