JumboCactuar
Well-known Member
- Joined
- Nov 16, 2016
- Messages
- 788
- Office Version
- 365
- Platform
- Windows
Hi,
I have the following code which inserts data into a table with ADODB connection or updates table depending on the if statement.
I think i can somehow do this all in one module using recordset somehow?
IF RECID is not found in table then INSERT else UPDATE
Does anyone know the best way to do this? Without locking the database longer than needed
any help appreciated
I have the following code which inserts data into a table with ADODB connection or updates table depending on the if statement.
VBA Code:
Sub Button1Click()
If Sheet3.Range("N3") <> True Then
insertDATA
Else
updateDATA
End If
End Sub
VBA Code:
Sub insertDATA()
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Driver={MySQL ODBC 5.1 Driver};SERVER=xxx;DATABASE=xxx;UID=xxx;PWD=xxx;PORT=3306;"
i = 2
Data2 = Sheet3.Cells(i, 2).Value
Data3 = Sheet3.Cells(i, 3).Value
Data4 = Sheet3.Cells(i, 4).Value
Data5 = Sheet3.Cells(i, 5).Value
Data6 = Sheet3.Cells(i, 6).Value
Data7 = Sheet3.Cells(i, 7).Value
SQLSTRING = "INSERT INTO TABLE1 (RECID,RECDATE,RECTIME,RECLOC,RECDATA,RECMEMO) VALUES ('" & Data2 & "','" & Data3 & "','" & Data4 & "','" & Data5 & "','" & Data6 & "','" & Data7 & "');"
cnn.Execute SQLSTRING
Next i
cnn.Close
Set cnn = Nothing
Sheet3.Range("N3") = True
End Sub
VBA Code:
Sub updateDATA()
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Driver={MySQL ODBC 5.1 Driver};SERVER=xxx;DATABASE=xxx;UID=xxx;PWD=xxx;PORT=3306;"
i = 2
Data2 = Sheet3.Cells(i, 2).Value
Data3 = Sheet3.Cells(i, 3).Value
Data4 = Sheet3.Cells(i, 4).Value
Data5 = Sheet3.Cells(i, 5).Value
Data6 = Sheet3.Cells(i, 6).Value
Data7 = Sheet3.Cells(i, 7).Value
SQLSTRING = "UPDATE TABLE1 " & _
"SET RECID = '" & Data2 & "' " & _
",RECDATE = '" & Data3 & "' " & _
",RECTIME = '" & Data4 & "' " & _
",RECLOC = '" & Data5 & "' " & _
",RECDATA = '" & Data6 & "' " & _
",RECMEMO = '" & Data7 & "' " & _
"WHERE RECID='" & Data2 & "';"
cnn.Execute SQLSTRING
Next i
cnn.Close
Set cnn = Nothing
End Sub
I think i can somehow do this all in one module using recordset somehow?
IF RECID is not found in table then INSERT else UPDATE
Does anyone know the best way to do this? Without locking the database longer than needed
any help appreciated