The *.dqy files I found were external to the Excel file, but were created by
the query creation process. If the path is held inside the Excel file (I
assume you're using 2007 or 2010), then you'd need to change it from VBA.
I quite like the idea of using a mapped network drive to point to the local
*.MDB files. That way you wouldn't need to change the path at all inside
Excel.
Regards, Dave S
----- Original Message -----
From: "Scott" <scott@mountainhighbnb.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Tuesday, November 22, 2011 5:00 AM
Subject: [ExcelVBA] Re: Changing Access Queries based on location.
> Dave,
> I poked around inside the template file after changing it's extension to
> ZIP and found a file called connections.xml. This is where the path to
> the Access Database file is saved.
>
> Scott
>
> --- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@...>
> wrote:
>>
>> (Disclaimer ... I haven't actually used Excel queries as such, so am just
>> having a quick look.)
>>
>> If the queries are being stored as *.dqy files that the Excel brings in
>> at
>> run time, then you'd be able to substitute local ones in those computers.
>>
>> Or, use a mapped drive (e.g. Z:) as your path to your Access *.mdb file
>> on
>> the server. This mapping could then be location-specific, and the
>> queries
>> wouldn't know - or need to know - where the database actually is.
>>
>> I assume you have the database replication worked out so that everyone
>> gets
>> the same information?
>>
>> All that aside, of course, pretty much everything is a collection in
>> Excel
>> and can be changed by VBA. Queries are probably there too, and could
>> presumably be changed, once you've worked out how to find them. (The
>> *.dqy
>> files seem just to be text files that contain database access paths plus
>> the
>> full select statement, plus headers, so the internal contents would
>> probably
>> be easy enough to edit with VBA.)
>>
>> Regards, Dave S
>>
>> ----- Original Message -----
>> From: "Scott" <scott@...>
>> To: <ExcelVBA@yahoogroups.com>
>> Sent: Saturday, November 19, 2011 9:09 AM
>> Subject: [ExcelVBA] Changing Access Queries based on location.
>>
>>
>> > Experts,
>> > Here's my problem. We use an Excel spreadsheet as a form for creating
>> > new
>> > part numbers at our Engineering facilities. The form, when opened uses
>> > 22
>> > Access Queries to get the latest data from a master Access database for
>> > the various pull downs in the form. This works great and keeps us from
>> > having to constantly issue a new version of the form, each time we add
>> > or
>> > remove data from the pull-downs.
>> >
>> > Now that we have expanded our Engineering facilities from San Diego, to
>> > Montreal and the Philippines, the users there are having a hard time
>> > getting the form to access the database here on our server in San
>> > Diego.
>> > It works, it's just painfully slow. So here's my question....
>> >
>> > Is it possible to edit the Access query so that it is different based
>> > on
>> > the users log in location? I can detect the server they log into, and
>> > if I
>> > could then change the query to access the database on THEIR server,
>> > then
>> > everyone would have a quick connection and all we would have to do is
>> > use
>> > a script to update the two external databases to the changes made in
>> > the
>> > master one, on a nightly basis.
>> >
>> > Everyone following me here?
>> >
>> >
>> >
>> > ------------------------------------
>> >
>> > ----------------------------------
>> > Be sure to check out TechTrax Ezine for many, free Excel VBA articles!
>> > Go
>> > here: http://www.mousetrax.com/techtrax to enter the ezine, then search
>> > the ARCHIVES for EXCEL VBA.
>> >
>> > ----------------------------------
>> > Visit our ExcelVBA group home page for more info and support files:
>> > http://groups.yahoo.com/group/ExcelVBA
>> >
>> > ----------------------------------
>> > More free tutorials and resources available at:
>> > http://www.mousetrax.com
>> >
>> > ----------------------------------Yahoo! Groups Links
>> >
>> >
>> >
>> >
>> >
>> > -----
>> > No virus found in this message.
>> > Checked by AVG - www.avg.com
>> > Version: 10.0.1411 / Virus Database: 2092/4022 - Release Date: 11/17/11
>> >
>>
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1411 / Virus Database: 2092/4029 - Release Date: 11/20/11
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar