RE: Making Temp Tables with ODBC
- From: Eli the Iceman <ElitheIceman@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 4 May 2006 07:23:03 -0700
I agree with you, that is exactly how I do it today, I create the table in
the legacy system and then populate it with Access. I tried the passthrough
and when I insert the DB2 SQL it works, but I lose all the visuals that is so
nice in Access, using queries to create tables by simply dropping the fields
desired. It would seem that there must be a way to insert something on the
Other DB Location line in the Make Table Query that will allow you to make it
a passthrough without having to use the Straight SQL. Oh Well, thanks for
the info.
"Brian" wrote:
"Brian" wrote:
I actually have a DB2 system that I manage, although I am not involved in.
modifying table structures.
However, when patches come through from my vendor, I see such SQL statements
as "CREATE TABLE...", so I know it is possible to create a table via SQL.
In a couple of cases, I use passthrough queries to hit the sequence
generators in DB2 and even to reset the sequence starting points, but I have
never created or dropped anything more than a trigger or two.
Try writing the SQL statement in the legacy system and then copying &
pasting it into a passthrough query. I would suggest that you try a few
SELECT statements this way first, to get comfortable with using the
passthrough queries before you try to create a table (you will probably also
need to GRANT rights to the DB2 table once it is created). Again, I'm a
little weak on the complexities of DB2's particular flavor of SQL, but as I
mentioned before, the passthrough query basically just passes through the
query to DB2 for you.
Better yet (this might be simpler, if it will work for you), create an empty
table once via the legacy software and just run the ODBC query to populate
the table as needed- this way you can just point to the newly-created table
via ODBC and use regular Access queries instead of passthrough queries.
"Eli the Iceman" wrote:
Thanks Brian,
I am creating a temp table, really a table that may be available for a year
or so, into a legacy system that hold thousands of tables. I need to do this
in order to allow many users through the system to be able to produce
reports. It is a TeraData system using DB2 SQL. Currently I have to do it
manually by going into the legacy system and writing the SQL statement, but I
wondered if there is a way to get Access to do it, it would be much better.
I am using an ODBC driver to connect to the Teradata system, but when I try
to get access to write the table to another DB, it does not give me the
option of using an ODBC. With a passthrough query, I still did not see on
how to write the table through the ODBC driver.
Thanks,
"Brian" wrote:
Is this creating a temporary table in an Access database from another Access
app via ODBC, or is it creating a temporary table in another database from
Access using a DSN? What DB platform?
You can pass virtually any SQL statement the database platform will accept
to the database using a SQL-Specific -> Passthrough query: such things as
creating/deleting tables, creating/running stored procedures, etc.
If you just need temporary data, why not just create your temporary table in
Access using data derived via ODBC? Or, if the data is very temporary, just
have two queries: one that pulls information via the ODBC and the other that
uses the first query as its source.
"Eli the Iceman" wrote:
Is it possible to create tables through an ODBC connection?
- Follow-Ups:
- RE: Making Temp Tables with ODBC
- From: Brian
- RE: Making Temp Tables with ODBC
- Prev by Date: Re: Importing Excel, problem with data types.
- Next by Date: Re: Importing Data Access
- Previous by thread: Re: Importing Excel, problem with data types.
- Next by thread: RE: Making Temp Tables with ODBC
- Index(es):
Relevant Pages
|
