ANmaSQL_UpdateDateStatement

Creates "Update Table " SQL statements to be executed inside SQL to modify column based on anther column.
Used to correct Date in MySQL table column after importing from Access mdb.
Example provided to create sql file if needed for large tables.

CodeFunctionName
What is this?

Public

Tested

Original Work
Sub TestSQL2()
    ' To apply and create sql file, use this sub
    Open "D:\Docs\Links v3.sql" For Output As #1
    Print #1, ANmaSQL_UpdateDateStatement("ANmaCCLinks")
    Close
End Sub

Function ANmaSQL_UpdateDateStatement(SQLTableName, Optional Shee = "Active", Optional Wb = "This", Optional StartCell = "A1")
    ' Creates "Update " SQL statements for a table found in Excel sheet to update certain field(s) based on another field of SQL table.
    ' Used to mainly correct the format of DATE field when moved from Access to MySQL database, it can be modified to work for any other types of update
    ' DateCol is the index of column to be modified (Date field in our case here)
    ' ByIDCol is the index of column to be used as ID to modify by, most of cases it will be 0, but can be changed.
    '
    If Wb = "This" Then Wb = ThisWorkbook.Name
    If Wb = "Active" Then Wb = ActiveWorkbook.Name
    If Shee = "Active" Then Shee = ActiveSheet.Name
   
    ColumnsCo = Workbooks(Wb).Worksheets(Shee).Range(StartCell).CurrentRegion.Columns.Count
    RowsCo = Workbooks(Wb).Worksheets(Shee).Range(StartCell).CurrentRegion.Rows.Count
   
    Rett = ""
    DateCol = 4 ' Head 1
    ByIDCol = 0 ' Head 2
    Head1 = Workbooks(Wb).Worksheets(Shee).Range(StartCell).Offset(0, DateCol).Value
    Head2 = Workbooks(Wb).Worksheets(Shee).Range(StartCell).Offset(0, ByIDCol).Value
   
    TabUpdate = "Update " & SQLTableName & " Set " & Head1 & " = '{{$V1$}}' Where " & Head2 & " = {{$V2$}} ;"
   
    For J = 1 To RowsCo - 1
        Value1 = Workbooks(Wb).Worksheets(Shee).Range(StartCell).Offset(J, DateCol).Value
        Value2 = Workbooks(Wb).Worksheets(Shee).Range(StartCell).Offset(J, ByIDCol).Value
        Value3 = Value1
        If IsDate(Value1) Then Value3 = Format(CDate(Value1), "yyyy-mm-dd HH:MM:SS")
        Rett = Rett & vbCrLf & Replace(Replace(TabUpdate, "{{$V2$}}", Value2), "{{$V1$}}", Value3)
    Next
    Rett = Rett & vbCrLf & ""
ByeBye:
    ANmaSQL_UpdateDateStatement = Rett
End Function

SQLTableName, Optional Shee = "Active", Optional Wb = "This", Optional StartCell = "A1"

Views 66

Downloads 16

CodeID
DB ID