我正在开发一个需要数据库后端的excel应用程序.我的偏好是使用sqlite 3并使其尽可能无缝和便携,以供最终用户使用.
最近我了解到Excel 2007文件只是一个带有xlsm扩展名的zip存档.我的问题是,我可以将我的后端sqlite 3数据库存储在Zip存档中,并使用ODBC与数据库进行交互.如果是这样,任何人都可以向我指出一些背景信息,文章,实现这一目标的指导.这种方法是否有任何缺点或者我应该知道更好的替代方案.
感谢您的输入.
一些笔记.到目前为止,还没有人抱怨该文件没有打开.请注意,Excel文件在运行ADO代码之前保存.
原文链接：https://www.f2er.com/sqlite/197748.html很隐蔽：
ThisWorkbook.Worksheets("Courses").Visible = xlVeryHidden
ThisWorkbook.Worksheets("System").Visible = xlVeryHidden
一段代码：
Const gCN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
< …>
Set rs = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")
Set fs = CreateObject("Scripting.FileSystemObject")
scn = gCN & ThisWorkbook.FullName _
& ";Extended Properties=""Excel 8.0;HDR=Yes;"";"
cn.Open scn
''If they do not have an ID,they do not exist.
ssql = "SELECT ID,FirstName,LastName," _
& "CourseName,AdditionalText,Format(ExpiryDate,'dd/mm/yyyy') As ExpiryDate " _
& "FROM [Applicants$] WHERE DateCancelled Is Null AND ID Is Not Null " _
& "AND (FirstName Is Null OR LastName Is Null Or CourseName Is Null " _
& "Or ExpiryDate Is Null) " & sWhere
rs.Open ssql,cn
参考文献：
Jet可用的大多数方法都可以与Excel一起使用
Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000
编辑重新评论
我没有发现泄漏特别糟糕,但我没有进行多次迭代,这是一台非常好的机器.
下面的代码使用DAO,它不会导致内存泄漏.
'Reference: Microsoft Office 12.0 Access Database Engine Object Library Dim ws As DAO.Workspace Dim db As DAO.Database Dim rs As DAO.Recordset Dim sDb As String Dim ssql As String sDb = ActiveWorkbook.FullName Set ws = DBEngine.Workspaces(0) Set db = ws.OpenDatabase(sDb,False,True,"Excel 8.0;HDR=Yes;") ssql = "SELECT * FROM [Sheet1$];" Set rs = db.OpenRecordset(ssql) Do While Not rs.EOF For i = 0 To rs.Fields.Count - 1 Debug.Print rs.Fields(i) Next rs.MoveNext Loop rs.Close db.Close ws.Close 'Release objects from memory. Set rs = Nothing Set db = Nothing Set ws = Nothing
