Run SQL Update via VBA

You will need to use this format in your SQL statement

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

More information on SQL here

http://www.w3schools.com/sql/sql_update.asp

Awesome...I thought that was what would be needed, but I wasn't 100% sure. I am going to show you what I am trying to do (and this is probably the most AWFUL way to do it) I have my code now set as:

Dim sql1 As String
Dim sql2 As String
Dim sDate As String
sql1 = "UPDATE tbl_One_Main SET tbl_One_Main.Received = 'yes', tbl_One_Main.Received_Date = Date()+1"
sql2 = "UPDATE tbl_One_Main SET tbl_One_Main.Received = 'yes', tbl_One_Main.Received_Date = Date()+2"

sDate = Format(Date, "ddd")

If sDate = "Tue" Then
'tell it to run sql1
Else
If sDate = "Wed" Then
'tell it to run sql2
End If
End If

I have modified my code to be set up like this....It debugs fine, compiles fine, but it's not actually updating the fields in my table! What is going on?

Sub TestingJ()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim wb As Workbook
Dim upRP As Variant
Dim sDate As String

sDate = Format(Date, "ddd")

upRP = Array("tbl_TryThis")

Set cn = New ADODB.Connection
cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=R:\Test.mdb;"

For q = LBound(upRP) To UBound(upRP)
    If sDate = "Fri" Then
        Call One(CStr(upRP(q)))
        Else
            If sDate = "Sat" Then
                Call Two(CStr(upRP(q)))
                Else
                    If sDate = "Sun" Then
                        Call Three(CStr(upRP(q)))
                    End If
            End If
    End If
Next q
End Sub

Public Function One()
sql1 = "UPDATE tbl_One_Main SET tbl_One_Main.Received = 'yes', tbl_One_Main.Received_Date = Date()+1"
End Function
Public Function Two()
sql2 = "UPDATE tbl_One_Main SET tbl_One_Main.Received = 'yes', tbl_One_Main.Received_Date = Date()+2"
End Function

that should not compile since you are passing arguments to functions that don't take any. you also never actually run any SQL commands in it.

Your function declaration

Public Function One()

does not declare any parameters so you cannot pass it arguments. the code you posted will not run. even if it did, the functions do not do anything other than assign some SQL to string variables. they don't actually run it-for that you would need a

cn.execute sql1

type of statement.

I have it functioning properly now...Thank you for pointing the error in my ways. Now I said it is working probably not the most efficient way of coding it. I modified my code to this:

Sub TestingJ()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim wb As Workbook
Dim upRP As Variant
Dim sDate As String

sDate = Format(Date, "ddd")

upRP = Array("tbl_TryThis")

Set cn = New ADODB.Connection
cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=R:\Test.db;"

For q = LBound(upRP) To UBound(upRP)
    If sDate = "Fri" Then
        Call One(CStr(upRP(q)), cn)
        Else
            If sDate = "Sat" Then
                Call Two(CStr(upRP(q)), cn)
                Else
                    If sDate = "Sun" Then
                        Call Three(CStr(upRP(q)), cn)
                    End If
            End If
    End If
Next q
End Sub

Public Function One(upRP As String, cn)
sql1 = "UPDATE " & tbl_One_Main & " SET tbl_One_Main.Received = 'yes', tbl_One_Main.Received_Date = Date()+1"
cn.Execute sql1
End Function
Public Function Two(upRP As String, cn)
sql2 = "UPDATE" & tbl_One_Main & " SET tbl_One_Main.Received = 'yes', tbl_One_Main.Received_Date = Date()+2"
cn.Execute sql2
End Function

I would just have the function return the SQL to run. I don't really understand why you are passing arguments to the functions if you don't use them?

I mean that you pass upRP to the functions but never use it. since the only difference in SQL seems to be number of days, you could have one function and pass that

Sub TestingJ()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim wb As Workbook
Dim upRP As Variant
Dim sDate As String
Dim sSQL as string
sDate = Format(Date, "ddd")

upRP = Array("tbl_TryThis")

Set cn = New ADODB.Connection
cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=R:\Test.db;"

For q = LBound(upRP) To UBound(upRP)
    If sDate = "Fri" Then
        Ssql = GetSQL(3)
    ElseIf sDate = "Sat" Then
        Ssql = GetSQL(2)
    ElseIf sDate = "Sun" Then
        Ssql = GetSQL(1)
     Else 
         Ssql = getsql(0)
    End If
    cn.Execute sql1
Next q
End Sub

Public Function GetSQL(n as long)
GetSQL = "UPDATE " & tbl_One_Main & " SET tbl_One_Main.Received = 'yes', tbl_One_Main.Received_Date = Date()+" & n
End Function