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
----------------------------------
Tidak ada komentar:
Posting Komentar