使用VBA连接SQL数据库获取数据

Private Sub CommandButton1_Click()
Dim i%, strCn$, strSQL$, serIP$, uid$, pwd$, dbName$, mydate, sht As Worksheet 'i为整数变量。
Dim cn As Object '定义数据链接对象 ，保存连接数据库信息
Dim rs As Object '定义记录集对象，保存数据表
Dim stime As Date, etime As Date
stime = Timer
serIP = "192.168.2.1\store"
uid = "sa"
pwd = "sa"
dbName = "beyond_store"

Set cn = CreateObject("ADODB.Connection") '创建数据链接对象
Set rs = CreateObject("ADODB.RecordSet") '创建记录集对象
strCn = "Provider=sqloledb;Server=" & serIP & ";Database=" & dbName & ";Uid=" & uid & ";Pwd=" & pwd & "; " '数据库链接
mydate = Date

'下面的语句将读取数据表数据，并将它保存到excel工作表中
'定义SQL查询命令字符串
strSQL = "select c_barcode,c_pluno,c_adno,c_gcode,c_provider,c_name,c_basic_unit,c_model,c_pt_cost,c_price,c_price_mem,c_price_disc,c_comment from tb_gds where (c_gcode > '1000000001' and c_gcode < '79999999999') ORDER BY c_barcode"
cn.Open strCn '与数据库建立连接，如果成功，返回连接对象cn
mandTimeout = 720
rs.Open strSQL, cn '执行strSQL所含的SQL命令，结果保存在rs记录集对象中

Set sht = ThisWorkbook.Sheets("商品资料")
sht.[a2:i50000].ClearContents
sht.[a2:i50000].NumberFormatLocal = "@"
sht.[a2].CopyFromRecordset cn.Execute(strSQL)

rs.Close '关闭记录集
cn.Close '关闭数据库链接,释放资源
Set rs = Nothing '清空对象
Set cn = Nothing '清空对象
etime = Timer
MsgBox "费时" & Format(etime - stime, "0.00") & "秒,更新完毕！"
End Sub