Good to hear, and thanks for the info. I'll pop it into my "useful
code" files.
Regards, Dave S
________________________________
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
Behalf Of Scott
Sent: Tuesday, 22 November 2011 08:21
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Re: Changing Access Queries based on location.
I figured it out David. I can loop through the Connections as per the
following, which I got partially from Recorder:
------------------- CODE ---------------------
Dim x As Integer
For x = 1 To 22
With ActiveWorkbook.Connections("Query from MS Access Database" &
x).ODBCConnection
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=c:\temp\001-70068_DataFields.accdb;DefaultDir=c:\temp;Drive
rId=25;FIL=MS Access;MaxBufferSize=2048;P" _
), Array("ageTimeout=5;"))
End With
With ActiveWorkbook.Connections("Query from MS Access Database" & x)
.Name = "Query from MS Access Database" & x
.Description = ""
End With
ActiveWorkbook.Connections("Query from MS Access Database" & x).Refresh
-------------------- END CODE --------------------
If I put this code in the Workbook Open event, with a case select
determined by the users login, then I can have three different loops,
one for each of the servers.
The problem with using mapped drives is that users frequently change
their mappings. I've run into this problem before.
Thanks for your help.
Scott B)
--- In ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ,
"David Smart" <smartware.consulting@...> wrote:
>
> 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@...>
> To: <ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.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 <mailto:ExcelVBA%40yahoogroups.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 <mailto:ExcelVBA%40yahoogroups.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
> >
>
----------
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.
UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.
[Non-text portions of this message have been removed]
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