Basic: Executing simple sql query to extract a value in vba

[msp4422]

Hi I am trying to execute a sql to fetch a value which will be source to a text box. Could you help me on how to write and execute sql's in vba?? tnx in advance.

I tried below but it is giving 3163: the field is too small to accept the value..........

Dim SS As String


DoCmd.SS = "SELECT qryAgreement_Data.[Unique ID] FROM qryAgreement_Data WHERE qryAgreement_Data.[Unique ID]=311;"



Me.Agreement_ID.Value = SS

thank you.

[pbaldy]

I think you made up the SS command there. You would have to open a recordset on that SQL to get the value. You may find DLookup() simpler:

http://access.mvps.org/access/general/gen0018.htm

[jzwp11]

If you are just getting the value from 1 field, it might be easier using a DLookup() function, but I have to ask if you are specifying the unique ID in the where clause why would you need the query to give you the unique ID field's value? Wouldn't it be 311?

"SELECT qryAgreement_Data.[Unique ID] FROM qryAgreement_Data WHERE qryAgreement_Data.[Unique ID]=311;"

[msp4422]

@[jzwp11]
I wrote that just as a sample query.. My main query is something different.. I wanted to know how we can execute a sql query like min, max, distinct functions and then use that value to assign another textbox. Could you show small example code on the same. I am new to MS access and VBA so it will be great if you guys can give me a source to learn the basics. i tried in net but couldn't find any good link. Thanks for your help and patience.

[jzwp11]

I typically use ADO (ActiveX Data Object) recordsets to get data via a query in code. Here is a code snippet:

 
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myrecset As New ADODB.Recordset
myrecset.ActiveConnection = cnn1


mySQL= "SELECT ..."

myrecset.Open mySQL


If myrecset.BOF And myrecset.EOF Then
    MsgBox "no records returned"
Else
Do Until myrecset.EOF

'do stuff here in the loop


myrecset.MoveNext
Loop
End if

The code snippet above sets up the objects and opens a recordset and then loops through the recordset. Obviously, you would do something within the loop.

This might be more than you need, and as I and Paul (pbaldy) mentioned, if you just want a couple of values from a record it would be easier using a DLookup() function.

[JrMontgom]

Hi I am trying to execute a sql to fetch a value which will be source to a text box. Could you help me on how to write and execute sql's in vba?? tnx in advance.

I tried below but it is giving 3163: the field is too small to accept the value..........

Dim SS As String


DoCmd.SS = "SELECT qryAgreement_Data.[Unique ID] FROM qryAgreement_Data WHERE qryAgreement_Data.[Unique ID]=311;"



Me.Agreement_ID.Value = SS

thank you.

Being very lazy and a poor typist I usually create the query with real values to test it and when I am happy with the results ask the Query Designer to show me the SQL commands I then cut/paste the SQL commands and replace the "real" data with the variable name I have chosen. The trick is that string variables need to be surrounded by single or double quotes, numerical data need no quotes around them and dates need "#" in the string containing the SQL commands. In your example and the answer from another user replace MySql with your string.