sql - Excel VBA ADODB - 如何直接从 SQL 数据库中根据 excel 列值填充数据,而无需将数据复制到 Excel 工作表中
问题描述
我正在运行以下代码,将数据附加到列 B 到 G。我的数据位于 Snowflake 数据仓库中。数据以百万计,所以我不能先将其复制到 Excel 表中。
Option Explicit
Sub GetData()
Dim rs as ADODB.Recordset
Dim cn as ADODB.Connection
Dim connstr as String
Dim sqlstr as String
Set cn = New ADODB.Connection
cn.ConnectionString = DSNdetails.connstr
'DSNdetails is a module that holds DSN, DBCName, Databasename,Uid and password
cn.Open connstr
cn.Execute "USE WAREHOUSE db_warehouse;"
sqlstr = "SELECT employee_num, name, address, email, phone_num FROM Company;"
Set rs = New ADODB.Recordset
rs.Open sqlstr, cn, adStateOpen, adLockReadOnly
Sheet1.Cells(2,1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
这段代码运行完美。但是,当我添加 WHERE 子句和 IN 运算符时,问题就开始了。
A | B | C | D |
Employee_num | name | address | phone
123
234
456
and so on
所以现在我的新查询如下所示,Table1 是 A 列(我也将其视为字符串,但同样的错误)如果 SQL/Snowflake 数据库中存在 A 列中的那个employee_num
sqlstr = "SELECT employee_num, name, address, phone_num FROM Company WHERE employee_num IN ('"& _
Join(Application.Transpose(Range("Table1[Employee_num]").Value),'",")&");"
调试时的代码显示点
rs.Open sqlstr, cn, adStateOpen, adLockReadOnly
编辑:现在我有一个新的运行时错误“ODBC 驱动程序不支持请求的属性”
解决方案
推荐阅读
- python - Pytorch LSTM predicts too low values
- typescript - Readable way for iterating over a JSON and creating rows
- c - A flexible list with indefinite number of nodes
- hibernate - How i can get lazy child in gsp with multi-tenant?
- c++ - IntelliSense in Visual Studio doesn't work - only for a specific project
- process - Is there any connection between the segments created in memory by a microprocessor and the memory structure of a process in an operating system?
- google-apps-script - Add one year to existing Google sheet strings
- axios - Axios's response, what's in it??.....const response = await axios
- testing - wiremock to mock public backend response
- mysql - MySQL:查找总价既不是最高也不是最低的产品类别?