|
一、ACCESS數(shù)據(jù)庫(kù)加密 1、ACCESS 2013數(shù)據(jù)庫(kù)加密方式 （1）使用舊版加密（適用于反向兼容和多用戶(hù)數(shù)據(jù)） （2）使用默認(rèn)加密（安全性較高） ADO連接僅支持舊版加密，使用VBA代碼動(dòng)態(tài)創(chuàng)建帶密碼的Access 數(shù)據(jù)庫(kù)文件，也是舊版加密。 2、數(shù)據(jù)庫(kù)如果采用手工設(shè)置密碼，則要設(shè)置為舊版加密方式： 打開(kāi)ACCESS 2013—Access選項(xiàng)—客戶(hù)端設(shè)置—加密方法--使用舊版加密（適用于反向兼容和多用戶(hù)數(shù)據(jù)） 二、設(shè)置ADO連接ACCESS數(shù)據(jù)庫(kù)方式一 （1）引用 Microsoft ActiveX Data Objects 2.x Library（操作方式：Visual Basic-工具-引用，不同office版本不同） 說(shuō)明：當(dāng)未引用，運(yùn)行后會(huì)顯示“用戶(hù)定義類(lèi)型未定義” （2）連接代碼 Sub 導(dǎo)入數(shù)據(jù)1() Dim cnn As ADODB.Connection '數(shù)據(jù)庫(kù)連接 Dim mydata As String '數(shù)據(jù)庫(kù)的完整路徑和名稱(chēng) Dim mytable As String '數(shù)據(jù)表名稱(chēng) Dim sql As String 'sql語(yǔ)句 Dim rs As ADODB.Recordset '臨時(shí)數(shù)據(jù)表紀(jì)錄 Dim i As Integer '循環(huán)數(shù)據(jù)變量（獲取數(shù)據(jù)表字段） '1、連接數(shù)據(jù)庫(kù) Set cnn = New ADODB.Connection mydata = ThisWorkbook.Path & "\進(jìn)銷(xiāo)存數(shù)據(jù)庫(kù).accdb" With cnn .Provider = "Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password='123456'" .Open mydata End With '2、建立與數(shù)據(jù)庫(kù)的連接 mytable = "采購(gòu)數(shù)據(jù)3" sql = "select 采購(gòu)數(shù)據(jù)3.ID,采購(gòu)數(shù)據(jù)3.采購(gòu)日期,采購(gòu)數(shù)據(jù)3.供貨類(lèi)型,采購(gòu)數(shù)據(jù)3.采購(gòu)分類(lèi),采購(gòu)數(shù)據(jù)3.供應(yīng)商,采購(gòu)數(shù)據(jù)3.名稱(chēng),采購(gòu)數(shù)據(jù)3.單位,采購(gòu)數(shù)據(jù)3.單價(jià),采購(gòu)數(shù)據(jù)3.數(shù)量,采購(gòu)數(shù)據(jù)3.金額,采購(gòu)數(shù)據(jù)3.入賬日期" & " from " & mytable & " order by 采購(gòu)日期" Set rs = New ADODB.Recordset rs.Open sql, cnn, adOpenKeyset, adLockOptimistic '3、復(fù)制數(shù)據(jù)庫(kù)數(shù)據(jù) ' (1)清除原數(shù)據(jù) ActiveSheet.Cells.ClearContents '（2）復(fù)制字段名 For i = 1 To rs.Fields.Count Cells(1, i) = rs.Fields(i - 1).Name Next i '（3）復(fù)制全部數(shù)據(jù) Range("A2").CopyFromRecordset rs rs.Close cnn.Close Set rs = Nothing Set cnn = Nothing End Sub 三、設(shè)置ADO連接ACCESS數(shù)據(jù)庫(kù)方式二 無(wú)需引用，直接創(chuàng)建連接 Sub 導(dǎo)入數(shù)據(jù)2() Dim cnn As Object '數(shù)據(jù)庫(kù)連接 Dim strcnn As String 'ACCESS連接語(yǔ)句 Dim mydata As String '數(shù)據(jù)庫(kù)的完整路徑和名稱(chēng) Dim mytable As String '數(shù)據(jù)表名稱(chēng) Dim sql As String 'sql查詢(xún)語(yǔ)句 Dim rs As Object '臨時(shí)數(shù)據(jù)表紀(jì)錄 Dim i As Integer '循環(huán)數(shù)據(jù)變量（獲取數(shù)據(jù)表字段） '1、連接數(shù)據(jù)庫(kù) Set cnn = CreateObject("ADODB.Connection") mydata = ThisWorkbook.Path & "\進(jìn)銷(xiāo)存數(shù)據(jù)庫(kù).accdb" Select Case Application.Version * 1 '設(shè)置連接字符串,根據(jù)版本創(chuàng)建連接 Case Is <= 11 strcnn = "Provider=Microsoft.Jet.Oledb.4.0;Jet OLEDB:Database Password='123456';Data Source=" & mydata Case Is >= 12 strcnn = "Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password='123456';Data Source=" & mydata End Select cnn.Open strcnn '打開(kāi)數(shù)據(jù)庫(kù)鏈接 '2、設(shè)置sql查詢(xún)語(yǔ)句 mytable = "采購(gòu)數(shù)據(jù)3" Set rs = CreateObject("ADODB.Recordset") sql = "select 采購(gòu)數(shù)據(jù)3.ID,采購(gòu)數(shù)據(jù)3.采購(gòu)日期,采購(gòu)數(shù)據(jù)3.供貨類(lèi)型,采購(gòu)數(shù)據(jù)3.采購(gòu)分類(lèi),采購(gòu)數(shù)據(jù)3.供應(yīng)商,采購(gòu)數(shù)據(jù)3.名稱(chēng),采購(gòu)數(shù)據(jù)3.單位,采購(gòu)數(shù)據(jù)3.單價(jià),采購(gòu)數(shù)據(jù)3.數(shù)量,采購(gòu)數(shù)據(jù)3.金額,采購(gòu)數(shù)據(jù)3.入賬日期" & " from " & mytable & " order by 采購(gòu)日期" Set rs = cnn.Execute(sql) '執(zhí)行查詢(xún)，并將結(jié)果輸出到記錄集對(duì)象 '3、復(fù)制數(shù)據(jù)庫(kù)數(shù)據(jù) With ActiveSheet .Cells.ClearContents For i = 0 To rs.Fields.Count - 1 '填寫(xiě)標(biāo)題 .Cells(1, i + 1) = rs.Fields(i).Name Next i .Range("A2").CopyFromRecordset rs '.Cells.EntireColumn.AutoFit '自動(dòng)調(diào)整列寬 '.Cells.EntireColumn.AutoFit '自動(dòng)調(diào)整列寬 End With rs.Close cnn.Close Set rs = Nothing Set cnn = Nothing End Sub |
|
|
來(lái)自： 昵稱(chēng)2548375 > 《access》
