How to use a VBA query results in non-VBA query?

[ats]

I need the results of a query in several other queries. The problem is
that the first query cannot be constructed using SQL, but can be in
VBA. How can I refenence the VBA query results in other queries?

The problem all steams from not being able to use another query or
function results for the "IN" of a query which references direct path
of the external database.

 

[Alex White MCDBA MCSE]

It sounds like a design problem, but you problem can normally be solved by a
temper table which it built via vba then queries are run against the temp
table. If you go down that route consider multi-user problems with the temp
table.

I urge you to review the structure of your data as there maybe a much
simpler/faster solution.

 

[Alex White MCDBA MCSE]

Sorry my spell checker has done me again

temper = temp

 

[Gary Walter]

I need the results of a query in several other queries. The problem is
that the first query cannot be constructed using SQL, but can be in
VBA. How can I refenence the VBA query results in other queries?

The problem all steams from not being able to use another query or
function results for the "IN" of a query which references direct path
of the external database.

Hi ats,

One method I would use is to save a query (say "qryExtDB"),
(it doesn't matter initially what it's SQL is).

In your VBA, construct the SQL with "IN somepath"

Then change the SQL for the stored query with a public
function in a module, like the following adapted from a
a routine once provided by Duane.

Public Function ChangeSQL(pstrQueryName As String, pstrSQL As String)
On Error GoTo Err_ChangeSQL
Dim db As DAO.Database
Dim qd As DAO.QueryDef

Set db = CurrentDb
Set qd = db.QueryDefs(pstrQueryName)
qd.SQL = pstrSQL

qd.Close
db.Close

Exit_ChangeSQL:
Set qd = Nothing
Set db = Nothing
Exit Function
Err_ChangeSQL:
MsgBox Err.Description
Resume Exit_ChangeSQL

End Function

[NOTE: need to make sure you reference DAO library]

=======================
So...all your other queries are based on "qryExtDB"
=======================
and in your VBA routine you do something like:

'{not password protected}
strSQL = "SELECT * FROM " & pTable _
& " IN '" & pPath & "';"
'{or password protected}
'strSQL = "SELECT * FROM pTable _
' & " IN '' [MS Access;PWD=" & pPWD _
' & ";DATABASE=" & pPath & "];"

'use the function to change SQL of stored query
ChangeSQL("qryExtDB", strSQL)

'or just do it w/o function (again using DAO)
CurrentDb.QueryDefs("qryExtDB").SQL = strSQL

Does that help (or just confuse things)?

gary

 

[ats]

It very well may be, so....

I need to access a table in another (Access) database, which has
people's names (w/unique IDs). The problem is that to reference the
second database, you have to use an absolute path. If a relative path
is used, Microsoft Access uses the logged on Windows user's document
path, not the path the current database was started from.

OK, then I'll use a function in the SQL to create the correct path. -
Nope, SQL and/or Microsoft doesn't allow functions in the "IN" of a SQL
query.

If the database wasn't going to be used on several different computers
located in different paths, then I could hard wire it, but I can't!

The reason I need the results in a query is I use that query in other
queries.

- One solution was to create the query SQL string when the program
starts, so that the SQL will have the corrected path every time the
program starts.

- If there is another way I'm always ready to lean!

 

[ats]

Gary,

I figured it out, but my code was along the same lines as you had
suggested.
Thanks for your time and effort to help me out!

----------------------

Dim relPath As String, SQL As String

'--- Get path of current database
With CurrentDb
relPath = Mid(.Name, 1, Len(.Name) - Len(Dir(.Name)))
End With

'--- update the query
updateSQL "extQ", "SELECT * FROM tbName IN """ & relPath &
"db2.mdb"""

----------------------

Public Sub updateSQL(QueryName As String, SQLtext As String)

CurrentDb.QueryDefs(QueryName).SQL = SQLtext

End Sub

----------------------