Cannot open ADO Recordset
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.OL
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
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.OL
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
ASKER
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?
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?
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
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
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.
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.
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.OL
cn.Open sCon
cmd.ActiveConnection = cn
cmd.CommandText = "SELECT * FROM Names"
cmd.CommandType = adCmdText
Set rs = cmd.Execute
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.OL
cn.Open sCon
cmd.ActiveConnection = cn
cmd.CommandText = "SELECT * FROM Names"
cmd.CommandType = adCmdText
Set rs = cmd.Execute
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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?
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?
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.Connectio
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
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.Connectio
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
Angel do you want this points ? If not steevenic can give them to me.
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.
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.
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.OL
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.
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sCon As String
Dim SQL As String
sCon = "Provider=Microsoft.Jet.OL
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.
another one that tries to get the points :-)
stevennic, you can accept one of my comments (in fact, the comment) as answer...
stevennic, you can accept one of my comments (in fact, the comment) as answer...
ASKER
Thanks for your time embenish. However I will stick to angel's answer.
ASKER
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.
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.
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.
'---Start of code
Set conTest = New ADODB.Connection
Set cmdTest = New ADODB.Command
Set rstTest = New ADODB.Recordset
conTest.ConnectionString = "Provider=Microsoft.Jet.OL
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.OL
conTest.Open
sSql = "SELECT * FROM Names"
rsTest.Open sSql,conTest,adOpenKeyset,
'Make sure that i spelled these right :)
This would work.