aabnormal
09-05-2008, 07:28 AM
So I got this code to work the other day - I believe - though I could have been halucinating due to high levels of improperly functioning code :dunno due completely to my inabilites :help.
What I am trying to have this code do is execute a list of SQL Update statements that I have created in column Q. I have rechecked the syntax of the SQL multiple times though I cannot figure out what is occuring - I do not even get an error message. Thanks
Option Explicit
Public Function ExecuteSQLCommand(ByVal SQL As String, con As ADODB.Connection) 'As ADODB.Recordset
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
'initialize command object
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = con
'execute the SQL statement
With cmd
.CommandText = Trim$(SQL)
.CommandType = adCmdText
.Execute
End With
ExecuteSQLCommand_Exit:
Set rst = Nothing
Set cmd = Nothing
Exit Function
End Function
Sub UpdateRecordsADO()
Dim cnn As ADODB.Connection
Dim UpdCommand As ADODB.Command
Dim dbstrg As String
Dim SQLstrg 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
Sheets("Sheet2").Select
Range("Q2").Select
Dim UpdateCell As Range
Dim UpdateRange As Range
Set UpdateRange = Range("Q2").CurrentRegion
For Each UpdateCell In UpdateRange
If Not IsEmpty(UpdateCell.Value) Then Call ExecuteSQLCommand(UpdateCell.Value, cnn)
Next UpdateCell
'' Close the connections and clean up.
cnn.Close
Set UpdCommand = Nothing
Set cnn = Nothing
End Sub
What I am trying to have this code do is execute a list of SQL Update statements that I have created in column Q. I have rechecked the syntax of the SQL multiple times though I cannot figure out what is occuring - I do not even get an error message. Thanks
Option Explicit
Public Function ExecuteSQLCommand(ByVal SQL As String, con As ADODB.Connection) 'As ADODB.Recordset
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
'initialize command object
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = con
'execute the SQL statement
With cmd
.CommandText = Trim$(SQL)
.CommandType = adCmdText
.Execute
End With
ExecuteSQLCommand_Exit:
Set rst = Nothing
Set cmd = Nothing
Exit Function
End Function
Sub UpdateRecordsADO()
Dim cnn As ADODB.Connection
Dim UpdCommand As ADODB.Command
Dim dbstrg As String
Dim SQLstrg 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
Sheets("Sheet2").Select
Range("Q2").Select
Dim UpdateCell As Range
Dim UpdateRange As Range
Set UpdateRange = Range("Q2").CurrentRegion
For Each UpdateCell In UpdateRange
If Not IsEmpty(UpdateCell.Value) Then Call ExecuteSQLCommand(UpdateCell.Value, cnn)
Next UpdateCell
'' Close the connections and clean up.
cnn.Close
Set UpdCommand = Nothing
Set cnn = Nothing
End Sub