Auto macro or sql request - Select all from temp table

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

jakeott

Thread Starter
Joined
Apr 16, 2002
Messages
6
Hi,

I'm hoping that someone can help me.

Through Oracle forms I create a temporary table and populate it with data from the db. The temp table may have a different structure each time it is created. One time it may have just an id column. The next id, name and city, the next time it is created it may just have id and name, etc. The table is a dynamic table that is only in the database while the user is in this portion of the application.

From the Oracle form where the temp table is created and populated a call to Excel is made, and Excel starts opening up the called Excel report. After the user closes excel and goes back to the Oracle form the temp table is dropped from the db.

What I am trying to do next is get Excel to select all from the temp table and show the results automatically when the called report is first opened. Keeping in mind that the table structure may not be the same way twice.

If anyone has any ideas/code how it would be appreciated.

I am not totally familiar with Excel and have been bouncing around trying a couple of ways. One way was to create a macro stepping through creating a database query. But this way seems to blow up as soon as the table structure is changed. And when I went into the macro and edited the macro to make the select statement Select * (all) from the temp table, it didn't like that syntax. The macro syntax uses the specific column names, which is no good as the table structure changes. I need it to take the generic select all.

Another way I have been trying is to use sql.request. I am not totally sure of the syntax here as my query result only brought back the first column header in A1 and then nothing else.

I put the sql.request code in A1

=SQL.REQUEST("DSN=dbsourcename; UID=user; PWD=password",A1,2,"Select * from temp_table",TRUE)

Any help would be appreciated. More than willing to try another path if anyone has some ideas on this, as well as to make it run the query automatically as the excel file opens.

Thanks:confused:
 
Joined
Jul 29, 2001
Messages
21,334
Create a template that has VB code running your SQL at startup. Why would the temp table be different every time? Seems your report would be changing all the time too. There should be criteria, but a differnt table is another story.
 

jakeott

Thread Starter
Joined
Apr 16, 2002
Messages
6
The Oracle form that the user comes from has 15 or 20 checkboxes and a variety of other radio buttons and text fields to determine what information they want to see in a report. The form is set up to take all of that information and create a tempory table populated with the results. The oracle form is part of a larger Oracle application. But the user wants this new form to display the results in excel.

The idea was that the temp table would have only the columns and information requested by the user. Then excel would open and select * from temp_table and display the results. So yes in fact the report would show differently everytime. To make it dynamic was part of the appeal.

It's been quite some time since I had to code in Vb, I've been lucky enough to work mainly in sql or plsql for the last few years. This might be an adventure. lol But I'll see if I can stumble around and find out how to get the template up and running.

Any other hints or ideas would be appreciated.

Thanks
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
What happens when you select *?

I thought you had to give the fieldnames too, so:

select * from *;

?

I'm not an SQLer, but that's how I've seen them.
 

jakeott

Thread Starter
Joined
Apr 16, 2002
Messages
6
Hi,

Select * means select all. When you code in SQL if you want to select all columns you use *
 

jakeott

Thread Starter
Joined
Apr 16, 2002
Messages
6
Some of those tables can get pretty large so rather than having to list EVERY column some bright fellow let us take the lazy way out. lol

Of course if you really WANT to you can still select by all the individual column names in the table. lol

However if you are only selecting certain columns in the table then you have to list them in your select statement.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top