Querying a Linked table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kayda - 29 May 2007 05:35 GMT

Hi:

I am trying to do this:

SELECT * FROM MySybaseLinkedTable WHERE dtDateTimeField >
(SELECT MAX(dtDateTimeField) FROM MyAccessTable)

When I run this query without the subselect and manually put the date
in like #5/26/2007# then the query comes back immediately. When I try
to do the above it crashes Access.

Is what I am trying to do possible? If not, what is the best way to
get a MAX value from an Access table and pass it into the linked table
query?

Thanks,
Kayda

Reply to this Message

John W. Vinson - 29 May 2007 06:18 GMT

>Hi:
>
[quoted text clipped - 10 lines]
>get a MAX value from an Access table and pass it into the linked table
>query?

What you're doing looks eminently reasonable, and it's very disturbing that it
crashes Access!

One possible way to avoid the problem, if the problem is a bug in subqueries
linked to Sybase, is to use the DMax() function instead:

SELECT * FROM MySybaseLinkedTable WHERE dtDateTimeField > "#" &
DMax("[dtDateTimeField]", "MyAccessTable") & "#";

The # delimiters might or might not be necessary.

John W. Vinson [MVP]

Reply to this Message

Kayda - 29 May 2007 08:04 GMT

Thanks John:

That doesn't seem to work. Using the "#" gave a data mismatch error,
and getting rid of them runs, but it takes a very long time (almost 10
minutes for what should be only 100 records or so, and then fails
with the error:

Reserved Error (-7776); there is no message for this error

I can't find anything good on this error on the net, I got it before
when using import data for large amounts of data from the same
database. But this time the data I'm getting is very small. Any idea
what that error indicates?

Reply to this Message

James - 29 May 2007 10:23 GMT

Hi

It may be treating it as text, try Cdate(MAX(dtDateTimeField)) i.e.

SELECT * FROM MySybaseLinkedTable WHERE dtDateTimeField >
(SELECT Cdate(MAX(dtDateTimeField)) FROM MyAccessTable)

Regards
James

> Thanks John:
>
[quoted text clipped - 9 lines]
> database. But this time the data I'm getting is very small. Any idea
> what that error indicates?

Reply to this Message

John W. Vinson - 29 May 2007 16:05 GMT

>Thanks John:
>
[quoted text clipped - 9 lines]
>database. But this time the data I'm getting is very small. Any idea
>what that error indicates?

I suspect that the Sybase - Access interface is at fault: it's either
incomplete or very picky.

If the goal is to import a small subset of a large Sybase table, your best bet
will probably be a "Passthrough Query" - create the SQL of a query in Sybase's
dialect of SQL, and set its Passthrough property to yes. This query won't let
you join to an Access table, but you can probably use syntax such as

[datefield] IN ('3/14/2007', '3/21/2007')

or however Sybase expects lists of date values.

I'll raise the issue with some of my colleagues to see if anyone's had similar
problems with Sybase.

John W. Vinson [MVP]

Reply to this Message

Kayda - 30 May 2007 06:11 GMT

Thanks for your work on this John.

This solution using "IN" with Sybase doesn't work as the main problem
is getting the date from Access. Maybe I can do this in VBA? I'm not
so familiar with VBA, but I know some VBScript. I could probably just
get the date variable from Access and run the script from VBA? Could
you give me an idea on how to do this?

Thanks

Reply to this Message

John W. Vinson - 30 May 2007 17:06 GMT

>Thanks for your work on this John.
>
[quoted text clipped - 5 lines]
>
>Thanks

I asked my friends and got a couple of responses; Peter Doering replied:
----
I had the same issue 2 days ago, with A03 FE and SQL2k5 BE, some tables
linked through ODBC and SQL Native Client, some tables local.

Access crashed on me during execution of a fairly complicated SQL statement
that included linked and local tables as well as 2 sub queries in the WHERE
clause.

The solution for me was to replace one sub query by outer joins. The other
one I couldn't (ambiguous outer join), but it was fine anyway.

This leads me to the conclusion that ODBC doesn't like sub queries too
much. I wouldn't blame it on the Sybase side immediately.
----

Maybe you could use a JOIN rather than a subquery:

SELECT * FROM MySybaseLinkedTable
INNER JOIN MyAccessTable
ON MySybaseLinkedTable.dtDateTimeField > MAX(MyAccessTable.dtDateTimeField);

Or construcing the query in VBA may be better:

Dim strSQL As String
strSQL = "SELECT * FROM MySybaseLinkedTable WHERE dtDateTimeField > #" _
& Format(DMax("[dtDateTimeField]", "[MyAccessTable]") & "#;")

and then either open a Recordset based on this query, or save it as a
Querydef.

John W. Vinson [MVP]

Reply to this Message