How do I access records using ADO?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
Allen_N said:
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!
Click to expand...

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) & "%'))"
 
Back
Top