Can we use dynamic queries on SQL-SERVER without ADO and without apersistent ODBC Connection?

[lyle fairfield]

By "dynamic" I mean the use of an SQL string in code.

This is my best to date:

I do not use ODBC frequently. Is there a simpler way?

Public Sub Whatever()
Dim q As DAO.QueryDef
With DBEngine(0)
..BeginTrans
On Error GoTo WhateverErr
Set q = .Databases(0).C reateQueryDef(" Trash" & Format(Now(),
"yyyymmddhhnnss "))
With q
..Connect = "ODBC;DRIVER=SQ L SERVER;SERVER=F FDBA-LAPTOP
\SONYLAPTOP;DAT ABASE=DB_51315; Trusted_Connect ion=yes"
..SQL = "SELECT * FROM FFDBATransactio ns WHERE Year([Date]) = 2007 AND
Month([Date]) = 3"
Debug.Print .OpenRecordset( ).Fields("[Date]").Value ' 2007-03-01
End With
End With
WhateverExit:
DBEngine(0).Rol lback
Exit Sub
WhateverErr:
MsgBox Err.Description
Resume WhateverExit
End Sub

[lyle fairfield]

Mumbling to myself ...
I think this can be more (than the previous) accurately called
dynamic, as it does not use a saved query, unsaved through the
application of a transaction.

Sub whatever2()
Dim w As DAO.Workspace
Dim c As DAO.Connection
Dim r As DAO.Recordset
Set w = DBEngine.Create Workspace("", "", "", dbUseODBC)
With w
Set c = .OpenConnection ("", _
dbDriverComplet e, _
True, _
"odbc;driver={s ql server};server= ffdba-laptop
\sonylaptop;dat abase=db_51315; trusted_connect ion=yes")
With c
Set r = .OpenRecordset( _
"SELECT * FROM FFDBATransactio ns WHERE Year([Date]) = 2007 AND
Month([Date]) = 3")
End With
End With
Debug.Print r.EOF
End Sub

I think this will not work with DAO < 3.6.

I'm interested because it may give me the kind of direct interaction
that I want with SQL-Server (and that I have with ADO) through ODBC.
Of course, ODBC users may be regular users of such code and I may have
just missed it here.

Yes, I know you're aghast that I haven't released the object
variables. My experience is that such code is unnecessary with DAO >=
3.6.