Search Database Problem

[zfrancis]

hello,

Hi there, this is my first question in this forum!!!

i'm using VB6 with Access 2000. I change the Microsoft DAO 3.51 to Microsoft DAO 3.6 or I'll get a "unregconize data format!" error. The Access and VB is successfully link. I tried to addnew record, delete and so on, no problems found.


However, when I tried to search for a data using SQL statement, like this:

Merit.RecordSource = "select * from Rekod Merit Pelajar where Nombor Sekolah like'*" & SNoSek.Text & "*'"

Merit.Refresh



I get a "runtime error '525'----Data Access Error"!!! The error occur at the line "Merit.Refresh".

So, I download some example and tried them out. I find that the main different is that they are using VB6 with Access 97 and Microsoft DAO 3.51 but not Access 2000 with Microsoft DAO 3.6 . I found that if I change the Reference "Microsoft DAO 3.51" to "Microsoft DAO 3.6", the same error will occur in their example.

So, any idea? I really need the solution as soon as possible. Please help me if you can or show me some example on search function in db using VB6 and Access 2000. Thank you very much.

[Cimperiali]

How about using ADO, instead?

Special thanks to Lothar "the Great" Haensler. Come back soon, you Guru.

[JPicasso_00]

Does your SQL statement work in Access?
The best way to debug SQL statements in Access is to copy them into the
qurey builder or compose them there to begin with and copy the SQL text out of it.

One thing I noticed is that your table name and field looks like they contain spaces..
I would also try puting a space between the LIKE keyword and the ' that follows it...

like this:

select * from [Rekod Merit Pelajar] where [Nombor Sekolah] like '*" & SNoSek.Text & "*'"

Hope this helps.

[zfrancis]

Sir,

I'm sorry, I still get the same error there. Thanks for your respone anyway.

I think I need to show some part of the coding:

Code I use to link VB6 and Access 2000 with DAO 3.6:

Private Sub Form_Load()
Dim db As Database
Dim filename As String

filename = App.Path
If Right$(filename, 1) <> "\" Then filename = filename & "\"
filename = filename & "sistem demerit.mdb"

Set db = DBEngine.OpenDatabase(filename)

Set Merit.Recordset = db.OpenRecordset("Rekod Merit Pelajar")
Set Demerit.Recordset = db.OpenRecordset("Rekod Demerit Pelajar")
End Sub

Then, i place 2 DBGrid in my form and set their DataSource to Merit and Demerit ("Merit" and "Demerit" are both Data Control)

when the form loaded, the DBGrid show the database and everything seem all right except when I use the "search" button with code:

Private Sub SCmd_carinosek_Click()
Merit.RecordSource = "select * from [Rekod Merit Pelajar] where [Nombor Sekolah] like'*" & SNoSek.Text & "*'"
Demerit.RecordSource = "select * from [Rekod Demerit Pelajar] where [Nombor Sekolah] like'*" & SNoSek.Text & "*'"
Merit.Refresh
Demerit.Refresh

End Sub

-"SNoSek" is the textbox for the string to search.
-"Rekod Merit Pelajar" is the table in Access2000 and "Nombor Sekolah" is one of the fields.
I get the runtime error '525' --- "Data Access Error"!!!
the error occured at the line Merit.Refresh

Any idea? Thanks again.

[jn8230]

I noticed you are explicitly setting the recordset property of the Data control on the form load, but you then try to set the recordsource and and use the refresh method to pull in the new recordset.
Is the databasename property of the data control properly set?
You might try explicitly setting the recordset of the data control on the Command Click event like:
[vbcode]Merit.RecordSet = db.OpenRecordset("select * from [Rekod Merit Pelajar] where [Nombor Sekolah] like'*" & SNoSek.Text & "*'" )

Jason

[vivek_29]

Hi there

First thing Access allows you to create table and column name with spaces but sql wont allow you that so you can replace spaces with underscore and second thing you can use '*' in like statement only in Access but when you want to run same query through VB you have to replace '*' with '%'
Hope this helps

[zfrancis]

WOW!!!!! YEAHHH!!!!!
THANK YOU THANK YOU THANK YOU SO MUCH!!!!!!
YOUR SUGGEST IS PERFECT!!!!!
MY PROBLEM SOLVED!!!!
I'm a bit regret for rating you so low.
If i know onebody can only rate once earlier, i will just rate you until the highest mark at the first time!
Thank you again for you help and concern, Jason.
: )

[Joe Keller]

FYI:
SQL does allow spaces in table names you simply bracket the name.. ex;
"SELECT * FROM [Table Name]"

[jn8230]

Don't worry about the rating, I don't. That was actually the first time I was able to solve someone's problem. All the ones I see that I know are already answered, so I am glad to finally be able to contribute. Until now, my only posts have been asking questions myself

Jason

[zfrancis]

Jason,

a very good personality you have, cool.
I've read some of your post, but I don't think i have the ability to help you...
sorry.