Re: Action pass-through with a form variable Access 2003

On Jul 16, 2:53 pm, "Larry Linson" <boun...@xxxxxxxxxxxxx> wrote:
You can't "integrate variables into a pass-through query" because those
variables don't exist on the server where it will execute.   What you can
do, however, is to use the values of those variables (Controls on a Form,
just By The Way, are not "variables" in Access terms) and construct the SQL
(in the Server's dialect of SQL, not the Access dialect) to pass to the
server.  By using a Pass-Through Query are telling Jet and ODBC to leave it
completely alone... to "pass it through" exactly as you have written it.

You can use variables in a regular query that will first be processed by the
Jet or ACCDB database engine before being handed over to the ODBC driver.
Not every query from Access that is executed on a server need be a
"pass-through query", but if you have / can write the SQL,
pass-through-queries may execute a bit faster.

Another note: in some servers, e.g., MS SQL Server, "timestamp" is a special
type of field, not a "date and time" as you know them in Access.

 Larry Linson
 Microsoft Office Access MVP

"magickarle" <magicka...@xxxxxxxxxxx> wrote in message

news:9bdf57af-8cd2-4dff-9861-3a0125e9ac92@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



Hi, I got a pass-through query (that takes about 15 mins to process)
I would like to integrate variables to it.
IE: something simple:
Select EmplID from empl_Lst
where empl_lst.timestamp between [Forms]![MainForm]![StrMonth] And
[Forms]![MainForm]![Endmonth]

Not sure how to do so (should it be a query in Access or a macro)
The connection would be ODBC.

I'm a bit new to create queries and macros but I learn fast loll.
Thank you.- Hide quoted text -

- Show quoted text -

ok. let say I got a button CmdExtract on the mainform:

Private Sub CmdExtract_Click()
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef
Dim strSql As String

Set loDB = CurrentDb
StrDate_ = Format(Me!TxtStrDate, "yyyy-mm-dd")
EndDate_ = Format(Me!TxtEndDate, "yyyy-mm-dd")

strSql = "Select EmplID from empl_Lst into TblTemp" & _
"where empl_lst.timestamp between '" + StrDate_ + "' and '" + EndDate_
+ "'"

With loDB
.Connect = "ODBC;DSN=DNS_NAME;UID=USERID;PWD=PWD;QueryTimeout=0"
.Execute strSql, dbSQLPassThrough
End With
End Sub

I'm getting Run time error 3146
ODBC call failed.

Is there a way to have more info than that?! That sounds line a
general error message.

What I'm trying to do is to execute an action query pass-through that
would dump the records in a newly created TblTemp.
After this, I would append instead of creating a table.

Thank you
.