|
直接用SELECT语句从数据库中查找不就行了,给你一个连接的示例
- 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 & "; " '数据库链接
- "Data Source=OLEDB;Provider=sqloledb;Server=服务器名;Database=数据库名;Uid=用户名;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
- cn.CommandTimeout = 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
复制代码
|
|