Using Excel as an ODBC database

Question

I'd like to know, how to create a database table in Excel, so that it may be used with ODBC

I want to use ODBC, and I have two options, either MS Access or Excel,

As you probably know, in order to indicate some MS Access file or Excel file as an ODBC source, you need to follow:

Administrative Tools -> Data Sources (ODBC) -> Choose User DSN -> Choose either 'Excel Files' or 'MS Access Database' from the list -> Press 'Configure' -> finally choose the file (MS Access or Excel) as ODBC source

Well, it works fine with MS Access, I can connect to the file and see all tables that I've created inside

But when it comes to Excel, although I can connect to the file, I can't see the table that I've created inside

I just used 'Table' in 'Insert' tab, added some headers as column names, and gave the table a meaningful name. Is that the way to do it?

Solution 1

There are several ways you can reference "table" data in an Excel workbook:

• An entire worksheet.
• A named range of cells on a worksheet.
• An unnamed range of cells on a worksheet.

They are explained in detail in the "Select Excel Data with Code" section of the Microsoft Knowledge Base article 257819.

The most straightforward way is to keep the data on a separate sheet, put column names in the first row (starting in cell A1), and then have the actual data start in row 2, like this

To test, I created a User DSN named "odbcFromExcel" that pointed to that workbook...

...and then ran the following VBScript to test the connection:

Option Explicit
Dim con, rst, rowCount
Set con = CreateObject("ADODB.Connection")
con.Open "DSN=odbcFromExcel;"
Set rst = CreateObject("ADODB.Recordset")
rst.Open "SELECT * FROM [Sheet1$]", con
rowCount = 0
Do While Not rst.EOF
    rowCount = rowCount + 1
    If rowCount = 1 Then
        Wscript.Echo "Data row 1, rst(""LastName"").Value=""" &  rst("LastName").Value & """"
    End If
    rst.MoveNext
Loop
Wscript.Echo rowCount & " data rows found."
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing

The results were

C:\Users\Gord\Documents\__tmp>cscript /nologo excelTest.vbs
Data row 1, rst("LastName").Value="Thompson"
10 data rows found.

I hope that helps your Excel connection issue.

As a final comment I have to say that if you are doing something that takes "several seconds" to do in Excel but "takes around 20-25 min" to do in Access then I strongly suspect that you are using Access in a very inefficient way, but that's a topic for another question (if you care to pursue it).

EDIT

If you want to INSERT data into an Excel workbook then that is possible, but be aware that the default setting for an Excel ODBC connection is "Read Only" so you have to click the "Options>>" button and clear that checkbox:

Once that's done, the following code...

Option Explicit
Dim con
Set con = CreateObject("ADODB.Connection")
con.Open "DSN=odbcFromExcel;"
con.Execute "INSERT INTO [Sheet1$] (ID, LastName, FirstName) VALUES (11, 'Dumpty', 'Humpty')"
con.Close
Set con = Nothing
Wscript.Echo "Done."

...will indeed append a new row in the Excel sheet with the data provided.

However, that still doesn't address the problem of no "Tables" being available for selection when you point your "sniffer" app at an Excel ODBC DSN.

One thing you could try would be to create an Excel sheet with column headings in row 1, then select those entire columns and create an Excel "Defined Name". Then, see if your "sniffer" app recognizes that as a "table" name that you can select.

FWIW, I defined the name myTable as =Sheet1!$A:$C in my Excel workbook, and then my original code sort of worked when I used SELECT * FROM [myTable]:

C:\Users\Gord\Documents\__tmp>cscript /nologo excelTest.vbs
Data row 1, rst("LastName").Value="Thompson"
1048576 data rows found.

As you can see, it retrieved the first "record" correctly, but then it didn't recognize the end of the valid data and continued to read the ~1 million rows in the sheet.

I doubt very much that I will be putting any more effort into this because I agree with the other comments that using Excel as an "ODBC database" is really not a very good idea.

I strongly suggest that you try to find out why your earlier attempts to use Access were so unsatisfactory. As I said before, it sounds to me like something was doing a really bad job at interacting with Access.

Solution 2

I had a similar problem with some data recently. The way I managed to get around it was to select the data as a range A1:XY12345, then use the Define Name tool to name the range. When you connect to the Excel workbook via ODBC, this named range will appear as a "table," while ranges that you actually defined (per Excel) as a table, do not.

Comments

[mangusta]

thanks for reply, still your example assumes that the "table" already contains some entries, i.e. not empty, and VB program simply reads those entries. The sniffer that I've mentioned in comments above, should export data through ODBC, either into Excel file or MS Access file (empty, but supposed to contain at least one table), and it suggests to enter the database filename, and after we enter filename and press 'Connect', the tables within the file are listed in a separate box. The problem is that when I enter Excel filename, no tables appear in the list