如何将MS Access引用的文件共享附件迁移至SQL Server Filestream

前提说明 #

假设你已经完成SQL Server FILESTREAM的基础配置，且目标表结构如下（可根据实际调整）：

CREATE TABLE SQL_Attachments (
    PK INT PRIMARY KEY IDENTITY(1,1),
    RecordID INT NOT NULL, -- 关联MainTable.RecordID，保证完整性
    Description VARCHAR(255),
    FileName VARCHAR(255),
    FileData VARBINARY(MAX) FILESTREAM,
    RowGuid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID()
)

同时已将Access的Attachments表数据导入到SQL Server临时表（如#Temp_Access_Attachments），或可直接在脚本中关联Access数据源。

方法1：T-SQL游标批量迁移（基于OPENROWSET） #

通过游标遍历所有附件记录，动态拼接文件路径，利用OPENROWSET(BULK...)读取文件并插入FILESTREAM表：

-- 1. 导入Access的Attachments表到临时表（示例用OLEDB连接Access）
SELECT PK, RecordID, Description, filename
INTO #Temp_Access_Attachments
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
                'Data Source=C:\YourAccessDB.accdb;Persist Security Info=False;', 
                'SELECT * FROM Attachments');

-- 2. 定义文件共享根路径
DECLARE @RootPath NVARCHAR(255) = '\\YourFileServer\AttachmentShare\';
DECLARE @FullFilePath NVARCHAR(500);
DECLARE @RecordID INT, @Desc VARCHAR(255), @FileName VARCHAR(255), @PK INT;

-- 3. 声明游标遍历临时表
DECLARE AttachCursor CURSOR FOR
SELECT PK, RecordID, Description, filename FROM #Temp_Access_Attachments;

OPEN AttachCursor;
FETCH NEXT FROM AttachCursor INTO @PK, @RecordID, @Desc, @FileName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 拼接完整文件路径：根路径 + RecordID子文件夹 + 文件名
    SET @FullFilePath = @RootPath + CAST(@RecordID AS NVARCHAR(10)) + '\' + @FileName;

    -- 插入到FILESTREAM表
    INSERT INTO SQL_Attachments (RecordID, Description, FileName, FileData)
    SELECT 
        @RecordID,
        @Desc,
        @FileName,
        BulkColumn
    FROM OPENROWSET(BULK @FullFilePath, SINGLE_BLOB) AS FileData;

    FETCH NEXT FROM AttachCursor INTO @PK, @RecordID, @Desc, @FileName;
END

CLOSE AttachCursor;
DEALLOCATE AttachCursor;

DROP TABLE #Temp_Access_Attachments;

注意事项：

• SQL Server服务账户需拥有文件共享的读取权限
• 提前校验所有文件路径的正确性，可新增判断逻辑跳过缺失文件
• 若文件路径包含特殊字符，需做转义处理

方法2：PowerShell批量迁移（灵活高效） #

PowerShell适合处理大量文件，无需在SQL Server端部署Access驱动：

# 配置参数
$accessDBPath = "C:\YourAccessDB.accdb"
$sqlServerInstance = "YourSQLServer\Instance"
$sqlDatabase = "YourDatabase"
$fileShareRoot = "\\YourFileServer\AttachmentShare\"

# 读取Access的Attachments表数据
$accessQuery = "SELECT PK, RecordID, Description, filename FROM Attachments"
$connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$accessDBPath;"
$conn = New-Object System.Data.OleDb.OleDbConnection($connectionString)
$conn.Open()
$cmd = New-Object System.Data.OleDb.OleDbCommand($accessQuery, $conn)
$reader = $cmd.ExecuteReader()

# 连接SQL Server
$sqlConnString = "Server=$sqlServerInstance;Database=$sqlDatabase;Integrated Security=True;"
$sqlConn = New-Object System.Data.SqlClient.SqlConnection($sqlConnString)
$sqlConn.Open()
$sqlCmd = $sqlConn.CreateCommand()

# 遍历每条记录并插入
while ($reader.Read()) {
    $recordID = $reader["RecordID"]
    $desc = $reader["Description"]
    $fileName = $reader["filename"]
    $fullPath = Join-Path $fileShareRoot (Join-Path $recordID $fileName)

    # 读取文件内容
    if (Test-Path $fullPath) {
        $fileBytes = [System.IO.File]::ReadAllBytes($fullPath)
        
        # 构造SQL插入语句
        $sqlCmd.CommandText = @"
INSERT INTO SQL_Attachments (RecordID, Description, FileName, FileData)
VALUES (@RecordID, @Desc, @FileName, @FileData)
"@
        $sqlCmd.Parameters.Clear()
        $sqlCmd.Parameters.AddWithValue("@RecordID", $recordID)
        $sqlCmd.Parameters.AddWithValue("@Desc", $desc)
        $sqlCmd.Parameters.AddWithValue("@FileName", $fileName)
        $sqlCmd.Parameters.AddWithValue("@FileData", $fileBytes)
        
        $sqlCmd.ExecuteNonQuery()
        Write-Host "迁移成功: $fullPath"
    }
    else {
        Write-Warning "文件不存在，跳过: $fullPath"
    }
}

# 关闭连接
$reader.Close()
$conn.Close()
$sqlConn.Close()

注意事项：

• 运行PowerShell的机器需安装Microsoft Access Database Engine（支持读取Access文件）
• 执行PowerShell的账户需同时拥有文件共享读取权限和SQL Server写入权限

方法3：Access VBA迁移（适合熟悉VBA的场景） #

直接在Access中编写VBA代码，连接SQL Server并批量迁移：

Sub MigrateAttachmentsToFILESTREAM()
    Dim connSQL As ADODB.Connection
    Dim rsAccess As DAO.Recordset
    Dim strSQLConn As String
    Dim strAccessSQL As String
    Dim strFullPath As String
    Dim fileBytes() As Byte
    Dim fs As Object
    
    ' SQL Server连接字符串（集成验证，或替换为SQL账户）
    strSQLConn = "Provider=SQLOLEDB;Server=YourSQLServer\Instance;Database=YourDatabase;Integrated Security=SSPI;"
    
    ' 打开Access的Attachments表
    strAccessSQL = "SELECT * FROM Attachments"
    Set rsAccess = CurrentDb.OpenRecordset(strAccessSQL)
    
    ' 连接SQL Server
    Set connSQL = New ADODB.Connection
    connSQL.Open strSQLConn
    
    ' 文件系统对象
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    ' 遍历每条记录
    Do While Not rsAccess.EOF
        strFullPath = "\\YourFileServer\AttachmentShare\" & rsAccess!RecordID & "\" & rsAccess!filename
        
        If fs.FileExists(strFullPath) Then
            ' 读取文件字节
            Open strFullPath For Binary As #1
            ReDim fileBytes(LOF(1) - 1)
            Get #1, , fileBytes
            Close #1
            
            ' 插入到SQL Server FILESTREAM表（转义单引号避免SQL注入）
            connSQL.Execute "INSERT INTO SQL_Attachments (RecordID, Description, FileName, FileData) " & _
                            "VALUES (" & rsAccess!RecordID & ", '" & Replace(rsAccess!Description, "'", "''") & _
                            ", '" & Replace(rsAccess!filename, "'", "''") & ", ?)", fileBytes
                            
            Debug.Print "迁移完成: " & strFullPath
        Else
            Debug.Print "文件缺失，跳过: " & strFullPath
        End If
        
        rsAccess.MoveNext
    Loop
    
    ' 清理资源
    rsAccess.Close
    connSQL.Close
    Set rsAccess = Nothing
    Set connSQL = Nothing
    Set fs = Nothing
End Sub

注意事项：

• Access需引用Microsoft ActiveX Data Objects（版本2.8以上）
• 处理含单引号的Description或FileName时，需用Replace转义
• 确保Access运行账户拥有文件共享读取权限和SQL Server写入权限

内容的提问来源于stack exchange，提问作者JuniperSquared