Build a query "on the fly"

[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?

 

[Allen Browne]

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

 

[Jeff Boyce]

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

 

[Guest]

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.

Thanks guys!

 

[Douglas J. Steele]

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.

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.

 

[Guest]

I think I can use .net form Access, can't I? Reference it?

 

[Douglas J. Steele]

I'm not sure.

 

[Jamie Collins]

ADO (and hence ADOX) is already
obsolete

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

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.

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.

--

 

[Jamie Collins]

I supposed you consider VBA to be obsolete because it has been
replaced by VB.NET...? LOL

I'm not sure.

....oops! ;-)

Jamie.

--