SQL Query to Microsoft Access

Question

4.00/5 (1 vote)

See more:

I have the following code:

SQL

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];

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.

Posted 18-Oct-14 19:49pm

Frans.Hendrik

Add a Solution

Comments

Mehdi Gholam 19-Oct-14 2:24am

Try removing the "dbo." prefix.

Frans.Hendrik 19-Oct-14 2:48am

Still gives me the same error.

Maciej Los 19-Oct-14 5:19am

What version of MS Access database?
What OS version where SQL server is placed (Win32 or Win64)?
What '' does before IN statement?
Is MS SQL server defined a system or user ODBC datasource?

Frans.Hendrik 19-Oct-14 6:15am

Well access 2013. Both 64bit.
I believe that this isn't the problem due to the query running fine without any joins, as well as the '' before the IN statement. The code is run in vb.net, the '' is just placeholder within vb.net.

The SQL server run on the same machine. Hence its working fine with just normal query to get data. My problem is with the joins. It uses the OBDC driver for SQL Server. That is fine since a simple query runs normal

Kornfeld Eliyahu Peter 19-Oct-14 5:47am

Why all those parentheses before FROM and after every join - you need no nested joins here (and in any case the syntax is wrong). Remove them!

Frans.Hendrik 19-Oct-14 5:54am

Ive tried both. Query works fine without the joins or just selecting one table. Its whenever I add the joins.
Nested joins are required in ms access queries

Maciej Los 19-Oct-14 6:10am

Please, answer my questions. Use 'Reply' widget.

Kornfeld Eliyahu Peter 19-Oct-14 6:21am

I didn't told - remove the join statements! I told remove the parentheses around them!!! A huge difference!

Maciej Los 19-Oct-14 6:28am

Kornfeld, He did what you said. Please, read His comment again. He's trying to tell us, that simple SELECT query works, but when he's trying to get data from another table via using joins, it causes error.

Kornfeld Eliyahu Peter 19-Oct-14 6:54am

I see...But still the nested joins he wrote has the wrong parentheses - posted him a link with the correct format...

Kornfeld Eliyahu Peter 19-Oct-14 6:53am

Try one of the following:
1. Use SELECT ... FROM TABLE1, TABLE2, TABLE3 fromat
2. Write the nested joins correctly (http://office.microsoft.com/en-001/access-help/inner-join-operation-HA001231487.aspx)

Frans.Hendrik 20-Oct-14 14:36pm

INSERT INTO [tableFinalData] (WorkActivity, SubProj, ClientCode, Client, ProjectYear, StaffCode, ProjectCode, Hours, Rate, Amount, BillingStatus,ClientGroup,ClientGroupCode) SELECT 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,c.EntityId, g.ShortName AS GroupName
FROM dbo.Entities AS g INNER JOIN dbo.EntityConnections AS c ON g.EntityId = c.GroupId INNER JOIN dbo.vw_TransWIPGeneric ON c.EntityId = 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];

Please I cant get it to work using the guide

Maciej Los 20-Oct-14 14:46pm

Please, see my first comment and answer the questions.

Frans.Hendrik 20-Oct-14 15:34pm

I have answered your questions already. Can you please assist with the inner join in access' formatting.
It is definitely my problem, however trying varies methods im unable to get it right, im just not understanding it correctly

Maciej Los 20-Oct-14 15:36pm

No, you don't. Please, see the second comment from top.

Frans.Hendrik 20-Oct-14 15:41pm

What else would you like to know?

Maciej Los 20-Oct-14 16:08pm

All what i ask at the first time:
What version of MS Access database?
What OS version where SQL server is placed (Win32 or Win64)?
Is MS SQL server defined a system or user ODBC datasource?

Frans.Hendrik 22-Oct-14 12:14pm

Hi there,
Latest version of access. 2013
Both are win64bit!
Im not sure about the 3rd one, ive setup the sql server using sql server manager

PhilLenoir 20-Oct-14 15:38pm

Frans, what's not clear to me is why are you using Access to work with SQL Server in this way. My normal approach would be to use linked tables from Access to SQL Server and work with the tables as if they were local. You will find that for most purposes, the queries will run faster and the syntax much simpler.

Alternately, you could use ADO within VB code to shift all query processing to the backend (possibly using stored procedures). Using a hybrid of Access and SQL Server query languages makes for hard work!

Frans.Hendrik 20-Oct-14 15:40pm

The database configuration can change, to update some stored procedure within your access file is then much harder to do! Thats why ive ruled out hard coded procedures within my scenario especially if that includes configuration settings.

PhilLenoir 20-Oct-14 15:48pm

A stored procedure would be on the back-end, not in Access.

Linked tables are easily re-linked in code if that's the issue.

If things are really that dynamic, how are you building and running the query? It has to be as easy to use ADO as what you are trying to do. In short, which query parser do you want to do the heavy lifting. If you want Jet (Access) then use linked tables, relinking if necessary; if SS then use ADO, you can then use dynamic configuration to adjust your connection or query.

Frans.Hendrik 20-Oct-14 15:51pm

Im using ole to do to execute the command, but this defeats the purpose of my question. I merely want to know how to configure the joins so that it works on access. Believe me, I have considered the alternatives and this is the best way to do it, considering all the factors.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-10-22T07:33:00

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:

SQL

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];

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[^]

SQL

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;

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.