Solved

Cannot open ADO Recordset

Posted on 2000-05-07
16
546 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
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
 
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 142

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 142

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 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Convert a string into a TDateTime 5 52
VB6 - Convert HH:MM into Decimal 8 53
IdTCPClient and IdTCPServer exchanging data on desktops and not Android 11 95
MsgBox 4 48
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

863 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

19 Experts available now in Live!

Get 1:1 Help Now