Action pass-through with a form variable Access 2003

[magickarle]

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.timest amp 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.

[Larry Linson]

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

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 " <magickarle@hot mail.comwrote in message
news:9bdf57af-8cd2-4dff-9861-3a0125e9ac92@k1 3g2000hse.googl egroups.com...

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.timest amp 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.

[Peter Doering]

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

Larry Linson wrote:

>[...]
>where empl_lst.timest amp between ...

[...]
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.

.... and is therefore a keyword that should not be used as field name.

--
Peter Doering [MVP Access]

[lyle fairfield]

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

magickarle <magickarle@hot mail.comwrote in news:9bdf57af-8cd2-4dff-9861-
3a0125e9ac92@k1 3g2000hse.googl egroups.com:

Hi, I got a pass-through query (that takes about 15 mins to process)
I would like to integrate variables to it.

Cheer up. All is not lost. If you say 200 Hail Marys and beat yourself
nightly with a horse hair whip for a month you may be forgiven.

[magickarle]

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

On Jul 16, 2:53 pm, "Larry Linson" <boun...@localh ost.notwrote:

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...@hot mail.comwrote in message
>
news:9bdf57af-8cd2-4dff-9861-3a0125e9ac92@k1 3g2000hse.googl egroups.com...
>
>
>

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.timest amp 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_Clic k()
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef
Dim strSql As String

Set loDB = CurrentDb
StrDate_ = Format(Me!TxtSt rDate, "yyyy-mm-dd")
EndDate_ = Format(Me!TxtEn dDate, "yyyy-mm-dd")

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

With loDB
.Connect = "ODBC;DSN=DNS_N AME;UID=USERID; PWD=PWD;QueryTi meout=0"
.Execute strSql, dbSQLPassThroug h
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

[magickarle]

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

On Jul 16, 5:28 pm, lyle fairfield <lylef...@yah00 .cawrote:

magickarle<magi cka...@hotmail. comwrote in news:9bdf57af-8cd2-4dff-9861-
3a0125e9a...@k1 3g2000hse.googl egroups.com:
>

Hi, I got a pass-through query (that takes about 15 mins to process)
I would like to integrate variables to it.

>
Cheer up. All is not lost. If you say 200 Hail Marys and beat yourself
nightly with a horse hair whip for a month you may be forgiven.

I'm fine with 200 Hail Marys, but I'll leave the wip to my girlfriend
loll!

[magickarle]

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

On Jul 17, 11:39 am, magickarle <magicka...@hot mail.comwrote:

On Jul 16, 2:53 pm, "Larry Linson" <boun...@localh ost.notwrote:
>
>
>
>
>

You can't "integrate variables into a pass-through query" because those
variables don't exist on the server where it will execute.   What youcan
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 theSQL
(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...@hot mail.comwrote in message

>

news:9bdf57af-8cd2-4dff-9861-3a0125e9ac92@k1 3g2000hse.googl egroups.com....

>

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.timest amp 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_Clic k()
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef
Dim strSql As String
>
Set loDB = CurrentDb
StrDate_ = Format(Me!TxtSt rDate, "yyyy-mm-dd")
EndDate_ = Format(Me!TxtEn dDate, "yyyy-mm-dd")
>
strSql = "Select EmplID from empl_Lst into TblTemp" & _
"where empl_lst.timest amp between '" + StrDate_ + "' and '" + EndDate_
+ "'"
>
With loDB
.Connect = "ODBC;DSN=DNS_N AME;UID=USERID; PWD=PWD;QueryTi meout=0"
.Execute strSql, dbSQLPassThroug h
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!

[Larry Linson]

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@hot mail.comwrote in message
news:5d6c4606-e40a-4990-8fa7-9175e4969c47@z7 2g2000hsb.googl egroups.com...
On Jul 17, 11:39 am, magickarle <magicka...@hot mail.comwrote:

On Jul 16, 2:53 pm, "Larry Linson" <boun...@localh ost.notwrote:
>
>
>
>
>

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...@hot mail.comwrote in message

>

news:9bdf57af-8cd2-4dff-9861-3a0125e9ac92@k1 3g2000hse.googl egroups.com...

>

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.timest amp 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_Clic k()
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef
Dim strSql As String
>
Set loDB = CurrentDb
StrDate_ = Format(Me!TxtSt rDate, "yyyy-mm-dd")
EndDate_ = Format(Me!TxtEn dDate, "yyyy-mm-dd")
>
strSql = "Select EmplID from empl_Lst into TblTemp" & _
"where empl_lst.timest amp between '" + StrDate_ + "' and '" + EndDate_
+ "'"
>
With loDB
.Connect = "ODBC;DSN=DNS_N AME;UID=USERID; PWD=PWD;QueryTi meout=0"
.Execute strSql, dbSQLPassThroug h
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!

[magickarle]

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

On Jul 17, 2:54 pm, "Larry Linson" <boun...@localh ost.notwrote:

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 " <magicka...@hot mail.comwrote in message
>
news:5d6c4606-e40a-4990-8fa7-9175e4969c47@z7 2g2000hsb.googl egroups.com...
On Jul 17, 11:39 am,magickarle<m agicka...@hotma il.comwrote:
>
>
>
>
>

On Jul 16, 2:53 pm, "Larry Linson" <boun...@localh ost.notwrote:

>

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 processedby
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...@hot mail.comwrote in message

>

>news:9bdf57a f-8cd2-4dff-9861-3a0125e9ac92@k1 3g2000hse.googl egroups.com....

>

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.timest amp 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_Clic k()
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef
Dim strSql As String

>

Set loDB = CurrentDb
StrDate_ = Format(Me!TxtSt rDate, "yyyy-mm-dd")
EndDate_ = Format(Me!TxtEn dDate, "yyyy-mm-dd")

>

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

>

With loDB
.Connect = "ODBC;DSN=DNS_N AME;UID=USERID; PWD=PWD;QueryTi meout=0"
.Execute strSql, dbSQLPassThroug h
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!- Hide quoted text -
>
- Show quoted text -

As the help manual of access:
expression.Conn ect
expression A variable that represents a Database object.
So the problem is the not connect string.

My problem is I don't know how to:
In a sub-routine in access:
Run a pass-through query that would create a "local access table" with
the result of the query

[Peter Doering]

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

magickarle wrote:

"Larry Linson" wrote:

>magickarle wrote:

>>Private Sub CmdExtract_Clic k()
>>Dim loDB As DAO.Database
>>Dim loQdf As DAO.QueryDef
>>Dim strSql As String

>>

>>Set loDB = CurrentDb
>>StrDate_ = Format(Me!TxtSt rDate, "yyyy-mm-dd")
>>EndDate_ = Format(Me!TxtEn dDate, "yyyy-mm-dd")

>>

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

>>

>>With loDB
>>.Connect = "ODBC;DSN=DNS_N AME;UID=USERID; PWD=PWD;QueryTi meout=0"
>>.Execute strSql, dbSQLPassThroug h
>>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.

The details are returned by the Errors collection:

For I = 0 To Errors.Count -1
Debug.Print Errors(I)
Next I

>>What I'm trying to do is to execute an action query pass-through that
>>would dump the records in a newly created TblTemp.

>>
>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!- Hide quoted text -

>
My problem is I don't know how to:
In a sub-routine in access:
Run a pass-through query that would create a "local access table" with
the result of the query

You cannot create a *local* table using pass-through (PT). If you need
TblTemp in your local mdb the easiest would be a standard query on the
linked table empl_Lst.

If you don't have a link on this table you can create one using:

DoCmd.TransferD atabase acLink, "ODBC Database", _
"ODBC;Driver={S QL Server};Server= Instance\YourSe rver;" & _
"Database=YourD B;UID=USERID;PW D=PWD;", _
acTable, "empl_Lst", "empl_Lst", , True

'Then

strSQL = "Select EmplID from empl_Lst into TblTemp ..."
loDB.Execute strSQL, dbFailOnError

--
Peter Doering [MVP Access]

[magickarle]

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

On Jul 18, 8:24 am, Peter Doering <nos...@doering .orgwrote:

magickarlewrote :

"Larry Linson" wrote:

>magickarlewrot e:
>Private Sub CmdExtract_Clic k()
>Dim loDB As DAO.Database
>Dim loQdf As DAO.QueryDef
>Dim strSql As String

>

>Set loDB = CurrentDb
>StrDate_ = Format(Me!TxtSt rDate, "yyyy-mm-dd")
>EndDate_ = Format(Me!TxtEn dDate, "yyyy-mm-dd")

>

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

>

>With loDB
>.Connect = "ODBC;DSN=DNS_N AME;UID=USERID; PWD=PWD;QueryTi meout=0"
>.Execute strSql, dbSQLPassThroug h
>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.

>
The details are returned by the Errors collection:
>
 For I = 0 To Errors.Count -1
  Debug.Print Errors(I)
 Next I
>

>What I'm trying to do is to execute an action query pass-through that
>would dump the records in a newly created TblTemp.

>

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!- Hide quoted text -

>

My problem is I don't know how to:
In a sub-routine in access:
Run a pass-through query that would create a "local access table" with
the result of the query

>
You cannot create a *local* table using pass-through (PT). If you need
TblTemp in your local mdb the easiest would be a standard query on the
linked table empl_Lst.

If it's not possible to create a local table, then would it be
possible to append to an access table the output of a pass-through
query?

>
If you don't have a link on this table you can create one using:
>
  DoCmd.TransferD atabase acLink, "ODBC Database", _
   "ODBC;Driver={S QL Server};Server= Instance\YourSe rver;" & _
   "Database=YourD B;UID=USERID;PW D=PWD;", _
   acTable, "empl_Lst", "empl_Lst", , True
>
'Then
>
 strSQL = "Select EmplID from empl_Lst into TblTemp ..."
 loDB.Execute strSQL, dbFailOnError
>
--
Peter Doering [MVP Access]- Hide quoted text -
>
- Show quoted text -

[Peter Doering]

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

magickarle wrote:

Peter Doering wrote:

>>
>You cannot create a *local* table using pass-through (PT). If you need
>TblTemp in your local mdb the easiest would be a standard query on the
>linked table empl_Lst.

>
If it's not possible to create a local table, then would it be
possible to append to an access table the output of a pass-through
query?

PT means that all control is handed over to the target Server and it
doesn't know about your local tables, so no, you cannot append either.

As mentioned, the easiest way would be to establish a link to the server
table and perform a local query.

You can use the sample to create the link:

|  DoCmd.TransferD atabase acLink, "ODBC Database", _
|   "ODBC;Driver={S QL Server};Server= Instance\YourSe rver;" & _
|   "Database=YourD B;UID=USERID;PW D=PWD;", _
|   acTable, "empl_Lst", "empl_Lst", , True

--
Peter Doering [MVP Access]

[magickarle]

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

On Jul 27, 2:35 pm, Peter Doering <nos...@doering .orgwrote:

magickarlewrote :

Peter Doering wrote:

>

You cannot create a *local* table using pass-through (PT). If you need
TblTemp in your local mdb the easiest would be a standard query on the
linked table empl_Lst.

>

If it's not possible to create a local table, then would it be
possible to append to an access table the output of a pass-through
query?

>
PT means that all control is handed over to the target Server and it
doesn't know about your local tables, so no, you cannot append either.
>
As mentioned, the easiest way would be to establish a link to the server
table and perform a local query.
>
You can use the sample to create the link:
>
|  DoCmd.TransferD atabase acLink, "ODBC Database", _
|   "ODBC;Driver={S QL Server};Server= Instance\YourSe rver;" & _
|   "Database=YourD B;UID=USERID;PW D=PWD;", _
|   acTable, "empl_Lst", "empl_Lst", , True
>
--
Peter Doering [MVP Access]

Actualy I've found a way:
In the GUI, I've create an action query that uses the pass-through
query.
From there, I can run it: It will run the pass-through as pass-through
and will append (or create) a table.
It is not a "direct data dump" from the pass-through but it does the
job.
cheers

[Peter Doering]

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

magickarle wrote:

Peter Doering wrote:

>magickarlewrot e:

>>Peter Doering wrote:

>>

>>>You cannot create a *local* table using pass-through (PT). If you need
>>>TblTemp in your local mdb the easiest would be a standard query on the
>>>linked table empl_Lst.

>>

>>If it's not possible to create a local table, then would it be
>>possible to append to an access table the output of a pass-through
>>query?

>>
>PT means that all control is handed over to the target Server and it
>doesn't know about your local tables, so no, you cannot append either.
>>
>As mentioned, the easiest way would be to establish a link to the server
>table and perform a local query.
>>
>You can use the sample to create the link:
>>
>>|  DoCmd.TransferD atabase acLink, "ODBC Database", _
>>|   "ODBC;Driver={S QL Server};Server= Instance\YourSe rver;" & _
>>|   "Database=YourD B;UID=USERID;PW D=PWD;", _
>>|   acTable, "empl_Lst", "empl_Lst", , True

>
Actualy I've found a way:
In the GUI, ...

of Access?

... I've create an action query that uses the pass-through
query.
From there, I can run it: It will run the pass-through as pass-through
and will append (or create) a table.

It will append (or create) a table on the server that you have passed the
query through to.

It is not a "direct data dump" from the pass-through but it does the
job.

Good for you.

--
Peter Doering [MVP Access]