Link to home
Start Free TrialLog in
Avatar of accessloans

asked on

Assign SELECT query result to variable using AccessDB in ADODB

I am trying to get the result of a select query assigned to a variable but not having any luck. An extract of my code is below. I would like the result assigned to a variable maxcountall.

Dim connectDATA As ADODB.Connection
Dim rst As ADODB.Recordset
connectDATA = New ADODB.Connection
rst = New ADODB.Recordset

connectDATA.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= ..\data\connectsmsdata.mdb;Jet OLEDB:Database Password=>one00%SmS..;")

mysql = "SELECT Max(TransAll_Query.Count)FROM TransAll_Query"
rst.Open(mysql, connectDATA)
Avatar of twobitbela

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ericwong27
You refer to below article

By the way you should use dataset instead of recordset in .NET
Avatar of accessloans


twobitbela, when type in the code below i get a blue line under 'rst.Fields.Item("MaxTransAll")'. when i mouse over it says 'Property access must assign to the property or use its value'.

how do i assign it to a variable maxcountall?

mysql = "SELECT Max(TransAll_Query.Count) AS MaxTransAll FROM TransAll_Query"
        rst.Open(mysql, connectDATA)
i typed maxcountall = rst.Fields.Item("MaxTransAll").

not sure what to make the datatype for maxcountall. The count column in the original table is an autonumber.
i have to use the autonumber as its the only unique identifier in the table.