SQL Query

[Mark Scholes]

Hi,
I want to create a temporary table and then join it to an
existing table in a remote database. Can I call a Stored
procedure and get it's output table into a excel workbook
or can I create more complex queries.

Thanks MarkS

 

[Guest]

1. Create an ADO record set object; using the ADDNEW method, create your initial table
2. Save the updated record set object as a disconnected record set object; use the SAVE method. Close the record set object
3. Use the ADO record set object and read what you want from the remote database; you need an SQL for what you want to read and a conection string
5. Write the record object as a disconnected record set object; use the Save method. Close the record set object
6. Specify an SQL to read the first disconnected record set and then a subquery to read the second and the conditions on which they must be linked
7. Use the Excel CopyFromRecordSet function to write the record object arrived at in 6 to a worksheet

OR

1. Create your temporary table in an Access database; populate it manually or import its content from, say, Excel
2. Link the remote database table to another table
3. Create a query linking the two tables
4. Export the contents ofthe query to Excel

This latter approach can be executed using the mouse alone; except for populating the temporary table.

 

[onedaywhen]

As you proably know, you cannot join tables unless they exist in the
same database. As you are dealing with a temp table, consider linked
servers. If you are using SQL Server (usually the case when stored
procedures are mentioned this ng) you can link to the Excel workbook
from there via Enterprise Manager.

If it's not SQL Server, or you do not have Enterprise Manager rights,
consider using a linked table in a Jet database created on the fly.

Personally, I'd prefer to import the data into Excel and query from
there using ADO. Yes, you can do quite complex queries on Excel data
but you are limited to the syntax of the MS OLEDB provider for Jet.
Version 4.0 is pretty good but it's a significantly short of ANSI
SQL-92 compliance.

If you choose the Jet linked table route, I'll point out don't need
the MS Access application to create and/or query a Jet database (.mdb
file), including linking tables. You can do all this on the fly using
only Excel and ADOX (to create the .mdb file) and ADO (use DDL
statements to create the schema e.g. CREATE TABLE etc and SQL for
queries).

You do need MDAC, free MS download and shipped with Excel, and Jet,
free MS download and shipped with early versions of MDAC, so it's
highly likely you already have the necessary components shipped with
Excel.

See the following links (in these articles, read 'Jet' for the word
'Access'
because the MS Access application is not actually used):

Creating an Access(sic) Database:
http://msdn.microsoft.com/library/d...s/odeopg/html/deovrcreatingaccessdatabase.asp

Running a Temporary Query:
http://msdn.microsoft.com/library/d...s/odeopg/html/deovrcreatingaccessdatabase.asp

Creating a Linked Table:
http://msdn.microsoft.com/library/d...n-us/odeopg/html/deovrcreatinglinkedtable.asp