Update table from ODBC

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been using a pass-through query to populate a report based on a table
from a database external to Access. This works fine, however now the
requirements have altered slightly and the results of the query need to be
written into an Access table and I have no idea how to achieve this, ideas
anyone?

Thanks Peter
 
Dear Peter:

Since you seem to be able to write a simple SELECT query on this, the next
step is rather simple. Change it to an append query and indicate the table
into which you want this appended.

Tom Ellison
 
Ahhh... I see with both eyes now. For some reason I had it in my mind that
pass-through and append queries were mutually exclusive.

Thankyou.
 
Dear Peter:

This option depends on your knowledge of the "foreign database engine" to
perform append queries. I'm not so sure it will permit you to append a
local Access (Jet or MSDE) table. You would, however, be able to append a
table within that external database.

I was actually thinking of you writing a Jet or MSDE (depending only on
which you are using) and appending to that table. This is not so
problematic. Have you been able to write such a query? Does it work to
change this to an append.

Tom Ellison
 
You can use the Pass-Through Query as the Source for your Append Query,
AFAIK.

Alternatively, you can use an Append Query with ODBC-linked Table(s) as the
Source for the Append Query.
 
Tom,

The foreign database engine is Pronto, using CI-SAM based on UNIX, the ODBC
driver is provided by the software vendor who supplied the database and has
performed very well to date, although I have never attempted to write to an
Access (Jet) table from it before.

I did modify the query to become an append query by verifying the data types
were as close as possible to being the same between the source and
destination tables, and then added the INSERT INTO statement to the query
prior to SELECT.

However the result is an "ODBC--call failed" error, so perhaps that option
may not work for me.
 
I tried using the pass-through query as the source for a separate append
query and it worked a treat.

Just for the excercise I also tried ODBC linking the source table in the
external DB and tried to run the append query from it - but the query return
odd results, it seemed to ignore the criteria that had been set. I wondered
if it was a linking problem, but I am only querying a single table???

Don't know - but the first method worked perfectly

Thanks.
 
Dear Peter,

Sorry, I'm not familiar with CI-SAM, but that's where your error seems to
be. However, I was saying to write your SELECT query for Access/Jet and
have it retrieve the desired columns. Then change this query into an append
query by adding "INTO TableName" just before the FROM, or by using the query
design interface to do the same kind of thing.

Tom Ellison
 
The 2 methods should result to the same set of Records appended unless the
criteria are interpreted differently. If you use Pass-Through, the ODBC
server interprets your criteria. If you use linked Table, JET uses
SQLGetInfo and SQLGetTypeInfo to ask the ODBC driver whether the server
supports operators + functions used in your criteria and try to pass as much
as possible to the server. However, there are numerous cases where JET will
actually performs the calculations rather than passing to the server.

If you want, post the SQL Strings of your Pass-Through Query, your Append
Query using the Pass-through and your Append Query using ODBC-linked Table
and others may know why the same criteria in 2 Queries behave differently.
 
Back
Top