Build a query "on the fly"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to execute the transferspreadsheet action with a subset of all the
records in a table (or query) based on a list of choices the user selects in
a couple of list boxes. Since transferspreadsheet requires a saved query, how
do I create the query on the fly? I'm OK with creating the SQL statement,
just not sure what the most appropriate way is to create the query so I can
pass it to the transferspreadsheet action.

How do you guys do it?
 
Save a query for the purpose - say qryExport.

You can then assign the SQL string to the SQL property of the QueryDef, like
this:
strSql = "SELECT ...
dbEngine(0)(0).QueryDefs("qryExport").SQL = strSql
 
Jim

A "query" might be a named/saved query, but I suspect you could also use a
SQL statement (e.g., "SELECT ... FROM ... WHERE ...").

Have you tried, in code, passing that built-up SQL statement as the "query"?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
JimS said:
Jeff,
I didn't try it...the MS docs specifically say it won't work...

I found the answer at www.fontstuff.com/access/acctut18pfv.htm,
though Allen's seems simpler. I used ADOX. I figure DAO won't be around
much longer, so not sense getting used to it.
Click to expand...

Actually, it's the other way around. ADO (and hence ADOX) is already
obsolete (it was replaced by ADO.Net), but Access 2007 includes ACE, which
is an enhanced version of DAO.
 
ADO (and hence ADOX) is already
obsolete
Click to expand...

MDAC 2.8 is not deprecated. ADO classic continues to be shipped with
Windows Vista as ADO 6.0. Therefore, ADO classic is not obsolete.
it was replaced by ADO.Net
Click to expand...

ADO classic is a COM component. ADO.Net is part of the .NET framework.
I can't use ADO.Net in VBA. Therefore ADO.NET is no a replacement for
ADO classic.
but Access 2007 includes ACE, which
is an enhanced version of DAO.
Click to expand...

ACEDAO (which still shows as just 'DAO' in the VBE Object Brower) has
not been enhanced to support the ANSI-92 Query Mode SQL syntax nor to
support key Jet 4.0 functionality (e.g. CHECK constraints). Therefore,
ADO classic is required to until ACEDAO recovers from DAO's 'lost
weekend' when ADO was in favour.

Also note that Microsoft invested in the production of an OLE DB
provider from the new engine named Microsoft.ACE.OLEDB.12.0 (therefore
'ACE' is not synonymous with 'new DAO' as you seem to suggest). ADO
classic can use the new OLE DB provider but ACEDAO cannot.

Jamie.

--
 
Back
Top