Results 1 to 9 of 9
  1. #1
    Star Lounger skip's Avatar
    Join Date
    May 2002
    Location
    Connecticut, USA, Connecticut, USA
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Query Modification (2000)

    I (my client) has a report (query) that gets its data through ODBC. The data source has been moved to a new server and the ODBC connection was modified to read from the new location.
    I can create a new table, access any table I need but the query in question continues to point to the old location. I know this because if I rename the old location folder, I get a 'table not accessible" error.
    Where in the query would I find this 'pointer'. It needs to be adjusted.

    Thanks in Advance!

    Skip
    Skip Whitten, MCITP
    http://www.skipwhitten.info

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Modification (2000)

    Open the query in design view, click in an empty part of the upper half of the query design window, then activate the Properties window.
    Have the SourceDatabase and SourceConnectStr properties been filled in?

  3. #3
    Star Lounger skip's Avatar
    Join Date
    May 2002
    Location
    Connecticut, USA, Connecticut, USA
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Query Modification (2000)

    Source Database says (current)
    Source Connect Str is blank
    Skip Whitten, MCITP
    http://www.skipwhitten.info

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Modification (2000)

    What is the SQL of the query (open it. then select View | SQL)?

  5. #5
    Star Lounger skip's Avatar
    Join Date
    May 2002
    Location
    Connecticut, USA, Connecticut, USA
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Query Modification (2000)

    SELECT [Work Orders Firm Planned].WorkOrder, [Work Orders Firm Planned].Status, [Work Orders Firm Planned].ItemBillNumber, [Work Orders Firm Planned].ItemBillDescription, [Work Orders Firm Planned].WODueDate, [Work Orders Firm Planned].WODate, WO2_WorkOrderMaterialDetail.ComponentItemNumber, WO2_WorkOrderMaterialDetail.Revision, WO2_WorkOrderMaterialDetail.ExtdQtyRequired, IM1_InventoryMasterfile.ItemDescription, IM1_InventoryMasterfile.ProductLine
    FROM ([Work Orders Firm Planned] INNER JOIN WO2_WorkOrderMaterialDetail ON [Work Orders Firm Planned].WorkOrder = WO2_WorkOrderMaterialDetail.WorkOrder) INNER JOIN IM1_InventoryMasterfile ON WO2_WorkOrderMaterialDetail.ComponentItemNumber = IM1_InventoryMasterfile.ItemNumber
    WHERE (((WO2_WorkOrderMaterialDetail.Revision)="000")) OR ((([Work Orders Firm Planned].WODueDate) Between [Forms]![Production Needs Report].[BeginningDate] And [Forms]![Production Needs Report].[EndingDate]));
    Skip Whitten, MCITP
    http://www.skipwhitten.info

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Modification (2000)

    Thanks. I don't see anything that would explain the behavior you describe. What happens if you create a new query and paste this SQL statement into the SQL view of the new query? Does the problem occur if you try to open this query?

  7. #7
    Star Lounger skip's Avatar
    Join Date
    May 2002
    Location
    Connecticut, USA, Connecticut, USA
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Query Modification (2000)

    Hmmm.
    I will have to try that. I have created a new database and gotten external data through the ODBC User DSN without any difficulty.
    I will try that suggestion, thanks.

    Skip
    Skip Whitten, MCITP
    http://www.skipwhitten.info

  8. #8
    Star Lounger skip's Avatar
    Join Date
    May 2002
    Location
    Connecticut, USA, Connecticut, USA
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Query Modification (2000)

    Wanted to tell you the resolution! <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
    I had to relink the tables in Tools, Database Utilities, Linked Table Manager.
    We updated all the tables to use the correct DSN and it worked perfectly.
    Thanks for the help Hans!

    Skip
    Skip Whitten, MCITP
    http://www.skipwhitten.info

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Modification (2000)

    Thanks for letting us know - it's good to know if a problem has been resolved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •