Building SQL Strings in Access/Vba

Building SQL strings in Access/VBA

I have a timesheet app with a reasonably complex unbound labour transaction entry form. There is a lot of data validation, rate calculation and other code. I decided to use the following to create my SQL Insert/Update fields.

The variables strSQLInsert, strSQLValues, strSQLUpdate are form level strings.

Many lines of the following:

Call CreateSQLString("[transJobCategoryBillingTypesID]", lngJobCategoryBillingTypesID)

followed by:

If lngTransID = 0 Then
strSQL = "INSERT into Transactions (" & Mid(strSQLInsert, 3) & ") VALUES (" & Mid(strSQLValues, 3) & ")"
Else
strSQL = "UPDATE Transactions SET " & Mid(strSQLUpdate, 3) & " WHERE transID=" & lngTransID & ";"
End If

conn.Open
conn.Execute strSQL, lngRecordsAffected, adCmdText

Note that the Mid lines remove the leading ", ". lngTrans is the value of the autonumber primamy kay.

Sub CreateSQLString(strFieldName As String, varFieldValue As Variant, Optional blnZeroAsNull As Boolean)
' Call CreateSQLString("[<fieldName>]", <fieldValue>)

Dim strFieldValue As String, OutputValue As Variant

On Error GoTo tagError

' if 0 (zero) is supposed to be null
If Not IsMissing(blnZeroAsNull) And blnZeroAsNull = True And varFieldValue = 0 Then
OutputValue = "Null"
' if field is null, zero length or ''
ElseIf IsNull(varFieldValue) Or Len(varFieldValue) = 0 Or varFieldValue = "''" Then
OutputValue = "Null"
Else
OutputValue = varFieldValue
End If

' Note that both Insert and update strings are updated as we may need the insert logic for inserting
' missing auto generated transactions when updating the main transaction
' This is an insert
strSQLInsert = strSQLInsert & ", " & strFieldName
strSQLValues = strSQLValues & ", " & OutputValue
' This is an update
strSQLUpdate = strSQLUpdate & ", " & strFieldName & " = " & OutputValue

On Error GoTo 0
Exit Sub

tagError:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateSQLString of VBA Document Form_LabourEntry"
Exit Sub
End Sub

I see that the other posters are all using the Execute method. The problem with DoCmd.RunSQL is that it can ignore errors. Either of the following will display any error messages received by the query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror.. For ADO use CurrentProject.Connection.Execute strCommand, lngRecordsAffected, adCmdText You can then remove the docmd.setwarnings lines.

If you're going to use docmd.setwarnings make very sure you put the True statement in any error handling code as well. Otherwise weird things may happen later on especially while you are working on the app. For example you will no longer get the "Do you wish to save your changes" message if you close an object. This may mean that unwanted changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two methods. One posting stated currentdb.execute took two seconds while docmd.runsql took eight seconds. As always YMMV.

MS Access using VBA and Form, build a query string and run it

Firstly, and most importantly, never concatenate user-obtained data as part of a SQL statement. Any application which uses this technique is wide open to a SQL injection attack and will also fail if the user includes a string delimiter (such as a single quote) or other reserved character in the input they provide.

Instead, use parameters.

For your scenario, I might suggest creating a saved query with the SQL:

select top 10 t.*
from linked_Table_Name t
where t.ID = Forms![Your Form Name]![textbox1]

Where Your Form Name is the name of your form containing the control textbox1.

Then, your on-click event can simply call the OpenQuery method of the DoCmd object:

DoCmd.OpenQuery "YourSavedQuery"

Microsoft Access VBA code with Select SQL String and Where clause

Try,

    Dim strSQL As String
strSQL = "SELECT StakeholderRegister.[StakeholderID], StakeholderRegister.[ProjectID], StakeholderRegister.[FirstName], StakeholderRegister.[LastName], StakeholderRegister.[EmailAddress] " & _
" FROM StakeholderRegister " & _
" WHERE StakeholderRegister.[ProjectID]=" & [Forms]![ChangeLog]![cboProjectID].Value & " ;"
Set rs = Db.OpenRecordset(strSQL)

if [ProjectID] field type is text then

    Dim strSQL As String
strSQL = "SELECT StakeholderRegister.[StakeholderID], StakeholderRegister.[ProjectID], StakeholderRegister.[FirstName], StakeholderRegister.[LastName], StakeholderRegister.[EmailAddress] " & _
" FROM StakeholderRegister " & _
" WHERE StakeholderRegister.[ProjectID]='" & [Forms]![ChangeLog]![cboProjectID].Value & "' ;"
Set rs = Db.OpenRecordset(strSQL)

Access VBA: Cannot combine variable and string in SQL query

I don't see how your code triggers that 3075 error. However I would use a parameter query instead. That way you can avoid problems with quoted values in your SQL statement.

Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSelect As String
Dim strUserName As String

strUserName = "A-user"
strSelect = "SELECT TOP 1 i.Code FROM [Item] AS i WHERE i.User = [which_user] ORDER BY i.ID DESC"
Set qdf = CurrentDb.CreateQueryDef(vbNullString, strSelect)
qdf.Parameters("which_user").Value = strUserName
Set rs = qdf.OpenRecordset

Passing a variable into SQL With VBA/Access

Can we see how this Conveyor_ID is being defined? It might make it easier to answer the question.

Assuming your [Conveyor_ID] is defined as Conveyor_ID in the VBA function, why not try

Set rs = db.OpenRecordSet("SELECT Width FROM " & Conveyor_ID)

Access VBA: sql character to VBA string

I think I see the issue. Null isn't the same thing as vbNullString. See here for more information. It's Java, but the idea holds.

You have to handle the Null occurrence with an if statement like this:

Sub test()
Dim rs As DAO.Recordset: Set rs = CurrentDb.OpenRecordset("myTable")
Dim MyStr As String
If Not (rs.EOF And rs.BOF) Then rs.MoveFirst

Do Until rs.EOF
If IsNull(rs!MyField) Then
MyStr = vbNullString
Else
MyStr = rs!MyField
End If
MsgBox (MyStr)
rs.MoveNext
Loop

rs.Close
End Sub

ms-access VBA long sql query string line split ( with double quote inline) in editor

finally it beautifully work by using chr$(39) way:

Dim MYSTR As String
MYSTR = "NULL"

'teststrSQL = "SELECT EXC,FC_CUSIP FROM TESTDATA " & _
"WHERE FC_CUSIP = " & Chr$(39) & MYSTR & Chr$(39)

finalstrSQL = "SELECT EXC AS MY_EXC, " & _
"SUM(IIF(MASTER_CUSIP <> " & Chr$(39) & MYSTR & Chr$(39) & " , 1, 0)) AS MY_MASTER_CUSIP, " & _
"SUM(IIF(MASTER_TICKER <> " & Chr$(39) & MYSTR & Chr$(39) & " , 1, 0)) AS MY_MASTER_TICKER, " & _
"SUM(IIF(TL_TICKER <> " & Chr$(39) & MYSTR & Chr$(39) & " , 1, 0)) AS MY_TL_TICKER, " & _
"SUM(IIF(TL_CUSIP <> " & Chr$(39) & MYSTR & Chr$(39) & " , 1, 0)) AS MY_TL_CUSIP, " & _
"SUM(IIF(FC_CUSIP <> " & Chr$(39) & MYSTR & Chr$(39) & " , 1, 0)) AS MY_FC_CUSIP, " & _
"SUM(IIF(FC_TICKER <> " & Chr$(39) & MYSTR & Chr$(39) & " , 1, 0)) AS MY_FC_TICKER " & _
"FROM TESTDATA GROUP BY EXC;"


Related Topics



Leave a reply



Submit