Problem with selecting from another database

[rdemyan via AccessMonster.com]

I don't understand why I can get the following to work:

------------------------------------------------------------------------------
--

SELECT A.*, B.* FROM TableA AS A, TableA AS B IN 'C:\......'
WHERE A.USER_ID = B.USER_ID;
------------------------------------------------------------------------------
--


But the following leads to an error:

------------------------------------------------------------------------------
--

SELECT A.*, B.* FROM TableA As A INNER JOIN TableA As B IN 'C:\.....'
ON (A.USER_ID = B.USER_ID)
------------------------------------------------------------------------------
--


In the latter case I keep getting a Syntax error in the From clause and it
hangs on the IN word.

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200612/1

[Lyle Fairfield]

Re: Problem with selecting from another database

rdemyan via AccessMonster.c om wrote:

the following leads to an error:

SELECT A.*, B.* FROM TableA As A INNER JOIN TableA As B IN 'C:\.....'
ON (A.USER_ID = B.USER_ID)

SELECT c.*, sq.*
FROM Customers c
INNER JOIN
(SELECT *
FROM [C:\Documents and Settings\Lyle Fairfield\My
Documents\Acces s\northwind.mdb].Customers) sq
ON c.CustomerID = sq.CustomerID

['69 Camaro]

Re: Problem with selecting from another database

Hi.

I don't understand why I can get the following to work:

.. . .

But the following leads to an error:

You may have noticed that Jet rewrites queries when you switch back and forth
between SQL View and Design View. Jet optimizes the syntax internally, so what
you originally wrote (or dragged across in the GUI during your design phase) is
not always what is ultimately saved in the QueryDef's SQL Property. In the case
of remote database tables, Jet is using [;DATABASE=Path\ FileName;].TableName
syntax internally, even if you use the IN operator in your own syntax. (Note
that you don't always see Jet's internal syntax in SQL View.)

Occasionally, Jet trips on the rewritten syntax. This happens with subqueries,
but it can also happen with the IN operator. To avoid it, use Jet's internal
syntax for the remote table name:

SELECT A.*, B.*
FROM TableA AS A INNER JOIN
[;DATABASE=C:\Te st\db1.mdb;].TableA AS B
ON A.USER_ID = B.USER_ID;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.


"rdemyan via AccessMonster.c om" <u6836@uwewro te in message
news:6b4398bd72 b87@uwe...

>I don't understand why I can get the following to work:
>
------------------------------------------------------------------------------
--
>
SELECT A.*, B.* FROM TableA AS A, TableA AS B IN 'C:\......'
WHERE A.USER_ID = B.USER_ID;
------------------------------------------------------------------------------
--
>
>
But the following leads to an error:
>
------------------------------------------------------------------------------
--
>
SELECT A.*, B.* FROM TableA As A INNER JOIN TableA As B IN 'C:\.....'
ON (A.USER_ID = B.USER_ID)
------------------------------------------------------------------------------
--
>
>
In the latter case I keep getting a Syntax error in the From clause and it
hangs on the IN word.
>
--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200612/1

>

[rdemyan via AccessMonster.com]

Re: Problem with selecting from another database

Thanks!

Lyle Fairfield wrote:

>the following leads to an error:

>

>SELECT A.*, B.* FROM TableA As A INNER JOIN TableA As B IN 'C:\.....'
>ON (A.USER_ID = B.USER_ID)

>
>SELECT c.*, sq.*
>FROM Customers c
>INNER JOIN
>(SELECT *
>FROM [C:\Documents and Settings\Lyle Fairfield\My
>Documents\Acce ss\northwind.md b].Customers) sq
>ON c.CustomerID = sq.CustomerID

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200612/1

[rdemyan via AccessMonster.com]

Re: Problem with selecting from another database

Thank you.

'69 Camaro wrote:

>Hi.
>

>I don't understand why I can get the following to work:

>. . .

>But the following leads to an error:

>
>You may have noticed that Jet rewrites queries when you switch back and forth
>between SQL View and Design View. Jet optimizes the syntax internally, so what
>you originally wrote (or dragged across in the GUI during your design phase) is
>not always what is ultimately saved in the QueryDef's SQL Property. In the case
>of remote database tables, Jet is using [;DATABASE=Path\ FileName;].TableName
>syntax internally, even if you use the IN operator in your own syntax. (Note
>that you don't always see Jet's internal syntax in SQL View.)
>
>Occasionally , Jet trips on the rewritten syntax. This happens with subqueries,
>but it can also happen with the IN operator. To avoid it, use Jet's internal
>syntax for the remote table name:
>
>SELECT A.*, B.*
>FROM TableA AS A INNER JOIN
[;DATABASE=C:\Te st\db1.mdb;].TableA AS B
>ON A.USER_ID = B.USER_ID;
>
>HTH.
>Gunny
>
>See http://www.QBuilt.com for all your database needs.
>See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
>

>>I don't understand why I can get the following to work:
>>

>[quoted text clipped - 18 lines]

>In the latter case I keep getting a Syntax error in the From clause and it
>hangs on the IN word.

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200612/1

[Smartin]

Re: Problem with selecting from another database

rdemyan via AccessMonster.c om wrote:

I don't understand why I can get the following to work:
>
------------------------------------------------------------------------------
--
>
SELECT A.*, B.* FROM TableA AS A, TableA AS B IN 'C:\......'
WHERE A.USER_ID = B.USER_ID;
------------------------------------------------------------------------------
--
>
>
But the following leads to an error:
>
------------------------------------------------------------------------------
--
>
SELECT A.*, B.* FROM TableA As A INNER JOIN TableA As B IN 'C:\.....'
ON (A.USER_ID = B.USER_ID)
------------------------------------------------------------------------------
--
>
>
In the latter case I keep getting a Syntax error in the From clause and it
hangs on the IN word.
>

In addition to the suggestions given, you could also link the remote
table in your MDB and write the SQL without worrying about the path.

--
Smartin

['69 Camaro]

Re: Problem with selecting from another database

You're welcome. Keep practicing SQL and experimenting with slightly different
"formulas" to see what the data sets produce. You'll keep getting better at it.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.


"rdemyan via AccessMonster.c om" <u6836@uwewro te in message
news:6b474adb14 88e@uwe...

Thank you.
>
'69 Camaro wrote:

>>Hi.
>>

>>I don't understand why I can get the following to work:

>>. . .

>>But the following leads to an error:

>>
>>You may have noticed that Jet rewrites queries when you switch back and forth
>>between SQL View and Design View. Jet optimizes the syntax internally, so
>>what
>>you originally wrote (or dragged across in the GUI during your design phase)
>>is
>>not always what is ultimately saved in the QueryDef's SQL Property. In the
>>case
>>of remote database tables, Jet is using [;DATABASE=Path\ FileName;].TableName
>>syntax internally, even if you use the IN operator in your own syntax. (Note
>>that you don't always see Jet's internal syntax in SQL View.)
>>
>>Occasionall y, Jet trips on the rewritten syntax. This happens with
>>subqueries,
>>but it can also happen with the IN operator. To avoid it, use Jet's internal
>>syntax for the remote table name:
>>
>>SELECT A.*, B.*
>>FROM TableA AS A INNER JOIN
> [;DATABASE=C:\Te st\db1.mdb;].TableA AS B
>>ON A.USER_ID = B.USER_ID;
>>
>>HTH.
>>Gunny
>>
>>See http://www.QBuilt.com for all your database needs.
>>See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>>http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
>>

>>>I don't understand why I can get the following to work:
>>>

>>[quoted text clipped - 18 lines]

>>In the latter case I keep getting a Syntax error in the From clause and it
>>hangs on the IN word.

>
--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200612/1

>