asked on

Defining a String Value using SQL

In MS Access VBA, is it possible to define the value for a string using SQL? I keep getting errors trying to get this to work. I've got a bit rusty in the past 4 years... Here is an example of what I have been attempting:

Private Sub Command1_Click()

Dim strCCN as String, strDESCR as String

strCCN = " & FRM_MAIN.CCN & "
strDESCR = DoCmd.RunSQL("SELECT DESCR FROM TBL_INV_REPORT WHERE TBL_INV_REPORT.CCN = ' " & strCCN & " ' ; )

Bembi

strCCN = FRM_MAIN.CCN (assuming this is a text field)
strDESCR = DoCmd.RunSQL("SELECT DESCR FROM TBL_INV_REPORT WHERE TBL_INV_REPORT.CCN = ' " & strCCN & " ' "; )

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fabrice Lambert

Hi,

Building an SQL query with user input as strings can be a source of troubles:
What if the user enter something with quotes ? Double quotes ? Or a mix of both ?
Your query will become invalid, and MS Access will complain (by raising an unexpected and unpleasant error, or not running the query).

to prevent this, you can use an advanced technique: Parameterized query:

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String

    '// First, define your SQL query, with parameters:
strSQL = vbNullString
strSQL = strSQL & "PARAMETERS CNNvalue Text(255); & vbcrlf
strSQL = strSQL & "SELECT DESCR" & vbcrlf
strSQL = strSQL & "FROM TBL_INV_REPORT" & vbcrlf
strSQL = strSQL & "WHERE TBL_INV_REPORT.CCN = [CNNvalue];"

    '// 2nd, create a temporary querydef object
    '// (name must be unique)
Set db = CurrentDb
Set qd = db.CreateQueryDef("tmpQry", strSQL)

    '// 3rd, fillup querydef parameters:
qd.Parameters("CNNvalue").Value = strCCN

    '// 4th, execute the querydef
qd.Execute dbFailOnError

    '// 5th: properly destroy objects
qd.Close
db.QueryDefs.Delete qd.Name
Set qd = Nothing
Set db = Nothing

Open in new window

Additional bonus: It protect from SQL injection.

Side notes:
Prefer using the Execute() function when running sql queries, so MS Access won't prompt you when running "Action queries", and you won't have to mess up with docmd.SetWarnings. Plus it can raise an error in case of troubles.

Gustav Brock

It was a select task, not an action task.