Need MS query to check Current Directory

I need to know how to make MS QUERY queries look at the 
CURRENT workbook Directory instead of the creation 
directory which it imbeds.  
I am NOT talking about the default directory in tool -> 
options-> general -> default file location.
Example of query MTHLYISSUE.DQY
XLODBC
1
DSN=MS Access 
Database;DBQ=C:\PROJECTS\BKL200401JAN\BOOKLEND.MDB;DefaultD
ir=C:\PROJECTS\BKL200401JAN;DriverId=25;FIL=MS 
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;
SELECT MonthlyIssuances.IssuanceMo, Sum
(MonthlyIssuances.BookCnt)  FROM BookLend.MonthlyIssuances 
MonthlyIssuances  GROUP BY MonthlyIssuances.IssuanceMo  
ORDER BY MonthlyIssuances.IssuanceMo DESC

I need to edit this query so that it looks for the Access 
database BOOKLEND in the Directory where workbook (and 
database) is being used from (CURRENT directory) not where 
the original workbook was created. 

Each month the workbook and database are copied to an new 
folder, the database is updated, and new pivot tables and 
reports are generated from this.  I need to be able to 
give this to other people to run without them having to 
edit the query.  There are 6 others within this workbook

Thanks
Patti
0
anonymous (74722)
4/2/2004 3:50:00 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
359 Views

Similar Articles

[PageSpeed] 58

Reply:

Similar Artilces:

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

Displaying MS Excel Chart control in ASP.NET Application
Hi, We are working on an ASP.NET (VB.NET) application. In one of th screens of our application, we need to show a graph and for this we ar using MS Excel Graph control. We have the graph prepared in Excel. Th application would feed in certain values into certain cells of th excel file and the graph would be drawn by the graph control based o these cells. We need some help on how exactly to display the excel graph contro directly on the screen of ASP.NET application. Thanks for the help oursm -- oursmp ...

Need to import EDI file
Hello. I have an EDI TS-130 file (academic transcript) that I would like to import into Access 2003. Is this possible to do for free? If so, how? ...

Safe Pay Check Amount Problem
I am trying to setup Safe Pay. Everything seems to be working fine except for one annoying quirk. Our bank's Positive Pay data format gives us 12 characters for check amounts ($$$$$$$$$$¢¢). The file that Safe Pay generates is working perfectly except for those checks that are greater than $9,999,999.99 (9 characters). If the check amount is greater than that (say $10,000,000.00), the value that the Safe Pay file shows for that check is 002147483647. And, even if none of the individual checks exceed $9,999,999.99, if the total of the checks in the batch exceeds that a...

Automating Auditing of Directory Service Objects
Hi. Is there a way to automate the deployment of Auditing settings to the OU's in AD? I only have a handful so it doesn't have to be TOO efficient. These have to be deployed automatically (via script, command line or whatever) if at all possible. Is this possible? If so, where can I start looking? Thanks! On Wed, 19 May 2010 06:42:49 -0700 (PDT), Neil <neilriches@googlemail.com> wrote: >Hi. Is there a way to automate the deployment of Auditing settings to >the OU's in AD? I only have a handful so it doesn't have to be TOO >efficient. These ...

need custom cut and paste functions
Hello, I once wrote here about a problem I had cutting and pasting where columns would turn to "REF!" after a cut and paste. I would work around it by copying, pasting and then manually deleting instead. I thought turning everything in the sheet to absolute references would solve the problem but it didn't so now I am thinking of a different solution. Could someone tell me what I need to do to write my own cut and paste functions which would basically copy the selection and then on a paste it would paste and then delete the original selection from where it was copied from...

Vendor transactions and checks
I'm trying to create a vendor payment report from my program. From which tables I can get the vendor payable transaction information??? I also need to know whether there is a check aleady issue to a transaction. Thank you. ...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

You wont believe this! SERVER DOWN
I was helping the operations/it director to get RPC over HTTP going on their exchange server for a company I use to be the sys admin for. The ops/it director was doing the add/remove programs portion to add rpc over http there, somehow in the process of doing this he managed to uninstall IIS -or- just NNTP and SMTP (I can't figure out which and he claims he did neither). Long story short, I've reinstalled NNTP and SMTP, reconfigured SMTP to the best recollection I have of the previous config and the server still refuses to send/receive email, even internally. All services are s...

Mailbox needs reducing
We have this mailbox that has been left for months and with no limits, it's now 11GB!! Ho can I trim in down, I don't really want to go into the mailbox and do it, can Exchange do it? In news:euNRN1iPHHA.404@TK2MSFTNGP02.phx.gbl, Gonzo <no@no.com> typed: > We have this mailbox that has been left for months and with no > limits, it's now 11GB!! > > Ho can I trim in down, I don't really want to go into the mailbox and > do it, can Exchange do it? Presuming you're not on 5x or earlier, you can use mailbox management policies to get rid of items ol...

Summing in A Query
Hello, I have a database which fuel records are stored in. The data is stored in two tables. The first records the daily logs that operators use each time they fuel up. It stores their name, the key they used (keylock fuel system - it's ancient) the unit number of the equipment using the fuel, and the amount of fuel they took. The second table stores the month end information retrieved from the key lock print out. It keeps a running total of the amount of fuel taken with each key, and the operator using that key. We have problems making sure all of the fuel is accounted for each mon...

Retrieving Current User Information from JScript (OnChange)
I am adding functionality to one of my client's OnChange scripts to append the Current User Name into a memo field with date and action information. I scoured the web but can not find a way to properly retrieve the Current User information. Any information would be incredibly appreciated. ...

How do add another code to a current one?
I have this following code to make the rows changed based on the critea in column 16, and I need add A "Red, Yellow, Green" for status to only one column 30 at the end of the spreadsheet. How do I add another code? I keep getting an error.. Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, clr As Long For Each c In Target.Cells If c.Column = 16 Then Select Case c.Value Case "Analyze": clr = RGB(204, 255, 255) Case "Build ": clr = RGB(204, 255, 255) Case ...

I need help restoring Public Folders
Hi I've been having issues getting Public Folder replication going between 2 Exchange 2003 servers. Prior to doing any work I performed a Backup using Veritas Backup Exec and this completed and verified correctly. I screwed things up and managed to delete some of the public folders on my original server. I'm not 100% sure what I did but it is a mess and the only backup I have is the Exchange backup I created before doing any work. The public folders contain a number of Contact Lists and a very important Event Calendar. I have restored the public folders but when the restore c...

Need help on a formula 05-20-10
I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

Can I have spell check ignore errors and alert me to real words?
Instead of stopping on misspellings and skipping real words, is there a way to stop on correct spellings to find actual words in a bunch of gibberish? Sort of an "UNspell check". For example, pass over "JJKSXO" but show me "SPELL". No, there is no way of doing that regards, Peo Sjoblom "JenLynFish" wrote: > Instead of stopping on misspellings and skipping real words, is there a way > to stop on correct spellings to find actual words in a bunch of gibberish? > Sort of an "UNspell check". For example, pass over "JJKSX...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

Need to retrieve a deleted Excel File
On Saturday I hide an excel sheet and then protected the workbook. When I got into work today to open the file it was non-existent. What do I have to do to retrieve the file? Does it make a difference if the file extension ended with .csv? Thanks in advance! Search in Windows to make sure it's still on your compute -or go to the folder the file is in and look to make sure it's still ther -or If you didn't do this, you might need to to text, which includes *.cs Excel is not set to open this type of file by defaul tj Did you (try to) open the file with Excel. If so : did ...

need some XL URL's please
I would appreciate some good site links for XL how to's and working examples aimed at Novice - Intermediate. Many thanks, Steve Hi Steven: See: Dave McRitchie: http://www.mvps.org/dmcritchie/excel/excel.htm Go to the heading: Excel Lessons & Tutorials (#tutorials) It's just after halfway down. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au It is imperative that the patches provided by Microsoft in its April Security Release be applied to Systems as soon as possible. It is believed that the likelihood of a worm being released SOON that ...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

ebay - ereseller stook my money
a terrible thing happenned to me on ebay. i am a disabled woman in and eresellers took my money and never sent my washer. his email address is david@tropivision.com. anything that you can do to help would be nice. ebay is doing nothing. i can give a reward This group is for discussing the Microsoft Money computer program. Does it really sound like an eBay buyer complaint forum? Do a google search on ebay and you will find a couple of groups, one of which may reply to your problem. That ebay seller has been selling on ebay for over 5 years. arthur -- On 1 Jun 2004 13:50:08 -0700, (An...

Problem with vba code to export query result in excel
Hi, I have a access report that exports to excel with click of a button after choosing parameters. This works well. However I have to modify couple of fields to utilize formula in the export module. I am not sure how to do this. I am writing the above code which seems to cause problem. I appreciate any help to resolve this issue. Thanks. Code: If lngColumn = 12 Then xlc.Offset(0, lngColumn).Value = =([UnitPrice]*[OriginalShippedQty])/1000 End If It seems the fields UnitPrice and OrigianalShippedQty are not being recognized here Jack wrote: >Hi, >I have a acces...