A2k7 UNION Query fails on 3rd SELECT

[S.Clark]

Access 2007

When I add a 3rd SELECT statement to a UNION query, I consistantly get the
following error:

The Microsoft Office Access Database engine could not find the object ".
Make sure the object exists and that you spell its name and the path name
correctly.

Now the trick... the tables included in the query are links to SharePoint
lists.
(...as I don't get the error when I use local tables.)

Bug?

 

[Jeff Boyce]

If you want folks to help evaluate/troubleshoot your query, posting the SQL
statement can help...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[S.Clark]

C'mon Jeff, you're not speaking to a rookie...

Select f1, f2, f3 from tablename1
UNION
Select f1, f2, f3 from tablename2
UNION
Select f1, f2, f3 from tablename3

When I do only two, no error... when I add the 3rd, error.

Steve Clark, former Access MVP

 

[KARL DEWEY]

I take it that you did not copy and paste the SQL so it might be analyzed for
any typos.

 

[S.Clark]

Y'all ain't even trying... c'mon, help a brother out.

Try this:
1. Link any table from A2k7 to a SharePoint 2007 list.
2. Create a new query
3. In the SQL View put:
Select 1, 2, 3 from tablename
UNION
Select 1, 2, 3 from tablename
(Literally put a 1, 2, and 3, and use your linked tablename)
4. Run it.
Do you get the error here? (I didn't)

5. Now add a 3rd instance for the UNION, and run it
Do you get the same error I did?

 

[vanderghast]

I don't use Share Point at all, neither Access 2007 much, but have you tried
to make a query with the first two tables and then, involved the third table
and the saved query into a new query?

Vanderghast, Access MVP

 

[Jeff Boyce]

AHA! I wondered if that was you hiding behind the cryptic inital!

I haven't run across this before, so I'll step back and see what other's
have found.

(good to hear from you again!)

Regards

Jeff

 

[S.Clark]

I corrected your post for you below...

And, yeah, I tried all kinds of stuff before posting.

I don't use Share Point at all, neither Access 2007 much, but [I did stay at a Holiday Inn Express,last night,] have you tried
to make a query with the first two tables and then, involved the third table
and the saved query into a new query?

Vanderghast, Access MVP

Y'all ain't even trying... c'mon, help a brother out.

Try this:
1. Link any table from A2k7 to a SharePoint 2007 list.
2. Create a new query
3. In the SQL View put:
Select 1, 2, 3 from tablename
UNION
Select 1, 2, 3 from tablename
(Literally put a 1, 2, and 3, and use your linked tablename)
4. Run it.
Do you get the error here? (I didn't)

5. Now add a 3rd instance for the UNION, and run it
Do you get the same error I did?