连接 Access 2007 的操作方法
//无密码的连接字符串
stringconStr = "Provider=Microsoft.Ace.OleDb.12.0;";
conStr += @"Data Source=E:\数据库\XiaoZhen.accdb;";
conStr += "Persist Security Info=False;";

//有密码的连接字符串
stringconStr = "Provider=Microsoft.Ace.OleDb.12.0;";
conStr += @"Data Source=E:\数据库\XiaoZhen.accdb;";
conStr += "Jet OleDb:DataBase Password='829321';";

连接 Access 2003的操作方法

//无密码的连接字符串
stringconStr = "Provider=Microsoft.Jet.OleDb.4.0;";
conStr += @"Data Source=E:\数据库\XiaoZhen.mdb;";
conStr += "Persist Security Info=False;";

//有密码的连接字符串
stringconStr = "Provider=Microsoft.Jet.OleDb.4.0;";
conStr += @"Data Source=E:\数据库\XiaoZhen.mdb;";
conStr += "Jet OleDb:DataBase Password='829321';";

<connectionStrings>
<add name="ConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=F:\TeacherSystem\App_Data\db.mdb;
Jet OLEDB:Database Password=123"providerName="System.Data.OleDb"/>
</connectionStrings>

Private Sub CommandButton2_Click()
    Dim myCon      As New ADODB.Connection
    Dim myRst      As New ADODB.Recordset
    Dim myFileName As String
    Dim myTblName  As String
    Dim myKey      As String
    Dim dbFile As String
    Dim mySht      As Worksheet
    Dim i          As Long
    Dim j          As Long
    Dim sql As String
    sql = Space$(10000)
    Dim sql2 As String
    sql2 = Space$(10000)
    Dim sql3 As String
    sql3 = Space$(10000)
    
    TbName = Cells(1, 8)
    myFileName = "db1.mdb"
    dbFile = "C:\Program Files\SWTOOLS\TXT\" & myFileName & ";"
    myCon.Open "Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & dbFile
    
        '创建表
        sql = "CREATE TABLE [" & TbName & "]("
        For i = 2 To 255
            ZdName = Cells(2, i)
            ZdType = Cells(3, i)
            If i = 2 And ZdType = "int" Then ZdType = "AUTOINCREMENT"
            ZdNull = Cells(5, i)
            If ZdName = "" Then Exit For
            If ZdNull = "Y" Then ZdNull = "NULL" Else ZdNull = "NOT NULL"
            sql2 = " " & ZdName & "  " & ZdType & "  " & ZdNull & ","
            sql = sql & sql2
        Next
        sql = sql & "PRIMARY KEY ([ID]));"
        
        'MsgBox Len(sql)
        myCon.Execute sql
        '创建记录
        For j = 6 To 40000
            sql = ""
            Rows(j).Select
            If Cells(j, 2) = "" Then Exit For
            sql = sql & "insert into " & TbName & " values" & Chr(13)
            sql = sql & "('"
            For i = 2 To 255
                ZdName = Cells(2, i)
                ZdCont = Cells(j, i)
                If ZdName = "" Then Exit For
                If i = 2 Then
                    sql = sql & ZdCont
                Else
                    sql = sql & "','" & ZdCont
                End If
            Next
            sql = sql & "');" & Chr(13)
            myCon.Execute sql
        Next
    Set myCon = Nothing
End Sub