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
Solved

Cannot open ADO Recordset

Posted on 2000-05-07
16
563 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TT Auto Dashboard 13 95
How to read File Date Created using VB6 8 52
How to make Sign in, using Clientdataset? 1 28
Powerpoint 2013: Change cell reference in excel link 3 82
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

828 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