SQL查询到Microsoft Access [英] SQL Query to Microsoft Access

问题描述

我有以下代码：

I have the following code:

INSERT INTO [tableFinalData] (ClientGroupCode, WorkActivity, SubProj, ClientCode, Client
                           , ProjectYear, StaffCode, ProjectCode, Hours, Rate, Amount, BillingStatus)
SELECT g.EntityNo AS [GroupName]
     , dbo.vw_TransWIPGeneric.WorkCodeNo
     , dbo.vw_TransWIPGeneric.WorkCodeName
     , dbo.vw_TransWIPGeneric.EntityNo
     , dbo.vw_TransWIPGeneric.EntityName
     , dbo.vw_TransWIPGeneric.ProjectTypeYearNo
     , dbo.vw_TransWIPGeneric.EmployeeNo
     , dbo.vw_TransWIPGeneric.SubprojectTypeNo
     , dbo.vw_TransWIPGeneric.Quantity
     , dbo.vw_TransWIPGeneric.Rate
     , dbo.vw_TransWIPGeneric.Amount
     , dbo.vw_TransWIPGeneric.BillingStatus
FROM ((dbo.vw_TransWIPGeneric
INNER JOIN dbo.EntityConnections AS [c] ON c.EntityId = dbo.vw_TransWIPGeneric.EntityId)
INNER JOIN dbo.Entities AS [g] ON c.GroupId = dbo.vw_TransWIPGeneric.EntityId)
''IN [ODBC;Driver={SQL Server};Server=FRANSWINDOWS10\SQLEXPRESS;Database=D:\USERS\FRANS\DOCUMENTS\WORK\SQL DATABASE\CATC14.MDF;Trusted_Connection=yes];

在我的SQL服务器上运行得很好，但在访问中没有去。

错误：[Microsoft] [ODBC Microsoft Access驱动程序] JOIN操作中的语法错误。错误代码：-3510



我甚至尝试添加额外的（（对于连接，同样的错误。



请提供任何帮助。

Runs great in my SQL server, but in access no go.
ERROR: [Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation. Error Code: -3510

Ive even tried adding the extra (( for joins, same error.

Please any help would be appreciated.

推荐答案

最后，在所有这些评论之后，我建议使用简单的 SELECT 语句而不是插入数据。



如下所述：内部加入操作（Microsoft Access SQL） [ ^ ]，正确的sql查询应如下所示：

Finally, after all these comments, i'd suggest to use simple SELECT statement rather than inserting data.

As is described here: INNER JOIN Operation (Microsoft Access SQL)[^], the proper sql query should look like:

FROM dbo.vw_TransWIPGeneric AS [t]
INNER JOIN (dbo.EntityConnections AS [c]) INNER JOIN dbo.Entities AS [g] ON c.EntityId = [t].EntityId)
ON c.GroupId = [t].EntityId
''IN [ODBC;Driver={SQL Server};Server=FRANSWINDOWS10\SQLEXPRESS;Database=D:\USERS\FRANS\DOCUMENTS\WORK\SQL DATABASE\CATC14.MDF;Trusted_Connection=yes];

但是（！）我需要警告你： [g] 和 [c] 。



按方式：MS Access数据库引擎喜欢几个括号（见注释）。



这是另一个建议：内部联合操作 [ ^ ]

BUT(!) i need to warn you: there is no relationship between [g] and [c].

By The Way: MS Access database engine likes several parentheses (see comments).

Here is another suggestion: INNER JOIN Operation[^]

SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3compoprtablex.fieldx)]
ON table2.field2compoprtable3.field3)
ON table1.field1compoprtable2.field2;

我不确定哪一个好。我还没有使用MS Access数据库。



让我知道它对你有用。

I'm not sure which one is good. I'm not using MS Access database yet.

Let me know if it was helpful to you.

这篇关于SQL查询到Microsoft Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！