MS Query

[SinghNZ]

I have created a Workbook(A) which extracts data from different
workbooks(B to G) by using MS Query and stores in Workbook (A)

Workbook(A) is sitting on Server A.

Workbooks (B to G) are located on N, O, P, Q, R, S i.e. 6 different
servers on 6 different locations in 6 different cities.

Workbook(A) has a macro assigned to run the MS Query (Refresh all)

I am connected to Server A and so as my colleague.

Workbook (A) is saved in a folder to which both of us have same access
rights to alter, modify etc. all the files.

I can always use macro to refresh all the data and the query runs
perfectly fine. However, my colleague encounters a problem and even
though can access the file, he is not able to use Macro to refresh and
run the query (the error message pertains to path)

I have reached a deadlock and do not have any answer to why. I can fully
understand that path problems will come for my colleagues sitting on
different servers but I have failed to understand as to why we 2 of us
sitting on same server in same folder but my colleague cannot run the
query.

Any help would be greatly appreciated.

Ps: I am not technically strong rather a DIY person.




--
SinghNZ

[GS]

The path specified in your macro must also be relative to the
workstation executing the macro if using mapped refs to the target
folders. Otherwise, try using an IP address for the target servers.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[raptor5618]

'GS[_2_ Wrote:
> ;1611977']The path specified in your macro must also be relative to the

IP address is probably the best way to go unless the computers are not
using a static address. If they use DHCP they will keep the same
address and usually everyone is happy but in the case where power goes
out or the PC is offline beyond its lease you may be assigned a new IP
address. If you use a full address make sure you are not getting that
address through a mapped drive where it looks like it should work but
does not. Also mapped drive might be different. I also amuse that all
the connections have been established for each location. The links for
one location will not be the same as those at another location.

Make sure the rights are set up correctly too. Access to a shared
folder does not mean you have rights to the folders they are in so the
link might be stopped due to improper rights.

Sometimes the surest way to to manual input the path for each location
and see if you can make a link.




--
raptor5618

[GS]

I agree!
Thanks for the expanded detail...

[SinghNZ]

raptor5618;1611981 Wrote:
> IP address is probably the best way to go unless the computers are not
> using a static address. If they use DHCP they will keep the same
> address and usually everyone is happy but in the case where power goes
> out or the PC is offline beyond its lease you may be assigned a new IP
> address. If you use a full address make sure you are not getting that
> address through a mapped drive where it looks like it should work but
> does not. Also mapped drive might be different. I also amuse that all
> the connections have been established for each location. The links for
> one location will not be the same as those at another location.
>
> Make sure the rights are set up correctly too. Access to a shared
> folder does not mean you have rights to the folders they are in so the
> link might be stopped due to improper rights.
>
> Sometimes the surest way to to manual input the path for each location
> and see if you can make a link.

===========================

Garry and Raptor
It does make sense to me. I guess the surest solution would be to do
manual input of the path for each location. I will give it a go this
weekend and see whether it will work or not.

Many thanks for your time and effort in these replies; much appreciated.
I will be back next week posting here my result whether I was successful
in making this work. As said being a DIY I get results after few hits
and trials.




--
SinghNZ

[SinghNZ]

SinghNZ;1611989 Wrote:
> ===========================
>
> Garry and Raptor
> It does make sense to me. I guess the surest solution would be to do
> manual input of the path for each location. I will give it a go this
> weekend and see whether it will work or not.
>
> Many thanks for your time and effort in these replies; much appreciated.
> I will be back next week posting here my result whether I was successful
> in making this work. As said being a DIY I get results after few hits
> and trials.

Dear Garry and Raptor
Many thanks for your valuable advise. After few tries and it works
perfectly now. I opened the connection in MS Excel and changed the path.
Thank you very much.

Now, I need to ask you another question please.

There are 6 connections on each worksheet.

There will be 12 workbooks; 1 for each month; each workbook will have 4
worksheets; each representing each week.

You can imagine the amount of work involved if I have to create and
manually correct each connection path.

Is there a simple way to work around this?

And to clarify further macro written in my first question. I did not
wrote any code; I do not know actually how to write it. All I had done
was recorded all the steps in a macro to activate 'refresh all' command
found under Data tab.

I am assuming that a code will need to be written for performing above
task. In simple terms, what I want is that every time I create a
connection, correct path is recorded by default rather than me going in
and correcting each path manually.

Any suggestions would be much appreciated.

Kindest regards




--
SinghNZ

[CellShocked]

On Wed, 29 May 2013 10:35:59 +0100, SinghNZ
<SinghNZ...@excelbanter.com> wrote:

>Is there a simple way to work around this?

Come up with a better paradigm for performing the data archiving task
you think this accomplishes.

It is like you are setting up all these interdependent links as an
exercise, as no professional would set up such an array to store critical
accounting data. It has too many vulnerabilities.

It would seem the location for a lot of the worksheets would best be on
the same sheet, instead of keeping them separate, thereby solving many of
the problems your concept has created.

[SinghNZ]

CellShocked;1612069 Wrote:
> On Wed, 29 May 2013 10:35:59 +0100, SinghNZ
> <SinghNZ...@excelbanter.com> wrote:

> -
> >Is there a simple way to work around this?-

>
>
> Come up with a better paradigm for performing the data archiving task
> you think this accomplishes.
>
> It is like you are setting up all these interdependent links as an
> exercise, as no professional would set up such an array to store
> critical
> accounting data. It has too many vulnerabilities.
>
> It would seem the location for a lot of the worksheets would best be
> on
> the same sheet, instead of keeping them separate, thereby solving many
> of
> the problems your concept has created.

Hi
Thanks for your suggestions. I am a novice. I have my own reasons for
doing this way, may be not efficient in the eyes of a professional.

(Vulnerabilities) Each worksheet will have data in about 400 to 500
rows. Having this amount of data on each worksheet, and all worksheets
in one workbook, in my humble view, opens up another grave vulnerability
- corruption of data. I am trying to create separate individual
worksheets to avoid post data corrupt scenario. Should this to occur,
only that particular sheet will get corrupted and unusable, and not the
entire data in all the worksheets. This is the sole reason for creating
individual worksheets.

(It would seem the location for a lot of the worksheets would best be on

the same sheet, instead of keeping them separate, thereby solving many

of the problems your concept has created.) - I would love to go your way
as suggested if this can save me all the effort of manually entering
path for each connection. My only worry is, I reiterate, post data
corruption scenario. Do I assume correctly that creating a back up file
could be the simple solution for post data corruption scenario? If so, I
will need to learn how to extract / use data from the back up file, (in
the case of data corruption in the main file) and make my main workbook
working again.

Regards




--
SinghNZ