VAB SQL Statement

[rpaulson]

Hello all,

my first try at writing VBA to add data to Access table.
I am not happy with the code i created, it works, but it does not appear to a good method, especially if there are many fields in my table.

I have to believe there is an easier way to insert \ update.

...
UserID = 66
Fname = "Tim"
Lname = "Jones"
Phone = "212-555-1212"

On Error GoTo 888 'i need this to avoid an error if the recordlready exists
    strSQL = "INSERT INTO users (userID,Fname,Lname,Phone) VALUES (" & UserID & ",'" & Fname & "','" & Lname & "'," & Phone & ")"  'add new record
    objConn.Execute strSQL
 
888
On Error Resume Next
    strSQL = "UPDATE users SET Fname='" & Fname & "',Lname='" & Lname & "',Phone='" & Phone & "' WHERE userID =" & UserID  'update an exusting record.
    objConn.Execute strSQL

...

thanks for looking,
Ross

 

[Micron]

I don't see anything "wrong" with what you posted except to say that using GoTo to direct flow is usually considered poor practice. I know, a lot of people do it.
There are many ways to get data into Access. If you google a term for that, you might find one that you prefer for now or next time. What comes to mind at the moment are these 3: linked spreadsheets, transferdatabase function and automation.

 

[rpaulson]

Micron, the GOTO does not really bother me.
I am more concerned about having to list all the fields and the data 2 times. (one for the insert and then again to the update.)

I'm looking for a way to the the same String for either option.

example:
string = (userID,Fname,Lname,Phone) VALUES (" & UserID & ",'" & Fname & "','" & Lname & "'," & Phone & ")"

then use something like:
strSQL = INSERT INTO users & string 'insert
strSQL = UPDATE users SET & string 'update

 

[Micron]

I am more concerned about having to list all the fields and the data 2 times.

Anything that is duplicated could probably be compartmentalized. Question I always consider is where to draw the line and say 2x for something is or isn't enough to be bothered with. So I would concatenate the repeated stuff into a variable, but you still have to use the variable for either line in your code where you are now repeating the field names. If you're ok with that then what you just posted is saying the same thing. That's telling me that you already know the solution. Are you looking for help to concatenate the sql variable? Maybe

Dim strFields As String
strFields = "Fname='" & Fname & "',Lname='" & Lname & "',Phone='" & Phone"
strSQL = "UPDATE users SET " & strFields & "' WHERE userID =" & UserID  'update an exusting record.

Sorry if I'm not understanding your goal.
EDIT - I I suspect that my answer is not correct because the double quotes would have to be wrapped in double quotes. By the time I got that done, the post would time out and be uneditable.

 

[Artik]

You can use custom functions to build SQL strings, which will build them for you.
Below is a function that builds simple INSERT and UPDATE queries. For more complex strings, it seems better to build separate functions.

Sub AAA()
    Dim vArrVals(0 To 3) As Variant
    Dim strFlds As String
    Dim strSQL As String

    'UserID = 66
    'Fname = "Tim"
    'Lname = "Jones"
    'Phone = "212-555-1212"

    vArrVals(0) = 66
    vArrVals(1) = "Tim"
    vArrVals(2) = "Jones"
    vArrVals(3) = "212-555-1212"

    strFlds = "userID,Fname,Lname,Phone"

    strSQL = SQLGenerator("insert", "users", strFlds, vArrVals)
    Debug.Print strSQL
    MsgBox strSQL

    strSQL = SQLGenerator("update", "users", strFlds, vArrVals)
    Debug.Print strSQL
    MsgBox strSQL

End Sub


Function SQLGenerator(method As String, Table As String, Fields As String, ParamArray Values()) As String
    Dim vFields As Variant
    Dim strResult As String
    Dim strResult2 As String
    Dim LastSep As Long
    Dim i As Long

    vFields = Split(Fields, ",")

    If UBound(vFields) <> UBound(Values(0)) Then
        MsgBox "Incompatible number of fields and values!", vbExclamation
        Exit Function
    End If

    Select Case UCase(method)
        Case "INSERT"
            strResult = "INSERT INTO " & Table & " (["
            strResult2 = "]) VALUES ("

            For i = 0 To UBound(vFields)
                strResult = strResult & (vFields(i) & "],[")

                If TypeName(Values(0)(i)) = "String" Then
                    strResult2 = strResult2 & ("'" & Values(0)(i) & "', ")
                    LastSep = 2    '= Len(", ")
                Else
                    strResult2 = strResult2 & (Values(0)(i) & ",")
                    LastSep = 1    '= Len(",")
                End If
            Next i

            strResult = Left(strResult, Len(strResult) - 3)    '3=Len("],[")
            strResult2 = Left(strResult2, Len(strResult2) - LastSep) & ");"

            SQLGenerator = strResult & strResult2

        Case "UPDATE"
            strResult = "UPDATE " & Table & " SET ["

            For i = 1 To UBound(vFields)
                If TypeName(Values(0)(i)) = "String" Then
                    strResult = strResult & (vFields(i) & "]='" & Values(0)(i) & "',[")

                Else
                    strResult = strResult & (vFields(i) & "]=" & Values(0)(i) & ",[")

                End If
            Next i

            strResult = Left(strResult, Len(strResult) - 2) & " WHERE [" & vFields(0) & "]="
            If TypeName(Values(0)(0)) = "String" Then
                strResult = strResult & "'" & Values(0)(0) & "'"
            Else
                strResult = strResult & Values(0)(0) & ";"
            End If

            SQLGenerator = strResult
    End Select
End Function

Note that when building UPDATE, the field that is the WHERE condition must be the first one in the vArrVals array.

Artik

 

[rpaulson]

Artik,

Nice solution, I think I can make that work.