Run SQL script under VBA Function

[shabar]

Hi All




If I want to run query through a vba module function. I have that currently under ms access form as follows


DoCmd.Requery Q_Check_Mismatches
DoCmd.RunSavedImportExport "Export-Q_Check_Mismatches"




Because I just want to chabge the DB depend on the query. So my plan is to pass saved SQL and DB path as parameters in the
function. How do I do that in function level. Do I needs to add references in this case






Cheers


Shabar

[June7]

Don't know what you mean by 'pass saved SQL and DB path as parameters'. Need more info about what you are doing.

Why the Requery? What is Q_Check_Mismatches doing? Why isn't it within quote marks? Is it working?

[shabar]

Thax for your response June7

I have this SQL script saved in my DB (Q_Check_Mismatches). When I call my function I can pass that as a string parameter to the function plus DB path as string. Because I got quite a lot saved queries and they are in different DB's (That's why I thought of parameterized the DB path as well)

It is working fine without quote. Requery method can run saved queries straightway. thats why I used it.


Hope this clear out to some extent

Cheers

Shabar

[June7]

Clarify what you mean by 'SQL script'. Is this just an Access query object?

Okay, I just never used Requery on query objects.

A query name and/or path can be passed in a variable.

Would have to see procedure code to offer more specific advice.

[shabar]

Yes I meant Access query object as "SQL script"


Following is the function used




Function QueryRun(strQuery As String, strDBPath As String)

Set dbs = OpenDatabase(strDBPath)

DoCmd.Requery strQuery
DoCmd.RunSavedImportExport "Export-Q_Check_Mismatches"

End Function




But I'm getting below error in step - DoCmd.Requery strQuery






Run-time error '2109'

There is no field named
'Q_Check_Mismatches' in the current record

Further is there a method where we can use to run access query object. Because ONLY requery work for me

[June7]

What I have used:

DoCmd.SetWarnings = False
DoCmd.RunQuery "query name or SQL statement"
DoCmd.SetWarnings = True

And I prefer:
CurrentDb.Execute "query name or SQL statement"

If you want to run a query that is in another db, I expect need to include the path in the Requery line. However, I have never tried this.

Google: Access VBA run query in another database
Review:
http://answers.microsoft.com/en-us/o...6-14c8bd3bc019

[shabar]

I used following code;

Set db = DBEngine.Workspaces(0).OpenDatabase(strDBPath)
db.Execute strQuery, dbFailOnError

But it gives an error "Cannot execute a select query"


And I tried below as well. There also error pop up Run-time error '-2147467259 (80004005)
unrecognzed database format




Dim strConnectionString As String
Dim objConn As ADODB.Connection
Dim objCommand As ADODB.Command

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strDBPath & _
";Jet OLEDB:Engine Type=4"

Set objConn = New ADODB.Connection
objConn.Open strConnectionString

Set objCommand = New ADODB.Command

objCommand.ActiveConnection = objConn

' Assign to ADO Command object
objCommand.CommandText = strQuery
objCommand.CommandType = adCmdStoredProc

objCommand.Execute

objConn.Close

Set objCommand = Nothing
Set objConn = Nothing


Do you have any idea regarding the cause for these issues

[June7]

Sorry, RunQuery and Execute methods only for action SQL (UPDATE, INSERT, DELETE)

SELECT query objects don't need to be explicitely 'requeried'.

Simply open the query or a form/report bound to the query or open a recordset or run an action SQL that uses the SELECT as a data source.

The point is, SELECT queries are always current. Just using the query forces a 'refresh'.

[shabar]

Hi June7

Following worked for me

Set dbs = DBEngine.Workspaces(0).OpenDatabase(strDBPath)
dbs.OpenRecordset (strQuery)

But next issue is I have Saved Report where I needs run "RunSavedImportExport" method which is not supporting for dbs object

[June7]

What purpose does opening recordset serve?

Maybe instead of trying to directly run query in other db, need to run a procedure in other db. That procedure can have code for the RunSavedImportExport method. http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[shabar]

Just another clarification....

If I run RunSavedImportExport method without the corresponding query to that report, does it give up to date results.
What I meant was every time when I run RunSavedImportExport does it run query first automatically and then the report or do I have to run the corresponding query first every time?

Cheers

Shabar

[June7]

I have never used RunSavedImportExport method. Not sure how this connects with running report. I need a better understanding of object behaviors and how they interact.

Is the query parameterized? Are the parameters input prompts or reference to form controls? Is this the query report is based on?

[shabar]

No It's just a SELECT query.

As per my findings whenever we run RunSavedImportExport ​method it executes correspondence query object automatically.

So I finally ended up with below code which is working for me as of now


Function QueryRun(strImportExport As String, strDBPath As String)



Dim objAccess As Access.Application

Set objAccess = CreateObject("Access.Application")

objAccess.OpenCurrentDatabase strDBPath
objAccess.DoCmd.RunSavedImportExport strImportExport


objAccess.CloseCurrentDatabase
objAccess.Quit

Set objAccess = Nothing


End Function


June7, Thanks a lot mate for your guidance. Appreciated


Cheers

Shabar