Re: SQL syntax over a network

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 09/13/04 

Date: Sun, 12 Sep 2004 22:33:09 -0400

"Wez.k" <Wezk@discussions.microsoft.com> wrote in message
news:D62845E0-BD21-4C35-8BD5-C33E2C65548C@microsoft.com
> Thanks for your reply Dirk, I am most grateful. If by paraphrased
> SQL you mean copied from the SQL view of a Query, that is correct. I
> have followed your instructions and the error message was
>
> Runtime error 3131
> Syntax error in FROM clause
>
> The contents of the immediate window are as follows
> INSERT INTO SYMGrpInstReqDetailsOUT ( ID, firstname, lastname,
> [instrument 1], [instrument 2], [instrument 3] ) IN 'C:\SYM\SYM Main
> Database.mdb' SELECT [Pupil details].ID, [Pupil details].firstname,
> [Pupil details].lastname, [Pupil details].[instrument 1], [Pupil
> details].[instrument 2], [Pupil details].[instrument 3] FROM [Pupil
> details] IN \\Wez\C on Wez\SYM\SYM Main Database_be.mdb WHERE
> ((([Pupil details].[SYM group 1])=[Forms]![Select SYM group form
> (registers)]![Combo0]) AND (([Pupil details].[tuition
> single/multiple])='2') AND (([Pupil details].[SYM group 1
> current])='-1')) OR ((([Pupil details].[tuition
> single/multiple])='2') AND (([Pupil details].[SYM group
> 2])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
> (([Pupil details].[SYM group 2 current])='-1')) OR ((([Pupil
> details].[tuition single/multiple])='2') AND (([Pupil details].[SYM
> group 3])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
> (([Pupil details].[SYM group 3 current])='-1')) OR ((([Pupil
> details].[tuition single/m ultiple])='2') AND (([Pupil details].[SYM
> group 4])=[Forms]![Select SYM
> group form (registers)]![Combo0]) AND (([Pupil details].[SYM group 4
> current])='-1')) OR ((([Pupil details].[tuition
> single/multiple])='2') AND (([Pupil details].[SYM group
> 5])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
> (([Pupil details].[SYM group 5 current])='-1')) OR ((([Pupil
> details].[tuition single/multiple])='2') AND (([Pupil details].[SYM
> group 6])=[Forms]![Select SYM group form (registers)]![Combo0]) AND
> (([Pupil details].[SYM group 6 current])='-1')) ORDER BY [Pupil
> details].lastname, [Pupil details].[instrument 1];
>
> Regards, Wesley

Huh? But that doesn't match the syntax that you originally said it did;
viz.:

> DoCmd.RunSQL "SELECT FieldList" _
> & " INTO TableName" _
> & " IN '' 'MS Access;Database=" & strPath & "'" _
> & " FROM [Pupil details]" _
> & " WHERE Field = 'XXXX'" _
> & " ORDER BY Field1,Field2];"

Not only does your target table's IN clause not match that syntax, but
you are trying to use yet another IN clause in the specification of your
source table:

> FROM [Pupil
> details] IN \\Wez\C on Wez\SYM\SYM Main Database_be.mdb

Not only does this one fail to follow that syntax, it even leaves off
the quotes. And that isn't the biggest problem -- the most serious
problem is that you can't have IN clauses referring to two separate
external databases in the same statement. See the help file topic for
"IN Clause" in the Jet SQL Reference. 

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)

Relevant Pages

  • Re: Statement parameter in Mailmerge.OpenDataSource
    ... You can certainly pass an SQL SELECT statement with a WHERE clause. ... the syntax right can be difficult, ... You may be able to find out more about that in SQL Server Books Online. ... > What i'm trying to do is, passing a statement calling a table-valued ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Joins over the equality sign
    ... Logically the FROM clause is processed first. ... I agree that if you *want* a Cartesian product, then use the new syntax and CROSS JOIN. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: MSACCESS 2003 CREATE PROC
    ... The query is incorrect in the GROUP BY clause. ... syntax instead of JET's syntax (Using an aggregate alias in an ... CurrentProject.Connection.Execute SQL ...
    (comp.databases.ms-access)
  • Re: Programming for a Dynamic Where clause
    ... diffcult to check the SQL syntax around the dates. ... When you're done building the SQL string use debug.print and pate the ... Tim. ... >> clause is ...
    (microsoft.public.excel.programming)
  • Re: CROSS JOIN
    ... > was that depreciating the original FROM .. ... WHERE syntax would never ... > The problem is that the WHERE clause is done after the FROM clause. ... but thats just outer join stuff. ...
    (comp.databases)