VB6通过ADO导入带Inner Join的MS-SQL数据到Access语法错误求助

问题原因 #

你当前写法的核心错误是：仅为dbo.InvcTax表指定了SQL Server外部数据源前缀，JOIN关联的dbo.InvcHead表未指定外部数据源，Access引擎会默认在本地mdb文件中查找该表，因此触发Syntax Error in Join Operation报错。

可行解决方案 #

方案1：保留原有SELECT INTO语法（推荐，效率最高） #

将关联、聚合逻辑全部放到SQL Server侧执行，Access仅接收最终结果写入本地表，无需适配Access侧JOIN语法，数据量大时性能优势明显，修改后的代码如下：

Dim myConnection As ADODB.connection
Dim mySQL As String

Set myConnection = New ADODB.connection
myConnection.Open "Driver={Microsoft Access Driver (*.mdb)};" & "Dbq=DB1.mdb;" & "DefaultDir=C:\Temp;" & "Uid=Admin;Pwd=;"

' 把SQL Server侧执行的整个查询放到ODBC数据源后的括号内
mySQL = "SELECT * INTO [Xray] FROM [ODBC;Driver=SQL Server; SERVER=myServer;DATABASE=myDatabase;UID=myUID;PWD=myPassword;].(" & _
        "SELECT dbo.InvcHead.InvoiceNum AS DocNum, SUM(dbo.InvcTax.TaxAmt) As Doctaxamt " & _
        "FROM dbo.InvcTax INNER JOIN dbo.InvcHead ON dbo.InvcTax.InvoiceNum = dbo.InvcHead.InvoiceNum " & _
        "GROUP BY dbo.InvcHead.Invoicenum)"

myConnection.Execute mySQL
Set myConnection = Nothing

方案2：记录集导入方案 #

如果需要基于记录集动态建表，可先从SQL Server查询出结果集，再写入Access本地表，代码如下：

Dim sqlConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim accessConn As ADODB.Connection
Dim accessRs As ADODB.Recordset
Dim fld As ADODB.Field
Dim createSql As String

' 连接SQL Server查询结果集
Set sqlConn = New ADODB.Connection
sqlConn.Open "Driver=SQL Server; SERVER=myServer;DATABASE=myDatabase;UID=myUID;PWD=myPassword;"
Set rs = New ADODB.Recordset
rs.Open "SELECT dbo.InvcHead.InvoiceNum AS DocNum, SUM(dbo.InvcTax.TaxAmt) AS DocTaxAmt " & _
        "FROM dbo.InvcTax INNER JOIN dbo.InvcHead ON dbo.InvcTax.InvoiceNum = dbo.InvcHead.InvoiceNum " & _
        "GROUP BY dbo.InvcHead.InvoiceNum", sqlConn, adOpenStatic, adLockReadOnly

' 连接本地Access数据库
Set accessConn = New ADODB.Connection
accessConn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=DB1.mdb;DefaultDir=C:\Temp;Uid=Admin;Pwd=;"

' 动态创建本地表
createSql = "CREATE TABLE [Xray] ("
For Each fld In rs.Fields
    createSql = createSql & "[" & fld.Name & "] " & GetAccessFieldType(fld.Type) & ","
Next
createSql = Left(createSql, Len(createSql) - 1) & ")"
accessConn.Execute createSql

' 批量写入数据
Set accessRs = New ADODB.Recordset
accessRs.Open "SELECT * FROM [Xray]", accessConn, adOpenKeyset, adLockOptimistic
accessRs.CopyFromRecordset rs

' 资源释放
rs.Close: Set rs = Nothing
sqlConn.Close: Set sqlConn = Nothing
accessRs.Close: Set accessRs = Nothing
accessConn.Close: Set accessConn = Nothing

需要额外添加字段类型映射辅助函数：

Function GetAccessFieldType(adoType As ADODB.DataTypeEnum) As String
    Select Case adoType
        Case adInteger: GetAccessFieldType = "LONG"
        Case adVarChar, adChar: GetAccessFieldType = "TEXT(255)"
        Case adNumeric, adDecimal, adDouble: GetAccessFieldType = "DOUBLE"
        Case adCurrency: GetAccessFieldType = "CURRENCY"
        Case adDate: GetAccessFieldType = "DATETIME"
        Case adBoolean: GetAccessFieldType = "YESNO"
        Case Else: GetAccessFieldType = "MEMO"
    End Select
End Function

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