使用VBA更新OLE DB连接命令文本 [英] Using VBA to update OLE DB Connection Command Text

问题描述

嘿，全部！

我一直在"玩"现在已经使用Excel VBA几年了，并且已经变得非常舒服了。 我现在正在学习的是通过数据模型进行SQL查询。 对于我的几个查询，我需要将数据限制为用户指定的
日期范围（由于交易量）。 为了让其他人可以使用工作簿，我正在寻找一种从VBA编辑CommandText的方法。

I've been "playing" around with Excel VBA for a few years now, and have become quite comfortable with it.  What I am learning now is SQL queries through the Data Model.  For several of my queries, I need to limit the data to a user specified date range (due to the volume of transactions).  In order to make the workbooks available to others to use, I am looking for a way to edit the CommandText from VBA.

这是一个我的查询示例：

This is an example of my query:

SELECT OrderNbr，Origin，Destination，ScheduledDate FROM vwOrders WHERE ScheduleDate> = DateAdd（D，-15，GetDate（））

SELECT OrderNbr, Origin, Destination, ScheduledDate FROM vwOrders WHERE ScheduleDate >= DateAdd(D, -15, GetDate())

我已经尝试在VBA中使用.CommandText更新该行失败。 如果我要求它显示在MsgBox中，我可以查看它，但我无法编辑它。 我得到运行时错误1004.（ActiveWorkbook.Connections（" DataConnection"）。ODBCConnection.CommandText
=" SELECT OrderNbr，Origin，Destination，ScheduledDate FROM vwOrders WHERE ScheduleDate> = DateAdd（D， - 15，GetDate（））" - 注意：一旦我弄清楚其余部分，我将用-15的变量替换字符串！）

I've tried in VBA to update that line using the .CommandText unsuccessfully.  I am able to view it if I ask it to display in a MsgBox, but I am unable to edit it.  I get the Run-Time error 1004. (ActiveWorkbook.Connections("DataConnection").ODBCConnection.CommandText = "SELECT OrderNbr, Origin, Destination, ScheduledDate FROM vwOrders WHERE ScheduleDate >= DateAdd(D, -15, GetDate())" - NOTE: I'll replace the string with a variable for the -15 once I get the rest figured out!)

我不知道需要多少信息来帮助解决这个问题，请告诉我。 任何建议都会非常感激，我的目标是将我的头撞在墙上！提前谢谢大家!!

I don't know how much more information is needed to help out with this, just let me know.  Any suggestions would be GREATLY appreciated, I'm to the point of banging my head against the wall! Thank you all in advance!!

推荐答案

试试。

ActiveWorkbook.Connections（" DataConnection"）。ODBCConnection.CommandText =" SELECT OrderNbr，Origin，Destination，ScheduledDate FROM vwOrders WHERE ScheduleDate> =" &安培; DateAdd（D，-15，GetDate（））

ActiveWorkbook.Connections("DataConnection").ODBCConnection.CommandText = "SELECT OrderNbr, Origin, Destination, ScheduledDate FROM vwOrders WHERE ScheduleDate >= " & DateAdd(D, -15, GetDate())

这篇关于使用VBA更新OLE DB连接命令文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！