Run Query in VBA By Query Name

[Ronster]

I am developing a small stand-alone Access application that uses many
queries and I am constantly making changes to the queries. I would
like to run the queries in VBA by simply calling them by name. This
seems easier than constantly changes the Select code in VBA. The
problem is I can't seem to get one query to run. From some other
posts it seems the best way to do this is to use ADO or ADODB neither
of which I am familiar with but I tried the following code and get the
error, "The database has been placed into a state by user Admin that
prevents it from being opened or locked."

I'm looking for a simple way to run queries from VBA. Am I on the
right track? Not sure what the error is telling me. I'm running
Access 2003 in Windows 2000.


Sub ExecuteQuery()


Dim adoComm As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim strStringConnection As String
Dim strSPName As String


' I just copied this string from another post and just changed
' the Data Source to point to my DB. Not sure it will work with my
version
strStringConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Import_Main.mdb;" _
& "Jet OLEDB:Engine Type=4"


strSPName = "DeleteDeductionRecords" 'NameOfYourQuery
adoComm.ActiveConnection = strStringConnection
adoComm.CommandType = adCmdStoredProc
adoComm.CommandText = strSPName


Set rs = adoComm.Execute


End Sub


Any help would be appreciated.

 

[Guest]

Docmd.OpenQuery "QueryNameGoesHere"
for Select queries
Docmd.RunSQL "QueryNameGoesHere"
for action queries.

 

[Van T. Dinh]

I am fairly sure the argument of the RunSQL method must be a SQL String (of
an "Action" Query type) and the OpenQuery can handle both Select and Action
Queries.

OTOH, I hardly ever use these methods as I don't open the DatasheetView of
the Select Queries to users and for actions, I use Execute more (I guess
would do the same).

 

[Van T. Dinh]

I am not entirely sure what you actually want to do ...

Do you want to open the Datasheet of the Query to present data to users for
viewing or editing or you want to create a Recordset in the memory based on
the Query for manipulation by code?

You code seem to indicate the later but then I can't see any code to
manipulate the Recordset (?0

 

[Guest]

Thanks for catching the typo. What I meant to write was
Currentdb.Execute
It is much faster than either of the others.

True, OpenQuery does handle both action and select queries, but as a matter
of practice, because of the performance difference and to easily avoid
warning messages, I prefer the Execute for action queries.

 

[Van T. Dinh]

I thought you and I would certainly use Excecute more ...

I actually meant to write in the parentheses ...

(I guess YOU would do the same)

but somehow missed the word "you" (I better proof-read my posts a bit more
carefully ...)

 

[Guest]

Az you can sea, I halwaze carefly edit my pots before I click Post
And yes, I would do the same. It is hard battle sometimes because my users
are all accountants that live in an Excel world and don't see any reason why
Access can't be just like Excel.

 

[Van T. Dinh]

L.O.L.

You get to my age, nothing is certain ... in the head.