以下excel连接mdb的方法,是在AutoCAD VBA二次开发教程P336页的基础,进行改编。改编要点与原书的区别,采用了function模块化功能,原书用的是sub,用call 调用。'生成access数据库模块Function CreateAccessDB(AccessDbName As String)
Dim AccessApp As Access.Application
Set AccessApp = CreateObject("Access.Application")
On Error Resume Next
AccessApp.NewCurrentDatabase ThisWorkbook.Path & AccessDbName
Debug.Print TypeName(AccessApp)
If Err.Number = 7865 Then
Err.Clear
'AccessApp.OpenCurrentDatabase ThisWorkbook.Path & "\Test.Mdb"
End If
If Not Err Then
MsgBox "Have bulid DataBase is " & ThisWorkbook.Path & AccessDbName
End If
AccessApp.CloseCurrentDatabase
Set AccessApp = Nothing
End Function'与Access数据库连接模块Private Function CreateConnection(AccessDbName As String) As ADODB.Connection
Dim ConStr As String, Cnn As ADODB.Connection
Set Cnn = New ADODB.Connection
With Cnn
.CursorLocation = adUseClient
.Provider = "Microsoft.Jet.OLEDB.4.0"
ConStr = "Data Source =" & ThisWorkbook.Path & AccessDbName
Cnn.Open ConStr
End With
Debug.Print "Have been Connection " & ThisWorkbook.Path & AccessDbName
Set CreateConnection = Cnn
End Function'在 Access数据库建立表的模块
Private Function CreateAccessDataTable(AccessDbName As String)
Dim Cnn As ADODB.Connection, Cmd As ADODB.Command
Set Cnn = CreateConnection(AccessDbName)
Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = Cnn
Cmd.CommandText = "Create table aa(aa char(10))"
Cmd.Execute
End Function'测试程序
Sub mm()
'CreateAccessDB ("\Test.Mdb")
CreateAccessDataTable ("\Test.Mdb")
End Sub
Dim AccessApp As Access.Application
Set AccessApp = CreateObject("Access.Application")
On Error Resume Next
AccessApp.NewCurrentDatabase ThisWorkbook.Path & AccessDbName
Debug.Print TypeName(AccessApp)
If Err.Number = 7865 Then
Err.Clear
'AccessApp.OpenCurrentDatabase ThisWorkbook.Path & "\Test.Mdb"
End If
If Not Err Then
MsgBox "Have bulid DataBase is " & ThisWorkbook.Path & AccessDbName
End If
AccessApp.CloseCurrentDatabase
Set AccessApp = Nothing
End Function'与Access数据库连接模块Private Function CreateConnection(AccessDbName As String) As ADODB.Connection
Dim ConStr As String, Cnn As ADODB.Connection
Set Cnn = New ADODB.Connection
With Cnn
.CursorLocation = adUseClient
.Provider = "Microsoft.Jet.OLEDB.4.0"
ConStr = "Data Source =" & ThisWorkbook.Path & AccessDbName
Cnn.Open ConStr
End With
Debug.Print "Have been Connection " & ThisWorkbook.Path & AccessDbName
Set CreateConnection = Cnn
End Function'在 Access数据库建立表的模块
Private Function CreateAccessDataTable(AccessDbName As String)
Dim Cnn As ADODB.Connection, Cmd As ADODB.Command
Set Cnn = CreateConnection(AccessDbName)
Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = Cnn
Cmd.CommandText = "Create table aa(aa char(10))"
Cmd.Execute
End Function'测试程序
Sub mm()
'CreateAccessDB ("\Test.Mdb")
CreateAccessDataTable ("\Test.Mdb")
End Sub
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货