(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@mountainhighbnb.com>
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
----------------------------------
Tidak ada komentar:
Posting Komentar