multitasking movefile and running queries?

[Mat N]

Hello access friends,

(Originally posted on access.formsprogramming which I think was the
wrong place)

I'm precalculating a bunch of data for my clients as per a profile I
have for each of them. Each data set that I generate, I put into an
separate Access file which I eventually move to a server location that
the particular client and I share access to.
Currently there are 10 clients, but this is expected to go up
considerable, and each of the precalculated data sets requires 25-30
minutes to generate (200+ Mb files). I have a form which does a batch
process to generate all of the files locally and then I go through the
files and manually post them up to the servers spaces.
What I'd like to do is include the manual moving of the files to the
server locations in the batch process however I have found that it is
quicker to do it manually because the code has to wait until it has
sent each file before continuing with the next batch item.
Anyone know how to just start transferring and move on to the next
record to start precalculating the next file before the file transfer
is complete?
The file transfer can take 30+ minutes depending on the location (some
are in Asia) and so its entirely possible that multiple files could be
transferring at the same time (when I do this manually this obviously
happens because all the file are ready to go).
I have been playing around with the following code:
Sub transfer(str_file As String, str_dest_location As String)
Dim fs As Variant
Set fs = CreateObject("Scripting.FileSystemObject")
fs.MoveFile str_file, str_dest_location
Set fs = Nothing
End Sub
Sub call_test()
Dim a As String
Dim b As String
a = "\\xxx-fs01\ClientLocation\TESTFile.xls"
b = "C:\Temp\TEST\TESTFile.xls"
Call transfer(b, a)
End Sub
My thinking is that I could either:
- cause another form, instance of access or an excel application to
load for each transfer which independently initiates the transfer
although I'm not convinced that pause in the code would go away.
- use a different method which I'm unaware of
Any ideas?

[Tom van Stiphout]

On 14 Oct 2006 03:58:42 -0700, "Mat N" <Ma******@gmail.comwrote:

Indeed a second instance of Access would do it.
So would a simple batch file spawned by the Shell command.

-Tom.

>Hello access friends,

(Originally posted on access.formsprogramming which I think was the
wrong place)

I'm precalculating a bunch of data for my clients as per a profile I
have for each of them. Each data set that I generate, I put into an
separate Access file which I eventually move to a server location that
the particular client and I share access to.
Currently there are 10 clients, but this is expected to go up
considerable, and each of the precalculated data sets requires 25-30
minutes to generate (200+ Mb files). I have a form which does a batch
process to generate all of the files locally and then I go through the
files and manually post them up to the servers spaces.
What I'd like to do is include the manual moving of the files to the
server locations in the batch process however I have found that it is
quicker to do it manually because the code has to wait until it has
sent each file before continuing with the next batch item.
Anyone know how to just start transferring and move on to the next
record to start precalculating the next file before the file transfer
is complete?
The file transfer can take 30+ minutes depending on the location (some
are in Asia) and so its entirely possible that multiple files could be
transferring at the same time (when I do this manually this obviously
happens because all the file are ready to go).
I have been playing around with the following code:
Sub transfer(str_file As String, str_dest_location As String)
Dim fs As Variant
Set fs = CreateObject("Scripting.FileSystemObject")
fs.MoveFile str_file, str_dest_location
Set fs = Nothing
End Sub
Sub call_test()
Dim a As String
Dim b As String
a = "\\xxx-fs01\ClientLocation\TESTFile.xls"
b = "C:\Temp\TEST\TESTFile.xls"
Call transfer(b, a)
End Sub
My thinking is that I could either:
- cause another form, instance of access or an excel application to
load for each transfer which independently initiates the transfer
although I'm not convinced that pause in the code would go away.
- use a different method which I'm unaware of
Any ideas?

[Mat N]

Ooo...that's good. Bat file is a lot simpler. I'll give it a go and let
you know thanks.

Mat N

Tom van Stiphout wrote:

On 14 Oct 2006 03:58:42 -0700, "Mat N" <Ma******@gmail.comwrote:

Indeed a second instance of Access would do it.
So would a simple batch file spawned by the Shell command.

-Tom.

Hello access friends,

(Originally posted on access.formsprogramming which I think was the
wrong place)

I'm precalculating a bunch of data for my clients as per a profile I
have for each of them. Each data set that I generate, I put into an
separate Access file which I eventually move to a server location that
the particular client and I share access to.
Currently there are 10 clients, but this is expected to go up
considerable, and each of the precalculated data sets requires 25-30
minutes to generate (200+ Mb files). I have a form which does a batch
process to generate all of the files locally and then I go through the
files and manually post them up to the servers spaces.
What I'd like to do is include the manual moving of the files to the
server locations in the batch process however I have found that it is
quicker to do it manually because the code has to wait until it has
sent each file before continuing with the next batch item.
Anyone know how to just start transferring and move on to the next
record to start precalculating the next file before the file transfer
is complete?
The file transfer can take 30+ minutes depending on the location (some
are in Asia) and so its entirely possible that multiple files could be
transferring at the same time (when I do this manually this obviously
happens because all the file are ready to go).
I have been playing around with the following code:
Sub transfer(str_file As String, str_dest_location As String)
Dim fs As Variant
Set fs = CreateObject("Scripting.FileSystemObject")
fs.MoveFile str_file, str_dest_location
Set fs = Nothing
End Sub
Sub call_test()
Dim a As String
Dim b As String
a = "\\xxx-fs01\ClientLocation\TESTFile.xls"
b = "C:\Temp\TEST\TESTFile.xls"
Call transfer(b, a)
End Sub
My thinking is that I could either:
- cause another form, instance of access or an excel application to
load for each transfer which independently initiates the transfer
although I'm not convinced that pause in the code would go away.
- use a different method which I'm unaware of
Any ideas?

[Mat N]

Ooo...that's good. Bat file is a lot simpler. I'll give it a go and let
you know thanks.

Mat N

Tom van Stiphout wrote:

On 14 Oct 2006 03:58:42 -0700, "Mat N" <Ma******@gmail.comwrote:

Indeed a second instance of Access would do it.
So would a simple batch file spawned by the Shell command.

-Tom.

Hello access friends,

(Originally posted on access.formsprogramming which I think was the
wrong place)

I'm precalculating a bunch of data for my clients as per a profile I
have for each of them. Each data set that I generate, I put into an
separate Access file which I eventually move to a server location that
the particular client and I share access to.
Currently there are 10 clients, but this is expected to go up
considerable, and each of the precalculated data sets requires 25-30
minutes to generate (200+ Mb files). I have a form which does a batch
process to generate all of the files locally and then I go through the
files and manually post them up to the servers spaces.
What I'd like to do is include the manual moving of the files to the
server locations in the batch process however I have found that it is
quicker to do it manually because the code has to wait until it has
sent each file before continuing with the next batch item.
Anyone know how to just start transferring and move on to the next
record to start precalculating the next file before the file transfer
is complete?
The file transfer can take 30+ minutes depending on the location (some
are in Asia) and so its entirely possible that multiple files could be
transferring at the same time (when I do this manually this obviously
happens because all the file are ready to go).
I have been playing around with the following code:
Sub transfer(str_file As String, str_dest_location As String)
Dim fs As Variant
Set fs = CreateObject("Scripting.FileSystemObject")
fs.MoveFile str_file, str_dest_location
Set fs = Nothing
End Sub
Sub call_test()
Dim a As String
Dim b As String
a = "\\xxx-fs01\ClientLocation\TESTFile.xls"
b = "C:\Temp\TEST\TESTFile.xls"
Call transfer(b, a)
End Sub
My thinking is that I could either:
- cause another form, instance of access or an excel application to
load for each transfer which independently initiates the transfer
although I'm not convinced that pause in the code would go away.
- use a different method which I'm unaware of
Any ideas?

[Mat N]

I'm a bit rusty on the BAT file syntax so I started there and
unfortunately I don't think it is possible to refer to a server
location without a drive letter assigned to it and I don't think you
can assign a drive letter on the fly in the BAT file either (although
this is perhaps possible in VBA?).

Unfortunately as each of the server locations is a truly different
server the simplest solution would require a different drive letter to
each one however with potentially 20+ locations this would be difficult
as I might run out of letters. I would also have to rely on the IT
administrators preserving these mappings whenever they send an update
which is fairly risky.

Assigning drive letters on the fly could also be problematic because it
maybe possible for the next file transfer to initiate before the last
has completed.

Let me know if I'm misguided. I'm going to give the seperate instance
of access or excel idea a go.

Thanks

Mat N wrote:

Ooo...that's good. Bat file is a lot simpler. I'll give it a go and let
you know thanks.

Mat N

Tom van Stiphout wrote:

On 14 Oct 2006 03:58:42 -0700, "Mat N" <Ma******@gmail.comwrote:

Indeed a second instance of Access would do it.
So would a simple batch file spawned by the Shell command.

-Tom.

>Hello access friends,
>
>(Originally posted on access.formsprogramming which I think was the
>wrong place)
>
>I'm precalculating a bunch of data for my clients as per a profile I
>have for each of them. Each data set that I generate, I put into an
>separate Access file which I eventually move to a server location that
>the particular client and I share access to.
>
>
>Currently there are 10 clients, but this is expected to go up
>considerable, and each of the precalculated data sets requires 25-30
>minutes to generate (200+ Mb files). I have a form which does a batch
>process to generate all of the files locally and then I go through the
>files and manually post them up to the servers spaces.
>
>
>What I'd like to do is include the manual moving of the files to the
>server locations in the batch process however I have found that it is
>quicker to do it manually because the code has to wait until it has
>sent each file before continuing with the next batch item.
>
>
>Anyone know how to just start transferring and move on to the next
>record to start precalculating the next file before the file transfer
>is complete?
>
>
>The file transfer can take 30+ minutes depending on the location (some
>are in Asia) and so its entirely possible that multiple files could be
>transferring at the same time (when I do this manually this obviously
>happens because all the file are ready to go).
>
>
>I have been playing around with the following code:
>
>
>Sub transfer(str_file As String, str_dest_location As String)
>Dim fs As Variant
>
>
>Set fs = CreateObject("Scripting.FileSystemObject")
>fs.MoveFile str_file, str_dest_location
>Set fs = Nothing
>
>
>End Sub
>
>
>Sub call_test()
>Dim a As String
>Dim b As String
>
>
>a = "\\xxx-fs01\ClientLocation\TESTFile.xls"
>b = "C:\Temp\TEST\TESTFile.xls"
>
>
>Call transfer(b, a)
>
>
>End Sub
>
>
>My thinking is that I could either:
>- cause another form, instance of access or an excel application to
>load for each transfer which independently initiates the transfer
>although I'm not convinced that pause in the code would go away.
>- use a different method which I'm unaware of
>
>
>Any ideas?

[Per]

Mat,

You can assign drive letters on the fly in BAT commands using the net
use command. But you argue that this is not ideal.

For copying files, you can use the xcopy command, which allows use of
UNC paths for source or destination.

Hope this helps.

/Per

Mat N wrote:

I'm a bit rusty on the BAT file syntax so I started there and
unfortunately I don't think it is possible to refer to a server
location without a drive letter assigned to it and I don't think you
can assign a drive letter on the fly in the BAT file either (although
this is perhaps possible in VBA?).

Unfortunately as each of the server locations is a truly different
server the simplest solution would require a different drive letter to
each one however with potentially 20+ locations this would be difficult
as I might run out of letters. I would also have to rely on the IT
administrators preserving these mappings whenever they send an update
which is fairly risky.

Assigning drive letters on the fly could also be problematic because it
maybe possible for the next file transfer to initiate before the last
has completed.

Let me know if I'm misguided. I'm going to give the seperate instance
of access or excel idea a go.

Thanks

Mat N wrote:

Ooo...that's good. Bat file is a lot simpler. I'll give it a go and let
you know thanks.

Mat N

Tom van Stiphout wrote:

On 14 Oct 2006 03:58:42 -0700, "Mat N" <Ma******@gmail.comwrote:
>
Indeed a second instance of Access would do it.
So would a simple batch file spawned by the Shell command.
>
-Tom.
>
>
Hello access friends,

(Originally posted on access.formsprogramming which I think was the
wrong place)

I'm precalculating a bunch of data for my clients as per a profile I
have for each of them. Each data set that I generate, I put into an
separate Access file which I eventually move to a server location that
the particular client and I share access to.


Currently there are 10 clients, but this is expected to go up
considerable, and each of the precalculated data sets requires 25-30
minutes to generate (200+ Mb files). I have a form which does a batch
process to generate all of the files locally and then I go through the
files and manually post them up to the servers spaces.


What I'd like to do is include the manual moving of the files to the
server locations in the batch process however I have found that it is
quicker to do it manually because the code has to wait until it has
sent each file before continuing with the next batch item.


Anyone know how to just start transferring and move on to the next
record to start precalculating the next file before the file transfer
is complete?


The file transfer can take 30+ minutes depending on the location (some
are in Asia) and so its entirely possible that multiple files could be
transferring at the same time (when I do this manually this obviously
happens because all the file are ready to go).


I have been playing around with the following code:


Sub transfer(str_file As String, str_dest_location As String)
Dim fs As Variant


Set fs = CreateObject("Scripting.FileSystemObject")
fs.MoveFile str_file, str_dest_location
Set fs = Nothing


End Sub


Sub call_test()
Dim a As String
Dim b As String


a = "\\xxx-fs01\ClientLocation\TESTFile.xls"
b = "C:\Temp\TEST\TESTFile.xls"


Call transfer(b, a)


End Sub


My thinking is that I could either:
- cause another form, instance of access or an excel application to
load for each transfer which independently initiates the transfer
although I'm not convinced that pause in the code would go away.
- use a different method which I'm unaware of


Any ideas?

[Mat N]

Per,

This has been a great help thanks.

"xcopy" does indeed work with UNC Paths however I haven't been able to
get it to work with Paths which include a space. I tried quotation
marks around the pathname but without success.

Fortunately "net use" does seem to work with Paths including space so
long as you do use quotation marks. Its not ideal because I expect to
be transferring multiple files at the same time to different location
which means that I will have to create code which manages the mapping
of the drives to avoid running out of drives to map and/or
disconnecting mapped drives which should be. Nonetheless I think a net
use / xcopy combination is the best answer.

In case anyone else comes across this post I'm planning to use the
follow additional items which I hope will help avoid mapping too many
drives or removing drives which are supposed to be mapped permenantly:
Drives collection
Driveexists method
DriveLetter property

This are all in the access help.

Multitasking here we come.

Mat N

Per wrote:

Mat,

You can assign drive letters on the fly in BAT commands using the net
use command. But you argue that this is not ideal.

For copying files, you can use the xcopy command, which allows use of
UNC paths for source or destination.

Hope this helps.

/Per

Mat N wrote:

I'm a bit rusty on the BAT file syntax so I started there and
unfortunately I don't think it is possible to refer to a server
location without a drive letter assigned to it and I don't think you
can assign a drive letter on the fly in the BAT file either (although
this is perhaps possible in VBA?).

Unfortunately as each of the server locations is a truly different
server the simplest solution would require a different drive letter to
each one however with potentially 20+ locations this would be difficult
as I might run out of letters. I would also have to rely on the IT
administrators preserving these mappings whenever they send an update
which is fairly risky.

Assigning drive letters on the fly could also be problematic because it
maybe possible for the next file transfer to initiate before the last
has completed.

Let me know if I'm misguided. I'm going to give the seperate instance
of access or excel idea a go.

Thanks

Mat N wrote:

Ooo...that's good. Bat file is a lot simpler. I'll give it a go and let
you know thanks.
>
Mat N
>
Tom van Stiphout wrote:
On 14 Oct 2006 03:58:42 -0700, "Mat N" <Ma******@gmail.comwrote:

Indeed a second instance of Access would do it.
So would a simple batch file spawned by the Shell command.

-Tom.


>Hello access friends,
>
>(Originally posted on access.formsprogramming which I think was the
>wrong place)
>
>I'm precalculating a bunch of data for my clients as per a profile I
>have for each of them. Each data set that I generate, I put into an
>separate Access file which I eventually move to a server location that
>the particular client and I share access to.
>
>
>Currently there are 10 clients, but this is expected to go up
>considerable, and each of the precalculated data sets requires 25-30
>minutes to generate (200+ Mb files). I have a form which does a batch
>process to generate all of the files locally and then I go through the
>files and manually post them up to the servers spaces.
>
>
>What I'd like to do is include the manual moving of the files to the
>server locations in the batch process however I have found that it is
>quicker to do it manually because the code has to wait until it has
>sent each file before continuing with the next batch item.
>
>
>Anyone know how to just start transferring and move on to the next
>record to start precalculating the next file before the file transfer
>is complete?
>
>
>The file transfer can take 30+ minutes depending on the location (some
>are in Asia) and so its entirely possible that multiple files could be
>transferring at the same time (when I do this manually this obviously
>happens because all the file are ready to go).
>
>
>I have been playing around with the following code:
>
>
>Sub transfer(str_file As String, str_dest_location As String)
>Dim fs As Variant
>
>
>Set fs = CreateObject("Scripting.FileSystemObject")
>fs.MoveFile str_file, str_dest_location
>Set fs = Nothing
>
>
>End Sub
>
>
>Sub call_test()
>Dim a As String
>Dim b As String
>
>
>a = "\\xxx-fs01\ClientLocation\TESTFile.xls"
>b = "C:\Temp\TEST\TESTFile.xls"
>
>
>Call transfer(b, a)
>
>
>End Sub
>
>
>My thinking is that I could either:
>- cause another form, instance of access or an excel application to
>load for each transfer which independently initiates the transfer
>although I'm not convinced that pause in the code would go away.
>- use a different method which I'm unaware of
>
>
>Any ideas?