Re: SQL syntax over a network

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

From: david epsom dot com dot au (david_at_epsomdotcomdotau)
Date: 09/13/04 

Date: Mon, 13 Sep 2004 19:05:10 +1000

> problem is that you can't have IN clauses referring to two separate
> external databases in the same statement. See the help file topic

You can, in this kind of example. You can code in one database,
use another for the source, and use a third for the destination.
If anything, the restriction mentioned in the help files refers
to using multiple IN clauses for the data source.

Haven't tried it with an ODBC data source or destination: but
would not be surprised if it failed if it got that far: (will
make table queries work against an ODBC data source/target?)
Presumably (don't know, haven't tested) Access will reject SQL
where there are multiple source /or/ destination IN clauses.
Access /will/ allow queries/linked tables as sources, and works
correctly with multiple disparate source MDB's when done that
way. Access can't correctly join ODBC linked tables from disparate
sources, it fails with unpredictable results, so you would not
be surprised if you got a similar failure when writing from one
ODBC source connection to a different ODBC target connection.

FWIW, Jet will accept a number of formats for this kind of
'action query', but this one is new to me! I've never seen
'MsAccess' shown explicitly, and I wouldn't have expected
it to work in Jet SQL ... which leads me to wonder if the
'working' example is for Jet SQL or ADO 'ANSI' SQL:
    IN '' 'MSAccess;Database=/dbFullPath/'
In Jet SQL 'MSAccess' is assumed before the semicolon, (linked
table connect strings look like this: ';Database=...') and the
database identifier is what goes in the first string literal
(the '' in the original example), so you can just fill in the
first string and drop the second string:
    IN '/dbFullPath/'

(
But this works just as well, and I like it even better:
    select ... into [dbFullPath_1].[table1] _
    from [dbFullPath_2].[table2]
)

Jet SQL is what you get by default in most? versions of Access
when you use CodeDB,CurrentDB or CurrentProject.Connection, so
if that is a valid ADO database string, you may need to check
that you are not running in Jet SQL mode.
(david)

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:e4d$KnTmEHA.3608@TK2MSFTNGP09.phx.gbl...
> "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: SQL syntax over a network
    ... You can code in one database, ... > to using multiple IN clauses for the data source. ... > database identifier is what goes in the first string literal ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL syntax over a network
    ... You can code in one database, ... > database identifier is what goes in the first string literal ... > Jet SQL is what you get by default in most? ...
    (microsoft.public.access.modulesdaovba)
  • Re: Is this possible Yes or no.
    ... > Access you can allow everyone to read and write to a database via ADO ... Intermediate Microsoft Jet SQL ... I can type and execute a SQL DDL script faster than I can ...
    (microsoft.public.excel.programming)
  • Re: Prolog type overloading
    ... If I have in my database next, it means ... nextclauses. ... It's not always clever to be clever. ... I would use different names for different predicates. ...
    (comp.lang.prolog)
  • Re: embedded SQL in COBOL
    ... I can't imagine specifying 200 different fields in the ... If you have 10 fields in your database, and the last 5 can be null, you'll have something like... ... The most common way is a copybook - we have a working-storage copybook for the data layouts, and a procedure copybook to do the select. ... In our network database, the database is defined with picture clauses, like COBOL is. ...
    (comp.lang.cobol)