vba如何调用Access的查询,并显示查询结果?

Question

想请教大家一个问题啊:
我写了一个VBA程序 ,与Access连起来了,我想执行Access里面的写好的查询(query).并将结果显示出来,请问,可以这样做吗 ?

Answer 1

当然可以，用DAO，可以在VB添加DAO组件。
然后，定义参数

'Database Connection
Dim cn As Workspace
Dim db As Database
Dim Rs As Recordset
Dim YOURPWD$, strSQL$, sContent$

'设置数据库
Set cn = DBEngine.Workspaces(0)
Set db = cn.OpenDatabase(sDbPath, False, False, ";pwd=" & YOURPWD)

'打开数据库
strSQL = "Select * From YourTable"
Set Rs = db.OpenRecordset(strSQL, , dbReadOnly)

'取出一个不为NULL的字符串字段值
sContent = trim$(Rs!Item1)

--------------------------------------------------------------
'使用前提在VB中加入VB组件

Dim xlsApp As Object, xlsBook As Object, xlsSheet As Object

Dim Row&, Col&

On Error GoTo ExcelInport_Err

'创建应用Excel程序

Set xlsApp = CreateObject("Excel.Application")

'Excel WorkBook 的添加

Set xlsBook = xlsApp.Workbooks.Add

’取得活动的Excel Sheet

Set xlsSheet = xlsBook.ActiveSheet

'Excel Sheet的标题

xlsSheet.Name = "Your Sheet Name"

'Excel Sheet 内容的填充

With xlsSheet

Row = 1
'行

Col = 1
'列

.Cells(Row, Col).Value = "Your Fill Content"

End With

‘Excel

xlsApp.Visible = True

Set xlsSheet = Nothing

Set xlsBook = Nothing

Set xlsApp = Nothing

Exit Sub

ExcelInport_Err:

'关闭时没有消息框

xlsApp.DisplayAlerts = False

xlsApp.Quit

Set xlsApp = Nothing