INSERT INTO vba

[kingnothingz]

Hi All,

I'm trying to create a function that insets into a table. The idea is to use this function to create an entry in table tblBackupLog. I intent to use this to backup the database, and add an entry into the table.

Public Function addBackupLog(strAction As String, strComments As String, strFileName As String) As Variant
Dim db As DAO.Database
Dim strSQL As String

   On Error GoTo addBackupLog_Error

Set db = CurrentDb()

strSQL = "INSERT INTO tblBackupLog ( dateOfAction, [Action], Comments, FileName, userName, computerName ) SELECT Now() AS Expr1, strAction AS Expr2, strComment AS Expr3, strFileName AS Expr4, getUserName() AS Expr5, getComputerName() AS Expr6;"

Debug.Print strSQL
db.Execute strSQL, dbFailOnError

db.Close

   On Error GoTo 0
   Exit Function

addBackupLog_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure addBackupLog of Module modBackup"

End Function

Everytime, i run this, i get this error message.

Error 3061 (Too few parameters. Expected 3) in procedure addBackupLog of Module modBackup

what am i doing wrong?Any help will be appreciated.

 

[delikedi]

You are attempting to use the variables directly inside the SQL statement. The SQL interpreter cannot distinguish that strAction is in fact a variable and read its value. You have to concatenate the values those variables hold to the SQL statement manually:

strSQL = "INSERT INTO tblBackupLog ( dateOfAction, [Action], Comments, FileName, userName, computerName ) SELECT Now() AS Expr1, " & strAction & " AS Expr2, " & strComment & " AS Expr3, " & strFileName & " AS Expr4, " & getUserName()  & " AS Expr5, " & getComputerName()  & " AS Expr6;"

Note that the values the variables hold may need further processing before becoming consumable by the SQL interpreter. Dates have to be in U.S. date format etc.

 

[kingnothingz]

Thanks for the reply delikedi,

Now i get a Syntax error when i run this.

I dont really need the "As Expr1" etc, that was something the query builder came up with.

Anyway, i tried doing this, the code shows up red in the VBA IDE

strSQL = _
    "INSERT INTO tblBackupLog ( dateOfAction, [Action], Comments, FileName, userName, computerName ) SELECT Chr$(34) & Now() & Chr$(34) & ", _
    " & Chr$(34) & strAction & Chr$(34) & ", _
    " & Chr$(34) & strComment & Chr$(34) & ", _
    " & Chr$(34) & strFileName & Chr$(34) & ", _
    " & Chr$(34) & getUserName() & Chr$(34) & ", _
    " & Chr$(34) & getComputerName() & Chr$(34) & ";"

What am I missing?

Every element has been surrounded by ' (Chr$(34) and all strings are in "

 

[kingnothingz]

SOLVED!

Used DAO instead of INSERT INTO, much more simpler to use.

Public Function addBackupLog(strAction As String, strComments As String, strFileName As String) As Variant
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

   On Error GoTo addBackupLog_Error

Set db = CurrentDb()
strSQL = "SELECT dateOfAction, [Action], Comments, FileName, userName, computerName FROM tblBackupLog;"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.AddNew
    rst!dateofAction = Now()
    rst!Action = strAction
    rst!Comments = strComment
    rst!fileName = strFileName
    rst!UserName = getUserName()
    rst!computerName = getComputerName()
rst.Update

exit_handler:
Set rst = Nothing
Set db = Nothing
   
   On Error GoTo 0
   Exit Function

addBackupLog_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure addBackupLog of Module modBackup"

End Function

 

[delikedi]

When the line

Debug.Print strSQL

executes, you should see something like

INSERT INTO tblBackupLog ( dateOfAction, [Action], Comments, FileName, userName, computerName ) VALUES ( #02/16/2013#, "Delete", "Deletion is good", "kingnothingz", "kingnothingzcomputer" )

in the immediate window, assuming that the fields "Action" and "Comments" are of string data type (you do not have to user double quotes for numeric data types).

The problems I see with your version is the lack of VALUES clause, the order of concatenation and the lack of commas during concatenation.

 

[delikedi]

Oh ok then

 

[kingnothingz]

Thanks for your help