Solved

Cannot open ADO Recordset

Posted on 2000-05-07
16
538 Views
Last Modified: 2013-11-25
Hi people,

I am just starting out with ADO and I can't get a simple recordset to open. Here is my code:

'---Start of code
Set conTest = New ADODB.Connection
Set cmdTest = New ADODB.Command
Set rstTest = New ADODB.Recordset

conTest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
conTest.Open

cmdTest.ActiveConnection = conTest    cmdTest.CommandText = "select * from Names"

rstTest.Open cmdTest
'---End of Code

Assume that all variables here are properly initialised.

Debugging through the code the connection opens fine (State indicates Open). However, when the rstTest.Open command tries to run, I get the following error:

Method 'Open' of object '_Recordset' failed.

Why? I have scoured MSDN but found nothing. Please help me open the recordset.

Thanks for your time
Steven
0
Comment
Question by:stevennic
  • 6
  • 4
  • 3
  • +2
16 Comments
 
LVL 6

Expert Comment

by:Marine
Comment Utility
if you going to use Command object then you should use Execute method to Set the recordset. Here is your example modified.

'---Start of code
Set conTest = New ADODB.Connection
Set cmdTest = New ADODB.Command
Set rstTest = New ADODB.Recordset

conTest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
conTest.Open

cmdTest.ActiveConnection = conTest    
cmdTest.CommandText = "select * from Names"

Set rsTest = cmdText.Execute
'---End of Code

This would create a read-Only Recordset.

Here is another way to open a recordset
conTest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
conTest.Open
sSql = "SELECT * FROM Names"
rsTest.Open sSql,conTest,adOpenKeyset,adLockOptimistic

'Make sure that i spelled these right :)
This would work.
0
 
LVL 1

Author Comment

by:stevennic
Comment Utility
Thanks Marine but it doesn't work.

The first recommendation gives me a
"Method 'Execute' of object '_Command' failed

and the second recommendation gives me the same as I normally get:
"Method 'Open' of object '_Recordset' failed.

Any ideas?
0
 
LVL 6

Expert Comment

by:Marine
Comment Utility
Here i just tested this and this has worked .

sCon is a string
cmd is command object
cn as Connection object
rs is Recordset object

Dim sCon As String
Dim cmd As New ADODB.Command
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
sCon = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=db2.mdb;" & _
"DefaultDir=" & App.Path & _
";Uid=Admin;Pwd=;"
cn.Open sCon
cmd.ActiveConnection = cn
cmd.CommandText = "SELECT * FROM Names"
cmd.CommandType = adCmdText
Set rs = cmd.Execute
0
 
LVL 6

Expert Comment

by:Marine
Comment Utility
2nd Method
Dim sCon as string,sSql as string
Dim cn as new adodb.connection
Dim rs as new adob.recordset
dim cmd as new adodb.command
sCon = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=db2.mdb;" & _
"DefaultDir=" & App.Path & _
";Uid=Admin;Pwd=;"

sSql = "SELECT * FROM Names"
rs.Open sSql,cn,3,3

This will work as well.
0
 
LVL 6

Expert Comment

by:Marine
Comment Utility
The previouse examples both used DSN-Less Connection. This example shows you how to use OLEDB Provider.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sCon As String
sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db2.mdb;"
cn.Open sCon
cmd.ActiveConnection = cn
cmd.CommandText = "SELECT * FROM Names"
cmd.CommandType = adCmdText
Set rs = cmd.Execute


0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
Comment Utility
I suggest several things to the code in general, without having solution for the problem. Only point (2) and (4) have chances to solve the problem:
1) use SET to assign connection SET cmd.ActiveConnection = cn
2) assign connection to Recordset
SET rs.ActiveConnection = cn
3) (for marine) you can easily use the open method, this should not be a problem, normally this way you can try to configure your recordset (cursor), because Command object returns always Readonly and Forwardonly recordset
4) You can try to enclose Names in brackets:
SELECT * FROM [Names]

Hope it helps
0
 
LVL 6

Expert Comment

by:Marine
Comment Utility
angel3 if you read thats what i said when executing command to set recordset this will be a read-only recorordset. I said that in previouse post. I think that the suggestions i posted were all valid. I tested them and they worked fine.
0
 
LVL 1

Author Comment

by:stevennic
Comment Utility
I have to admit here that I went straight for angel's quick & dirty comments before I got my hands dirty with Marine's code, so I tried adding the square brackets to Names and it worked!

I don't know if this is because Names is a reserved word or something but it worked straight away without any modifications to my original code.

The tough part now is who do I give the answer to, since it was angel's comment that made the code work but I really appreciate Marine's work since it will doubtless be very useful and it's probably taken quite a bit of your time.

What do you guys think?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Expert Comment

by:philchang
Comment Utility
Try this:

1. Create a connection in the designer instead of manually, just because it's easier to test the connection. (double click on the designer DataEnvironment1, rightclick connection1 and click properties).  Make sure it works with Test Connection.

2. Add this code:

Dim conn as adodb.connection
Dim rs as adodb.recordset
dim query as string

set conn = DataEnvironment1.Connection1
set rs = new adodb.recordset

query = "select * from names"
rs.open query, conn, adOpenStatic, adLockReadOnly, adcmdtext

do while not (rs.eof)
   ' do something
   rs.movenext
loop
rs.close
set rs = nothing

As someone else pointed out, depending on your data provider (i.e. flavor of SQL), if names is a keyword, you must put [] around it.  Words like Name, Date, Time, Count, etc. are often keywords.

Phil




0
 
LVL 6

Expert Comment

by:Marine
Comment Utility
Angel do you want this points ? If not steevenic can give them to me.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I'm always happy about points, and this time i am a little bit suprised that my suggestion solved the problem :-)

Following EE Guidelines, i think i should get this points, but stevennic can give you some points too for your investigation.

stevennic, choose what you want to do.
0
 

Expert Comment

by:mbenish
Comment Utility
Did you try this with the ADO (not using the command object)

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sCon As String
Dim SQL As String

sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db2.mdb;"
cn.Open sCon

SQL = "SELECT * FROM Names"
Set rs=cn.Execute(SQL)

Do while not (rs.EOF)
......
Loop

Always found the more obejcts you can eliminate, the better things run.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
another one that tries to get the points :-)

stevennic, you can accept one of my comments (in fact, the comment) as answer...
0
 
LVL 1

Author Comment

by:stevennic
Comment Utility
Thanks for your time embenish. However I will stick to angel's answer.
0
 
LVL 1

Author Comment

by:stevennic
Comment Utility
Thanks all for your time and effort. Your work is appreciated. Considering I am using Access for the database it's rather funny "Names" has to be surrounded by square brackets. As far as I know 'Names' isn't a reserved word.
0
 

Expert Comment

by:mbenish
Comment Utility
No problem.  Just trying to help.  First
time I've run across this exchange, and
I plan to use it to help myself.

Only interested in the points to the
degree that it allows me to look at
other answers.  I'm not looking to win the Marlboro jacket.
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 (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

763 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

9 Experts available now in Live!

Get 1:1 Help Now