Execute Query from Access via Excel Query in VBA

问题:

Access has saved a query that was designed with the query builder called 'myQuery'. The database is connected to the system via ODBC connection. Macros are all enabled.

Excel Has makes a ADODB connection to connect to the database via

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
With con
 .Provider = "Microsoft.ACE.OLEDB.12.0"
 .Open "MyDatabase.accdb"
End With

Usually you would go ahead and just write your SQL, which is perfectly fine and then just do something like

Dim sqlQuery As String
sqlQuery = "SELECT * FROM myTable"
Set rs = New ADODB.Recordset
rs.Open sqlQuery, con, ...

But I want to access the query that I saved in the access database. So how do I call the saved query in the database that I just connected.

Tried already

1. con.Execute("EXEC myQuery") but that one told me it could not be find myQuery.
2. rs.Open "myQuery", con but that one is invalid and wants SELECT/etc statements from it

回答1:

I think you can treat it like a stored procedure.

If we start right before Dim sqlQuery As String

 Dim cmd as new ADODB.Command
 cmd.CommandType = adCmdStoredProc
 cmd.CommandText = "myQuery"
 cmd.ActiveConnection = con

 Set rs = cmd.Execute()

Then pickup your recordset work after this.

回答2:

You were nearly there:

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
With con
 .Provider = "Microsoft.ACE.OLEDB.12.0"
 .Open "z:\docs\MyDatabase.accdb"
End With
con.Execute "MyQuery"

Just leave out Exec.

You can add parameters, too, this is a little old, but should help: update 2 fields in Access database with Excel data and probably a Macro

回答3:

I was able to run an update query that was already saved in Access using:

Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc

This gave me errors until I replaced spaces in the query name with underscores in both the Access database and the execute statement.

回答4:

This is sort of a hack job, but you can query a query. That is, replace your sql string with the following:

sqlQuery = "SELECT * FROM QueryName;"

Before running this, one must ensure that the Access Database has been saved ie. press Ctrl+S (it is not sufficient that the query was run in Access).

回答5:

Long time since this thread was created. If I understand it correctly, I might have something useful to add. I've given a name to what the OP describes, that being the process of using SQL from a query saved in an ACCDB to run in VBA via DAO or ADOBD. The name I've given it is "Object Property Provider", even with the acronym OPP in my notes, and for the object name prefix/suffix.

The idea is an existing object in an ACCDB (usually a query) provides a property (usually SQL) that you need to use in VBA. I slapped together a function just to suck SQL out of queries for this; see below. Forewarning: sorry, but this is all in DAO, I don't have much use for ADODB. Hope you will still find the ideas useful.

I even went so far as to devise a method of using/inserting replaceable parameters in the SQL that comes from these OPP queries. Then I use VBA.Replace() to do the replacing before I use the SQL in VBA.

The DAO object path to the SQL of a query in an ACCDB is as follows:

mySqlStatement = Access.Application.CurrentDb.QueryDefs("myQueryName").SQL

The way I use replaceable parameters is by evaluating what needs to be replaced, and choosing an unusual name for the paramater that cannot possibly exist in the real database. For the most part, the only replacements I've made are field or table names, or the expressions of WHERE and HAVING clauses. So I name them things like "{ReplaceMe00000001}" and then use the Replace() function to do the work...

sqlText = VBA.Replace(sqlText, "{ReplaceMe00000001}", "SomeActualParameter")

...and then use the sqlText in VBA. Here's a working example:

Public Function MySqlThing()
    Dim sqlText as String
    Dim myParamater as String
    Dim myExpression as String

    'Set everything up.
    sqlText = getSqlTextFromQuery("myQuery")
    myParameter = "{ReplaceMe00000001}"
    myExpression = "SomeDateOrSomething12/31/2017"

    'Do the replacement.
    sqlText = VBA.Replace(sqlText, myParameter, myExpression)

    'Then use the SQL.
    db.Execute sqlText, dbFailOnError
End Function

Function getSqlTextFromQuery(ByVal oppName As String) As String

    Dim app As Access.Application
    Dim db As DAO.Database
    Dim qdefs As DAO.QueryDefs
    Dim qdef As DAO.QueryDef
    Dim sqlText As String

    Set app = Access.Application
    Set db = app.CurrentDb
    Set qdefs = db.QueryDefs
    Set qdef = qdefs(oppName)

    oppGetSqlText = qdef.SQL

End Function