How do I access records using ADO?

[Guest]

In my ADP project, I want to read records from a view into an array. This
worked in the MDB version:

Dim rst As DAO.Recordset
Dim strSQL as String
....
strSQL = "SELECT SUM ([SALES_QUANTITY_" & Format(lngMonth) _
& "] * [AVERAGE_COST]) AS Sales " _
& "FROM [" & strView & "] " _
& "WHERE ((([" & strView & "].BrRank) LIKE '" _
& Chr(lngRank + Asc("A") - 1) & "*'))"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
BranchRankSalesByMonth(iRow, lngRank) = rs(0)
....

where a typical value of strSQL was

"SELECT SUM ([SALES_QUANTITY_1] * [AVERAGE_COST]) AS Sales FROM
[view_RanksCostsSalesCur] WHERE ((([view_RanksCostsSalesCur].BrRank) LIKE
'A*'))"

I've tried these changes:

Dim rs As New ADODB.Recordset
....
Set rs = CurrentProject.Connection.OpenRecordset(strSQL, dbOpenSnapshot)

but I get the error message:

"Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another."

I haven't found Help to be very helpful on this one. Thanks for any advice
you may render!

 

[AccessVandal via AccessMonster.com]

Try,

Dim con as New ADODB.Conection
Dim rs As New ADODB.Recordset
dim strSQL as String

Set con = CurrentProject.Connection 'if current database
Set rs = New ADODB.Recordset

strSQL = "SELECT SUM ([SALES_QUANTITY_1] * [AVERAGE_COST]) AS Sales FROM _
& " [view_RanksCostsSalesCur] WHERE ((([view_RanksCostsSalesCur].BrRank) LIKE
'A*'))"

rs.Open strSQL, con, adOpenKeyset, adLockOptimistic

'do something here

rs.close
con.close
rs=nothing
con=nothing

 

[RoyVidar]

In my ADP project, I want to read records from a view into an array.
This worked in the MDB version:

Dim rst As DAO.Recordset
Dim strSQL as String
...
strSQL = "SELECT SUM ([SALES_QUANTITY_" & Format(lngMonth) _
& "] * [AVERAGE_COST]) AS Sales " _
& "FROM [" & strView & "] " _
& "WHERE ((([" & strView & "].BrRank) LIKE '"
_ & Chr(lngRank + Asc("A") - 1) & "*'))"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
BranchRankSalesByMonth(iRow, lngRank) = rs(0)
...

where a typical value of strSQL was

"SELECT SUM ([SALES_QUANTITY_1] * [AVERAGE_COST]) AS Sales FROM
[view_RanksCostsSalesCur] WHERE ((([view_RanksCostsSalesCur].BrRank)
LIKE 'A*'))"

I've tried these changes:

Dim rs As New ADODB.Recordset
...
Set rs = CurrentProject.Connection.OpenRecordset(strSQL,
dbOpenSnapshot)

but I get the error message:

"Arguments are of the wrong type, are out of acceptable range, or
are in conflict with one another."

I haven't found Help to be very helpful on this one. Thanks for any
advice you may render!

The ADO object model and methods differs some from DAO, try

Set rs = CurrentProject.Connection.Execute(strSQL, , adCmdText)

Note also that the ADO wildcard differs from DAO - use % in stead of
*, i e

....LIKE '"& Chr(lngRank + Asc("A") - 1) & "%'))"