select and copy files

I have a table that has following fields: 1) account number 2)assiciated file 
name and 3) description of file 4) file path.  Filenames are formatted as 
account number with 2 digit alphanumeric code following (i.e. 12345a2, 
67890b1)

table has approximately 300,000 records each associated with one of 130,000 
unique account numbers.

Goal is to select and copy to new destination only those files associated 
with a subset (approc 100,000) of 130,000 unique account numbers.

Inversely, I could copy all 300,000 files then select and delete those 
associated with the 30,000 account numbers.

any assistance would be greatly appreciated.  

Thanks in advance.  

 
0
Utf
4/25/2007 8:30:02 PM
access 16762 articles. 3 followers. Follow

10 Replies
1114 Views

Similar Articles

[PageSpeed] 13

This doesn't appear to be a difficult problem, except for the fact that you 
are dealing with such a large number of records and files. Clearly, you need 
to proceed carefully. Here are some questions for you:

1.    Do you have a list of the 100,000 or the 30,000 unique account 
numbers?

2.    Alternatively, do you have a method for creating the 100,000 or 30,000 
unique account numbers?  For example, does the subset consists of all 
account numbers beginning with "123"?

3.    Do you want to end up with 270,000 files in the new location and 
30,000 files in the original location?  To have 270,000 files in one 
location seems unwieldy but no doubt you have your reasons. I'm not sure if 
there is a limit for the number of files in a single folder (other than the 
root folder).

4.    Do you want to move the 270,000 records from the current table to an 
archive table?

5.    Do you want to update the 270,000 records in the archive table so that 
the FilePath field is updated to reflect the new file location?

6.    Do you have any experience using VBA (Visual Basic for Applications)?

Regards
Geoff




"rjreilly" <rjreilly@discussions.microsoft.com> wrote in message 
news:3001EBAC-34EA-4976-8C03-E6CD0FDC5349@microsoft.com...
>I have a table that has following fields: 1) account number 2)assiciated 
>file
> name and 3) description of file 4) file path.  Filenames are formatted as
> account number with 2 digit alphanumeric code following (i.e. 12345a2,
> 67890b1)
>
> table has approximately 300,000 records each associated with one of 
> 130,000
> unique account numbers.
>
> Goal is to select and copy to new destination only those files associated
> with a subset (approc 100,000) of 130,000 unique account numbers.
>
> Inversely, I could copy all 300,000 files then select and delete those
> associated with the 30,000 account numbers.
>
> any assistance would be greatly appreciated.
>
> Thanks in advance.
>
> 


0
GeoffG
4/26/2007 7:36:56 AM
Thanks for your assistance, GeoffG.

In answer to your questions:

1) I have a list of the 100,000 unique accounts and can easily create the 
list of the 30,000 unique exceptions.
2) no the subset is pretty much a random selection of accounts that met 
certain criteria not at all representative in the account number.
3) I want to keep all 300,000 files in the origional location and only the 
205,000 files associated witht the 100,000 unique accounts in the target 
location.
4) the problem is rerally just selection and copying files.  I will be 
creating a new db for the 100,000 unique records (not only this one table) 
and have the export steps already saved. 
5) nice thought but i'm not sure of the path the recipient of the db and 
files relating to the 100,000 unique accounts will assign.  Have to let 
he/she worry about this.
6) Limited VBA experience - recording macros but Iam a fast learner and have 
capable basic querry, forms and reporting skills.  Less VB experience (as 
opposed to VBA).

"GeoffG" wrote:

> This doesn't appear to be a difficult problem, except for the fact that you 
> are dealing with such a large number of records and files. Clearly, you need 
> to proceed carefully. Here are some questions for you:
> 
> 1.    Do you have a list of the 100,000 or the 30,000 unique account 
> numbers?
> 
> 2.    Alternatively, do you have a method for creating the 100,000 or 30,000 
> unique account numbers?  For example, does the subset consists of all 
> account numbers beginning with "123"?
> 
> 3.    Do you want to end up with 270,000 files in the new location and 
> 30,000 files in the original location?  To have 270,000 files in one 
> location seems unwieldy but no doubt you have your reasons. I'm not sure if 
> there is a limit for the number of files in a single folder (other than the 
> root folder).
> 
> 4.    Do you want to move the 270,000 records from the current table to an 
> archive table?
> 
> 5.    Do you want to update the 270,000 records in the archive table so that 
> the FilePath field is updated to reflect the new file location?
> 
> 6.    Do you have any experience using VBA (Visual Basic for Applications)?
> 
> Regards
> Geoff
> 
> 
> 
> 
> "rjreilly" <rjreilly@discussions.microsoft.com> wrote in message 
> news:3001EBAC-34EA-4976-8C03-E6CD0FDC5349@microsoft.com...
> >I have a table that has following fields: 1) account number 2)assiciated 
> >file
> > name and 3) description of file 4) file path.  Filenames are formatted as
> > account number with 2 digit alphanumeric code following (i.e. 12345a2,
> > 67890b1)
> >
> > table has approximately 300,000 records each associated with one of 
> > 130,000
> > unique account numbers.
> >
> > Goal is to select and copy to new destination only those files associated
> > with a subset (approc 100,000) of 130,000 unique account numbers.
> >
> > Inversely, I could copy all 300,000 files then select and delete those
> > associated with the 30,000 account numbers.
> >
> > any assistance would be greatly appreciated.
> >
> > Thanks in advance.
> >
> > 
> 
> 
> 
0
Utf
4/26/2007 1:14:09 PM
Could you please provide some more detailed information by answering 
questions (a) to (g) below?

> 1) I have a list of the 100,000 unique accounts and can easily
> create the list of the 30,000 unique exceptions.

Very good!  The list of 100,000 should be fine.

To give me clues about what VBA code you need:

(a)    How is the list stored please?

For example, is the list in an Access table?  What is the name of the table? 
Are there 100,000 records in this table, with each record containing one 
unique account number?  What is the name of the field containing the unique 
account number?

(b)    Alternatively, is the list in a text file?  Is there one unique 
account number on each line of the text file?

> 3) I want to keep all 300,000 files in the origional location and
> only the 205,000 files associated witht the 100,000 unique
> accounts in the target location.

OK, let me be quite sure I've got this right as it's crucial. You currently 
have 300,000 files in one location and you want keep all of them in that 
location. You want to copy (ie not move) the 205,000 of those files that are 
associated with the 100,000 unique account numbers (that are in the list) to 
a new location. This means that, when the copying process has finished, you 
will be storing a total of 505,000 files.

(c)    Is that right please?

> 4) the problem is rerally just selection and copying files.
> I will be creating a new db for the 100,000 unique records
> (not only this one table) and have the export steps already
> saved.

Given what you've said so far, selecting and copying files should be a 
cinch. You have the source file's path in one field and the source file's 
filename in another field. So:

(d)   What is the table name in which these fields appear?

(e)   What are these field names?

(f)    Will the source file's PathName be a concatenation these two fields? 
For example:

Source File Path Name = [PathField]\[FileNameField]

(g)    Will all the files be copied to the same target folder?  Please give 
the full target path.

> 6) Limited VBA experience - recording macros but Iam a
> fast learner and have capable basic querry, forms and
> reporting skills.  Less VB experience (as opposed to VBA).

I'm happy to walk you through the process and post the code you need.

Regards
Geoff



"rjreilly" <rjreilly@discussions.microsoft.com> wrote in message 
news:4B0238F4-B079-4A2C-BBEF-6D5264D34977@microsoft.com...
> Thanks for your assistance, GeoffG.
>
> In answer to your questions:
>
> 1) I have a list of the 100,000 unique accounts and can easily create the
> list of the 30,000 unique exceptions.
> 2) no the subset is pretty much a random selection of accounts that met
> certain criteria not at all representative in the account number.
> 3) I want to keep all 300,000 files in the origional location and only the
> 205,000 files associated witht the 100,000 unique accounts in the target
> location.
> 4) the problem is rerally just selection and copying files.  I will be
> creating a new db for the 100,000 unique records (not only this one table)
> and have the export steps already saved.
> 5) nice thought but i'm not sure of the path the recipient of the db and
> files relating to the 100,000 unique accounts will assign.  Have to let
> he/she worry about this.
> 6) Limited VBA experience - recording macros but Iam a fast learner and 
> have
> capable basic querry, forms and reporting skills.  Less VB experience (as
> opposed to VBA).
>
> "GeoffG" wrote:
>
>> This doesn't appear to be a difficult problem, except for the fact that 
>> you
>> are dealing with such a large number of records and files. Clearly, you 
>> need
>> to proceed carefully. Here are some questions for you:
>>
>> 1.    Do you have a list of the 100,000 or the 30,000 unique account
>> numbers?
>>
>> 2.    Alternatively, do you have a method for creating the 100,000 or 
>> 30,000
>> unique account numbers?  For example, does the subset consists of all
>> account numbers beginning with "123"?
>>
>> 3.    Do you want to end up with 270,000 files in the new location and
>> 30,000 files in the original location?  To have 270,000 files in one
>> location seems unwieldy but no doubt you have your reasons. I'm not sure 
>> if
>> there is a limit for the number of files in a single folder (other than 
>> the
>> root folder).
>>
>> 4.    Do you want to move the 270,000 records from the current table to 
>> an
>> archive table?
>>
>> 5.    Do you want to update the 270,000 records in the archive table so 
>> that
>> the FilePath field is updated to reflect the new file location?
>>
>> 6.    Do you have any experience using VBA (Visual Basic for 
>> Applications)?
>>
>> Regards
>> Geoff
>>
>>
>>
>>
>> "rjreilly" <rjreilly@discussions.microsoft.com> wrote in message
>> news:3001EBAC-34EA-4976-8C03-E6CD0FDC5349@microsoft.com...
>> >I have a table that has following fields: 1) account number 2)assiciated
>> >file
>> > name and 3) description of file 4) file path.  Filenames are formatted 
>> > as
>> > account number with 2 digit alphanumeric code following (i.e. 12345a2,
>> > 67890b1)
>> >
>> > table has approximately 300,000 records each associated with one of
>> > 130,000
>> > unique account numbers.
>> >
>> > Goal is to select and copy to new destination only those files 
>> > associated
>> > with a subset (approc 100,000) of 130,000 unique account numbers.
>> >
>> > Inversely, I could copy all 300,000 files then select and delete those
>> > associated with the 30,000 account numbers.
>> >
>> > any assistance would be greatly appreciated.
>> >
>> > Thanks in advance.
>> >
>> >
>>
>>
>> 


0
GeoffG
4/26/2007 10:56:09 PM
Just a few more points......

In the event that something goes wrong during the coping process, it would 
be sensible for the copying program to keep a record of which files have 
been copied and which files cannot be found (if any). Then, if there's a 
power outtage, or disc full error, or some other error, it will be possible 
to restart the program where it left off.

Therefore, could you create Yes/No fields for "Copied" and "FileNotFound" in 
either the table you originally mentioned or, if it exists, in the table of 
100,000 unique numbers?

Also, could you please confirm that these are tables in an Access database?

Regards
Geoff





"rjreilly" <rjreilly@discussions.microsoft.com> wrote in message 
news:4B0238F4-B079-4A2C-BBEF-6D5264D34977@microsoft.com...
> Thanks for your assistance, GeoffG.
>
> In answer to your questions:
>
> 1) I have a list of the 100,000 unique accounts and can easily create the
> list of the 30,000 unique exceptions.
> 2) no the subset is pretty much a random selection of accounts that met
> certain criteria not at all representative in the account number.
> 3) I want to keep all 300,000 files in the origional location and only the
> 205,000 files associated witht the 100,000 unique accounts in the target
> location.
> 4) the problem is rerally just selection and copying files.  I will be
> creating a new db for the 100,000 unique records (not only this one table)
> and have the export steps already saved.
> 5) nice thought but i'm not sure of the path the recipient of the db and
> files relating to the 100,000 unique accounts will assign.  Have to let
> he/she worry about this.
> 6) Limited VBA experience - recording macros but Iam a fast learner and 
> have
> capable basic querry, forms and reporting skills.  Less VB experience (as
> opposed to VBA).
>
> "GeoffG" wrote:
>
>> This doesn't appear to be a difficult problem, except for the fact that 
>> you
>> are dealing with such a large number of records and files. Clearly, you 
>> need
>> to proceed carefully. Here are some questions for you:
>>
>> 1.    Do you have a list of the 100,000 or the 30,000 unique account
>> numbers?
>>
>> 2.    Alternatively, do you have a method for creating the 100,000 or 
>> 30,000
>> unique account numbers?  For example, does the subset consists of all
>> account numbers beginning with "123"?
>>
>> 3.    Do you want to end up with 270,000 files in the new location and
>> 30,000 files in the original location?  To have 270,000 files in one
>> location seems unwieldy but no doubt you have your reasons. I'm not sure 
>> if
>> there is a limit for the number of files in a single folder (other than 
>> the
>> root folder).
>>
>> 4.    Do you want to move the 270,000 records from the current table to 
>> an
>> archive table?
>>
>> 5.    Do you want to update the 270,000 records in the archive table so 
>> that
>> the FilePath field is updated to reflect the new file location?
>>
>> 6.    Do you have any experience using VBA (Visual Basic for 
>> Applications)?
>>
>> Regards
>> Geoff
>>
>>
>>
>>
>> "rjreilly" <rjreilly@discussions.microsoft.com> wrote in message
>> news:3001EBAC-34EA-4976-8C03-E6CD0FDC5349@microsoft.com...
>> >I have a table that has following fields: 1) account number 2)assiciated
>> >file
>> > name and 3) description of file 4) file path.  Filenames are formatted 
>> > as
>> > account number with 2 digit alphanumeric code following (i.e. 12345a2,
>> > 67890b1)
>> >
>> > table has approximately 300,000 records each associated with one of
>> > 130,000
>> > unique account numbers.
>> >
>> > Goal is to select and copy to new destination only those files 
>> > associated
>> > with a subset (approc 100,000) of 130,000 unique account numbers.
>> >
>> > Inversely, I could copy all 300,000 files then select and delete those
>> > associated with the 30,000 account numbers.
>> >
>> > any assistance would be greatly appreciated.
>> >
>> > Thanks in advance.
>> >
>> >
>>
>>
>> 


0
GeoffG
4/27/2007 10:20:18 AM
a) everything in access 2007 (i suppose i coulod convert this to just about 
anything).  

Today, from the larger database will create a second relating to only the 
100,000 unique accounts. 

There are/will be 11 tables but i believe all we will be concerned with is 
table "Images".  Only the 205,000 filenames i am interested in COPYING from 
source to destination directories will be contained in table "Images" I will 
create today. (There will be 205,000 records each assiciated with oner of 
100,000 unique account numbers but I think this is beside the point) 

b) i think we have this covered.

c) You are correct I wish to retain all 300,000 image files in source 
directory and copy only 205,000 of them to the destination directory  

d) Table "Images"

e) Table Images has the following fields: 1) acct 2) ImgName 3)ImgType 
4)RecID 5)Directory and 6)Path (path is combination of imagename and 
directory formatted as hyperlink which allows us to view file(s))

f) I believe field "path" is all we will need field "directory" = J:/ and 
field "filename" = name.tif.  Field "path" is a essentially the full path and 
filename (i.e. J:/name.tif)  

g) Yes all files to be coppied to single directory (no sub-directories).  
Destination is external usb drive.  We can call it almost anything - assume 
K:/

h) 



"GeoffG" wrote:

> Could you please provide some more detailed information by answering 
> questions (a) to (g) below?
> 
> > 1) I have a list of the 100,000 unique accounts and can easily
> > create the list of the 30,000 unique exceptions.
> 
> Very good!  The list of 100,000 should be fine.
> 
> To give me clues about what VBA code you need:
> 
> (a)    How is the list stored please?
> 
> For example, is the list in an Access table?  What is the name of the table? 
> Are there 100,000 records in this table, with each record containing one 
> unique account number?  What is the name of the field containing the unique 
> account number?
> 
> (b)    Alternatively, is the list in a text file?  Is there one unique 
> account number on each line of the text file?
> 
> > 3) I want to keep all 300,000 files in the origional location and
> > only the 205,000 files associated witht the 100,000 unique
> > accounts in the target location.
> 
> OK, let me be quite sure I've got this right as it's crucial. You currently 
> have 300,000 files in one location and you want keep all of them in that 
> location. You want to copy (ie not move) the 205,000 of those files that are 
> associated with the 100,000 unique account numbers (that are in the list) to 
> a new location. This means that, when the copying process has finished, you 
> will be storing a total of 505,000 files.
> 
> (c)    Is that right please?
> 
> > 4) the problem is rerally just selection and copying files.
> > I will be creating a new db for the 100,000 unique records
> > (not only this one table) and have the export steps already
> > saved.
> 
> Given what you've said so far, selecting and copying files should be a 
> cinch. You have the source file's path in one field and the source file's 
> filename in another field. So:
> 
> (d)   What is the table name in which these fields appear?
> 
> (e)   What are these field names?
> 
> (f)    Will the source file's PathName be a concatenation these two fields? 
> For example:
> 
> Source File Path Name = [PathField]\[FileNameField]
> 
> (g)    Will all the files be copied to the same target folder?  Please give 
> the full target path.
> 
> > 6) Limited VBA experience - recording macros but Iam a
> > fast learner and have capable basic querry, forms and
> > reporting skills.  Less VB experience (as opposed to VBA).
> 
> I'm happy to walk you through the process and post the code you need.
> 
> Regards
> Geoff
> 
> 
> 
> "rjreilly" <rjreilly@discussions.microsoft.com> wrote in message 
> news:4B0238F4-B079-4A2C-BBEF-6D5264D34977@microsoft.com...
> > Thanks for your assistance, GeoffG.
> >
> > In answer to your questions:
> >
> > 1) I have a list of the 100,000 unique accounts and can easily create the
> > list of the 30,000 unique exceptions.
> > 2) no the subset is pretty much a random selection of accounts that met
> > certain criteria not at all representative in the account number.
> > 3) I want to keep all 300,000 files in the origional location and only the
> > 205,000 files associated witht the 100,000 unique accounts in the target
> > location.
> > 4) the problem is rerally just selection and copying files.  I will be
> > creating a new db for the 100,000 unique records (not only this one table)
> > and have the export steps already saved.
> > 5) nice thought but i'm not sure of the path the recipient of the db and
> > files relating to the 100,000 unique accounts will assign.  Have to let
> > he/she worry about this.
> > 6) Limited VBA experience - recording macros but Iam a fast learner and 
> > have
> > capable basic querry, forms and reporting skills.  Less VB experience (as
> > opposed to VBA).
> >
> > "GeoffG" wrote:
> >
> >> This doesn't appear to be a difficult problem, except for the fact that 
> >> you
> >> are dealing with such a large number of records and files. Clearly, you 
> >> need
> >> to proceed carefully. Here are some questions for you:
> >>
> >> 1.    Do you have a list of the 100,000 or the 30,000 unique account
> >> numbers?
> >>
> >> 2.    Alternatively, do you have a method for creating the 100,000 or 
> >> 30,000
> >> unique account numbers?  For example, does the subset consists of all
> >> account numbers beginning with "123"?
> >>
> >> 3.    Do you want to end up with 270,000 files in the new location and
> >> 30,000 files in the original location?  To have 270,000 files in one
> >> location seems unwieldy but no doubt you have your reasons. I'm not sure 
> >> if
> >> there is a limit for the number of files in a single folder (other than 
> >> the
> >> root folder).
> >>
> >> 4.    Do you want to move the 270,000 records from the current table to 
> >> an
> >> archive table?
> >>
> >> 5.    Do you want to update the 270,000 records in the archive table so 
> >> that
> >> the FilePath field is updated to reflect the new file location?
> >>
> >> 6.    Do you have any experience using VBA (Visual Basic for 
> >> Applications)?
> >>
> >> Regards
> >> Geoff
> >>
> >>
> >>
> >>
> >> "rjreilly" <rjreilly@discussions.microsoft.com> wrote in message
> >> news:3001EBAC-34EA-4976-8C03-E6CD0FDC5349@microsoft.com...
> >> >I have a table that has following fields: 1) account number 2)assiciated
> >> >file
> >> > name and 3) description of file 4) file path.  Filenames are formatted 
> >> > as
> >> > account number with 2 digit alphanumeric code following (i.e. 12345a2,
> >> > 67890b1)
> >> >
> >> > table has approximately 300,000 records each associated with one of
> >> > 130,000
> >> > unique account numbers.
> >> >
> >> > Goal is to select and copy to new destination only those files 
> >> > associated
> >> > with a subset (approc 100,000) of 130,000 unique account numbers.
> >> >
> >> > Inversely, I could copy all 300,000 files then select and delete those
> >> > associated with the 30,000 account numbers.
> >> >
> >> > any assistance would be greatly appreciated.
> >> >
> >> > Thanks in advance.
> >> >
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
Utf
4/27/2007 12:02:08 PM
I like the thought and will be happy to add Yes/No fields for "Copied" and 
"FileNotFound" to table Images.

I am running access 2007

GgeoffG, I really appreciate your help!  

"GeoffG" wrote:

> Just a few more points......
> 
> In the event that something goes wrong during the coping process, it would 
> be sensible for the copying program to keep a record of which files have 
> been copied and which files cannot be found (if any). Then, if there's a 
> power outtage, or disc full error, or some other error, it will be possible 
> to restart the program where it left off.
> 
> Therefore, could you create Yes/No fields for "Copied" and "FileNotFound" in 
> either the table you originally mentioned or, if it exists, in the table of 
> 100,000 unique numbers?
> 
> Also, could you please confirm that these are tables in an Access database?
> 
> Regards
> Geoff
> 
> 
> 
> 
> 
> "rjreilly" <rjreilly@discussions.microsoft.com> wrote in message 
> news:4B0238F4-B079-4A2C-BBEF-6D5264D34977@microsoft.com...
> > Thanks for your assistance, GeoffG.
> >
> > In answer to your questions:
> >
> > 1) I have a list of the 100,000 unique accounts and can easily create the
> > list of the 30,000 unique exceptions.
> > 2) no the subset is pretty much a random selection of accounts that met
> > certain criteria not at all representative in the account number.
> > 3) I want to keep all 300,000 files in the origional location and only the
> > 205,000 files associated witht the 100,000 unique accounts in the target
> > location.
> > 4) the problem is rerally just selection and copying files.  I will be
> > creating a new db for the 100,000 unique records (not only this one table)
> > and have the export steps already saved.
> > 5) nice thought but i'm not sure of the path the recipient of the db and
> > files relating to the 100,000 unique accounts will assign.  Have to let
> > he/she worry about this.
> > 6) Limited VBA experience - recording macros but Iam a fast learner and 
> > have
> > capable basic querry, forms and reporting skills.  Less VB experience (as
> > opposed to VBA).
> >
> > "GeoffG" wrote:
> >
> >> This doesn't appear to be a difficult problem, except for the fact that 
> >> you
> >> are dealing with such a large number of records and files. Clearly, you 
> >> need
> >> to proceed carefully. Here are some questions for you:
> >>
> >> 1.    Do you have a list of the 100,000 or the 30,000 unique account
> >> numbers?
> >>
> >> 2.    Alternatively, do you have a method for creating the 100,000 or 
> >> 30,000
> >> unique account numbers?  For example, does the subset consists of all
> >> account numbers beginning with "123"?
> >>
> >> 3.    Do you want to end up with 270,000 files in the new location and
> >> 30,000 files in the original location?  To have 270,000 files in one
> >> location seems unwieldy but no doubt you have your reasons. I'm not sure 
> >> if
> >> there is a limit for the number of files in a single folder (other than 
> >> the
> >> root folder).
> >>
> >> 4.    Do you want to move the 270,000 records from the current table to 
> >> an
> >> archive table?
> >>
> >> 5.    Do you want to update the 270,000 records in the archive table so 
> >> that
> >> the FilePath field is updated to reflect the new file location?
> >>
> >> 6.    Do you have any experience using VBA (Visual Basic for 
> >> Applications)?
> >>
> >> Regards
> >> Geoff
> >>
> >>
> >>
> >>
> >> "rjreilly" <rjreilly@discussions.microsoft.com> wrote in message
> >> news:3001EBAC-34EA-4976-8C03-E6CD0FDC5349@microsoft.com...
> >> >I have a table that has following fields: 1) account number 2)assiciated
> >> >file
> >> > name and 3) description of file 4) file path.  Filenames are formatted 
> >> > as
> >> > account number with 2 digit alphanumeric code following (i.e. 12345a2,
> >> > 67890b1)
> >> >
> >> > table has approximately 300,000 records each associated with one of
> >> > 130,000
> >> > unique account numbers.
> >> >
> >> > Goal is to select and copy to new destination only those files 
> >> > associated
> >> > with a subset (approc 100,000) of 130,000 unique account numbers.
> >> >
> >> > Inversely, I could copy all 300,000 files then select and delete those
> >> > associated with the 30,000 account numbers.
> >> >
> >> > any assistance would be greatly appreciated.
> >> >
> >> > Thanks in advance.
> >> >
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
Utf
4/27/2007 12:06:03 PM
> There are/will be 11 tables but i believe all we will be
> concerned with is table "Images".  Only the 205,000
> filenames i am interested in COPYING from
> source to destination directories will be contained in
> table "Images" I will create today. (There will be
> 205,000 records each assiciated with oner of
> 100,000 unique account numbers but I think this is
> beside the point)

That's good! You've done half my job for me!

It will only be necessary to loop through all the records in the table named 
"Images".


> f) I believe field "path" is all we will need field
> "directory" = J:/ and
> field "filename" = name.tif.
> Field "path" is a essentially the full path and
> filename (i.e. J:/name.tif)
> g) Yes all files to be coppied to single directory
> (no sub-directories).  Destination is external usb drive.
> We can call it almost anything - assume K:/

There used to be a limit to the number of files you could store in the root 
directory of a disc. I can't remember what the limit was and I can't 
remember what version of DOS or Windows it applied to. It may be that 
different limits have applied over the years to different versions of 
Windows.

This may not be a problem now because it seems from your above reply that 
you have all 205,000 source files (and more) in the root directory of drive 
(J:) and you want them copied to the root directory of drive (K:).

Whatever your requirement, it will be very simple to amend the program so 
that it uses any source or target drive or drive\folder combination.  For 
example, it will be simple to amend the source from "J:\" to, say, 
"D:\MyFolder" and to amend the target from "K:\" to, say, "M:\Images". This 
will only be necessary if a file limit applies to the root directory of your 
USB disc or if you change your mind about the source or target.

You use forward slashes in the examples you give - namely, "J:/" for the 
directory field and "J:/name.tif" for the path field. The copying program 
will use backslashes to copy the files.  Therefore, I shall get the program 
to take what's in the Path field, store it in a variable, replace all 
forward slashes with backslashes (in the variable), and use the variable to 
copy the file. This means the contents of the Path field in the Images table 
will remain unchanged after the program has run. This is probably more 
information than you need at this stage, but I mention it in case your 
forward slash was a typing mistake.


> I like the thought and will be happy to add Yes/No
> fields for "Copied" and "FileNotFound" to table Images.

Good!  I shall assume they exist.


I don't think I need any more information. I'll let you know if I do.

This should be a simple program. I'll write it and run some tests before 
posting.

I'll ensure you can break into the program when it's running (with 
CTRL-BREAK) - just in case copying 205,000 files takes longer than you 
expect.  You'll be able to resume when convenient. Roughly how large is the 
average file?

As this is a one-off, it doesn't need a fancy user interface. I'll put a 
progress message in the Status Bar in the bottom left-hand corner of the 
Access window so you can see how many files have been copied so far as the 
program runs.

I'm running Access 2002. I don't think this will cause any compatibility 
problems, but we'll soon find out!

I hope to fit this in over the weekend.

Regards
Geoff



0
GeoffG
4/27/2007 4:21:15 PM
Below is a VBA program that will copy files listed in the "Path" field of
the "Images" table. The program works OK on my computer, running Windows XP
and Access 2002.  Whether it will work OK on your computer remains to be
seen!  I have tried to provide for every glitch I can think of, but you may
run into something I've not thought of.  If a problem develops or if you
need further explanation of the code, I'd be happy to help.

As you are new to VBA, I have put extensive notes with the code at the
beginning of the program, where you meet things for the first time.  The
lines that begin with an apostrophe are the notes. These will show up in
green in the VBA editor. The notes lines do not execute.

I tested the program by copying 88,000 files. The copying proceeded quickly
to begin with. But after a few tens of thousands of files, the program slows
down. This is unavoidable because Windows is having to do so much work
keeping track of such a large number of files in one folder. (I hope you've
got a fast computer! You'll probably need to go to lunch and have an
afternoon nap while it's running!)  The solution to speeding things up would
be to get the program to bunch files in, say, groups of 10,000 files in
separate subfolders on the target drive. But you may have a good reason for
wanting all the files in one folder.

Below is an introduction to the program and a step-by-step procedure for
installing and running it. Follow the step-by-step procedure carefully.  The
step-by-step procedure applies to Access 2002. You will need to adapt it if
your version of Access uses different ways of doing the same things.

INTRODUCTION

The VBA program uses two object libraries, which need to be referenced by
the database. These object libraries define programming objects, which the
program uses. The Microsoft DAO 3.6 Object Library allows the program to get
data out of the Images table.  The Microsoft Scripting Runtime Object
Library allows the program to copy files on the hard disc.

STEP-BY-STEP PROCEDURE

Here's the procedure you need to follow carefully:

1.    In the database window, click "Modules" to move to the VBA modules
windowpane. Click the "New" button to create a new standard module. The VBA
editor opens. Usually, the editor windowpane is on the right. Other
windowpanes may also appear, depending on whether the other windowpanes were
left open when the VBA editor was last used. For example, you may have the
Project Explorer windowpane open on the left, showing all the modules in the
database.

2.    In the VBA editor, open the Tools menu and select References. The
References dialog opens. In the References dialog, scroll down and select
"Microsoft DAO 3.6" and select "Microsoft Scripting Runtime". Click OK to
close the References dialog and return to the VBA editor.

3.    If either or both of the following two lines appear at the top of the
VBA editor, delete them:

Option Compare Database
Option Explicit

4.    In this post, highlight the following code, copy it to the Windows
clipboard (CTRL-C), click back in the VBA editor, and paste the code
(CTRL-V) into the VBA editor windowpane. Then scroll down this post and
carry on with step 5.


Option Compare Database
Option Explicit

'   Declare constant holding target path:
Private Const TARGETFOLDER As String = "K:\"

'   Declare constant holding name of table:
Private Const TABLENAME As String = "Images"

'   Declare constants holding the names of fields
'   in the above table:
Private Const PATHFIELD As String = "Path"
Private Const FILENOTFOUNDFIELD As String = "FileNotFound"
Private Const COPIEDFIELD As String = "Copied"

'   Declare object variables to be used in program:
Private mobjDB As DAO.Database
Private mobjRS As DAO.Recordset
Private mobjFSO As Scripting.FileSystemObject

'   Declare other variables:
Private mstrMessage As String
Private mintButtons As VbMsgBoxStyle
Private mstrHeading As String
Private mMsgRetVal As VbMsgBoxResult
Private mCopyOneFile As VbMsgBoxResult
Private mlngRecCount As Long
Private mlngProcessed As Long
Private mlngCopied As Long
Private mlngNotFound As Long
Private mstrTargetFolder As String
Private mlngRemainder As Long


Public Sub CopyImageFiles()

    '   MAIN PROGRAM.
    '
    '   This Public subprocedure calls Private subprocedures
    '   and functions in this module to copy files from one
    '   folder to another.
    '
    '   The difference between subprocedures and functions
    '   is this:  a called subprocedure does not a return value
    '   to this main subprocedure; whereas functions do. A
    '   function's return value can, for example, be evaluated
    '   by this main subprocedure to control program flow.
    '   This will be evident from the following.


    Dim fRetVal As Boolean

    '   Set up error handler:
    On Error GoTo CopyImageFiles_ErrorHandler

    '   Call the IsOK_ToStart() function and capture
    '   its return value in the variable "fRetVal":
    fRetVal = IsOK_ToStart()

    '   See whether the IsOK_ToStart() function returned
    '   TRUE or FALSE. If FALSE, jump to the end of the
    '   program to stop execution:
    If Not fRetVal Then GoTo Bye

    '   Call the CopyOneFile() function:
    mCopyOneFile = CopyOneFile()
    If mCopyOneFile = vbCancel Then GoTo Bye

    '   Call the InitialiseObjectVariables subprocedure.
    '   There's no return value to capture from a
    '   subprocedure.
    Call InitialiseObjectVariables

    '   Call the RecordsetContainsRecords() function:
    fRetVal = RecordsetContainsRecords()

    '   See if the function returned TRUE or FALSE.
    '   If FALSE, show message that there are no records
    '   to copy and jump to end of program:
    If Not fRetVal Then
        Call Message_NoRecordsToCopy
        GoTo Bye
    End If

    '   Records exist, so get record count:
    Call GetRecordCount

    '   See if target folder exists:
    fRetVal = TargetFolderExists()
    If Not fRetVal Then
        Call Message_TargetFolderDoesNotExist
        GoTo Bye
    End If

    '   Set counters:
    mlngProcessed = 0
    mlngCopied = 0
    mlngNotFound = 0

    '   Loop through the recordset until we reach
    '   the end of the recordset (EOF):
    Do Until mobjRS.EOF
        Call CopyImageFile
        '   See if loop is to stop after copying one file:
        If mCopyOneFile = vbYes Then Exit Do
        '   Move to next record in Recordset:
        mobjRS.MoveNext
    Loop

    Call Message_Finished

Bye:

    Call ClearStatusBar
    Call DestroyObjectVariables
    Exit Sub

CopyImageFiles_ErrorHandler:

    mstrMessage = "The follow error occurred:" _
        & vbNewLine & vbNewLine _
        & Err.Description
    mintButtons = vbOKOnly + vbExclamation
    mstrHeading = "Error Number = " & Err.Number
    MsgBox mstrMessage, mintButtons, mstrHeading
    GoTo Bye

End Sub

Private Function IsOK_ToStart() As Boolean

    '   Display a message asking if it's OK to start.
    '
    '   This function returns a Boolean (TRUE or FALSE)
    '   value because of "As Boolean" in above statement.

    '   Initialise variables for message:
    mstrMessage = "Start copying files?"
    mintButtons = vbYesNo + vbDefaultButton2 + vbQuestion
    mstrHeading = "Program Starting"

    '   The next code line calls VBA's built-in MsgBox()
    '   function to display a message. The function uses
    '   information stored in above three variables.
    '
    '   The above value of the "mintButtons" variable makes
    '   the MsgBox() function display only the "Yes" and "No"
    '   buttons.  The default button is the "No" button
    '   because "No" is the second button.  The "vbQuestion"
    '   built-in constant displays the Question Mark icon.
    '
    '   Capture the MsgBox() function's return value in the
    '   variable GetVal:
    mMsgRetVal = MsgBox(mstrMessage, mintButtons, mstrHeading)

    '   See if user clicked the "Yes" button by comparing
    '   GetVal with the VBA built-in constant "vbYes".
    '
    '   If user clicked "Yes", the next code line sets this
    '   function's return value to TRUE; if the user clicked
    '   "No", the next code line sets this function's return
    '   value to FALSE:
    IsOK_ToStart = (mMsgRetVal = vbYes)


End Function

Private Function CopyOneFile() As VbMsgBoxResult

    '   Initialise message variables:
    mstrMessage = "Copy one file as a test?" _
        & vbNewLine & vbNewLine _
        & "Click 'Yes' to copy one file." & vbNewLine _
        & "Click 'No' to copy all files." & vbNewLine _
        & "Click 'Cancel' to terminate program."
    mintButtons = vbYesNoCancel + vbDefaultButton1 + vbQuestion
    mstrHeading = "Copy Files"

    '   Show message:
    mMsgRetVal = MsgBox(mstrMessage, mintButtons, mstrHeading)

    '   Set function's return value:
    CopyOneFile = mMsgRetVal

End Function

Private Sub InitialiseObjectVariables()

    '   Declare string variable:
    Dim strSQL As String

    '   Create a FileSystemObject:
    Set mobjFSO = New Scripting.FileSystemObject

    '   Point variable "mobjDB" to the current database:
    Set mobjDB = CurrentDb()

    '   The following code line gives the "strSQL" variable
    '   a value. The value is an SQL SELECT statement. The
    '   SQL statement is used to create a Recordset object.
    '   The SQL statement selects all fields (represented
    '   by the asterisk) from the table named in the TABLENAME
    '   constant.  Only those table records whose "Copied"
    '   field is FALSE will be selected. This means any files
    '   already copied will not be selected:
    strSQL = _
        "SELECT " & TABLENAME & ".*" & vbNewLine _
        & "FROM " & TABLENAME & vbNewLine _
        & "WHERE (((" & TABLENAME & ".Copied)=False));"

    '   Use the SQL statement to create a recordset object
    '   that allows us to edit records:
    Set mobjRS = mobjDB.OpenRecordset(strSQL, dbOpenDynaset)

End Sub

Private Function RecordsetContainsRecords() As Boolean

    '  RETURNS:
    '
    '   This function returns TRUE if the recordset
    '   contains records and FALSE if the recordset
    '   does not contain records.

    '   The BOF (beginning of file) and EOF (end of file)
    '   properties of the recordset are both TRUE if the
    '   recordset contains no records. Therefore, we can
    '   write the following expression to give this
    '   function its return value:

    RecordsetContainsRecords = Not (mobjRS.BOF And mobjRS.EOF)

End Function

Private Sub Message_NoRecordsToCopy()

    '   Display a message saying there are no records
    '   in the table with the "Copied" field set to
    '   FALSE.

    mstrMessage = "Table:" & vbTab & TABLENAME _
        & vbNewLine & vbNewLine _
        & "Sorry, there are no records in the above table " _
        & "that need to be copied." _
        & vbNewLine _
        & "Either the table contains no records or the " _
        & "'Copied' field for all records is set to TRUE."
    mintButtons = vbOKOnly + vbInformation
    mstrHeading = "Information"
    MsgBox mstrMessage, mintButtons, mstrHeading

End Sub

Private Sub GetRecordCount()

    '   Show message in Status Bar:
    Access.SysCmd acSysCmdSetStatus, "Please wait... Getting Record Count"

    '   Get accurate record count
    '   (Moving to last record in a large recordset
    '   may take a while!)
    mobjRS.MoveLast
    mlngRecCount = mobjRS.RecordCount
    mobjRS.MoveFirst

End Sub

Private Function TargetFolderExists() As Boolean

    '   It is ESSENTIAL that the target folder exists
    '   before the program to proceeds and that the
    '   target path ends in a backslash; otherwise,
    '   the CopyFile method will assume the destination
    '   is the file name, not the folder name.

    Dim strLastChar As String

    '   Initialise destination path,
    '   ensuring it ends in a backslash:
    mstrTargetFolder = TARGETFOLDER
    strLastChar = Right(mstrTargetFolder, 1)
    If Not strLastChar = "\" Then
        mstrTargetFolder = mstrTargetFolder & "\"
    End If

    '   Set this function's return value to TRUE or FALSE:
    TargetFolderExists = mobjFSO.FolderExists(mstrTargetFolder)

End Function

Private Sub Message_TargetFolderDoesNotExist()

    mstrMessage = "Destination Folder:  " & TARGETFOLDER _
        & vbNewLine & vbNewLine _
        & "The above destination folder does not exist. " _
        & vbNewLine _
        & "Please create the destination folder and " _
        & "restart this program." _
        & vbNewLine _
        & "Alternatively, change the destination folder " _
        & "in the program."
    mintButtons = vbOKOnly + vbExclamation
    mstrHeading = "Program Terminated"
    MsgBox mstrMessage, mintButtons, mstrHeading

End Sub

Private Sub CopyImageFile()

    '   Get the PathName from the current record
    '   in the Recordset, see if it exists and copy it.

    Dim strSourcePath As String
    Dim fRetVal As Boolean

    '   Update status bar:
    mlngProcessed = mlngProcessed + 1
    Call UpdateStatusBar

    '   Call the GetPath() function:
    strSourcePath = GetPath()

    '   See if the source file exists:
    fRetVal = mobjFSO.FileExists(strSourcePath)

    '   If file does not exist, then:
    '   1.  Edit the current record and put TRUE in the
    '       "FileNotFound" field and
    '   2.  Exit this subprocedure to return to the loop
    '       in the main routine:
    If fRetVal = False Then
        mlngNotFound = mlngNotFound + 1
        With mobjRS
            .Edit
                .Fields(FILENOTFOUNDFIELD) = True
            .Update
        End With
        Exit Sub
    End If

    '   ***************************************
    '   If we are here, the source file exists.
    '   ***************************************

    '   Copy source file to target folder, overwriting
    '   any existing file in target folder with same
    '   name (there shouldn't be an existing file given
    '   the SELECT SQL statement used earlier):
    mobjFSO.CopyFile strSourcePath, mstrTargetFolder, True

    '   Increment counter:
    mlngCopied = mlngCopied + 1

    '   Edit the current record, putting TRUE
    '   in the "Copied" field:
    With mobjRS
        .Edit
            .Fields(COPIEDFIELD) = True
        .Update
    End With

    '   The loop will take up all of the CPU's processing
    '   power (unless we do something). This will result
    '   in the computer not responding to the keyboard or
    '   mouse until the loop has finished. Therefore,
    '   we allow Windows some time to process any CTRL-BREAK
    '   with the DoEvents statement.
    '
    '   However, DoEvents will slow down the program somewhat.
    '   Therefore, the following code line implements DoEvents
    '   after every five files have been copied. You may want
    '   to change this number based on the size of the files
    '   being copied: if the files are large (so copying is
    '   slow), you may want to decrease the number from five;
    '   alternatively, if the files are small (so copying
    '   is fast), you may want to increase the number. The
    '   point is that you want Windows to respond to the
    '   DoEvents command within a reasonable period of time:
    '
    '   Divide the record count by 5 and get the remainder:
    mlngRemainder = mlngProcessed Mod 5
    If mlngRemainder = 0 Then
        DoEvents
    End If

End Sub

Private Function GetPath() As String

    '   The field name stored in the PATHFIELD constant
    '   is a hyperlink field.  Therefore, it may
    '   contain the path in different formats;
    '   it may contain the path:
    '   1.  Without a leading and trailing hash (#),
    '       (ie the Display Path).
    '   2.  With a leading and trailing hash.
    '   3.  With a leading "#http://" and a trailing hash.
    '
    '   This function extracts the path from the
    '   PATHFIELD field for the current record in the
    '   recordset.


    Dim strRetVal As String
    Dim lngHashPos1 As Long
    Dim lngHashPos2 As Long
    Dim lngPathLength As Long
    Dim strPathStart As String

    '   Get path to image from Recordset:
    strRetVal = mobjRS.Fields(PATHFIELD)

    '   Find positions of the two hashes if they exist:
    lngHashPos1 = InStr(strRetVal, "#")
    lngHashPos2 = InStr(lngHashPos1 + 1, strRetVal, "#")

    '   If hash not found, then assume path is in
    '   already strRetVal:
    If lngHashPos1 = 0 Then GoTo Bye

    '   If second hash not found, return empty string:
    If lngHashPos2 = 0 Then
        strRetVal = ""
        GoTo Bye
    End If

    '   Calculate characters between the two hashes:
    lngPathLength = lngHashPos2 - lngHashPos1 - 1

    '   If path length is zero, return empty string:
    If lngPathLength = 0 Then
        strRetVal = ""
        GoTo Bye
    End If

    '   Get characters between the hashes:
    strRetVal = Mid(strRetVal, lngHashPos1 + 1, lngPathLength)

    '   If path begins with http://, then remove it:
    strPathStart = LCase(Left(strRetVal, 7))
    If strPathStart = "http://" Then
        strRetVal = Mid(strRetVal, 8)
    End If

Bye:

    '   Replace forward slashes with back slashes:
    strRetVal = Replace(strRetVal, "/", "\")

    '   Set this function's return value:
    GetPath = strRetVal

    Exit Function

End Function

Private Sub Message_Finished()

    mstrMessage = _
        "Records:" & vbTab & vbTab & mlngProcessed _
        & vbNewLine _
        & "Files Copied:" & vbTab & mlngCopied & vbNewLine _
        & "Files Not Found:" & vbTab & mlngNotFound _
        & vbNewLine & vbNewLine _
        & "Copying program has finished."
    mintButtons = vbOKOnly + vbInformation
    mstrHeading = "Program Finished"
    MsgBox mstrMessage, mintButtons, mstrHeading

End Sub

Private Sub DestroyObjectVariables()

    '   Clean up object variables at end of program.

    '   Close the Recordset if it has been created.
    If Not mobjRS Is Nothing Then mobjRS.Close

    '   Variables can be set to Nothing whether or
    '   not they have yet been initialised.
    Set mobjRS = Nothing
    Set mobjDB = Nothing
    Set mobjFSO = Nothing

End Sub

Private Sub UpdateStatusBar()

    Access.SysCmd acSysCmdSetStatus, "Records Processed: " _
        & mlngProcessed & " of " & mlngRecCount

End Sub

Private Sub ClearStatusBar()

    Access.SysCmd acSysCmdClearStatus

End Sub


5.    Open the Debug menu and select the "Compile..." option. This will
compile the code. This should complete in a second without any problems. If
there's a problem, compilation will stop and the problem line will be
highlighted.  You'll need to post back the details if that happens, but I
don't expect it will (because the program compiles OK on my machine).

6.    Notice the program begins by declaring the following constant:

'   Declare constant holding target path:
Private Const TARGETFOLDER As String = "K:\"

7.    If you decide to change the destination (ie the folder where the files
are to be copied to), then you need to change what's in the quotation marks
at the end of the line. For example, you could change K:\ to M:\Images.

8.    The destination drive and folder must exist or the program will stop
and alert you to the fact that the destination cannot be found.

9.    There are four more constants defined at the top of the program. These
define the tablename and fieldnames as you indicated them. You can change
these constants if you change the tablename or fieldnames.

10.    The main subprocedure is:

Public Sub CopyImageFiles()

11.    Notice the above line begins with "Public Sub".  Being a public
subprocedure means you can create a custom toolbar button in the database
window to run this subprocedure. To do this, follow these steps:

(a)    In the database window, right-click any toolbar at the top to open
the shortcut menu and select "Customize". The Customize dialog opens.

(b)    In the Customize dialog, click the "Toolbars" tab at the top to show 
all the possible Toolbars.

(c)    Click the "New" button to create a new toolbar and give the new
toolbar a name (or accept the default name).

(d)    Look carefully at the screen. You will notice a new blank toolbar has 
been created. On my computer, the new toolbar is just to the right, and at 
the bottom, of the Customize dialog.

(e)    Click the "Commands" tab at the top of the Customize dialog. The list
of Commands appears.

(f)    In the Categories List on the left, select "File" (if it is not
already selected).

(g)    In the Commands List on the right, select "Custom".

(h)    Drag the "Custom" button and drop it on to the new blank toolbar. The
new Custom button should be surrounded with a black line, indicating that it
is selected. (If it is not selected, click it to select it.)

(i)    In the "Customize" dialog, click the "Modify Selection" button (to 
modify the new, selected button). A menu opens.

(j)    On the menu, click in the "Name" box, delete "Custom" and enter "Copy
Image Files" (with spaces between the words).

(k)    Click the "Modify Selection" button again, and, just for kicks, point
to "Change Button Image", and select the yellow smiley face icon to
distinguish your new button.

(l)    Click the "Modify Selection" button again and select Properties.  The
Properties sheet for the new toolbar button opens.

(m)    In the Properties dialog, click in the "On Action" box and enter 
"CopyImageFiles". Notice, this is the name of the subprocedure that's to be 
run, which is mentioned in step 10 above. The subprocedure name does not 
have any spaces, therefore, the "On Action" property must NOT have any 
spaces.

(n)    Click the "Close" button to close the button-properties dialog.

(o)    Click the "Close" button to close the Customize dialog.

(p)    Click on the Titlebar across the top of the new custom toolbar, hold 
the mouse button down on the Titlebar, and drag the new toolbar to the top 
of the database window, aligning the new toolbar to the right of one of the 
standard toolbars.

(q)    Click the "Copy Image Files" button to run the program. The "Start
Program?" dialog opens.

(r)    Click the "Yes" button.  The "Copy Files" dialog opens.

(r)   Notice, you have three options: "Yes", "No", and "Cancel".

(s)    "Yes" will copy just one file. You may want to do this as a test.

(t)    "No" will copy all files. Notice, when you copy all files, a message
appears in the status bar in the bottom, left corner of the Access window,
telling you how many files have been copied so far.

(u)    "Cancel" will stop the program.

(v)    When you click "Yes" or "No", a message will appear when the program
finishes, telling you:

(i)    how many records in the Images table were processed,
(ii)    how many files were copied to the target folder, and
(iii)    how many files were not found.

(w)    Remember, any records in the Images table with the "Copied" field set 
to TRUE, will be excluded from the copying process.  This will be handy if 
you stop and restart the program.

(w)    You can press CTRL-BREAK to stop the program at any time. This will
leave the status bar message in place, until you start the program again or
restart Access.

I hope that's enough, and not too much, of an explanation.

Good luck. Let me know if you need anything else.

Regards
Geoff


0
GeoffG
4/30/2007 9:06:36 AM
Small correction to the following subprocedure:

Private Sub InitialiseObjectVariables()

In the above subprocedure, you will will see:

    strSQL = _
        "SELECT " & TABLENAME & ".*" & vbNewLine _
        & "FROM " & TABLENAME & vbNewLine _
        & "WHERE (((" & TABLENAME & ".Copied)=False));"

Please delete the above and paste in the following instead:

    strSQL = _
        "SELECT " & TABLENAME & ".*" & vbNewLine _
        & "FROM " & TABLENAME & vbNewLine _
        & "WHERE (((" & TABLENAME & "." & COPIEDFIELD & ")=False));"

They will both work if the field is called "Copied". But there's little
point in declaring the constant COPIEDFIELD at the top of the program if
that constant is not used in the above SQL statement!

Regards
Geoff




0
GeoffG
4/30/2007 11:23:26 AM
Many thanks.  I will try this and advise.

Any canace, you can recommend any literature on the topic of complex 
queries, access programming and visual basic.  I am a power business user (as 
opposed to a programmer or database administrator) and too often I have to 
fal back on my excel skills to tackle multi conditional queries with unary 
and array formulas.
  
You may reach me directly at fractionalreserve@yahoo.com


  

"GeoffG" wrote:

> Small correction to the following subprocedure:
> 
> Private Sub InitialiseObjectVariables()
> 
> In the above subprocedure, you will will see:
> 
>     strSQL = _
>         "SELECT " & TABLENAME & ".*" & vbNewLine _
>         & "FROM " & TABLENAME & vbNewLine _
>         & "WHERE (((" & TABLENAME & ".Copied)=False));"
> 
> Please delete the above and paste in the following instead:
> 
>     strSQL = _
>         "SELECT " & TABLENAME & ".*" & vbNewLine _
>         & "FROM " & TABLENAME & vbNewLine _
>         & "WHERE (((" & TABLENAME & "." & COPIEDFIELD & ")=False));"
> 
> They will both work if the field is called "Copied". But there's little
> point in declaring the constant COPIEDFIELD at the top of the program if
> that constant is not used in the above SQL statement!
> 
> Regards
> Geoff
> 
> 
> 
> 
> 
0
Utf
4/30/2007 12:06:02 PM
Reply:

Similar Artilces:

Excluding hidden columns and rows when copying to another workbook
When I print part of a worksheet that has hidden columns and rows - the hidden columns and rows do not print. That's what I want. Now--I'd like to take that same data and copy it to another workbook excluding the formulas and hidden columns and rows so that the new file contains only the data as was printed. How can I do that? PJ Select your range including hidden rows and columns then Edit>Go To>Special>Visible cells only>OK Now do your copy/paste. Gord Dibben Excel MVP On Fri, 6 Feb 2004 10:16:07 -0800, "PJ" <anonymous@discussions.microsoft.com> wr...

Need help getting files from Exchange Server.
We have a computer that had an account on Outlook attached to a Microsoft exchange server. Apparently for the moment, this computer and the server are inextricably tied together. Outlook cannot even be started without logging into the server because information store or whatever is missing. How do you get the information out of the exchange server to wean the computer from it? tom <Spamblocker@ameritech.net> wrote: > We have a computer that had an account on Outlook > attached to a Microsoft exchange server. > > Apparently for the moment, this computer and the > se...

Copying Modified Invoices
We currently use SOP Short Invoice and I want to copy the report to the SOP Other Invoice in report writer. The invoice layout is totally different, and I am trying not to spend days recreating it. I could not locate any type of copy functionality from one modified invoice to another. Any ideas out there. We are on version 7.0 Zach 1.export the modified Short Invoice report as a package file. 2. open the Package file in notepad and change the Component name and Report SOP Other Invoice and save 3. import the package file Voila! HS "Zach Morgan" <ZachMorgan@discussions....

format changes when copying from one cell to another
I'm sure this is simple, but it is not clear to me... I am copying the contents of a group of cells that exist on one worksheet to another. In Sheet2, I tell cell A1 that it should equal Sheet1's A1 value. That works fine. But if the cell is blank on Sheet1, it comes up as "0" on Sheet2. I figured it was probably assuming numeric formatting, but when I right-click > format cell on both Sheet1 and Sheet2, both show up as "general", so why would Sheet2 take a blank value and populate it with "0" ?? I gather than you are putting a formula in one...

Copying to a disk
when I try to copy data to a disk, it often can't read the disk or it says the disk is full when there is only 1 small file on it. I also can't erase the file. I can't find a command in file to allow me to erase or delete the file or format the disk. Wha tdo I do?? Dear jqh: This is a Great Plains forum. Although someone here might be able to answer your question, you might have better luck on a MS Windows forum. Frank Hamelly MCP-GP, MCT East Coast Dynamics www.eastcoast-dynamics You throw the disk away and get a new one. You're asking for trouble copying data t...

Just saved file, cannot be found. ?
Even Global search ? Win 7 ... Whatz up? if you saved it, it should be in the mru list? what version of excel are you using? -- Gary Keramidas Excel 2003 "Boswell" <Boswell@discussions.microsoft.com> wrote in message news:E9D6ED74-ABC5-469D-9572-2D8AE93F36CD@microsoft.com... > Even Global search ? > > Win 7 ... > > Whatz up? one other question, how did you open it, outlook email? if that's the case, search will never find it. it is probably here: C:\Users\Your user name\AppData\Local\Microsoft\Windows\Temporary Intern...

when i save a file that i have worked on, excel7 closes down work.
i have recently purchased office pro 2007. Excel 2007, when i save a filethat i have worked on, excel closes down and work is lost, done all updates, re installed from disc, please help i have lost 2 hours of work this moring that i need for a report a few questions: 1. what happens with save as? 2. what happens in safe mode? (hold shift key while starting excel) 3. do you have add-ins? 4. what operating system? x64? -- Gary Keramidas Excel 2003 "rob" <rob@discussions.microsoft.com> wrote in message news:0483F09B-3289-42B4-8849-0C0A61380B11@mic...

Excel 2003 file freezes laptop
I took a laptop away from the office and tried to open a large spreadsheet on the local C: drive. Each time I tried the laptop froze and had to be rebooted. When I connected back to the LAN at work, and opened the same file on the local C: drive I had no problems. What could cause this? If there is any corruption is there a way to decompile or something as you would in Access? Thanks, Mich ...

How to change .pst file location on hard drive(s)?
I want to move my .PST file c/w archives to a partition other than C:\. How can I do this with Win Live Mail? Thanks, John. "Machinist60" <e_john_wilson.no.spam.@hotmail.com> wrote in message news:hj5s5l$q8r$1@news.eternal-september.org... > I want to move my .PST file c/w archives to a partition other than C:\. > How can I do this with Win Live Mail? > > Thanks, John. I didn't realize WLM utilized the .pst file. I had to convert my .pst file data into the Windows Mail format and then import the data into WLM. Please explain what you...

clearing pictures copied from websites
Hello, I posed this question a couple of years ago and someone helped by providing a macro which I unfortunately lost. I often create spreadsheets with prices I copy from pricewatch.com. I want to keep the product image but I want to get rid of the buy now button. I can't delete that row or column - the image remains. Thanks Try rightclicking on that button and hitting the delete key. Boe wrote: > > Hello, > > I posed this question a couple of years ago and someone helped by providing > a macro which I unfortunately lost. I often create spreadsheets with ...

How to search against XML files in the file system?
Putting the search textbox on the page is the easy part. What's preferred way to find terms in XML files located on the file system? Like finding stuff saved in XML files some of the blogs use these days to store their blog items? There can be lots and lots and lots of XML files on the file system to search. -- <%= Clinton Gallagher NET csgallagher AT metromilwaukee.com URL http://clintongallagher.metromilwaukee.com/ clintonG wrote: > Putting the search textbox on the page is the easy part. What's preferred > way to find terms in XML files located ...

OT: managing file fragmentation on external USB backup drive (SBS2003)
Just wondering how others are managing file fragmentation on their external USB backup drives (SBS 2003)? I have two WD 500GB drives that i rotate weekly (each backup is around 55GB currently) but periodically the server will "freeze" during a backup (fans on, but no response at all) and requires a hard reboot to get going again. The first time it did it, i had no idea what caused it, but on subsequent occurrences I narrowed it down to the external hard drive itself - after the reboot, simply trying to access the drive via Windows Explorer on the server caused the se...

Unattend configure Send/Receive Group / PRF file -Outlook XP
Hi, Have to rollout 4000 laptops with Offline Storage enabled. I want to automatically configure outlook to: - use OST file - Synchronize when logging on and off - Download and sync. the general address book I have made a PRF file (using CIW) that creates OST file...so far so good... But I can't unattend set the option to sync when logging on and off. It seems this setting is stored in an SRS file in the users profile!! Second item, default the first address book in the list is synchronised. Is there a way to auto configure Outlook XP to sync OST fiel when logging on and off, (ins...

Recovery excel file
Anybody knows if there is any free tools to recover a advanced excel file (using macro, graphics, formulas, and several formats)???????? Tks, Marcelo Rayol ...

Open 2003 files with 2007
Hello all, I just upgraded my Office 2003 with Office 2007 on an EP Pro machine. The only snaffoo I find(so far..) is with Excel 2007. When I double-click on an Excel 2003 file, Excel 2007 starts up just fine, BUT my file is not displayed. At that point if I click the Restore button up at top-right, the file appears when Excel is restored up or down, then all is well. Anyone have any idea whats up with that and how to fix it??? Thanks John Lots of people have complained that when they doubleclick on a file in windows explorer, that the workbook doesn't seem to show up in xl20...

copy rows to another file
Dear Experts I have following code,i need to copy desirde rows to new file (r.xls) on sheet 1. This code is able to copy desired rows from active sheet to sheet2(same file) based on values in column G. Would you please guide me? regards Sub Marine() Dim arrParts() As String Dim MyRange As Range, CopyRange As Range Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = Range("G1:G" & LastRow) response = InputBox("Enter rows to copy in the format nnn,nnn,nn") arrParts = Split(response, ",") For Each ...

To have a selection of year to merge
Hello, In my Form there is a commandkey to merge to the All records and below is the VBA, my question is how can we make it like a combo box to chose the year, because the user is not a programmer, and is not able to open it in design view and change the VBA. Private Sub cmdMergeAll_Click() Me.Refresh MergeAllWord ("select * from PelayanJemaat Where TahunPel=2008") 'Note that you could use a condtion in the above sql End Sub Thanks in advance for any help provided. -- H. Frank Situmorang Put a control on your form where the user can enter the date. For exa...

Appending XML to an existing XML file
Hey all, I've read a few articles about speed and XML processing - so I just want to make sure that I'm using the right strategy for what I want to achieve. I have an XML file that I'm appending to every time a user submits their information. Right now I'm using XMLDocument (Load and Save) in conjuncture with XmlElement objects. Is this the right approach or is there a faster approach? Thanks, Novice PS Here is a simplified version of what I'm doing: XmlDocument xdoc = new XmlDocument(); xdoc.Load("results.xml"); XmlNode node = xdoc.SelectSingleNode("...

copy without space
I copy a cell to another app. and it contains a space. How should I cop from Excel without this space -- Message posted from http://www.ExcelForum.com ...

How do I export .dba calendar files from a Palm Treo to Outlook?
Chapura won't do it. Something about a problem with calendar files. ...

File Naming for Picture Order
I want my photos within a folder to appear in a certain order and want them in numberical order; however, when I put them in numerical order and get past 9 (into 2 digit numbers) the order gets all whacky. bjackson wrote: > I want my photos within a folder to appear in a certain order and > want them in numberical order; however, when I put them in numerical > order and get past 9 (into 2 digit numbers) the order gets all whacky. ======================= Instead of... 1, 2, 3,.....10, 11, 12... Try this... 0001, 0002, 0003,.....0010, 0011, 0012 If you are batch renumbering the f...

Report: "You have no transactions for the item you selected."
Hi, I've run into a glitch I can't figure out. I'm working with the Monthly income and expenses report. There are amounts listed under the Income - Unassigned category. When I double-click the category to see the transactions, I get the message in the Subject. I did a split-half search to find the supposed account that it registering the amount, but cannot find any transactions that are in error. I tried a Standard Repair without any change. Help! I should add that this is Money 2007. "Robert Berus" <rberus@columbus.rr.com> wrote in message news:OFn...

How to get the handle of a Help file belonging to other process.
Hi all, In my application,I want to change the Index of a HTMLHELP file which belongs to a dialog running in other exe.In order to do this I need to find whether the Help file is open or not & get the handle of that to use HH_DISPLAY_INDEX .Is there any way to do this?? Note: I dont want to use FindWindow() as it is giving rise to some problems in my application.. Thanks in advance ...

Case select returning error when cell contains #N/A : how must i avoid this error
Title says it all Thanks, Luc maybe this will do what you want Sub test() If Not IsError(Range("C1").Value) Then Select Case Range("C1").Value Case 1 MsgBox "1" Case 2 MsgBox "2" End Select End If End Sub -- Gary Keramidas Excel 2003 "Luc" <lferr@live.be> wrote in message news:7AB07996-F1EE-41C1-8D9D-8A7DA54CED91@microsoft.com... > Title says it all > > Thanks, > > Luc Thanks for your help !!!!! Luc "Gary Keramidas" <GKeramidasAtMSN.com> schreef in berich...

Can't open files
Hello -- I'm sure this isn't the proper place to post my problem, but thought I'd start here anyways. Let me start off by saying that I'm extremely computer-stupid, so please forgive my ignorance. I'm running Windows Vista Ultimate, and yahoo email. For some reason, I'm unable to open attachments that have been sent to me, and saved to my computer. PLEASE HLP!! These are important documents that I really need to access. Thanks for your help -- It's not very informative to say you are not able to open attachments. What happens when you try? Is ther...