Executing Multiple SQL statements in Excel

[aabnormal]

Hello All-

I am attempting to execute a list of update statements in excel to write to an Access db. I have been able to connet to the db and execute select statements and such by creating dynamic strings in my code --though I now would like to run a number of dynamic update statements built off of the data in my worksheets. My issues is that I am not sure whether I should/could write an Update string in my code that loops though cells to create and execute the SQL or if I should/could construct the Update statements in a column on the sheet, using concatenate, etc. functions, and then loop an execute statement through this column???

Examples are greatly appreciated with any reccommendations. Thanks for all your help in advance and please let me know if I can provide any additional information.
AAB

[CreganTur]

Welcome to the forum! Always good to see new people.


I have been able to connet to the db and execute select statements and such by creating dynamic strings in my code
Are you using DAO or ADO connections?


My issues is that I am not sure whether I should/could write an Update string in my code that loops though cells to create and execute the SQL or if I should/could construct the Update statements in a column on the sheet, using concatenate, etc. functions, and then loop an execute statement through this column?
What you're wanting to accomplish is feasible. Can you provide an example spreadsheet of data you're wanting to update into the database, along with the code you're using to connect? Please wrap your code in VBA tags- it'll format it according to VBIDE-making it easy to read.

[Kenneth Hobs]

Depends on what you want to do. Here are two methods.

Sub demo()
Dim objRS As Object, nwindPath As String
Set objRS = CreateObject("ADODB.Recordset")
nwindPath = ThisWorkbook.Path & "\nwind.mdb"

Dim r As Range
[a1] = "LastName"
[b1] = "FirstName"
[a2] = "Hobson"
[b2] = "Kenneth"
Set r = [a1:b2]
r.Name = "MyRange"

objRS.Open "INSERT INTO Employees SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & nwindPath

Set objRS = Nothing
End Sub

Update is the key to update the Access database in this one.
Sub ADO()
' the Microsoft ActiveX Data Objects 2.x Library
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer, Row As Integer, s As String

'On Error GoTo EndNow
' Set window and calc off to speed updates
SpeedOn

' Database information
'DBFullName = "C:\myfiles\vbabook\Names.mdb"
DBFullName = "u:\Material\ADO\NWind.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset
' Next two lines critical to work in QPro properly. Excel does not need them.
Recordset.CursorType = adOpenKeyset
Recordset.LockType = adLockOptimistic

With Recordset
' Filter
Src = "SELECT * FROM Orders "
'Src = Src & "and CategoryID = 30"
Recordset.Open Source:=Src, ActiveConnection:=Connection

' Cells.Clear 'Used in Excel to clear a sheet
' Write the field names
'For Col = 0 To .Fields.Count - 1
'Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name 'Excel method
'Next

If .RecordCount < 1 Then GoTo EndNow 'Query found no matching records
' Write the recordset by Excel method
'Range("A1").Offset(1, 0).CopyFromRecordset Recordset
'Add a new record (not pushed to the database until Update)
MsgBox CStr(.RecordCount), vbInformation, "#Records"
.AddNew
Recordset("ShipName") = [Name!A2]
Recordset("ShipAddress") = [Address!B6]
Recordset("ShipCity") = Worksheets("City").Range("C3")
.Update
MsgBox CStr(.RecordCount), vbInformation, "#Records"

End With

EndNow:
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
' Reset window and calculation
SpeedOff
End Sub

[aabnormal]

Welcome to the forum! Always good to see new people.


Are you using DAO or ADO connections?


What you're wanting to accomplish is feasible. Can you provide an example spreadsheet of data you're wanting to update into the database, along with the code you're using to connect? Please wrap your code in VBA tags- it'll format it according to VBIDE-making it easy to read.


I am using ADO

Below is some of the code I have been messing around with. There are a few different methods in here and though it is certainly a little messy I thought that my inclusion of it may give you a little more insight as to what I have been trying. - Attached is the excel sheet it references.

Sub UpdateSomeRecordsADO()
Dim cnn As ADODB.Connection
Dim UpdCommand As ADODB.Command
Dim dbstrg As String
Dim UpdStrg As String
dbstrg = ""
dbstrg = dbstrg & "O:\AP\2008\testdb.mdb"
' dbstrg = dbstrg & Cells(2, 12)
' dbstrg = dbstrg & "\North American "
' dbstrg = dbstrg & Cells(2, 11)
' dbstrg = dbstrg & ".mdb"

'' Open the connection.
Set cnn = New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Open dbstrg

'' Set the command text.
Set UpdCommand = New ADODB.Command
Set UpdCommand.ActiveConnection = cnn
'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
' UpdStrg = ""
' UpdStrg = UpdStrg & "UPDATE [Sheet1] SET [Paid] = 'Confirmed' WHERE [Acct] = 00003 AND [T/D] = 20080512 AND [B/S] = 1 AND [Price] = 1388.25"
' vtSql = vtSql & " UPDATE " & "Sheet1"
' vtSql = vtSql & " SET Salaryx= 24680"
' vtSql = vtSql & " WHERE Namex='Jennings'"

' With UpdCommand
' .CommandText = UpdStrg
' .CommandType = adCmdText
' .Execute
' End With
Range("Q2").Select
UpdStrg = ActiveCell
' Loop
Do
DoCmd.RunSQL UpdStrg
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -16))

'' Close the connections and clean up.
cnn.Close
Set UpdCommand = Nothing
Set cnn = Nothing
End Sub