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

I've never used Connect as a property of a database, only a table. But, to
set the connect property of your CurrentDB (which clearly is not a connected
database, but a local Access database) or a database object (LoDB) derived
from it, just doesn't make any sense to me. Also, in Access, you can reset
the .Connect property of a table, then execute a .RefreshLink, but, when I
used this with servers, it didn't work and the TableDef had to be
re-created.

And, as Peter pointed out, .timestamp is a MS SQL Server reserved word, so
shouldn't be used as a Field Name.

Oh, I'm not sure that you said you were using MS SQL Server. Whatever
server DB you are using, the SQL string you construct must be in that server
DB's dialect of SQL. I'm not the one to "vet" the SQL for any server DB...
when need be for me, I carefully check it out in that server DB's
documentation. And, I'm definitely not the one to "vet" a Connection string.

Lazy as I am, my recollection is that I set up the passthrough query as a
Query object, and simply replace the SQL string with the modified one. And,
that is where you'd set the Connection (but it's been long enough that I am
not sure of the details, anymore).

And, the last time I fiddled around with passthrough Queries was "'way back
when", using an Informix server DB, and I don't have a copy of the database
to refresh my memory.

I'm afraid I've about exhausted my store of hints and tips on the subject,
so I'm hoping, if you don't get it working and need more, that someone else,
with more recent experience will jump in.

Larry Linson
Microsoft Office Access MVP



"magickarle" <magickarle@xxxxxxxxxxx> wrote in message
news:5d6c4606-e40a-4990-8fa7-9175e4969c47@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jul 17, 11:39 am, magickarle <magicka...@xxxxxxxxxxx> wrote:
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- Hide quoted text -

- Show quoted text -

Ahh, the problem is in my original pass-through query, I got Into
TblTemp but since it's a pass-through, it doesn't know what to do with
the into.
Is there a way to dump the resulting recordsets to a new Table?
Thanks a lot people!


.

Loading