Question

我有“Excel VBA”代码,从一个管理系统接入数据库获取数据:

Dim Data_Len As Integer, I As Integer, LastRow As Integer, lastRowAcurite As Integer
Dim objConn As ADODB.Connection, objRS As ADODB.Recordset, strSQL As String
Set objConn = New ADODB.Connection
If Err.Number <> 0 Then MsgBox ("ADODB.Connection error")
objConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\servermywebFurnace.mdb;"
If Err.Number <> 0 Then MsgBox ("Connection string error")
Set objRS = New ADODB.Recordset
If Err.Number <> 0 Then MsgBox ("ADODB.Recordset error")
strSQL = "SELECT * FROM Furnace WHERE (Date_Reading > now()-1.) ORDER BY Date_Reading "
objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
If Err.Number <> 0 Then MsgBox ("Furnace open error")
Worksheets("temp2").Range("A1").CopyFromRecordset objRS
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

I want to convert it to retrieve data directly from a SQL Server database on a Linux computer.

我对Windows ODBC条目下了定义,如下文《工作守则》所核实。该法典似乎过于复杂:

Private Sub NewWorkbookWithODBCConnection()
Dim myWorkBook As Workbook
Dim myWorkbookConnection As WorkbookConnection
Dim myWorksheet As Worksheet
Dim myQuerytable As QueryTable

Set myWorkBook = Workbooks.Add
Set myWorkbookConnection = myWorkBook.Connections.Add2( _
  Name:="French Furnace", _
  Description:="Whatever", _
  ConnectionString:="ODBC;DSN=SQL2Pi;", _
  CommandText:="")
With myWorkbookConnection.ODBCConnection
    .BackgroundQuery = True
    .CommandType = xlCmdSql
    .Connection = "ODBC;DSN=SQL2Pi;"
    .RefreshOnFileOpen = False
    .SavePassword = False
    .SourceConnectionFile = "D:MyDocsMy Data SourcesFurnace Temps.odc"
    .SourceDataFile = ""
    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
    .AlwaysUseConnectionFile = False
End With
Set myWorksheet = myWorkBook.Worksheets.Add
Set myQuerytable = myWorksheet.ListObjects.Add( _
  SourceType:=0, _
  Source:="ODBC;DSN=SQL2Pi;", _
  Destination:=Range("$A$1")).QueryTable

With myQuerytable
    .CommandText = Array("SELECT * FROM `furnace`.`temps` limit 20")
          & "WHERE (Date_Time> > #08/09/22# ORDER BY Date_Time")
          & "WHERE (Date_Time>{ts  " & firstdate & " 00:00:00 })" & " ORDER BY Date_Time")
         .CommandText = Array("SELECT Date_Time, BattV, ArrayV, OutputA, InputA, ChargerSt, OutputW, VOCV FROM `solar`.`outback` " _
          & " Where Date_Time Between  " & firstdate & " 00:00:00  AND  " & seconddate & " 00:00:00  ORDER BY Date_Time")

    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .SourceConnectionFile = "D:MyDocsMy Data SourcesFurnace Temps.odc"
    .Refresh BackgroundQuery:=False
End With
Columns("B:B").NumberFormat = "m/d/yy h:mm;@"

我想使用非行物体,而不是工作手册物体,但我没有说明如何执行这些物品。在访问数据库中,界定连接和记录物体是直截了当的。

我怎么能从A/63/1Q服务器中提取数据?

Answer 1

Option Explicit

Sub DemoSQL()

    Const DSN = "SQL2Pi"
    Const SQL = " SELECT * FROM Furnace" & _
                " WHERE Date_Reading > DATEADD(day,-1,GETDATE())" & _
                " ORDER BY Date_Reading"
     Debug.Print SQL
    
    Dim conn As Object, rs As Object
    
    Set conn = CreateObject("ADODB.Connection")
    With conn
       .Open "DSN=" & DSN
       Set rs = .Execute(SQL)
    End With
    
     result
    Sheet1.Range("A1").CopyFromRecordset rs

End Sub

友情链接