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
SQL Dynamic Datepart When Using Datediff
Pass a Table Variable to Sp_Executesql
Declaring & Setting Variables in a Select Statement
How to Change the Collation of SQLite3 Database to Sort Case Insensitively
Subtracting One Row of Data from Another in SQL
Django Orm - Get Latest Record for Group
Exec Stored Procedure into Dynamic Temp Table
Insert Multiple Rows in SQLite
How to Create Ordinal Numbers (I.E. "1St" "2Nd", etc.) in SQL Server
Nolock VS. Transaction Isolation Level
Efficient Way to Convert Second to Minute and Seconds in SQL Server 2005
SQL Like Statement on a Datetime Type
Conversion to Datetime Fails Only on Where Clause
Stratified Random Sampling with Bigquery
Maintaining Subclass Integrity in a Relational Database