Querying a Linked table

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

0
Kayda
5/29/2007 4:35:36 AM
access 16762 articles. 3 followers. Follow

6 Replies
1061 Views

Similar Articles

[PageSpeed] 31

On 28 May 2007 21:35:36 -0700, Kayda <blairjee@gmail.com> wrote:

>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?

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]
0
John
5/29/2007 5:18:02 AM
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?

0
Kayda
5/29/2007 7:04:24 AM
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


On May 29, 8:04 am, Kayda <blair...@gmail.com> wrote:
> 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?


0
James
5/29/2007 9:23:09 AM
On 29 May 2007 00:04:24 -0700, Kayda <blairjee@gmail.com> wrote:

>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?

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]
0
John
5/29/2007 3:05:46 PM
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


0
Kayda
5/30/2007 5:11:00 AM
On 29 May 2007 22:11:00 -0700, Kayda <blairjee@gmail.com> wrote:

>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
>

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]

0
John
5/30/2007 4:06:21 PM
Reply:

Similar Artilces:

Email messages with links
How can I get rid of all the URL links after every sentence? It is very annoying. Thanks. Can you post a screenshot somewhere so we can see what you are talking about? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Do you sync your mailbox with a smartphone or pda? http://forums.sl...

Looping through Query to create multiple sheets in excel- Just need the loop
I figured out where I should start the loop in order to keep the excel work open and still be able to add more sheets, but I can't figure out how to add code to For Next loop to go through a query "qryManufacturer" and take each one and put them into the string (strManuf) I always get to this point and I can't figure out how to loop through a recordset. I have put the string in the query at the bottom. Public Sub CopyRs2SheetHacked(strSql As String, strWorkBook As String, _ Optional strWorkSheet As String, Optional strRange As String) 'Uses the Excel...

Summary that links to other sheets
I have coding that pulls data from 70-80 worksheets into a summary sheet (the number changes every month), and that's worked fine for quite a while, but others are going to be using this workbook now, and I'd like for them to be able to make changes on the worksheets and have it update the summary when they do. The coding below pulls just the values in. Can anybody tell me what I need to do to have it link to the cells instead of just copying them? Or do I need to just start over? Thank you for your help. Sub Summary() Dim sh As Worksheet Dim DestSh As Worksheet ...

Pivot Table and Formatting Subtotals Automatically
Hi, by arranging some data into the pivot table, my boss exactly gets what he wants; the only thing, he asked me to format the subtotals (like putting all in bold or color background of the cells), so 1) I can do it manually but then i have to redo it every time i refresh the table 2) I should be able to do it automatically and so that if I refresh the table, it's not necessary to refresh all the time And I'm looking after a number 2) solution. Thx for any help Dries. .. On the Pivot toolbar, choose PivotTable>Table Options. Add a check mark to 'Preserve formatting...

Job Link to Sales Invoice
We had something weird hapen in Job Cost when we posted an Invoice in Sales order Processing that was linked to a job it inserted a row in some of the Job Cost tables but not all or them. In ICJC1002, ICJC1200, ICJC 2000 and ICJC 9000 but not in the ICJC11000. We have a report that is looking at the ICJC1100 and the job information is missing on the report. We are not sure how to fix this, has this ever happened to anyone else? Thanks, Marie ...

loading excel tables
We are moving our factbook to publisher. Is there a way to link excel files to publisher? How about linking a bunch of files at the same time? Thanks In news:185f601c44a45$66e2b970$a001280a@phx.gbl, UNL user <anonymous@discussions.microsoft.com> posted: > We are moving our factbook to publisher. Is there a way to > link excel files to publisher? How about linking a bunch > of files at the same time? Thanks Linking the Excel files in what manner? So the actual Excel workbook is displayed in Publisher? So there is a hyperlink to a location on your local hard drive? So ther...

Run macro for each record in a query
Hello All I have a table [practices], which contains details of 'client organisations'. From time to time I need to send an email to certain 'client organisations'. I have a macro called 'send_emails' which uses the SendObject command, which I use to send the emails (a button on a form runs the macro). The emails include a report that is specific to the 'client organisation'. Currently I select the 'client organisation' with a combobox, run the macro, select another 'client organisation', run the macro again, etc. etc. It would be extrem...

Pivot Table
Hia! Happy New Year Friends. I have spent almost 3 hours not but I am not able to spot the bug; The following codes create a Pivot Table but the table does not generate reports except for the field headings; Sub SalesReports() Dim pc As PivotCache Dim pt As PivotTable Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=Range("b5:g39")) Set pt = ActiveSheet.PivotTables.Add(PivotCache:=pc, tabledestination:=Range("i5")) Application.GoTo Range("i5") End Sub What is the problem in the codes? Thanks. H...

results table dilemma
hi there everyone, I'm hopeful that someone can help me with my excel problem. Here's my dilemma: I'm trying to do a results table where results will come from a worksheet that has different columns, one of which is a date column(let's call it worksheet A). Now the results table is in another worksheet in the same workbook, and before results are generated, I'd like the user to specify a date and then the results table will be populated with entries from worksheet a that matches the specified date and dates from the previous week. im not quite sure if it's possi...

Copying Web Tables To Spreadsheets
I'm trying to copy a table from a website to an Excel 2003 spreadsheet so that the results are listed in a single line for each nursing home listed. When I initially attempted this, I copied the table including the first column for the check boxes. The transfer appeared to go well but before I could examine it in depth, I chose to delete that first column of check boxes. Then I decided that the best way to clean up the results would be to copy a page at a time then just position my cursor at the appropriate place at each Paste operation. Unfortunately, I've been unable to get a ...

Strange Value in my chart data table
Hello, I have created a chart with 3 different data ranges. One of the data ranges takes data that has been manually entered. They are quite long figures i.e. 18288388.31. In the corresponding entry on the data table I am getting these values: 5e+06. Anybody know what this means? Thanks. "owl37" wrote: > Hello, > I have created a chart with 3 different data ranges. > One of the data ranges takes data that has been manually entered. They are > quite long figures i.e. 18288388.31. > In the corresponding entry on the data table I am getting these values: > 5e...

Links showing #VALUE!
I have an EXCEL file (named 'Master') with 2 worksheets. On each of these worksheets I have formulae which links with data on 2 different EXCEL files (named 'Data 1' & 'Data 2'). These 2 files only hold tables containing data inputs (i.e. no formulae). When I open the 'Master' file it asks if I want to refresh the Links, which I do. When this is completed one worksheet in the 'Master' file shows what I expect it too, having been linked to 'Data 1' file. However, the other worksheet shows #VALUE! in all the cells that relate to a link in the...

adding columns to crosstab queries
I have a very simple crosstab query and I am trying to create a new column thatr divides uses the following expression: [Total of Share Total]/[Share Total] My code: TRANSFORM Sum(MSF07.[Share Total]) AS [SumOfShare Total] SELECT MSF07.Aircraft, Sum(MSF07.[Share Total]) AS [Total Of Share Total] FROM MSF07 GROUP BY MSF07.Aircraft PIVOT Format([Date Closed],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"); I am also having troubl...

Dynamic SQL againest a table variable
Hello, i need to issue dynamic SQL with a table variable not a real table but it does not work, here is a simple example declare @t table (storeid int) insert into @t (storeid) values (1) exec('select * from @t') --this does not work exec('select * from ' + @t) --this also does not work is it possible ? thanks Bassam On SQL Server 2008 you can use a table-valued parameter: CREATE TYPE store_type AS TABLE (storeid INT); GO DECLARE @t store_type; INSERT INTO @t (storeid) VALUES(1); EXEC sp_executesql N'SELECT storeid FROM @t'...

absolute as opposed to relative links auto-insterted in emails
Is there a setting which causes a linked file in a MS Outlook email to be referenced like this server name <file://\\civic\itsgroups$\Project Office\Projects\505 EDMS Project\02 Investigation\Steering Group meeting minutes\EDMS Steering Group Minutes 060515.doc> Rather than like this (with the drive mapping) and therefore less useful if the mapping is not the same between sender and receiver in a large organisation with differently mapped drives but all files running of the same server. <file://G:\Project Office\Projects\505 EDMS Project\02 Investigation\Steering Group meeting mi...

Navigation Pane unhides when creating liked table
I have a runtime/accde app to be distributed to users that can't have access to the nav pane or objects, and even though I've disabled special keys, including shift keys, and turned off the nav pane option, the nav pane reappears during app startup. Found out using docmd.transferdatabase acLink is the culprit. 1) does MS know about this and 2) what's the fix if I want to re-create table connections during startup behind the scenes? -- Mark A. Kann Access Developer/Data Analyst Rockford, IL HarkitsMark wrote: > I have a runtime/accde app to be distributed ...

linked (multiple?) files
When will Money Plus support linking multiple files to a transaction? This would be great for organizing warranty and home inventory information. Where you need to have your owners guide, receipt, extended warranty for the same transaction all accessable. Kane, I think you already have the ability to link to multiple files. Just put all the stuff you want to link to into a folder, then link to that folder. Ken "Kane" <Kane@discussions.microsoft.com> wrote in message news:205593A0-B679-49E5-9A3D-A99013472355@microsoft.com... | When will Money Plus support linking multi...

total query with last date
I have a table with 5 fields ID PersonID Date Amount and Type I want a query with the last record of each person based on date and I want the results like this PersonID LastOfDate Amount Type How can i do that thanks On Tue, 19 Jun 2007 17:45:20 -0700, zionsaal@gmail.com wrote: >I have a table with 5 fields >ID PersonID Date Amount and Type > >I want a query with the last record of each person based on date and I >want the results like this > >PersonID LastOfDate Amount Type > >How can i do that >thanks A Subquery will do this: SELECT PersonID, [Date],...

Missing 512 records in a query
I was setting up a query that had been troublesome so I was pulling each element of the query separately to locate which of 5 related tables was causing the problem. I forgot to enter a criteria in the first element of the query so it should have retrieved the entire table. The table contains 24100 records but the query only retrieved 23588. A little investigation found a few of the missing records and I can find them in the table but they don't appear in the query. Why don't they appear in the query? What have I done wrong that a query can't see these records they look fin...

Link multiple accounts to an opportunity
I'm a newbie, so this is probably a very easy thing to do... I have an Opportunity and want to link 1 or more accounts to it of different types, with different relationship roles to the opportunity. For instance, one of my CRM users should be able to open Opportunity A and view a list of the accounts who are defined as "Sub contractor type 1s" for that opportunity. How do I go about doing this? Thanks for your help!!! Jason Go to Settings -> Relationship Roles and set up the different roles that may exist. Then open your opportunity, and click on relationships. Add a...

Querying 2 field unless duplicated
New at this, so please bear with me: I am looking to merge 2 fields unless duplicated. The fields are in the same row in the same table Female Surname & Male Surname and I have no problem when the Surnames are different, I think.. (seems a tad convoluted). e.g., FemaleSurname and MaleSurname as per below Contact Name: IIf(IsNull([Female Surname]),IIf(IsNull([Male Surname]),[Male Surname]),IIf(IsNull([Male Surname]),[Female Surname],[Male Surname] & " and " & [Female Surname])) but where the surnames are the same I would like to show it just once. Can y...

Query from text box value
My idea is create a query. The field set as the job number field and the criteria i set is as "[Forms]![WIPCategory]![txtC_WIPCategoryID]" But it simply can't work... I want to display this result to be in combo box -- Message posted via http://www.accessmonster.com EMILYTAN via AccessMonster.com wrote: > My idea is create a query. The field set as the job number field and the > criteria i set is as > "[Forms]![WIPCategory]![txtC_WIPCategoryID]" > But it simply can't work... > I want to display this result to be in combo box > -----BEGIN PG...

How to reference 2 datasets within a table in a Reporting services report
How can i calculate within my report the movements of two columns coming from 2 different datasets within a table in a Reporting services report. The 2 different datasets are MDX queries with exactly the same number of rows but running over 2 different periods. I just want to calculate within the report the different between the two columns data i.e col A - Col B. I am only able to reference one dataset within the table and for the second dataset, I can only reference the Sum of this column measure or the First/Last record of this column measure, but not each data rows of this column ...

Table Split
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How can I delete a split table? Hi SuperShuffler, What exactly do you want to delete? The split, the upper table, lower table or the lot? -- Cheers macropod [Microsoft MVP - Word] <SuperShuffler@officeformac.com> wrote in message news:59bb5b16.-1@webcrossing.JaKIaxP2ac0... > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How can I delete a split table? Hey macropod, <br><br>I want to delete the actual split so the upper table and lower table...

Calculating data from records results of a cross tab query
Hi, I have a report based on a cross tab query that give sales for 2007 and 2008 and budget amounts for 2008 by month for each client. The rows headers of the cross tab are Client and Year (2007 Actual, 2008 Actual and 2008 Budget). The field headers are the months in the year (January - December). I have created a report where the page header is the Months, the FullName hear is the Client Name and the Detail section has the results. It would look something like this: January February March April May June July ..... Joe Smith 2007 Actual $400 ...