I’m trying to execute a couple of SQL functions in VBA as a part of a recordset. Can I do more than just SELECT in the SQL statement string, i.e. UPDATE, DELETE, etc?
Here is the function I’m trying to execute. The VB Editor, however gives me an error message (“Invalid procedure”), after I try to pass it.
___________________________________________
Function CalcPCentRows(ByVal str As String) As Boolean
CalcPCentRows = False
Dim strSQL1 As String, rstPCentRows As Recordset, strSQL2 As String
dbNaic.TableDefs.Refresh
If str <> “Demographics” Then
strSQL1 = “SELECT For_2000 FROM” _
& ” 5YearHistorical WHERE Line_No >= ’29’ AND Line_No <= '38'" _
& vbCr & "UPDATE 5YearHistorical SET For_2000 = For_2000/100000 WHERE" _
& " Line_No >= ’29’ AND Line_No <= '38';"
Set rstPCentRows = dbNaic.OpenRecordset(strSQL1, dbOpenDynaset)
rstPCentRows.MoveLast
rstPCentRows.MoveFirst
Debug.Print rstPCentRows.RecordCount
rstPCentRows.Close
End If
CalcPCentRows = True
End Function
_____________________________________________
I have tried all types of combinations and I'm sure my SQL syntax is correct, the question is can I create a recordset in VBA with this SQL syntax.