Cheers for this!

I had most of it, but the extended properties bit for excel, dBase,
paradox & csv files is most helpful.  Cheers again.

padgett

-----Original Message-----
From: Toby Bascom [mailto:ThomasOBascom@c...] 
Sent: Thursday, 30 August 2001 3:38 PM
To: professional vb
Subject: [pro_vb] Re: Uniform method for accessing schemas across
providers

I've been looking for a uniform method of shoveling data from
one format to another but each format seems to have its own
idosyncrasies.

The SELECT Statement will work for Excel Tables and Named Ranges
within the Excel file. Note that when you set up the connection
string for the Excel "database", you'll have to set the Extended
Properties to one of the following:

     Extended Property           Notes
     -----------------        -------------
     Excel 3.0                No multiple sheets
     Excel 4.0                No multiple sheets
     Excel 5.0
     Excel 97
     Excel 8.0                = Access 2000

Example:
========

    Dim oCon As ADODB.Connection
    Dim oRst As ADODB.Recordset
    Set oCon = new ADODB.Connection
    oCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=d:\ExcelFiles\myExcel.xls;" & _
                            "Extended Properties=Excel 8.0;"

    oCon.Open
    Set oRst = oCon.OpenSchema(adSchemaTables)

The oRst will contain a list of tables (Excel sheets) and Named Ranges.
I think it is fair to say that a Named Range is analogous to an Access
View. Sheet names always end with a "$" sign; Named Ranges do not:

    If Right(Trim(oRst("TABLE_NAME").Value), 1)="$" Then
      msgBox "This is a Excel Sheet
    Else
      msgBox "We've got a named range here"
    End If

and your Select statement would look like this:

    "SELECT * FROM [" & oRst("TABLE_NAME").Value & "]"

The "[ ]" brackets are crucial!

The "Data Source" property of your connection string is equal to the
name of the subdirectory when your database is of the following types:

             dBASE III
             dBASE IV
             dBASE 5.0
             Paradox 3.x
             Paradox 4.x
             Paradox 5.x
             Text       (i.e. CSV files)

The above types are, at the same time, the values you must assign to the
Extended Properties property (be sure to include the semicolon).

As for Access-specific issues (Tables & Views) you'll either have to
do 2 separate queries:

' Get the non-system tables:
Set oRs = oCon.OpenSchema(adSchemaTables, _
                           Array(Empty, Empty, Empty, "TABLE"))


' Get the views
Set oRs = oCon.OpenSchema(adSchemaTables, _
                           Array(Empty, Empty, Empty, "VIEW"))

or get all tables:

Set oRs = oCon.OpenSchema(adSchemaTables)

and then screen:

     If oRs("TABLE_TYPE").Value = "VIEW" or _
        oRs("TABLE_TYPE").Value = "TABLE" Then

     End If

Regards,
-Toby



>From: "Padgett Rowell" <padgett@i...>
>Date: Wed, 29 Aug 2001 14:17:23 +0800
>
>Hello all,
>
>I have a VB application which I would like to be able to use with any
>(Microsoft) ODBC compliant data source.
>
>Once connected to a data source, the application needs to the ability
>to:
>
>         1) List all tables and views (queries) in the data source
>         2) Execute select statements against the data source
>
>I do not need to update the data source in any way, just read from it.
>
>My question is, is there a uniform way to access this information
across
>multiple providers?
>
>I know MS access 97 & MS access 200 both expose different methods to
>access the database container through the object model.  SQL Server
does
>it differently again.
>
>Can I use the OPEN_SCHEMA method to gain this information from Access
>97, 2000, 2002, SQL 7 & SQL 2000?
>
>How does MS Excel work?  Can you execute a select statement against an
>Excel worksheet?