使用Excel作为ODBC数据库 [英] Using Excel as an ODBC database

问题描述

我想知道，如何在Excel中创建数据库表，以便它可以与ODBC一起使用

我想使用ODBC，而我有两个选项，MS Access或Excel，

您可能知道，为了将一些MS Access文件或Excel文件指定为ODBC源，您需要遵循：

管理工具 - >数据源（ODBC） - >选择用户DSN - >从列表中选择Excel文件或MS Access数据库 - >新闻'配置' - >最后选择文件（MS Access或Excel）作为ODBC源

嗯，它可以正常使用MS Access，我可以连接到该文件，看到我在

中创建的所有表格但是当涉及到Excel时，尽管我可以连接到该文件，但我看不到我创建的表在

我刚在'插入'标签中使用'表'，添加了一些标题作为列名，并给出了
a表的有意义的名称。是这样做的吗？

解决方案

有几种方法可以在Excel工作簿中引用表数据： / p>

• 整个工作表。


• 工作表上指定的单元格范围。


• 工作表上的一个未命名的单元格范围。

他们在选择Excel数据代码部分Microsoft知识库文章 257819 。

最直接的方法是将数据保存在单独的工作表中，将列名称放在第一行（从单元格A1开始），然后将实际数据从第2行开始，就像这样

要测试，我创建了一个名为odbcFromExcel的用户DSN，指向该工作簿...

...然后运行以下VBScript来测试连接：

  Option Explicit 
 Dim con，rst，rowCount 
 Set con = CreateObject（ADODB.Connection）
 con.OpenDSN = odbcFromExcel; 
设置rst = CreateObject（ADODB.Recordset）
 rst.OpenSELECT * FROM [Sheet1 $]，con 
 rowCount = 0 
 Do While Not rst.EOF 
 rowCount = rowCount + 1 
如果rowCount = 1然后
 Wscript.Echo数据行1，rst（LastName）值=& rst（LastName）。Value& 
 End If 
 rst.MoveNext 
 Loop 
 Wscript.Echo rowCount& 找到数据行。 
 rst.Close 
 Set rst = Nothing 
 con.Close 
 Set con = Nothing 
  

结果是

  C：\Users\Gord\Documents\__tmp> ; cscript / nologo excelTest.vbs 
数据行1，rst（LastName）。Value =Thompson
找到10个数据行。 
  

希望能帮助您的Excel连接问题。

作为一个最后的评论，我不得不说，如果你正在做一些需要几秒钟在Excel中做但需要大约20-25分钟的事情在Access中，我强烈怀疑您以非常低效的方式使用Access，但这是另一个问题的主题（如果您关心追求）。

编辑

如果要将数据插入到Excel工作簿中，那么这是可能的，但请注意，Excel ODBC连接的默认设置为只读，所以你必须点击选项>>按钮并清除该复选框：

一旦完成，以下代码...

  Option Explicit 
 Dim con 
 Set con = CreateObject（ADODB.Connection）
 con.OpenDSN = odbcFromExcel; 
 con.ExecuteINSERT INTO [Sheet1 $]（ID，LastName，FirstName）VALUES（11，'Dumpty'，'Humpty'）
 con.Close 
 Set con = Nothing 
 Wscript.Echo完成。 
  

...将确实在Excel工作表中添加一个新行。 >

但是，当您将嗅探器应用程序指向Excel ODBC DSN时，仍然没有解决没有表可供选择的问题。

$您可以尝试一件事是创建一个Excel表格，其中列标题在第1行，然后选择这些整列并创建Excel定义的名称。然后，看看您的嗅探器应用程序是否识别为可以选择的表名称。

FWIW，我定义了名称 myTable在我的Excel工作簿中 as = Sheet1！$ A：$ C 然后我的原始代码 排序 当我使用 SELECT * FROM [myTable] ：

  C：\Users\Gord\Documents\__tmp> cscript / nologo excelTest.vbs 
数据行1，rst（LastName）。Value =Thompson
 1048576 data找到行
  

正如你所看到的，它正确地检索了第一个记录，但是它没有识别结束有效的数据，并继续阅读工作表中的〜100万行。

我非常怀疑，我将会更加努力，因为我同意使用Excel作为ODBC数据库的其他注释确实不是一个好主意。

我强烈建议您尝试找出您之前尝试使用Access的原因如何令人不满意。正如我之前所说，听起来像我在与Access进行互动时，正在做一件非常糟糕的工作。

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?

解决方案

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.

这篇关于使用Excel作为ODBC数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！