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: Mon, 13 Sep 2004 11:02:28 -0400

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:OdCtQBXmEHA.2740@TK2MSFTNGP12.phx.gbl
>> 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)

Huh. I had to check this, because it was contrary to what I have long
believed, but you are right. You *can* use IN clauses for both target
and source, and you can use just the database path, without specifying
the type. I was looking at an example where it was necessary to specify
the database password, and for that I think you may need to specify it
using the whole connect string. However, I haven't tried any
alteratives recently enough to be sure of that.

That last alternate syntax -- [dbFullPath].[table1] -- is completely new
to me, but it works. Thanks for setting me straight and telling me a
nifty new thing! 

-- 
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. ... 'working' example is for Jet SQL or ADO 'ANSI' SQL: ... database identifier is what goes in the first string literal ...
    (microsoft.public.access.modulesdaovba)
  • Re: A little Rolodex [revised]
    ... including alpha sort and searching for any embedded string, ... In this application, a database is a directory, ... you may optionally provide any alternate UCASE program, ... NN -> first store NN as key length ...
    (comp.sys.hp48)
  • Re: return multiple rows from sql statement
    ... strings from input values is almost certainly a safe path to SQL ... Also, being a MySQL function, it knows what MySQL needs or uses. ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)
  • Re: Invalid variant type conversion
    ... because within regular programming, there would be no agreement upon what ... applications built by standard Delphi controls, ... NULL is the same as "" in a database. ... >> The empty string tells it all. ...
    (comp.lang.pascal.delphi.misc)
  • Re: Code to delete/unlink Linked tables
    ... Public intLinkODBCTables As Variant, intLinkDB2Tables As Variant ... Public strLinkBackendDB As String, strLinkDSNname As String, strLinkLibName ... ' MsgBox "This database is in MDE format...I will delete/recreate ODBC ... Public Sub fncLinkDB2Table() ...
    (microsoft.public.access.modulesdaovba)