Extracting Data From A Table Using SQL inside VBA Module

[Dexsquab]

Hello, helpful people.

I'm trying to extract a value from a table using SQL while within a VBA module attached to an onClick event used on some forms.

For the most part, the button is used to push data that's been calculated from the form input to various tables, using SQL. All the pushes work, but I also need to pull in one element.

Specifically, I've written the following:

    strSQL = "SELECT " & strUptake & " FROM [Client Uptake];"
    DoCmd.RunSQL strSQL

Now I know this is going to go to the right table, find the column in question, and build a subtable with the data from that column (an integer value). The table is only one element deep, so this call will always find a single value.

My question is, how do I bring that data in as a variable in the VBA module? In other words, I want to bring the data from the column in table [Client Uptake] and place it into a variable that I can then manipulate. At the moment I'm not sure where the data will end up after the call above. What I'd like to see is the extracted data placed in a variable called intUptakeValue.

Also, clarification on whether or not I need the square brackets in the SQL statement would be appreciated.

*prays before the god of Omniscient Coding* all praise be to ye, Lord of Code!

 

[Dexsquab]

Ok, problem solved... I'll just use DLookup instead.

 

[robherms]

If I'm not using DlookUp...

I have a table that I am liked to via ODBC and have opened as an ADODB connection. Each row has a unique key called a SeqNo. I would like to run a SQL command that would search by SeqNo, and then insert the value of one of the columns retuned into a variable that I can use in a module (the variable will become part of a filename).
Being as I don’t think it is possible to use a table that I am linked to via ODBC in dlookup, I would like to know if there is anyway that I could do this in a SQL statement? Or any other way that someone may know of?

Note: I currently have the tables linked in access via ODBC (File, Get External Data, Link Tables) but I get the following error on occasion:
ODBC – call failed.
[MySQL] [ODBC 3.51 Driver] [MySquld – 4.0.17 – log] MySQL server has gone away (#2006)
Apparently there are a number of possible causes for this error and there have not been many successful solutions, so I decided to change the way I go about accessing my tables to resolve this issue.