MS Query

[SinghNZ]

I have created a Workbook(A) which extracts data from differen
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 differen
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 acces
rights to alter, modify etc. all the files.

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

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

Any help would be greatly appreciated.

Ps: I am not technically strong rather a DIY person

 

[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]

;1611977']The path specified in your macro must also be relative to th

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

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

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

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

 

[GS]

I agree!
Thanks for the expanded detail...

--
Garry

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

 

[SinghNZ]

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

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

Sometimes the surest way to to manual input the path for each locatio
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 d
manual input of the path for each location. I will give it a go thi
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 successfu
in making this work. As said being a DIY I get results after few hit
and trials

 

[SinghNZ]

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

Garry and Rapto
It does make sense to me. I guess the surest solution would be to d
manual input of the path for each location. I will give it a go thi
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 successfu
in making this work. As said being a DIY I get results after few hit
and trials

Dear Garry and Rapto
Many thanks for your valuable advise. After few tries and it work
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
worksheets; each representing each week.

You can imagine the amount of work involved if I have to create an
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 no
wrote any code; I do not know actually how to write it. All I had don
was recorded all the steps in a macro to activate 'refresh all' comman
found under Data tab

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

Any suggestions would be much appreciated

Kindest regard

 

[CellShocked]

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]

On Wed, 29 May 2013 10:35:59 +0100, SinghN



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

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

It would seem the location for a lot of the worksheets would best b
o
the same sheet, instead of keeping them separate, thereby solving man
o
the problems your concept has created

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

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

(It would seem the location for a lot of the worksheets would best be o
the same sheet, instead of keeping them separate, thereby solving man
of the problems your concept has created.) - I would love to go your wa
as suggested if this can save me all the effort of manually enterin
path for each connection. My only worry is, I reiterate, post dat
corruption scenario. Do I assume correctly that creating a back up fil
could be the simple solution for post data corruption scenario? If so,
will need to learn how to extract / use data from the back up file, (i
the case of data corruption in the main file) and make my main workboo
working again

Regard