使用VBA或PowerShell将所有MS Access SQL查询导出到文本文件

Sub ExportSQLQueriesToTextFile()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strFilePath As String
    Dim fso As Object
    Dim file As Object

    ' 设置数据库路径和文件名
    strFilePath = "C:\Path\To\Text\File.txt"

    ' 设置SQL查询语句
    strSQL = "SELECT * FROM TableName"

    ' 连接到数据库
    Set db = CurrentDb

    ' 执行查询
    Set rs = db.OpenRecordset(strSQL)

    ' 创建文件对象
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set file = fso.CreateTextFile(strFilePath, True)

    ' 将查询结果逐行写入文本文件
    Do Until rs.EOF
        file.WriteLine rs.Fields(0) ' 假设第一列是要导出的数据
        rs.MoveNext
    Loop

    ' 关闭文件和数据库连接
    file.Close
    rs.Close
    db.Close

    ' 释放对象
    Set file = Nothing
    Set fso = Nothing
    Set rs = Nothing
    Set db = Nothing

    MsgBox "SQL queries exported to text file successfully!"
End Sub

$accessDBPath = "C:\Path\To\Database.accdb"
$exportFilePath = "C:\Path\To\Text\File.txt"
$sqlQuery = "SELECT * FROM TableName"

# 创建连接字符串
$connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$accessDBPath"

# 建立连接
$connection = New-Object System.Data.OleDb.OleDbConnection($connectionString)
$connection.Open()

# 创建命令对象
$command = New-Object System.Data.OleDb.OleDbCommand($sqlQuery, $connection)

# 执行查询并保存结果
$result = $command.ExecuteReader()

# 创建文件并将查询结果导出
$result | Export-Csv -NoTypeInformation -Delimiter "`t" -Path $exportFilePath

# 关闭连接
$connection.Close()

Write-Host "SQL queries exported to text file successfully!"