As others have said, it's probably better to utilize parameters in the first place. However, ...
I, too, have missed a concatenation operator, having become accustomed to .= in PHP. In a few cases, I've written a function to do it, though not specific to concatenating SQL strings. Here's the code for one I use for creating a query string for an HTTP GET:
Public Sub AppendQueryString(strInput As String, _
ByVal strAppend As String, Optional ByVal strOperator As String = "&")
strAppend = StringReplace(strAppend, "&", "&")
strInput = strInput & strOperator & strAppend
End Sub
And an example of where I've called it:
AppendQueryString strOutput, "InventoryID=" & frm!InventoryID, vbNullstring
AppendQueryString strOutput, "Author=" & URLEncode(frm!Author)
...and so forth.
Now, for constructing SQL WHERE clauses, you might consider something like that as a wrapper around Application.BuildCriteria:
Public Sub ConcatenateWhere(ByRef strWhere As String, _
strField As String, intDataType As Integer, ByVal varValue As Variant)
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & Application.BuildCriteria(strField, _
intDataType, varValue)
End Sub
You would then call that as:
Dim strWhere As String
ConcatenateWhere strWhere,"tblInventory.InventoryID", dbLong, 10036
ConcatenateWhere strWhere,"tblInventory.OtherAuthors", dbText, "*Einstein*"
Debug.Print strWhere
strSQL = "SELECT tblInventory.* FROM tblInventory"
strSQL = strSQL & " WHERE " & strWhere
...and the Debug.Print would output this string:
tblInventory.InventoryID=10036 AND tblInventory.OtherAuthors Like "*Einstein*"
Variations on that might be more useful to you, i.e., you might want to have an optional concatenation operator (so you could have OR), but I'd likely do that by constructing a succession of WHERE strings and concatenating them with OR line by line in code, since you'd likely want to place your parentheses carefully to make sure the AND/OR priority is properly executed.
Now, none of this really addresses the concatenation of VALUES for an INSERT statement, but I question how often you're actually inserting literal values in an Access app. Unless you're using an unbound form for inserting records, you will be using a form to insert records, and thus no SQL statement at all. So, for VALUES clauses, it seems that in an Access app you shouldn't need this very often. If you are finding yourself needing to write VALUES clauses like this, I'd suggest you're not using Access properly.
That said, you could use something like this:
Public Sub ConcatenateValues(ByRef strValues As String, _
intDatatype As Integer, varValue As Variant)
Dim strValue As String
If Len(strValues) > 0 Then
strValues = strValues & ", "
End If
Select Case intDatatype
Case dbChar, dbMemo, dbText
' you might want to change this to escape internal double/single quotes
strValue = Chr(34) & varValue & Chr(34)
Case dbDate, dbTime
strValue = "#" & varValue & "#"
Case dbGUID
' this is only a guess
strValues = Chr(34) & StringFromGUID(varValue) & Chr(34)
Case dbBinary, dbLongBinary, dbVarBinary
' numeric?
Case dbTimeStamp
' text? numeric?
Case Else
' dbBigInt , dbBoolean, dbByte, dbCurrency, dbDecimal,
' dbDouble, dbFloat, dbInteger, dbLong, dbNumeric, dbSingle
strValue = varValue
End Select
strValues = strValues & strValue
End Sub
...which would concatenate your values list, and then you could concatenate into your whole SQL string (between the parens of the VALUES() clause).
But as others have said, it's probably better to utilize parameters in the first place.