passing SQL strings in VBA

[borach]

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&quot, 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
(dbNaic and str are globally defined variables)

If str <> &quot;Demographics&quot; Then

strSQL1 = &quot;SELECT For_2000 FROM&quot; _
& &quot; 5YearHistorical WHERE Line_No >= '29' AND Line_No <= '38'&quot; _
& vbCr & &quot;UPDATE 5YearHistorical SET For_2000 = For_2000/100000 WHERE&quot; _
& &quot; Line_No >= '29' AND Line_No <= '38';&quot;

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.

 

[kerrigirrl]

try coding the sql statement to run with the &quot;runSQL&quot; command: docmd.runsql sql statement.

 

[tlbroadbent]

Opening a recordset with an Update statement isn't a valid operation. You should use RunSQL for the update and openrecordset to select the data. Terry

;-) &quot;When I hear somebody sigh, 'life is hard', I am always tempted to ask, 'compared to what'?&quot; - Sydney Harris