Using Excel cells as parameters to update a database

I am trying to update an access database using information stored in excel, Below is an example of the cells I will be using:

01234   Past
43343   failed
43743   past

This is what I have so far:

Dim strQry As String
Dim strID As String
Dim strStatus As String

strID = CStr(ThisWorkbook.Sheets("Test").Range("A1").FormulaR1C1)
strStatus = CStr(ThisWorkbook.Sheets("Test").Range("B1").FormulaR1C1)

strQry = "Update Tasks SET Status = '" & strStatus & "'" WHERE id= '" & strID & "'"

I keep getting an syntax error. Basically I want to update the status of the task using the ID.

I’m fresh out of Uni and this is the first programming job so any help would be greatly appreciated.

Answer

Without knowing more about your project it is difficult to help.

But the following should be a step in the right direction:

Dim strQry As String, strID As String, strStatus As String

With ThisWorkbook.Worksheets("Test")
    strID = .Range("A1")
    strStatus = .Range("B1")
End With

strQry = "Update Tasks SET Status = '" & strStatus & "' WHERE id= '" & strID & "'"

Advertisement

Answer