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).CreateQueryDef("Trash" & Format(Now(),
"yyyymmddhhnnss"))
With q
..Connect = "ODBC;DRIVER=SQL SERVER;SERVER=FFDBA-LAPTOP
\SONYLAPTOP;DATABASE=DB_51315;Trusted_Connection=y es"
..SQL = "SELECT * FROM FFDBATransactions WHERE Year([Date]) = 2007 AND
Month([Date]) = 3"
Debug.Print .OpenRecordset().Fields("[Date]").Value ' 2007-03-01
End With
End With
WhateverExit:
DBEngine(0).Rollback
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.CreateWorkspace("", "", "", dbUseODBC)
With w
Set c = .OpenConnection("", _
dbDriverComplete, _
True, _
"odbc;driver={sql server};server=ffdba-laptop
\sonylaptop;database=db_51315;trusted_connection=y es")
With c
Set r = .OpenRecordset( _
"SELECT * FROM FFDBATransactions 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.