Passthrough query

[Sandy Hayman]

Hi
Does anyone know if it is possible to use an sql passthrough query to append
data from a local table to an sql server database.

I have tried several sql statements that work when I try to append from sql
server database to sql server database tables but as soon as I select from
the same structured local table, the query doesn't do anything.

Some of the sql statements I have tried are:

SELECT INTO cdb.comm_data SELECT * FROM comm_data
SELECT INTO cdb.comm_data (data_date, id, ivalue) SELECT data_date, id,
ivalue FROM comm_data

cdb.comm_data is the sql server table.
comm_data is the local table

These statements work if I select from a replica table on the server
database but not when I select from local.

Any clues?

Thanks in advance
Sandy

 

[Danny J. Lesandrini]

Sandy, my gut feeling is no, this is not possible.

Think about what a Pass Through means. You're passing a COMMAND through
to SQL Server. It is unaware of any local tables.

You'll have to use a linked table or, if the data is small, you might create an
array of INSERT statements with the data values hard coded, passing through
the data as well.

 

[Bob Barrows]

Hi
Does anyone know if it is possible to use an sql passthrough query to
append data from a local table to an sql server database.

No. Again, sql statements in passthrough queries are "passed" "through"
to the destination database server to be executed. The only elements in
the sql statement that the destination server will recognize are _those
that exist in the destination database_.

I have tried several sql statements that work when I try to append
from sql server database to sql server database tables but as soon as
I select from the same structured local table, the query doesn't do
anything.

Are you suppressing errors? An error should be raised by these attempts.

Some of the sql statements I have tried are:

SELECT INTO cdb.comm_data SELECT * FROM comm_data
SELECT INTO cdb.comm_data (data_date, id, ivalue) SELECT data_date,
id, ivalue FROM comm_data

cdb.comm_data is the sql server table.
comm_data is the local table

These statements work if I select from a replica table on the server
database but not when I select from local.

Of course! By definition. Would you be surprised if these statements
raised errors when using SSMS (or SQL Query Analyser if pre-SQL2005) to
execute them?

You have two choices:
1. use linked tables in your Access database in a regular
(non-passthrough) local query
2. create a linked server on your sql server to the Access database and
write the passthrough query to utilize the 4-part naming convention that
will cause it to look to the server link for the remote table in your
sql statement
3. use VBA to build insert...values() statements which you use to set
the SQL property of the passthrough querydef - of course, that limits
you to doing a single row at a time

Actually, you have other choices:
Use Access's Export Data functionality
Use SQL Server's DTS/SSIS functionality
There are other choices that depend on the version of SQL Server you are
using (BULK INSERT, OPENQUERY, OPENROWSET, etc.) so I won't go into
details

 

[John Spencer]

A passthrough query is executed by the database engine that receives the
SQL statement. MS SQL Server has no idea of the existence of the Access
(JET) database, therefore it can't use the Access database.

MS SQL Server can "link" to the Access database. I can't tell you
exactly how to set that up at this time, since I am not on a
computer/network where I have MS SQL server available.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================