By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,808 Members | 1,481 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,808 IT Pros & Developers. It's quick & easy.

Problem with selecting from another database

P: n/a
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.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 25 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
rdemyan via AccessMonster.com 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\Access\northwind.mdb].Customers) sq
ON c.CustomerID = sq.CustomerID

Dec 25 '06 #2

P: n/a
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:\Test\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.com" <u6836@uwewrote in message
news:6b4398bd72b87@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.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 25 '06 #3

P: n/a
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\Access\northwind.mdb].Customers) sq
ON c.CustomerID = sq.CustomerID
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 25 '06 #4

P: n/a
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:\Test\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.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 25 '06 #5

P: n/a
rdemyan via AccessMonster.com 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
Dec 25 '06 #6

P: n/a
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.com" <u6836@uwewrote in message
news:6b474adb1488e@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.)

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:\Test\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.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 26 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.