Execute sql

[kirkm]

I understand you cannot run an sql Select statement in VBA.
So I tried the following

      sql = "SELECT AAAA.Field1 FROM AAAA ORDER BY (Asc(Mid([Field1]," & AplhaPos & ",1))+Asc(Mid([Field1]," & AplhaPos + 1 & ",1)));"
                    Set srt = CurrentDb.OpenRecordset(sql)
                    srt.OpenRecordset
                        DoEvents
                    srt.Close

But that did nothing. So obviously wrong. Although works fine in direct mode.
How would I get the desired result in code? Thanks.

 

[Pat Hartman]

What did you expect to happen? When you open a recordset in code, you generally have a loop to read it record by record. Otherwise there is no point.

If you want to display data for a user to interact with, always use a form or a report. Queries have no events so you have no control over what the users do. They can add, change, or delete records at will. With a form you can control their actions. With a report, they can only view. They cannot do any updating.

 

[isladogs]

Save your SQL statement as a query e.g. qryAAAA

Then put the following code into a procedure:
DoCmd.OpenQuery "qryAAAA"

Your query will open but of course as It's a SELECT query, it won't actually do anything

However the recordset code is then redundant as Pat said - remove it

 

[Pat Hartman]

What Pat said was - DO NOT EVER OPEN A RECORDSET FOR THE USER TO INTERACT WITH BY OPENING a QUERY. This is a really poor practice as I explained. Use a form or report. I DELIBERATELY did not offer DoCmd.OpenQuery.

 

[isladogs]

What Pat said was - DO NOT EVER OPEN A RECORDSET FOR THE USER TO INTERACT WITH BY OPENING a QUERY. This is a really poor practice as I explained. Use a form or report. I DELIBERATELY did not offer DoCmd.OpenQuery.

I'd already written my reply before seeing yours & just edited the last sentence

Yes of course I agree that end users should only ever use forms or reports in a multi user environment

However, you are supposing this is a multi user database. It may not be.

Also if the select query is read only, no records can get edited anyway.

 

[kirkm]

What I was after was the same action (from code) that occurs if RUN from the database window. I wanted it sorted to then Move through doing something, but I didn't see it sorted. I realise now that makes no sense as the table isn't sorted, only the recordset. My fault, sorry! You get hungup on a something and forget the basics.

 

[Lightwave]

Another option is to have a table of SQL Strings and the call the SQL and run it from VBA - this can be good if you have a large amount of SQL strings to run.

Public Function RunQueriesFromTable(SQLSource As String)

DoCmd.SetWarnings False

Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL As String

StartTime = Now()

Set rstZ = CurrentDb.OpenRecordset(SQLSource)

Do Until rstZ.EOF

strSQL = rstZ!SQL
DoCmd.RunSQL strSQL
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

MsgBox "Finished ALL SQL update queries! Process started at " & StartTime & " and finished at " & EndTime

End Function