使用 Excel 从 Linux 电脑上的 SQL Server 检索数据-解网

问：

我有以下工作代码，我在 Excel 中使用它从 MS Access 数据库获取数据：

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=\\server\myweb\Furnace.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

现在，我想将其转换为直接从 Linux 计算机上的 SQL Server 数据库检索数据。我已经在网上搜索了，但没有找到任何可以给我提供如何继续的线索。

我已经定义了 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:\MyDocs\My Data Sources\Furnace 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:\MyDocs\My Data Sources\Furnace Temps.odc"
   .Refresh BackgroundQuery:=False
End With
Columns("B:B").NumberFormat = "m/d/yy h:mm;@"

我想使用 ADODB 对象而不是 WorkBook 对象，但我还没有弄清楚如何实现它们。在 Access 数据库的示例中，定义连接和记录集对象非常简单。

谁能帮助我朝着正确的方向前进，从 Linux SQL Server 中提取数据?....RDK系列

sql-server excel vba

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

使用 Excel 从 Linux 电脑上的 SQL Server 检索数据

Using Excel to retrieve data from SQL Server on Linux PC

评论

评论