Link to home
Start Free TrialLog in
Avatar of accessloans
accessloans

asked on

Assign SELECT query result to variable using AccessDB in VB.net 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)
ASKER CERTIFIED SOLUTION
Avatar of twobitbela
twobitbela

Link to home
membership
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
http://support.microsoft.com/kb/315974

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

ASKER

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)
        rst.Fields.Item("MaxTransAll")
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.