ADO OpenRecordset Error 80004005

[Alistair F]

I'm new to ADO and an trying to converting DAO programs to ADO. I get an
error, 80004005, when using the following code to try to open a recordset.

Set rstTable = New ADODB.Recordset
With rstTable
.Source = strSQL
.ActiveConnection = dbCurrent
.CursorType = adOpenStatic
.Open
End With

The same code has worked at earlier stages in the program but fails at this
stage. The SQL query does return records from the Access database when I use
the strSQL text as the basis for the query.

Is the problem likely to be with the query or somewhere else?

Thanks in anticipation

 

[Dirk Goldgar]

I'm new to ADO and an trying to converting DAO programs to ADO. I get an
error, 80004005, when using the following code to try to open a recordset.

Set rstTable = New ADODB.Recordset
With rstTable
.Source = strSQL
.ActiveConnection = dbCurrent
.CursorType = adOpenStatic
.Open
End With

The same code has worked at earlier stages in the program but fails at
this
stage. The SQL query does return records from the Access database when I
use
the strSQL text as the basis for the query.

Is the problem likely to be with the query or somewhere else?

Thanks in anticipation

What are:

(a) the error description returned along with the error?
(a) dbCurrent? How declared, how set?
(b) strSQL?

 

[Alistair F]

Many thanks for the very prompt response. here's the info you asked for.

Alistair



Error Description:
method 'Open' of object_Recordset failed

dbCurrent
declared as: Public dbCurrent As ADODB.Connection
defined as:
Set dbCurrent = New ADODB.Connection
With dbCurrent
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & CurrentProject.Path & "\Reports
2007 Pre v1.mdb"
End With

strSQL="SELECT [School Boxplot Data].Year, [School Boxplot Data].Min,
[School Boxplot Data].Lower, [School Boxplot Data].Median, [School Boxplot
Data].Upper, [School Boxplot Data].Max, [School Boxplot Data].Percent,
[School Boxplot Data].NCD, [School Boxplot Data].Average, [School Boxplot
Data].[Current Year Comment], [School Boxplot Data].[Trend Code], [School
Boxplot Data].[Trend Duration], [School Boxplot Data].[EA Percent] FROM
[School Boxplot Data] WHERE ((([School Boxplot Data].[Centre Code])=5244439)
AND (([School Boxplot Data].Stage)='S4') AND (([School Boxplot
Data].Cohort)='All') AND (([School Boxplot Data].Measure)='5+ Level 5'))
ORDER BY [School Boxplot Data].Year"

 

[Dirk Goldgar]

Many thanks for the very prompt response. here's the info you asked for.

Alistair



Error Description:
method 'Open' of object_Recordset failed

dbCurrent
declared as: Public dbCurrent As ADODB.Connection
defined as:
Set dbCurrent = New ADODB.Connection
With dbCurrent
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & CurrentProject.Path & "\Reports
2007 Pre v1.mdb"
End With

strSQL="SELECT [School Boxplot Data].Year, [School Boxplot Data].Min,
[School Boxplot Data].Lower, [School Boxplot Data].Median, [School Boxplot
Data].Upper, [School Boxplot Data].Max, [School Boxplot Data].Percent,
[School Boxplot Data].NCD, [School Boxplot Data].Average, [School Boxplot
Data].[Current Year Comment], [School Boxplot Data].[Trend Code], [School
Boxplot Data].[Trend Duration], [School Boxplot Data].[EA Percent] FROM
[School Boxplot Data] WHERE ((([School Boxplot Data].[Centre
Code])=5244439)
AND (([School Boxplot Data].Stage)='S4') AND (([School Boxplot
Data].Cohort)='All') AND (([School Boxplot Data].Measure)='5+ Level 5'))
ORDER BY [School Boxplot Data].Year"

Did you ever open the connection?

dbCurrent.Open

?

 

[Alistair F]

Yes, the statement

dbCurrent.Open

follows the connection setup

Cheers

Alistair

Many thanks for the very prompt response. here's the info you asked for.

Alistair



Error Description:
method 'Open' of object_Recordset failed

dbCurrent
declared as: Public dbCurrent As ADODB.Connection
defined as:
Set dbCurrent = New ADODB.Connection
With dbCurrent
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & CurrentProject.Path & "\Reports
2007 Pre v1.mdb"
End With

strSQL="SELECT [School Boxplot Data].Year, [School Boxplot Data].Min,
[School Boxplot Data].Lower, [School Boxplot Data].Median, [School Boxplot
Data].Upper, [School Boxplot Data].Max, [School Boxplot Data].Percent,
[School Boxplot Data].NCD, [School Boxplot Data].Average, [School Boxplot
Data].[Current Year Comment], [School Boxplot Data].[Trend Code], [School
Boxplot Data].[Trend Duration], [School Boxplot Data].[EA Percent] FROM
[School Boxplot Data] WHERE ((([School Boxplot Data].[Centre
Code])=5244439)
AND (([School Boxplot Data].Stage)='S4') AND (([School Boxplot
Data].Cohort)='All') AND (([School Boxplot Data].Measure)='5+ Level 5'))
ORDER BY [School Boxplot Data].Year"

Did you ever open the connection?

dbCurrent.Open

?


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Yes, the statement

dbCurrent.Open

follows the connection setup

I found out what's causing the problem, but I don't know why. Your SQL
statement contains lots of reserved words as field names, but only one of
them seems to need fixing up. I find that if I put square brackets around
the field name "Lower", so that the SQL string is:

strSQL="SELECT [School Boxplot Data].Year, [School Boxplot Data].Min,
[School Boxplot Data].[Lower], [School Boxplot Data].Median, [School Boxplot
Data].Upper, [School Boxplot Data].Max, [School Boxplot Data].Percent,
[School Boxplot Data].NCD, [School Boxplot Data].Average, [School Boxplot
Data].[Current Year Comment], [School Boxplot Data].[Trend Code], [School
Boxplot Data].[Trend Duration], [School Boxplot Data].[EA Percent] FROM
[School Boxplot Data] WHERE ((([School Boxplot Data].[Centre Code])=5244439)
AND (([School Boxplot Data].Stage)='S4') AND (([School Boxplot
Data].Cohort)='All') AND (([School Boxplot Data].Measure)='5+ Level 5'))
ORDER BY [School Boxplot Data].Year"

.... then it works for me. I have no idea why this one reserved word
requires the special bracketing, out of all the reserved words you've used.

I'd recommend also changing this statement:

.ActiveConnection = dbCurrent

.... to this:

Set .ActiveConnection = dbCurrent

Though the way you had it would work, I think what it was really doing was
passing the connection string of dbCurrent to the recordset, rather than the
connection object itself.

 

[Alistair F]

Many thanks, this works a treat. Saved me a lot of time trying different
options.

Alistair

Yes, the statement

dbCurrent.Open

follows the connection setup

I found out what's causing the problem, but I don't know why. Your SQL
statement contains lots of reserved words as field names, but only one of
them seems to need fixing up. I find that if I put square brackets around
the field name "Lower", so that the SQL string is:

strSQL="SELECT [School Boxplot Data].Year, [School Boxplot Data].Min,
[School Boxplot Data].[Lower], [School Boxplot Data].Median, [School Boxplot
Data].Upper, [School Boxplot Data].Max, [School Boxplot Data].Percent,
[School Boxplot Data].NCD, [School Boxplot Data].Average, [School Boxplot
Data].[Current Year Comment], [School Boxplot Data].[Trend Code], [School
Boxplot Data].[Trend Duration], [School Boxplot Data].[EA Percent] FROM
[School Boxplot Data] WHERE ((([School Boxplot Data].[Centre Code])=5244439)
AND (([School Boxplot Data].Stage)='S4') AND (([School Boxplot
Data].Cohort)='All') AND (([School Boxplot Data].Measure)='5+ Level 5'))
ORDER BY [School Boxplot Data].Year"

... then it works for me. I have no idea why this one reserved word
requires the special bracketing, out of all the reserved words you've used.

I'd recommend also changing this statement:

.ActiveConnection = dbCurrent

... to this:

Set .ActiveConnection = dbCurrent

Though the way you had it would work, I think what it was really doing was
passing the connection string of dbCurrent to the recordset, rather than the
connection object itself.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)