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