vba連接資料庫創建表

1. excel中vba怎麼連接mysql資料庫並展示數據

可以使用以下代碼在EXCEL上使用VBA連接MYSQL資料庫 ：

DimstrconntAsString
strconnt=""
Setconnt=NewADODB.Connection
Dimsevip,Db,user,pwdAsString
'設伺服器地址、所連數據，及登錄用戶密碼
sevip="localhost"
Db=「test"
user="root"
pwd="123456"
strconnt="DRIVER={MySqlODBC5.3UnicodeDriver};SERVER="&sevip&";Database="&Db&";Uid="&user&";Pwd="&pwd&";Stmt=setnamesGBK"
connt.ConnectionString=strconnt
connt.Open

2. vbaExcel連接資料庫的好處

EXCEL vba 資料庫連接

聽語音

• 原創

• |

• 瀏覽：7393

• |

• 更新：2016-06-23 16:12

• |

• 標簽：EXCELVBA

• 

3. 濡備綍鐢╒BA鎶婅繖涓琛ㄦ牸 鏂板炲埌SQL SERVER鏁版嵁搴撻噷鐨勫憳宸ユ昏〃浜嗭紝鍙瑕佽繖9琛岀殑錛

澶х害鍚庨潰鍐嶆坊涓婁互涓嬭鍙ワ紝娉ㄦ剰WHERE寮濮嬫槸鏉′歡鍙浠ヤ笉瑕侊紝7鍒楀瓧孌靛悕鎸夋垜鏍煎紡涓涓涓濉涓婂幓
cn.Open strCn
strSQL = "Select 琛ㄥ悕.濮撳悕,琛ㄥ悕.鎬у埆 from 琛ㄥ悕 where 琛ㄥ悕.鏉′歡鍒楀悕=鏉′歡"
rs.Open strSQL, cn, 1, 3
Sheet1.Range("A1", "Z65000").Clear
Sheet1.Range("A1").CopyFromRecordset rs
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

4. ACCESS閲屾墦寮鏌愭暟鎹搴撳悗濡備綍鐢╒BA寮曠敤琛ㄣ佽板綍鍙婂瓧孌靛

涓烘わ紝璇鋒寜鐓т笅鍒楁ラゆ搷浣滐細 鍚鍔 Access 2003銆 鎵撳紑涓涓 Access 鏁版嵁搴撱 鎸 ALT+F11 鎵撳紑 Visual Basic 緙栬緫鍣ㄣ 鍦 Visual Basic 緙栬緫鍣ㄧ獥鍙ｄ腑錛屽崟鍑燴滃伐鍏封濊彍鍗曚笂鐨勨滃紩鐢ㄢ濄傚湪鈥滃紩鐢ㄢ濆硅瘽妗嗕腑錛屾偍鍙浠ョ湅鍒版墍閫夌殑寮曠敤銆傛敞鎰 褰撻夋嫨浜嗕竴涓寮曠敤鍚庯紝鎮ㄨ繕鍙浠ユ煡鐪嬫湁鍏充嬌鐢ㄨュ紩鐢ㄥ繀欏誨姞杞界殑鏂囦歡鐨勪俊鎮銆傜浉鍏蟲枃浠跺彲鑳芥槸綾誨瀷搴撱佸硅薄搴撴垨鎺т歡搴撱傛瘡涓寮曠敤鐨勭浉鍏蟲枃浠朵細鏍規嵁鈥滃紩鐢ㄢ濆硅瘽妗嗕腑鏄劇ず鐨勪俊鎮榪涜屽姞杞姐備絾鏄錛屽傛灉鎵句笉鍒扮浉搴旂殑鏂囦歡錛孉ccess 2003 灝嗗湪璁＄畻鏈轟笂鐨勫叾浠栦綅緗鎼滅儲瀹冦傚逛簬鎵閫夌殑姣忎釜 VBA 寮曠敤錛孉ccess 2003 閮戒細鎵ц屼互涓嬩換鍔★細 Access 楠岃瘉鏄鍚﹀凡鍔犺澆浜嗗紩鐢ㄧ殑鏂囦歡銆 濡傛灉褰撳墠鏈鍔犺澆寮曠敤鐨勬枃浠訛紝Access 灝嗛獙璇 RefLibPaths 娉ㄥ唽琛ㄩ」鏄鍚﹀瓨鍦ㄣ 濡傛灉RefLibPaths 娉ㄥ唽琛ㄩ」瀛樺湪錛孉ccess 灝嗘悳緔涓庡紩鐢ㄥ叿鏈夌浉鍚屽悕縐扮殑鍛藉悕鍊箋傚傛灉鏈夊尮閰嶇殑鍛藉悕鍊礆紝鍒 Access 灝嗕粠鍛藉悕鍊間腑鎻愬強鐨勮礬寰勫姞杞藉紩鐢ㄣ傛敞鎰 鎮ㄥ彲浠ュ皢 RefLibPaths 娉ㄥ唽琛ㄩ」鎵嬪姩娣誨姞鍒版敞鍐岃〃涓錛岀劧鍚庡彲浠ユ坊鍔 RefLibPaths 娉ㄥ唽琛ㄩ」涓嬩換浣曞栨帴紼嬪簭鎴栦換浣曞簱鐨勫悕縐板拰浣嶇疆銆備負姝わ紝璇鋒寜鐓т笅鍒楁ラゆ搷浣溿 鍗曞嚮鈥滃紑濮嬧濓紝鐒跺悗鍗曞嚮鈥滆繍琛屸濄傚湪鈥滄墦寮鈥濇嗕腑錛岄敭鍏 regedit錛岀劧鍚庡崟鍑燴滅『瀹氣濄傚湪鈥滄敞鍐岃〃緙栬緫鍣ㄢ濈獥鍙ｄ腑錛屾壘鍒頒互涓嬫敞鍐岃〃欏癸細HKEY_LOCAL_MACHINE\Software\Microsoft\Office\11.0\Access 鍙抽敭鍗曞嚮鈥淎ccess鈥濇敞鍐岃〃欏癸紝鎸囧悜鈥滄柊寤衡濓紝鐒跺悗鍗曞嚮鈥滈」鈥濄 灝嗘柊鍒涘緩鐨勯」鍛藉悕涓 RefLibPaths銆 鍗曞嚮鈥淩efLibPaths鈥濄 鍙抽敭鍗曞嚮鍙崇獥鏍間腑鐨勪換鎰忎綅緗錛岀劧鍚庡崟鍑燴滃瓧涓插尖濄 浣跨敤涓 VBA 寮曠敤鐩稿悓鐨勫悕縐板懡鍚嶆柊鍒涘緩鐨勫瓧絎︿覆鍊箋 鍙抽敭鍗曞嚮鎮ㄥ湪絎 8 姝ヤ腑鍒涘緩鐨勫瓧絎︿覆鍊礆紝鐒跺悗鍗曞嚮鈥滀慨鏀光濄傚湪鈥滅紪杈戝瓧絎︿覆鈥濆硅瘽妗嗕腑錛岄敭鍏ュ繀欏誨姞杞界殑鏂囦歡鐨勪綅緗浠ヤ笌 VBA 涓鐨勫紩鐢ㄥ瑰簲銆傛敞鍐岃〃鍊煎悕縐板繀欏繪槸鏂囦歡鍚嶅姞鎵╁睍鍚嶃備綅緗錛堟暟鍊兼暟鎹錛夊繀欏繪槸璺寰勫姞鏂囦歡鍚嶃備緥濡傦紝濡傛灉璁劇疆瀵 Northwind 紺轟緥鏁版嵁搴撶殑寮曠敤錛屽垯鍙浠ユ坊鍔犱互涓嬪礆細 鏁板煎悕縐幫細Northwind.mdb 鏁板兼暟鎹錛欳:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb 閲嶅嶇 7 姝ュ埌絎 10 姝ワ紝灝嗛傚綋鐨勫栨帴紼嬪簭鎴栧簱鐨勫悕縐板拰浣嶇疆浣滀負瀛楃︿覆鍊兼坊鍔犮 鍦ㄢ滄枃浠垛濊彍鍗曚笂錛屽崟鍑燴滈鍑衡濄傚傛灉 RefLibPaths 娉ㄥ唽琛ㄩ」涓嶅瓨鍦ㄦ垨鏈鍖呭惈姝ｇ『鐨勫紩鐢錛屽垯 Access 浣跨敤鈥淪earchPath鈥滱PI 鎼滅儲寮曠敤鐨勬枃浠躲傚皢鎵ц屼互涓嬫悳緔錛 鎼滅儲鑼冨洿璇存槑 搴旂敤紼嬪簭鐩褰 Msaccess.exe 鐨勪綅緗銆 褰撳墠鐩褰曞湪鈥滄枃浠垛濊彍鍗曚笂鍗曞嚮鈥滄墦寮鈥濇椂鎵鐪嬪埌鐨勭洰褰曘 緋葷粺鐩褰 Windows 鏂囦歡澶規垨 WINNT 鏂囦歡澶逛腑鐨 System 鏂囦歡澶瑰拰 System32 鏂囦歡澶廣 WinDir 鎿嶄綔緋葷粺鏂囦歡鍦ㄥ叾涓榪愯岀殑鏂囦歡澶廣傝繖閫氬父鏄 Windows 鏂囦歡澶規垨 WINNT 鏂囦歡澶廣 PATH 鐜澧冨彉閲忔ょ郴緇熷彉閲忓寘鍚緋葷粺鍙鐩存帴璁塊棶鐨勪竴緇勬枃浠跺す銆侻icrosoft Windows NT 4.0錛氬湪鎺у埗闈㈡澘涓錛屽弻鍑燴滅郴緇熲濓紝鐒跺悗鍗曞嚮鈥滅幆澧冣濋夐」鍗°侾ATH 鍙橀噺鏄劇ず鍦ㄢ滅郴緇熷彉閲忊濆垪琛ㄤ腑銆侻icrosoft Windows 2000 鎴 Microsoft Windows Server 2003錛氬湪鎺у埗闈㈡澘涓錛屽弻鍑燴滅郴緇熲濓紝鍗曞嚮鈥滈珮綰р濋夐」鍗★紝鐒跺悗鍗曞嚮鈥滅幆澧冨彉閲忊濄侾ATH 鏄劇ず鍦ㄢ滅郴緇熷彉閲忊濆垪琛ㄤ腑銆 鏂囦歡鐩褰曞寘鍚 .mdb 鏂囦歡銆.mde 鏂囦歡銆.adp 鏂囦歡鎴 .ade 鏂囦歡鐨勬枃浠跺す鍜屼換浣曞瓙鏂囦歡澶廣傛敞鎰 濡傛灉鎮ㄨ佸紩鐢ㄧ殑鏂囦歡浣嶄簬涓婅〃涓鎻愬埌鐨勪換浣曠洰褰曚腑錛屽垯 Access 涓嶉渶瑕 RefLibPaths 娉ㄥ唽琛ㄩ」銆傚傛灉Access 2003 鏃犳硶鎵懼埌鐩稿叧寮曠敤錛屽綋鎮ㄧ紪璇戦」鐩鎴栧皾璇曡繍琛屾煇涓榪囩▼鏃訛紝灝嗘敹鍒頒互涓嬮敊璇淇℃伅錛歒our Microsoft Office Access database or project contains a missing or broken reference to the file file name.* To ensure that your database or project works properly, you must fix this reference.
娉ㄦ剰 鍦ㄦ暟鎹搴撳凡鎵撳紑鐨勬儏鍐典笅錛屽逛簬姣忎釜鏃犳硶鎵懼埌鐨勫紩鐢錛屾偍閮戒細鏀跺埌涓嬈″墠闈㈡彁鍙婄殑閿欒淇℃伅銆備緥濡傦紝濡傛灉鎮ㄦ墦寮浜 MyDatabase.mdb 騫朵笖 MyDatabase.mdb 涓㈠け浜嗗 Microsoft Calendar 鎺т歡鍜 Microsoft DAO 搴撶殑寮曠敤錛屽垯鎮ㄤ細鏀跺埌涓ゆ潯閿欒淇℃伅錛屼涪澶辯殑姣忎釜寮曠敤瀵瑰簲涓鏉￠敊璇淇℃伅銆傚傛灉鎮ㄦ湭淇澶嶈繖浜涘紩鐢錛岄偅涔堝綋鎮ㄥ叧闂鏁版嵁搴撶劧鍚庡啀閲嶆柊鎵撳紑鏃訛紝鎮ㄥ皢鍐嶆℃敹鍒拌繖浜涢敊璇淇℃伅銆傚傛灉鎮ㄤ慨澶嶄簡榪欎簺寮曠敤騫朵繚瀛樹簡鏁版嵁搴擄紝鍒欎笅嬈℃墦寮璇ユ暟鎹搴撴椂灝變笉浼氭敹鍒拌繖浜涢敊璇淇℃伅浜嗐侭rokenReference 灞炴Access 鐨 Application 瀵硅薄鍏鋒湁涓涓 BrokenReference 灞炴э紝璇ュ睘鎬у彲浠ュ憡璇夋偍鏄鍚︽湁浠諱綍寮曠敤鍑虹幇浜嗛敊璇銆傝嫢瑕佹鏌 BrokenReference 灞炴э紝璇鋒寜鐓т笅鍒楁ラゆ搷浣滐細 鍚鍔 Access 2003銆 鎵撳紑涓涓 Access 鏁版嵁搴撱 鎸 ALT+F11 鎵撳紑 Visual Basic 緙栬緫鍣ㄣ 鎸 CTRL+G 緇勫悎閿鎵撳紑鈥滅珛鍗斥濈獥鍙ｃ 鍦ㄢ滅珛鍗斥濈獥鍙ｄ腑錛岄敭鍏ヤ互涓嬪懡浠わ紝鐒跺悗鎸 Enter 閿錛?Application.BrokenReference璇鋒敞鎰忥紝濡傛灉瀛樺湪涓㈠け鐨勫紩鐢錛孉pplication 瀵硅薄鐨 BrokenReference 灞炴у皢榪斿洖鈥淭rue鈥濄

5. 如何用vba代碼將access資料庫連接求代碼

下面這段程序是連接資料庫，並將excel表格內容寫入資料庫。代碼如下：
Sub FYMXDL()
Dim XQID As Integer
Dim JZID As Integer
Dim FYID As Integer
Dim FBXZ As String '分包性質
Dim DW As String
Dim SARR(1 To 31) As Double
Dim rst As New ADODB.Recordset
mYpath = ThisWorkbook.Path & "\jzfydata.accdb"
Set cONn = CreateObject("ADODB.Connection")
cONn.ConnectionString = "Provider=Microsoft.Ace.OleDB.12.0;Data Source=" & mYpath
cONn.ConnectionString = cONn.ConnectionString & ";Jet OLEDB:Database "
cONn.Open
XQID = Cells(3, 2).Value
JZID = Cells(3, 5).Value
'清空改小區-建築的費用明細
Sql = "delete from fymxb where 小區ID=" & XQID & " AND 建築ID = " & JZID
cONn.Execute Sql
Const kshh = 7
hh = kshh
Do While Cells(hh, 3).Value > 0
FYID = Cells(hh, 3).Value
FBXZ = Cells(hh, 11).Text
For i = 1 To 31
SARR(i) = Round(Cells(hh, 13 + i - 1).Value, 2)
Next i
Sql = "INSERT INTO fymxb(小區ID,建築ID,費用ID,分包性質,工作量,單價合計_中標,人工費_中標, 主材費_中標, 輔材費_中標, 機械費_中標, 管理費_中標, 利潤_中標,規費_中標,稅金_中標,合價_中標,單價合計_標准成本,人工費_標准成本,主材費_標准成本,輔材費_標准成本,機械費_標准成本,管理費_標准成本,利潤_標准成本,規費_標准成本,稅金_標准成本,合價_標准成本,單價合計_實際成本,人工費_實際成本,主材費_實際成本,輔材費_實際成本,機械費_實際成本,管理費_實際成本,利潤_實際成本,規費_實際成本,稅金_實際成本,合價_實際成本) VALUES (" & XQID & ", " & JZID & ", " & FYID & ", '" & FBXZ & "'"
For i = 1 To 31
Sql = Sql & "," & SARR(i)
Next i
Sql = Sql & " )"

cONn.Execute Sql
hh = hh + 1
Loop
End Sub

6. EXCELvba寫入ACCESS資料庫表

第一步：連接數來據庫，下自面是參考代碼
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.Ace.OleDB.12.0;Data Source=" & mypath
conn.ConnectionString = conn.ConnectionString & ";Jet OLEDB:Database Password=1"
conn.Open
第二步：插入數據，參考代碼如下：

Sql = "insert into skmxb (內部管理號,收款日期,金額"
Sql = Sql & ") values ('" & nbglH & "', #" & Format(myD, "yyyy/mm/dd") & "#,"
Sql = Sql & jE
Sql = Sql & ")"
conn.Execute Sql