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.
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
' 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 86
Downloads 31
CodeID
DB ID