By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Action pass-through with a form variable Access 2003

P: n/a
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.
Jul 16 '08 #1
Share this Question
Share on Google+
13 Replies


P: n/a
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" <ma********@hotmail.comwrote in message
news:9b**********************************@k13g2000 hse.googlegroups.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.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.

Jul 16 '08 #2

P: n/a
Larry Linson wrote:
>[...]
where empl_lst.timestamp 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]
Jul 16 '08 #3

P: n/a
magickarle <ma********@hotmail.comwrote in news:9bdf57af-8cd2-4dff-9861-
3a**********@k13g2000hse.googlegroups.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.
Jul 16 '08 #4

P: n/a
On Jul 16, 2:53*pm, "Larry Linson" <boun...@localhost.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...@hotmail.comwrote in message

news:9b**********************************@k13g2000 hse.googlegroups.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.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
Jul 17 '08 #5

P: n/a
On Jul 16, 5:28*pm, lyle fairfield <lylef...@yah00.cawrote:
magickarle<magicka...@hotmail.comwrote in news:9bdf57af-8cd2-4dff-9861-
3a0125e9a...@k13g2000hse.googlegroups.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!
Jul 17 '08 #6

P: n/a
On Jul 17, 11:39*am, magickarle <magicka...@hotmail.comwrote:
On Jul 16, 2:53*pm, "Larry Linson" <boun...@localhost.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...@hotmail.comwrote in message
news:9b**********************************@k13g2000 hse.googlegroups.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.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!
Jul 17 '08 #7

P: n/a
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" <ma********@hotmail.comwrote in message
news:5d**********************************@z72g2000 hsb.googlegroups.com...
On Jul 17, 11:39 am, magickarle <magicka...@hotmail.comwrote:
On Jul 16, 2:53 pm, "Larry Linson" <boun...@localhost.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...@hotmail.comwrote in message
news:9b**********************************@k13g2000 hse.googlegroups.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.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!
Jul 17 '08 #8

P: n/a
On Jul 17, 2:54*pm, "Larry Linson" <boun...@localhost.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...@hotmail.comwrote in message

news:5d**********************************@z72g2000 hsb.googlegroups.com...
On Jul 17, 11:39 am,magickarle<magicka...@hotmail.comwrote:


On Jul 16, 2:53 pm, "Larry Linson" <boun...@localhost.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...@hotmail.comwrote in message
>news:9b**********************************@k13g200 0hse.googlegroups.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.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!- Hide quoted text -

- Show quoted text -
As the help manual of access:
expression.Connect
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
Jul 17 '08 #9

P: n/a
magickarle wrote:
"Larry Linson" wrote:
>magickarle wrote:
>>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.
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.TransferDatabase acLink, "ODBC Database", _
"ODBC;Driver={SQL Server};Server=Instance\YourServer;" & _
"Database=YourDB;UID=USERID;PWD=PWD;", _
acTable, "empl_Lst", "empl_Lst", , True

'Then

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

--
Peter Doering [MVP Access]
Jul 18 '08 #10

P: n/a
On Jul 18, 8:24*am, Peter Doering <nos...@doering.orgwrote:
magickarlewrote:
"Larry Linson" wrote:
>magickarlewrote:
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.

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.TransferDatabase acLink, "ODBC Database", _
* *"ODBC;Driver={SQL Server};Server=Instance\YourServer;" & _
* *"Database=YourDB;UID=USERID;PWD=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 -
Jul 24 '08 #11

P: n/a
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.TransferDatabase acLink, "ODBC Database", _
|* *"ODBC;Driver={SQL Server};Server=Instance\YourServer;" & _
|* *"Database=YourDB;UID=USERID;PWD=PWD;", _
|* *acTable, "empl_Lst", "empl_Lst", , True

--
Peter Doering [MVP Access]
Jul 27 '08 #12

P: n/a
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.TransferDatabase acLink, "ODBC Database", _
|* *"ODBC;Driver={SQL Server};Server=Instance\YourServer;" & _
|* *"Database=YourDB;UID=USERID;PWD=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
Jul 30 '08 #13

P: n/a
magickarle wrote:
Peter Doering wrote:
>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.TransferDatabase acLink, "ODBC Database", _
|* *"ODBC;Driver={SQL Server};Server=Instance\YourServer;" & _
|* *"Database=YourDB;UID=USERID;PWD=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]
Jul 30 '08 #14

This discussion thread is closed

Replies have been disabled for this discussion.