使用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>
- 整个工作表。
- 工作表上的命名范围的单元格。
- 工作表上未命名的单元格范围。
Microsoft知识库文章 257819 的代码部分。
最简单的方法是将数据保存在单独的工作表中，将列名称放在第一行（从单元格A1开始），然后让实际数据从第2行开始，例如
要测试，我创建了一个名为odbcFromExcel的用户DSN，它指向该工作簿...
...然后运行以下VBScript来测试连接：
选项显式
Dim con，rst，rowCount
设置con = CreateObject（ADODB.Connection）
con.OpenDSN = odbcFromExcel;
Set 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）。Value =& rst（LastName）。
End If
rst.MoveNext
Loop
Wscript.Echo rowCount& 找到的数据行。
rst.Close
Set rst = Nothing
con.Close
设置con = Nothing
结果是
C：\Users\Gord\Documents\__tmp> ; cscript / nologo excelTest.vbs
数据行1，rst（LastName）。Value =Thompson
找到10个数据行。
我希望这有助于您的Excel连接问题。
作为最后的评论，我不得不说，如果你做的事情需要几秒钟在Excel中做，但需要大约20-25分钟在Access中，我强烈怀疑您正在以非常低效的方式使用Access，但这是另一个问题的主题（如果你关心它）。
EDIT
如果要将数据插入到Excel工作簿中，那么这是可能的，但请注意，Excel ODBC连接的默认设置为只读，因此您必须点击选项>>按钮并清除该复选框：
完成后，下面的代码...
选项显式
Dim con
设置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表单中附加一个新行，并提供数据。
但是，当您将sniffer应用程序指向Excel ODBC DSN时，仍然没有解决表格无法选择的问题。
您可以尝试创建一个Excel表格，其中列标题在第1行，然后选择这些整个列并创建一个Excel定义的名称。然后，查看您的sniffer应用程序是否识别出您可以选择的表名称。
FWIW，我定义了名称 myTable在我的Excel工作簿中，作为 = Sheet1！$ A：$ C ，然后我的原始代码 ：
<$ c> $ c> C：\Users\Gord\Documents\__tmp> cscript / nologo excelTest.vbs
数据行1，rst（LastName）。值=Thompson
1048576数据找到的行。
正如你所看到的，它正确地检索到了第一个记录有效数据的结束，并继续读取表中〜1百万行。
我非常怀疑，我会付出更多努力，因为我同意其他意见，使用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屋！
