How do I get back value via SQL for use in VBA ? 2

[pharcyder]

Hello !

I'm using a DAO-connection to a SQL-Server 2000. This means I'm linking tables from a database on the server to my Access-Database.

Now I want to know how can I use a simple select-query in VBA to get back a useable value (for use in a variable i.e.) ?

I don't want to use the table as source in a form or in
a query in Access, because I need 3 values (maximum value from 3 different columns in one table) in 3 variables.

Can I directly query the SQL-Server 2000 with Transact-SQL and get back a value (in VB-Code ??) or is there a possibility to execute an Access-SQL query to get back a value ? And I need to do it in VBA !!

PS: My queries all give back only one value (the maximum value in a table).

PPS: The queries are ready to use and do work in Access-queries and Query-Analyzer, I just need the code HOW I get these values in variables !!!

Thanks in advance,
Jens.

 

[rajeessh]

Dim Rst as RecordSet
Set Rst = currentdb.openRecordSet(&quot;Select Max(&quot;Field1&quot as F1,Max(&quot;Field2&quot as F2,Max(&quot;Field3&quot as F3 from <Table>&quot
If Not Rst.EOF Then
Msgbox NZ(Rst(&quot;F1&quot,0)
Msgbox NZ(Rst(&quot;F2&quot,0)
Msgbox NZ(Rst(&quot;F3&quot,0)
End if


Check whether above works out Sir?

 

[cmmrfrds]

There are an infinite variety of ways. Rajeessh has given you one way of executing SQL code directly. In Access 2000 and in ADO terminology, VIEWS are where the queries are kept. So, you can read the views collection and pick your query out to execute. Here is a sample function you can paste into your standards module to test out. Of course, change to use your query names. If you don't want to use ADO, there is probably an equivelant in DAO.

Public Function testAccess()
''- MDAC libraries needed
''- Microsoft ADO Ext. 2.6 for DDL and Security
''- Microsoft ADO Object 2.6 Library

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As ADODB.Recordset, cnn as ADODB.Connection
Set rs = New ADODB.Recordset

Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection
Set cnn.ActiveConnection = CurrentProject.Connection

Dim v As View
Dim vn As View
For Each v In cg.Views
Debug.Print &quot;views = &quot;; v.Name
If v.Name = &quot;query1&quot; Then
Set vn = v
End If
Next

rs.Open vn.Name, cnn, adOpenForwardOnly, adLockReadOnly
Debug.Print rs.EOF
If Not rs.EOF Then
Debug.Print rs.Fields(0).Name
Debug.Print rs.Fields(0).Value
Debug.Print rs.Fields(1).Name
Debug.Print rs.Fields(1).Value
End If

End Function

 

[pharcyder]

Both given answers work like a charm, many thanks.

Blame me, but I didn't know I can directly get back the value from the fields in a recordset...

My problem was try using the given MS-Access queries,
but with these posibilities are restricted in many ways
(Can't use in textboxes, when used in dropdown- or listboxes, you can't select the queried values.....)

Thanks again !