Solved

Cannot open ADO Recordset

Posted on 2000-05-07
16
581 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +2
16 Comments
 
LVL 6

Expert Comment

by:Marine
ID: 2785694
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
ID: 2785906
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
ID: 2785953
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 6

Expert Comment

by:Marine
ID: 2785959
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
ID: 2786005
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 2786065
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
ID: 2786103
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
ID: 2786150
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
 
LVL 1

Expert Comment

by:philchang
ID: 2786184
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
ID: 2786270
Angel do you want this points ? If not steevenic can give them to me.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2787590
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
ID: 2788725
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2788776
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
ID: 2789378
Thanks for your time embenish. However I will stick to angel's answer.
0
 
LVL 1

Author Comment

by:stevennic
ID: 2789401
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
ID: 2789437
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

726 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