我希望你能幫助我或指出我正確的方向。多年來，我一直使用一種非常干凈和簡單的方法將資料（在 VB6 中，是的！）從 MS-SQL 表匯入到 MS-Access 資料庫/表中，如下所示：-
沒有內部連接的作業查詢
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=;"
mySQL = "SELECT * INTO [Xray] FROM [ODBC;Driver=SQL Server; SERVER=myServer;DATABASE=myDatabase;UID=myUID;PWD=myPassword;].[ShipTo] WHERE [ShipTo].Company='ABC001'"
myConnection.Execute mySQL
Set myConnection = Nothing
但是，這是第一次，我現在需要開始使用內部聯接。我以為我可以做一些像上面一樣簡單的事情，但我已經解脫相當嚴重，不知道如何進步。我的內部連接查詢如下：-
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**
由于我明顯缺乏使用連接和經過測驗的方法的經驗，我收到一個錯誤，這可能是因為我的語法，但我就是看不到光明。以下是新的連接字串/查詢嘗試：-
內部連接查詢失敗
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=;"
mySQL = ""
mySQL = mySQL & "SELECT "
mySQL = mySQL & "dbo.InvcHead.InvoiceNum AS DocNum, "
mySQL = mySQL & "SUM(dbo.InvcTax.TaxAmt) As Doctaxamt "
mySQL = mySQL & "INTO [Xray] FROM [ODBC;Driver=SQL Server; SERVER= myServer;DATABASE= myDatabase;UID= myUID;PWD=myPassword;].[dbo.InvcTax] "
mySQL = mySQL & "Inner Join dbo.InvcHead ON (dbo.InvcTax.InvoiceNum = dbo.InvcHead.InvoiceNum)"
mySQL = mySQL & "Group By dbo.InvcHead.Invoicenum"
myConnection.Execute mySQL
Set myConnection = Nothing
我收到的錯誤訊息是“加入操作中的語法錯誤”
是否可以通過內部連接查詢使用此方法進行提取？我懷疑我的問題與我參考連接的第一個表的腳本區域有關。
.[dbo.InvcTax]"
如果這是不可能的，有沒有辦法動態地創建一個表（就像我所做的那樣）而不是從記錄集？
任何朝著正確方向引導的幫助將不勝感激。
謝謝，杰克
uj5u.com熱心網友回復：
dbo 前綴在 Access SQL 中失敗，您錯過了第二個表的資料庫參考：
mySQL = ""
mySQL = mySQL & "SELECT "
mySQL = mySQL & "T1.InvoiceNum AS DocNum, "
mySQL = mySQL & "SUM(T1.TaxAmt) As Doctaxamt "
mySQL = mySQL & "INTO [Xray] FROM [ODBC;Driver=SQL Server;SERVER=myServer;DATABASE=myDatabase;UID=myUID;PWD=myPassword;].dbo.InvcTax AS T1 "
mySQL = mySQL & "INNER JOIN [ODBC;Driver=SQL Server;SERVER=myServer;DATABASE=myDatabase;UID=myUID;PWD=myPassword;].dbo.InvcHead AS T2 ON (T1.InvoiceNum = T2.InvoiceNum)"
mySQL = mySQL & "Group By T2.Invoicenum"
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374991.html
標籤：sql-server ms-access VB6 内部联接 废话