Making Temp Tables with ODBC

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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,
 
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.
 
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.
:
 
Yes, you will not find any drag-and-drop functionality within Access for
funcionality outside Access. With a little experimentation, however, you may
be able to automate this. You can build a query in Access, then switch to SQL
view, copy the SQL, and then paste it into the passthrough query.

However, this has a couple of limitations:

1. There may be slight variances in the SQL syntax so that you would have to
modify the SQL after pasting.
2. There is no direct relationship between the CREATE method of creating a
table in DB2 and the SELECT..INTO method available in Access (well, there may
be, but I have never tried this exactly).

In some cases, I build the SQL string in VBA: allow the user to select items
such as fields in combo boxes, then use the content of the combo boxes in VBA
at runtime to build either a SQL string or the the passthrough query.

Or, you can just build the passthrough query once for the table and modify
it programmatically or manually with anything specific to the particular
instance. I certainly prefer to automate anything that is possible if it is
repetitious.
 
Back
Top