ADO Recordset Error 
June 29th, 2009, 03:43 PM
|  | Member | | Join Date: Dec 2007
Posts: 62
| |
I get the following error:
run-time error '-2147217904 (80040e10)'
Method 'open' of object '_Recordset' field
How to solve the problem?
Please Help me. I am waiting for your response. -
Dim CONN As New ADODB.Connection
-
Dim RS As New ADODB.Recordset
-
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;"
-
query="select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by DrugSort"
-
RS.Open query, CONN, adOpenKeyset, adLockOptimistic
-
-
RS.Close
-
CONN.Open
-
CONN.Close
-
| 
June 30th, 2009, 12:28 PM
| | Expert | | Join Date: Jun 2007 Location: Derbyshire, UK
Posts: 344
Provided Answers: 5 | | | re: ADO Recordset Error Quote:
Originally Posted by pentahari I get the following error:
run-time error '-2147217904 (80040e10)'
Method 'open' of object '_Recordset' field
How to solve the problem?
Please Help me. I am waiting for your response. -
Dim CONN As New ADODB.Connection
-
Dim RS As New ADODB.Recordset
-
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;"
-
query="select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by DrugSort"
-
RS.Open query, CONN, adOpenKeyset, adLockOptimistic
-
-
RS.Close
-
CONN.Open
-
CONN.Close
-
| Hi
Perhaps instead of this - RS.Open query, CONN, adOpenKeyset, adLockOptimistic
-
-
RS.Close
-
CONN.Open
-
you could try this - CONN.Open
-
RS.Open query, CONN, adOpenKeyset, adLockOptimistic
-
RS.Close
???
MTB
| 
July 1st, 2009, 12:43 AM
| | Member | | Join Date: Jun 2009
Posts: 33
| | | re: ADO Recordset Error
it also doesn't look like you've declared the query string variable.
or is this a new function that my old old software doesn't have?
| 
July 1st, 2009, 03:53 PM
|  | Member | | Join Date: Dec 2007
Posts: 62
| | | re: ADO Recordset Error
I Change my coding to yours reply but that error still come. -
Dim CONN As New ADODB.Connection
-
Dim RS As New ADODB.Recordset
-
Dim query as String
-
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;"
-
query="select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by DrugSort"
-
CONN.Open
-
RS.Open query, CONN, adOpenKeyset, adLockOptimistic
-
RS.Close
-
CONN.Close
-
| 
July 1st, 2009, 04:00 PM
| | Member | | Join Date: Jun 2009
Posts: 33
| | | re: ADO Recordset Error
try: -
Dim CONN As New ADODB.Connection
-
Dim RS As New ADODB.Recordset
-
Dim query as String
-
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;"
-
query = "select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by DrugSort"
-
CONN.Open
-
RS.ActiveConnection = CONN
-
RS.Open (query)
-
-
RS.Close
-
CONN.Close
-
| 
July 2nd, 2009, 12:26 PM
| | Expert | | Join Date: Jun 2007 Location: Derbyshire, UK
Posts: 344
Provided Answers: 5 | | | re: ADO Recordset Error Quote:
Originally Posted by pentahari I Change my coding to yours reply but that error still come. -
Dim CONN As New ADODB.Connection
-
Dim RS As New ADODB.Recordset
-
Dim query as String
-
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;"
-
query="select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by DrugSort"
-
CONN.Open
-
RS.Open query, CONN, adOpenKeyset, adLockOptimistic
-
RS.Close
-
CONN.Close
-
| Hi
Perhaps you could try - Dim CONN As New ADODB.Connection
-
Dim RS As New ADODB.Recordset
-
Dim query as String
-
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;"
-
query="select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by IIf([AllergyType]=""Drug Allergy"",1,0)"
-
CONN.Open
-
RS.Open query, CONN, adOpenKeyset, adLockOptimistic
-
RS.Close
-
CONN.Close
??
I have changed the ORDER BY clause, ie it is explicitly calculated.
MTB
| 
August 26th, 2009, 09:48 AM
| | Familiar Sight | | Join Date: Oct 2007
Posts: 158
| | | re: ADO Recordset Error
hi
if datatype of allergyType filed is memo then iif condition will generate error.
because iif dont support memo datatypes.
if this is not the case and all of the aforementioned suggestions failed then run the
query in database and look for #Error in the DataSheet view. if found then remove it first.
regards
manpreet singh dhillon hoshiarpur
| 
September 4th, 2009, 04:51 AM
| | Newbie | | Join Date: May 2009
Posts: 11
| | | re: ADO Recordset Error
Hi everyone,
I have a program about search engine about music.. I like to add music player also but for now i just focus on searching a correct data on a database.. Since I use to study visual basic, I use practice as well. Below is the code wherein I have to search all the data on a database (THIS IS TO TYPE ALL THE TEXTBOXES AND WHEN ONE TEXTBOX IS NOT FILL, THE MESSAGEBOX WILL SHOW "Search not found". These are Title, Artist, Album and Year. I want to search only one or two textboxes and it will show "search found". For example: I type the title on a textbox and when I click the search button it should be "search found" even if i'm not filling the Artist, the Year and Album textbox.. I try to using IF..THEN(nesting) on the next statement but i'm confused on getting a "search not found" even I use the rs.movenext.. What are the alternative ways to do this?
Private Sub CommandButton1_Click()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\databases\Songs.mdb;Persist Security Info=False"
rs.Open "Select * from Songs1", cn, adOpenForwardOnly, adLockReadOnly
Dim a As String
Dim b As String
Dim c As String
Dim d As String
a = txtTitle.Text
b = txtArtist.Text
c = txtYear.Text
d = txtAlbum.Text
If a = "" And b = "" And c = "" And d = "" Then
MsgBox "Please fill the textboxes"
Else
Do While Not rs.EOF
If b <> rs!Artist And a <> rs!Title Then
rs.MoveNext
ElseIf c <> rs!Year And d <> rs!Album Then
rs.MoveNext
Else
MsgBox "Search found"
Set rs = Nothing
cn.Close
Exit Sub
End If
Loop
MsgBox "No search found"
Set rs = Nothing
cn.Close
End If
End Sub
I will appreciated for those who'd rply my message..
Very thanks.. =)
| 
September 19th, 2009, 04:56 AM
|  | Familiar Sight | | Join Date: Dec 2007 Location: Lucknow, India
Posts: 194
| | | re: ADO Recordset Error
In place of -
If b <> rs!Artist And a <> rs!Title Then
-
rs.MoveNext
-
ElseIf c <> rs!Year And d <> rs!Album Then
-
rs.MoveNext
-
Else
-
try using: -
If b <> rs!Artist And a <> rs!Title And c <> rs!Year And d <> rs!Album Then
-
rs.MoveNext
-
Else
-
| 
September 22nd, 2009, 04:55 AM
| | Newbie | | Join Date: May 2009
Posts: 11
| | | re: ADO Recordset Error
Hello smartchap,
Thanks for the reply and I really appreciated it.. it works and i pass it to flexgrid.. and I try also using a procedure and I seperate the Title, Artist, year and album then calling the procedure.. thanks for the help.. Actually it works in terms of searching a record. my problem is that when I search it, the data will found but when I type another record, it adds to what I've search on the previous one.. I like to search only those records and it will not add another record on the previous one.. I hope u understand.. here is the code on what I did.. -
Private Sub CommandButton1_Click()
-
-
Dim rs As New ADODB.Recordset
-
Dim cn As New ADODB.Connection
-
-
If txtTitle.Text = "" And txtArtist.Text = "" And txtYear.Text = "" And txtAlbum.Text = "" Then
-
MsgBox "Please fill the textboxes"
-
ElseIf Not txtTitle.Text = "" Then
-
Call TitleSearch
-
ElseIf Not txtArtist.Text = "" Then
-
Call ArtistSearch
-
ElseIf Not txtYear.Text = "" Then
-
Call YearSearch
-
ElseIf Not txtAlbum.Text = "" Then
-
Call AlbumSearch
-
End If
-
-
End Sub
-
-
Private Sub Form_Load()
-
Dim LWidth As Long
-
Dim i As Integer
-
Dim j As Integer
-
Const BarWidth = 320
-
-
With MSFlexGrid1
-
-
.Cols = 4
-
-
For i = 0 To 3
-
MSFlexGrid1.ColAlignment(i) = flexAlignCenterCenter
-
Next i
-
-
For j = 0 To 3
-
.ColWidth(j) = 1445
-
Next j
-
-
LWidth = .Width - BarWidth
-
.FixedCols = 0
-
.Rows = 0
-
-
.AddItem "Title" & vbTab & "Artist" & vbTab & "Album" & vbTab & "Year"
-
.Rows = 2
-
.FixedRows = 1
-
.WordWrap = True
-
.RowHeight(0) = .RowHeight(0) * 2
-
-
End With
-
End Sub
-
-
Public Sub ArtistSearch()
-
Dim rs As New ADODB.Recordset
-
Dim cn As New ADODB.Connection
-
Dim b As String
-
-
b = txtArtist.Text
-
-
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\databases\Songs.mdb;Persist Security Info=False"
-
rs.Open "Select * from Songs1", cn, adOpenForwardOnly, adLockReadOnly
-
-
Do While Not rs.EOF
-
If b <> rs!Artist Then
-
rs.MoveNext
-
Else
-
MsgBox "Search found"
-
MSFlexGrid1.AddItem rs!Title & vbTab & rs!Artist & vbTab & rs!Album & vbTab & rs!Year
-
Set rs = Nothing
-
cn.Close
-
Exit Sub
-
End If
-
Loop
-
MsgBox "No search found"
-
Set rs = Nothing
-
cn.Close
-
End Sub
-
-
Public Sub TitleSearch()
-
Dim rs As New ADODB.Recordset
-
Dim cn As New ADODB.Connection
-
Dim a As String
-
-
a = txtTitle.Text
-
-
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\databases\Songs.mdb;Persist Security Info=False"
-
rs.Open "Select * from Songs1", cn, adOpenForwardOnly, adLockReadOnly
-
-
Do While Not rs.EOF
-
If a <> rs!Title Then
-
rs.MoveNext
-
Else
-
MsgBox "Search found"
-
MSFlexGrid1.AddItem rs!Title & vbTab & rs!Artist & vbTab & rs!Album & vbTab & rs!Year
-
Set rs = Nothing
-
cn.Close
-
Exit Sub
-
End If
-
Loop
-
MsgBox "No search found"
-
Set rs = Nothing
-
cn.Close
-
End Sub
-
-
Public Sub YearSearch()
-
-
Dim rs As New ADODB.Recordset
-
Dim cn As New ADODB.Connection
-
Dim c As String
-
-
c = txtYear.Text
-
-
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\databases\Songs.mdb;Persist Security Info=False"
-
rs.Open "Select * from Songs1", cn, adOpenForwardOnly, adLockReadOnly
-
-
Do While Not rs.EOF
-
If c <> rs!Year Then
-
rs.MoveNext
-
Else
-
MsgBox "Search found"
-
MSFlexGrid1.AddItem rs!Title & vbTab & rs!Artist & vbTab & rs!Album & vbTab & rs!Year
-
Set rs = Nothing
-
cn.Close
-
Exit Sub
-
End If
-
Loop
-
MsgBox "No search found"
-
Set rs = Nothing
-
cn.Close
-
End Sub
-
-
Public Sub AlbumSearch()
-
Dim rs As New ADODB.Connection
-
Dim cn As New ADODB.Connection
-
Dim d As String
-
-
d = txtAlbum.Text
-
-
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\databases\Songs.mdb;Persist Security Info=False"
-
rs.Open "Select * from Songs1", cn, adOpenForwardOnly, adLockReadOnly
-
-
Do While Not rs.EOF
-
If d <> rs!Album Then
-
rs.MoveNext
-
Else
-
MsgBox "Search found"
-
MSFlexGrid1.AddItem rs!Title & vbTab & rs!Artist & vbTab & rs!Album & vbTab & rs!Year
-
Set rs = Nothing
-
cn.Close
-
Exit Sub
-
End If
-
Loop
-
MsgBox "No search found"
-
Set rs = Nothing
-
cn.Close
-
-
End Sub
-
I'll be wait for the reply.. thanks a lot.. =)
Last edited by debasisdas; September 25th, 2009 at 08:55 AM.
Reason: formatted using code tags
| 
September 25th, 2009, 08:39 AM
|  | Familiar Sight | | Join Date: Dec 2007 Location: Lucknow, India
Posts: 194
| | | re: ADO Recordset Error
OK you do one thing. In the command1_Click event modify code as below: -
Dim rs As New ADODB.Recordset
-
Dim cn As New ADODB.Connection
-
-
Me.MSFlexGrid1.Clear
-
Also, write code for clearing the textboxes as soon as any one of them is clicked, so that previous data is cleared otherwise it will always search first for Title then for Artist and so on. So if u search for a title and next if you search for an artist, it will again search for previous Title only.
Hope it clears your query.
| 
September 30th, 2009, 06:18 AM
| | Newbie | | Join Date: May 2009
Posts: 11
| | | re: ADO Recordset Error
Hi smartchap..
Thanks for the rply.. Now I have another problem with my program again which is searching music and since it is ok i guess in searching a correct data... now I have 2 problems.. problem no. 1 in searching a data again, what if i have the same title when I search but different artist and same artist but different titles? for example, I type in titlebox Because of you by Ne-yo and Because of you by 98 Degrees.. Also regarding its year.. what if some song year was the same and genre? there some songs that are 2005 and 2006. Should I change my sql syntax in recordset: "Select * from Songs1 where artist = "" "something like that?.. my problem no.2 is previewing the data with the use of media player.. I add a form for a preview so that it will play.. now i know the components to insert a media player.. I add a path column on a database so that I call it and play.. Now my problem is that, how should I call those mp3s and its path in a database in order to play the song?.. Sorry for asking again a question.. because i'm really new in vb and trying hard to get some concepts in order to understand the logic of my program on what i'm doing.. and in the next program that i will do.. I will again wait for the rply.. The code is above from my previous post but i change the .Cols = 4 into Textmatrix which is the column and row..
Thanks
Kyosuke18
| 
October 1st, 2009, 09:00 AM
|  | Familiar Sight | | Join Date: Dec 2007 Location: Lucknow, India
Posts: 194
| | | re: ADO Recordset Error
Dear Kyosuke
For question 1, modify all 4 subroutines as per code given below: -
Public Sub ArtistSearch()
-
Dim rs As New ADODB.Recordset
-
Dim cn As New ADODB.Connection
-
Dim b As String
-
Dim bFound As Boolean
-
-
b = txtArtist.Text
-
bFound = False
-
-
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=(full path here)\Songs.mdb;Persist Security Info=False"
-
rs.Open "Select * from Songs1", cn, adOpenForwardOnly, adLockReadOnly
-
-
Do While Not rs.EOF
-
If LCase(b) <> LCase(rs!Artist) Then
-
rs.MoveNext
-
Else
-
'MsgBox "Search found"
-
MSFlexGrid1.AddItem rs!Title & vbTab & rs!Artist & vbTab & rs!Album & vbTab & rs!Year
-
bFound = True
-
rs.MoveNext
-
End If
-
Loop
-
If bFound = False Then MsgBox "No search found"
-
Set rs = Nothing
-
cn.Close
-
End Sub
-
For question 2, better u post the code (or project) what u have done so far so that I will try to modify / correct that itself. You can post a zip file. Also next time post ur code using code tags.
| 
October 6th, 2009, 04:36 AM
| | Newbie | | Join Date: May 2009
Posts: 11
| | | re: ADO Recordset Error
Hi Smartchap..
Thanks for the reply.. I test it and it works, thanks for that. Attached herewith is my program that I did so far.. I'm thinking of something to the table in database that I created, do I have to create another table for artist table, album table something like that instead of storing those records into one table? In the 3rd form that I created, I just tested on how to open a music and play it in a media player.. It is not connected on the database yet.. But rather I declared rs as adodb.recordset and cn as adodb.connection.. Is just that I don't know what should i go first.. And another question, Is it possible that when I double click the data on the flexgrid when I search it, follows on playing the music? Please check if there are some possible solution.. Sorry if I didn't design yet my program but my aim is to how it works.. Thank you very very much for the help.. I learn some solutions from and I think I should work harder and practice more on this.. Thanks again..
Regards,
Kyosuke18
| 
October 7th, 2009, 11:13 AM
|  | Familiar Sight | | Join Date: Dec 2007 Location: Lucknow, India
Posts: 194
| | | re: ADO Recordset Error
Dear Kyosuke18
Have a full working (& corrected) project for WindowsMedia Player. See all the * marked lines. I think u don't require Form3 as MediaPlayer can be played in Form2 itself so that if u need to search next file u can.
| 
October 8th, 2009, 04:54 AM
|  | Familiar Sight | | Join Date: Dec 2007 Location: Lucknow, India
Posts: 194
| | | re: ADO Recordset Error
Dear Kyosuke
I hope it solved your queries. If any other query, ask & I will try to help.
One thing, file vbskfr2.ocx was not in the zip file provided by you and I don't know what is the use of this file in your program.
| 
October 22nd, 2009, 09:13 AM
| | Newbie | | Join Date: May 2009
Posts: 11
| | | re: ADO Recordset Error
hi smartchap,
I read your message thanks for the help.. regarding about vbskfr2.ocx is a vb skinner.. I change the design of the form instead of using a classic form. I just follow ur instructions on the green or asterisk.. I have problems again regarding flexgrid.. since you've entered the
RowClicked = Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.RowSel, 4)
in the MSFlexGrid1_Click event. When I clicked this flexgrid without doing a search the error pop ups that "Subscript out of range".. I try this solution just to avoid the error but when I search it, Example "Because of you", since there are 2 titles but different artist, when I clicked the Because of you by Ne-yo which is in the first row(assuming) it will play.. But when I clicked the Because of you by 98 degrees, only the 1st because of you by Ne-yo is still playing and not from 98 degress.. Here is the code below:
If Rowclicked = "" Then
Msgbox "No selected fields", vbCritical + vbOk, "Flexgrid"
MSFlexgrid1.Enabled = False
ElseIf Rowclicked = Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.RowSel, 4) Then
MSFlexgrid1.Enabled = True
End If
I add another form for adding, deleting and updating a record.. In adding a record, I don't have problems regarding that because it works on a adding.. but I have problem in deleting a record or deleting an entire row.. The code below is deletion of selected row that I added.. Now the problem is when the selected row is deleted in the flexgrid it deletes, but when I open the database name "songs1" in microsoft access the selected row that I deleted didn't.. I've tried using the sql syntax "DELETE * FROM SONGS" in the recordset that I declared and the whole record is deleted.. so I think I should use the WHERE clause next to SONGS, but I can't type the record one by one just to delete, for example "DELETE * FROM SONGS WHERE Title = "xdhsd", Artist = "dhjd", Genre = "Pop", Album = "Popfest", Year = "1954" " what should I do?
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim ab As String
Dim d As String
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Songsdb.mdb;Persist Security Info=False" '*
rs.Open "Delete * from Songs1", cn, adOpenDynamic, adLockPessimistic
d = MsgBox("Do you want to delete a record?", vbQuestion + vbYesNo, "Delete Record")
If MSFlexGrid1.Rows > MSFlexGrid1.FixedRows + 1 Then
MSFlexGrid1.RemoveItem (MSFlexGrid1.Row)
Else
MSFlexGrid1.Rows = MSFlexGrid1.FixedRows
End If
And again, Thank you very much for the help and some guides that you've gave to me everytime I have problems regarding vb programming.. I really appreciate it.. Attach here with is the zip file regarding the program.. I just modify the codes and add some.. I also try my best to find a solution so that my program will work.. Thanks
Kyosuke18=)
|  | | | | /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 225,652 network members.
|