how to select, rename, and save a file using Access VBA ?

[rfburn]

I am trying to build an interface for users to browse to a photo file, select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work. Here
is a code snippet which shows the value inserted into a table as well as the
filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & " AS Cpt, "
& vPositionID & " AS vPos;"
DoCmd.SetWarnings True

 

[tina]

assuming that the File field in the table is Text data type, and the RefID
and PositionID fields are Number data type, i'm not seeing any obvious
errors in the SQL statement itself. but unless it's all on one line in the
code window, there should be line breaks, and i don't see any of those
either. in case that's the problem, try

DoCmd.RunSQL "INSERT INTO tblProduit_Photo " _
& "( File, RefID, PositionID ) SELECT '" _
& strFiles & "', " & Forms!frm_Site_Info!ID _
& ", " & vPositionID, False

notice that i removed the field aliases, not because they cause a problem
AFAIK, but just because you don't need them. also not that i set the
UseTransaction argument to False; read up on the argument in VBA Help to
determine if that's the best setting for your situation.

if missing line breaks was not the problem, post back with an explanation of
what happens when you run the code.

hth

 

[dch3]

Is the problem with the renaming/backing up of an existing file or the code
that you posted? You post doesn't make it clear as to which part you're
having problem's with.

 

[rfburn]

Sorry, I wasnt clear about the actual problem. The code is working fine,
that is to say, it is saving the text name of the graphics file into the
proper photo table, and it is saving the associated graphics file into the
folder on my hard drive. The issue comes into play when using the FileCopy
function, it overwrites any existing graphics files stored in the hard drive
folder if they have the same name. What I have been attempting to do is one
of two things, either run the Dir() to check for pre-existing matching
filenames that will alert the user to the potential overwriting of the file,
or the second solution is to simply rename the file automatically with the
Position ID and the PK ID. I have had success with renaming the filename
that is placed in the table, easy enough. But I have had no success with
appending the filename of the graphics photo stored in the hard drive folder,
of which it must obviously match the text filename stored in the photo table.
As it is, I am unintentionally appending the original graphics file rather
than the targeted graphics folder, or for lack of a better word, the upload
folder. I thought it would be easy to do either or both, but I have busted
my head on this for three days, its just crazy. I have a file I can post on
a webserver if needed for an example of the code in development.

Is the problem with the renaming/backing up of an existing file or the code
that you posted? You post doesn't make it clear as to which part you're
having problem's with.

I am trying to build an interface for users to browse to a photo file, select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work. Here
is a code snippet which shows the value inserted into a table as well as the
filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & " AS Cpt, "
& vPositionID & " AS vPos;"
DoCmd.SetWarnings True

 

[rfburn]

I must have had word wrap enabled when I posted. the code runs straight
across the page, otherwise. I see and understand your point about breaking
up the SQL into multiple lines with the joiner: &

The code works as is, but it does not perform the way I need, which needs to
be further developed, to either append the file names or catch the duplicate
filenames to prevent an overwrite. Thanks for any thoughts on the matter,
greatly appreciated.

assuming that the File field in the table is Text data type, and the RefID
and PositionID fields are Number data type, i'm not seeing any obvious
errors in the SQL statement itself. but unless it's all on one line in the
code window, there should be line breaks, and i don't see any of those
either. in case that's the problem, try

DoCmd.RunSQL "INSERT INTO tblProduit_Photo " _
& "( File, RefID, PositionID ) SELECT '" _
& strFiles & "', " & Forms!frm_Site_Info!ID _
& ", " & vPositionID, False

notice that i removed the field aliases, not because they cause a problem
AFAIK, but just because you don't need them. also not that i set the
UseTransaction argument to False; read up on the argument in VBA Help to
determine if that's the best setting for your situation.

if missing line breaks was not the problem, post back with an explanation of
what happens when you run the code.

hth

I am trying to build an interface for users to browse to a photo file, select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work. Here
is a code snippet which shows the value inserted into a table as well as the
filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & " AS Cpt, "
& vPositionID & " AS vPos;"
DoCmd.SetWarnings True

 

[tina]

i guess i don't get it. you're listing the photo files in
tbltblProduit_Photo, correct? and each record in that table has a primary
key, correct? can you just use the primary key value to name (or include in
the name of) the file when you copy it to the folder? then it should always
be a unique filename, due to the inclusion of a unique primary key value.

hth

I must have had word wrap enabled when I posted. the code runs straight
across the page, otherwise. I see and understand your point about breaking
up the SQL into multiple lines with the joiner: &

The code works as is, but it does not perform the way I need, which needs to
be further developed, to either append the file names or catch the duplicate
filenames to prevent an overwrite. Thanks for any thoughts on the matter,
greatly appreciated.

assuming that the File field in the table is Text data type, and the RefID
and PositionID fields are Number data type, i'm not seeing any obvious
errors in the SQL statement itself. but unless it's all on one line in the
code window, there should be line breaks, and i don't see any of those
either. in case that's the problem, try

DoCmd.RunSQL "INSERT INTO tblProduit_Photo " _
& "( File, RefID, PositionID ) SELECT '" _
& strFiles & "', " & Forms!frm_Site_Info!ID _
& ", " & vPositionID, False

notice that i removed the field aliases, not because they cause a problem
AFAIK, but just because you don't need them. also not that i set the
UseTransaction argument to False; read up on the argument in VBA Help to
determine if that's the best setting for your situation.

if missing line breaks was not the problem, post back with an explanation of
what happens when you run the code.

hth

I am trying to build an interface for users to browse to a photo file, select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work. Here
is a code snippet which shows the value inserted into a table as well

as
the

filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & " AS

Cpt,
"

& vPositionID & " AS vPos;"
DoCmd.SetWarnings True

 

[rfburn]

Hmmm, that would be worth checking out. I will try it and see what I can
come up with. Hopefully it will provide a work around to the problem at
hand. Thanks for your response.

i guess i don't get it. you're listing the photo files in
tbltblProduit_Photo, correct? and each record in that table has a primary
key, correct? can you just use the primary key value to name (or include in
the name of) the file when you copy it to the folder? then it should always
be a unique filename, due to the inclusion of a unique primary key value.

hth

I must have had word wrap enabled when I posted. the code runs straight
across the page, otherwise. I see and understand your point about breaking
up the SQL into multiple lines with the joiner: &

The code works as is, but it does not perform the way I need, which needs to
be further developed, to either append the file names or catch the duplicate
filenames to prevent an overwrite. Thanks for any thoughts on the matter,
greatly appreciated.

assuming that the File field in the table is Text data type, and the RefID
and PositionID fields are Number data type, i'm not seeing any obvious
errors in the SQL statement itself. but unless it's all on one line in the
code window, there should be line breaks, and i don't see any of those
either. in case that's the problem, try

DoCmd.RunSQL "INSERT INTO tblProduit_Photo " _
& "( File, RefID, PositionID ) SELECT '" _
& strFiles & "', " & Forms!frm_Site_Info!ID _
& ", " & vPositionID, False

notice that i removed the field aliases, not because they cause a problem
AFAIK, but just because you don't need them. also not that i set the
UseTransaction argument to False; read up on the argument in VBA Help to
determine if that's the best setting for your situation.

if missing line breaks was not the problem, post back with an explanation of
what happens when you run the code.

hth


I am trying to build an interface for users to browse to a photo file,
select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process
wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work.
Here
is a code snippet which shows the value inserted into a table as well as
the
filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & " AS Cpt,
"
& vPositionID & " AS vPos;"
DoCmd.SetWarnings True

 

[tina]

you're welcome

Hmmm, that would be worth checking out. I will try it and see what I can
come up with. Hopefully it will provide a work around to the problem at
hand. Thanks for your response.

i guess i don't get it. you're listing the photo files in
tbltblProduit_Photo, correct? and each record in that table has a primary
key, correct? can you just use the primary key value to name (or include in
the name of) the file when you copy it to the folder? then it should always
be a unique filename, due to the inclusion of a unique primary key value.

hth

I must have had word wrap enabled when I posted. the code runs straight
across the page, otherwise. I see and understand your point about breaking
up the SQL into multiple lines with the joiner: &

The code works as is, but it does not perform the way I need, which

needs
to

be further developed, to either append the file names or catch the duplicate
filenames to prevent an overwrite. Thanks for any thoughts on the matter,
greatly appreciated.

:

assuming that the File field in the table is Text data type, and the RefID
and PositionID fields are Number data type, i'm not seeing any obvious
errors in the SQL statement itself. but unless it's all on one line

in
the

code window, there should be line breaks, and i don't see any of those
either. in case that's the problem, try

DoCmd.RunSQL "INSERT INTO tblProduit_Photo " _
& "( File, RefID, PositionID ) SELECT '" _
& strFiles & "', " & Forms!frm_Site_Info!ID _
& ", " & vPositionID, False

notice that i removed the field aliases, not because they cause a problem
AFAIK, but just because you don't need them. also not that i set the
UseTransaction argument to False; read up on the argument in VBA Help to
determine if that's the best setting for your situation.

if missing line breaks was not the problem, post back with an explanation of
what happens when you run the code.

hth


I am trying to build an interface for users to browse to a photo file,
select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process
wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work.
Here
is a code snippet which shows the value inserted into a table as

well
as

the
filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & "

AS
Cpt,

"
& vPositionID & " AS vPos;"
DoCmd.SetWarnings True

 

[dch3]

See my inline comments/questions...

Sorry, I wasnt clear about the actual problem. The code is working fine,
that is to say, it is saving the text name of the graphics file into the
proper photo table, and it is saving the associated graphics file into the
folder on my hard drive. The issue comes into play when using the FileCopy
function, it overwrites any existing graphics files stored in the hard drive
folder if they have the same name.

Is FileCopy a custom function that you wrote that uses the .CopyFile method
of the FileSystemObject? If you're using .CopyFile, the third parameter
indicates if any existing file should be overwritten as in
fso.CopyFile [sourceFile], [targetFile], true/false
http://msdn.microsoft.com/en-us/library/e1wf9e7w(VS.85).aspx

What I have been attempting to do is one

of two things, either run the Dir() to check for pre-existing matching
filenames that will alert the user to the potential overwriting of the file,
or the second solution is to simply rename the file automatically with the
Position ID and the PK ID.

Go with the .FileExists method of the FSO as in
if fso.fileExists(fileName) then
[Do something here]
end if
http://msdn.microsoft.com/en-us/library/x23stk5t(VS.85).aspx

I have had success with renaming the filename

that is placed in the table, easy enough. But I have had no success with
appending the filename of the graphics photo stored in the hard drive folder,
of which it must obviously match the text filename stored in the photo table.

Use the .CopyFile function of the FSO using the new name as the destination.
fso.CopyFile "C:\MyFile1.txt", "C:\MyFile1_Backup.txt"

....and chant continually... "The FileSystemObject is my friend. I like the
FileSystemObject. The FileSystemObject makes me happy."

As it is, I am unintentionally appending the original graphics file rather
than the targeted graphics folder, or for lack of a better word, the upload
folder. I thought it would be easy to do either or both, but I have busted
my head on this for three days, its just crazy. I have a file I can post on
a webserver if needed for an example of the code in development.

Is the problem with the renaming/backing up of an existing file or the code
that you posted? You post doesn't make it clear as to which part you're
having problem's with.

I am trying to build an interface for users to browse to a photo file, select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work. Here
is a code snippet which shows the value inserted into a table as well as the
filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & " AS Cpt, "
& vPositionID & " AS vPos;"
DoCmd.SetWarnings True

 

[rfburn]

Very good. I will rewrite that block of code to use the fso.CopyFile method
and see what I can produce. Thanks for the response, it will help.

See my inline comments/questions...

Sorry, I wasnt clear about the actual problem. The code is working fine,
that is to say, it is saving the text name of the graphics file into the
proper photo table, and it is saving the associated graphics file into the
folder on my hard drive. The issue comes into play when using the FileCopy
function, it overwrites any existing graphics files stored in the hard drive
folder if they have the same name.

Is FileCopy a custom function that you wrote that uses the .CopyFile method
of the FileSystemObject? If you're using .CopyFile, the third parameter
indicates if any existing file should be overwritten as in
fso.CopyFile [sourceFile], [targetFile], true/false
http://msdn.microsoft.com/en-us/library/e1wf9e7w(VS.85).aspx

What I have been attempting to do is one

of two things, either run the Dir() to check for pre-existing matching
filenames that will alert the user to the potential overwriting of the file,
or the second solution is to simply rename the file automatically with the
Position ID and the PK ID.

Go with the .FileExists method of the FSO as in
if fso.fileExists(fileName) then
[Do something here]
end if
http://msdn.microsoft.com/en-us/library/x23stk5t(VS.85).aspx

I have had success with renaming the filename

that is placed in the table, easy enough. But I have had no success with
appending the filename of the graphics photo stored in the hard drive folder,
of which it must obviously match the text filename stored in the photo table.

Use the .CopyFile function of the FSO using the new name as the destination.
fso.CopyFile "C:\MyFile1.txt", "C:\MyFile1_Backup.txt"

...and chant continually... "The FileSystemObject is my friend. I like the
FileSystemObject. The FileSystemObject makes me happy."

As it is, I am unintentionally appending the original graphics file rather
than the targeted graphics folder, or for lack of a better word, the upload
folder. I thought it would be easy to do either or both, but I have busted
my head on this for three days, its just crazy. I have a file I can post on
a webserver if needed for an example of the code in development.

Is the problem with the renaming/backing up of an existing file or the code
that you posted? You post doesn't make it clear as to which part you're
having problem's with.

:

I am trying to build an interface for users to browse to a photo file, select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work. Here
is a code snippet which shows the value inserted into a table as well as the
filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & " AS Cpt, "
& vPositionID & " AS vPos;"
DoCmd.SetWarnings True

 

[dch3]

You may also want to investigate using the Windows CommonDialog to select the
file. Check out this article...

http://www.mvps.org/access/api/api0001.htm

Very good. I will rewrite that block of code to use the fso.CopyFile method
and see what I can produce. Thanks for the response, it will help.

See my inline comments/questions...

Sorry, I wasnt clear about the actual problem. The code is working fine,
that is to say, it is saving the text name of the graphics file into the
proper photo table, and it is saving the associated graphics file into the
folder on my hard drive. The issue comes into play when using the FileCopy
function, it overwrites any existing graphics files stored in the hard drive
folder if they have the same name.

Is FileCopy a custom function that you wrote that uses the .CopyFile method
of the FileSystemObject? If you're using .CopyFile, the third parameter
indicates if any existing file should be overwritten as in
fso.CopyFile [sourceFile], [targetFile], true/false
http://msdn.microsoft.com/en-us/library/e1wf9e7w(VS.85).aspx

What I have been attempting to do is one

of two things, either run the Dir() to check for pre-existing matching
filenames that will alert the user to the potential overwriting of the file,
or the second solution is to simply rename the file automatically with the
Position ID and the PK ID.

Go with the .FileExists method of the FSO as in
if fso.fileExists(fileName) then
[Do something here]
end if
http://msdn.microsoft.com/en-us/library/x23stk5t(VS.85).aspx

I have had success with renaming the filename

that is placed in the table, easy enough. But I have had no success with
appending the filename of the graphics photo stored in the hard drive folder,
of which it must obviously match the text filename stored in the photo table.

Use the .CopyFile function of the FSO using the new name as the destination.
fso.CopyFile "C:\MyFile1.txt", "C:\MyFile1_Backup.txt"

...and chant continually... "The FileSystemObject is my friend. I like the
FileSystemObject. The FileSystemObject makes me happy."

As it is, I am unintentionally appending the original graphics file rather
than the targeted graphics folder, or for lack of a better word, the upload
folder. I thought it would be easy to do either or both, but I have busted
my head on this for three days, its just crazy. I have a file I can post on
a webserver if needed for an example of the code in development.

:

Is the problem with the renaming/backing up of an existing file or the code
that you posted? You post doesn't make it clear as to which part you're
having problem's with.

:

I am trying to build an interface for users to browse to a photo file, select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work. Here
is a code snippet which shows the value inserted into a table as well as the
filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & " AS Cpt, "
& vPositionID & " AS vPos;"
DoCmd.SetWarnings True

 

[rfburn]

Very cool. I researched the fso method and adjusted the code accordingly.
It appears to be working fine, although I still need to kick the tires and do
several test runs before moving on with the development. I am posting the
code I used, with the line commented out that was changed. Thanks for
pointing me in that direction. Now if it continues to work I will need to
develop a block of code to rename the uploaded files with the PK and the
Position ID. So far no luck with that portion, but the file overwriting
seems to be nipped in the bud thanks to you.

Here is the revised block of code for anyone wishing to follow this method:

AddFile:


If fso.FileExists(strIMGPath & Dir(strFiles)) Then
GoTo Blooper
End If

fso.CopyFile strFiles, strIMGPath & Dir(strFiles), False

' FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & Dir(strFiles) & "' AS Files, " & Forms!frm_Site_Info!ID & " AS
Cpt, " & vPositionID & " AS vPos;"
DoCmd.SetWarnings True

Me!lstImage.Requery
Fini:
Exit Sub

Blooper:
MsgBox "Filename Exists, Try a different filename."

End Sub

See my inline comments/questions...

Sorry, I wasnt clear about the actual problem. The code is working fine,
that is to say, it is saving the text name of the graphics file into the
proper photo table, and it is saving the associated graphics file into the
folder on my hard drive. The issue comes into play when using the FileCopy
function, it overwrites any existing graphics files stored in the hard drive
folder if they have the same name.

Is FileCopy a custom function that you wrote that uses the .CopyFile method
of the FileSystemObject? If you're using .CopyFile, the third parameter
indicates if any existing file should be overwritten as in
fso.CopyFile [sourceFile], [targetFile], true/false
http://msdn.microsoft.com/en-us/library/e1wf9e7w(VS.85).aspx

What I have been attempting to do is one

of two things, either run the Dir() to check for pre-existing matching
filenames that will alert the user to the potential overwriting of the file,
or the second solution is to simply rename the file automatically with the
Position ID and the PK ID.

Go with the .FileExists method of the FSO as in
if fso.fileExists(fileName) then
[Do something here]
end if
http://msdn.microsoft.com/en-us/library/x23stk5t(VS.85).aspx

I have had success with renaming the filename

that is placed in the table, easy enough. But I have had no success with
appending the filename of the graphics photo stored in the hard drive folder,
of which it must obviously match the text filename stored in the photo table.

Use the .CopyFile function of the FSO using the new name as the destination.
fso.CopyFile "C:\MyFile1.txt", "C:\MyFile1_Backup.txt"

...and chant continually... "The FileSystemObject is my friend. I like the
FileSystemObject. The FileSystemObject makes me happy."

As it is, I am unintentionally appending the original graphics file rather
than the targeted graphics folder, or for lack of a better word, the upload
folder. I thought it would be easy to do either or both, but I have busted
my head on this for three days, its just crazy. I have a file I can post on
a webserver if needed for an example of the code in development.

Is the problem with the renaming/backing up of an existing file or the code
that you posted? You post doesn't make it clear as to which part you're
having problem's with.

:

I am trying to build an interface for users to browse to a photo file, select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work. Here
is a code snippet which shows the value inserted into a table as well as the
filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & " AS Cpt, "
& vPositionID & " AS vPos;"
DoCmd.SetWarnings True

 

[dch3]

See my inline comment

Very cool. I researched the fso method and adjusted the code accordingly.
It appears to be working fine, although I still need to kick the tires and do
several test runs before moving on with the development. I am posting the
code I used, with the line commented out that was changed. Thanks for
pointing me in that direction. Now if it continues to work I will need to
develop a block of code to rename the uploaded files with the PK and the
Position ID.

Shouldn't be too difficult - should be along the lines of

fso.CopyFile myfile.txt, strPK & "_" & intPositionID & "_" & myfile.txt

By placing the variables strPK and intPositionID, the file system object
will concatonate them to the front of 'myfile.txt' as in
'25507_01_myfile.txt'.

I would probably do a check after the rename using FSO.FileExists to confirm
that everything went smoothly. Yes Err.Number can be checked, but my
preferance to check for the file.

So far no luck with that portion, but the file overwriting

seems to be nipped in the bud thanks to you.

Here is the revised block of code for anyone wishing to follow this method:

AddFile:


If fso.FileExists(strIMGPath & Dir(strFiles)) Then
GoTo Blooper
End If

fso.CopyFile strFiles, strIMGPath & Dir(strFiles), False

' FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & Dir(strFiles) & "' AS Files, " & Forms!frm_Site_Info!ID & " AS
Cpt, " & vPositionID & " AS vPos;"
DoCmd.SetWarnings True

Me!lstImage.Requery
Fini:
Exit Sub

Blooper:
MsgBox "Filename Exists, Try a different filename."

End Sub

See my inline comments/questions...

Sorry, I wasnt clear about the actual problem. The code is working fine,
that is to say, it is saving the text name of the graphics file into the
proper photo table, and it is saving the associated graphics file into the
folder on my hard drive. The issue comes into play when using the FileCopy
function, it overwrites any existing graphics files stored in the hard drive
folder if they have the same name.

Is FileCopy a custom function that you wrote that uses the .CopyFile method
of the FileSystemObject? If you're using .CopyFile, the third parameter
indicates if any existing file should be overwritten as in
fso.CopyFile [sourceFile], [targetFile], true/false
http://msdn.microsoft.com/en-us/library/e1wf9e7w(VS.85).aspx

What I have been attempting to do is one

of two things, either run the Dir() to check for pre-existing matching
filenames that will alert the user to the potential overwriting of the file,
or the second solution is to simply rename the file automatically with the
Position ID and the PK ID.

Go with the .FileExists method of the FSO as in
if fso.fileExists(fileName) then
[Do something here]
end if
http://msdn.microsoft.com/en-us/library/x23stk5t(VS.85).aspx

I have had success with renaming the filename

that is placed in the table, easy enough. But I have had no success with
appending the filename of the graphics photo stored in the hard drive folder,
of which it must obviously match the text filename stored in the photo table.

Use the .CopyFile function of the FSO using the new name as the destination.
fso.CopyFile "C:\MyFile1.txt", "C:\MyFile1_Backup.txt"

...and chant continually... "The FileSystemObject is my friend. I like the
FileSystemObject. The FileSystemObject makes me happy."

As it is, I am unintentionally appending the original graphics file rather
than the targeted graphics folder, or for lack of a better word, the upload
folder. I thought it would be easy to do either or both, but I have busted
my head on this for three days, its just crazy. I have a file I can post on
a webserver if needed for an example of the code in development.

:

Is the problem with the renaming/backing up of an existing file or the code
that you posted? You post doesn't make it clear as to which part you're
having problem's with.

:

I am trying to build an interface for users to browse to a photo file, select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work. Here
is a code snippet which shows the value inserted into a table as well as the
filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & " AS Cpt, "
& vPositionID & " AS vPos;"
DoCmd.SetWarnings True

 

[rfburn]

I wrote the code before checking your post. Here is what I came up with,
although I like your idea of putting the "_" in as well. I will likely go
back and add it in. Here is what I came up with, it synchs both the text
being loaded in the table and the actual file name being uploaded to the
folder... Its working fine. Thanks a bunch, I will see about that error
correction as well, sounds like a great idea to me.

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblLocPhoto", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblLocPhoto ( File, RefID, PositionID ) SELECT
'" & (Forms!frm_Site_Info!ID & vPositionID & Dir(strFiles)) & "' AS Files, "
& Forms!frm_Site_Info!ID & " AS Cpt, " & vPositionID & " AS vPos;"
DoCmd.SetWarnings True

fso.CopyFile strFiles, strIMGPath & (Forms!frm_Site_Info!ID & vPositionID &
Dir(strFiles)), False

See my inline comment

Very cool. I researched the fso method and adjusted the code accordingly.
It appears to be working fine, although I still need to kick the tires and do
several test runs before moving on with the development. I am posting the
code I used, with the line commented out that was changed. Thanks for
pointing me in that direction. Now if it continues to work I will need to
develop a block of code to rename the uploaded files with the PK and the
Position ID.

Shouldn't be too difficult - should be along the lines of

fso.CopyFile myfile.txt, strPK & "_" & intPositionID & "_" & myfile.txt

By placing the variables strPK and intPositionID, the file system object
will concatonate them to the front of 'myfile.txt' as in
'25507_01_myfile.txt'.

I would probably do a check after the rename using FSO.FileExists to confirm
that everything went smoothly. Yes Err.Number can be checked, but my
preferance to check for the file.

So far no luck with that portion, but the file overwriting

seems to be nipped in the bud thanks to you.

Here is the revised block of code for anyone wishing to follow this method:

AddFile:


If fso.FileExists(strIMGPath & Dir(strFiles)) Then
GoTo Blooper
End If

fso.CopyFile strFiles, strIMGPath & Dir(strFiles), False

' FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & Dir(strFiles) & "' AS Files, " & Forms!frm_Site_Info!ID & " AS
Cpt, " & vPositionID & " AS vPos;"
DoCmd.SetWarnings True

Me!lstImage.Requery
Fini:
Exit Sub

Blooper:
MsgBox "Filename Exists, Try a different filename."

End Sub

See my inline comments/questions...

:

Sorry, I wasnt clear about the actual problem. The code is working fine,
that is to say, it is saving the text name of the graphics file into the
proper photo table, and it is saving the associated graphics file into the
folder on my hard drive. The issue comes into play when using the FileCopy
function, it overwrites any existing graphics files stored in the hard drive
folder if they have the same name.

Is FileCopy a custom function that you wrote that uses the .CopyFile method
of the FileSystemObject? If you're using .CopyFile, the third parameter
indicates if any existing file should be overwritten as in
fso.CopyFile [sourceFile], [targetFile], true/false
http://msdn.microsoft.com/en-us/library/e1wf9e7w(VS.85).aspx

What I have been attempting to do is one
of two things, either run the Dir() to check for pre-existing matching
filenames that will alert the user to the potential overwriting of the file,
or the second solution is to simply rename the file automatically with the
Position ID and the PK ID.
Go with the .FileExists method of the FSO as in
if fso.fileExists(fileName) then
[Do something here]
end if
http://msdn.microsoft.com/en-us/library/x23stk5t(VS.85).aspx

I have had success with renaming the filename
that is placed in the table, easy enough. But I have had no success with
appending the filename of the graphics photo stored in the hard drive folder,
of which it must obviously match the text filename stored in the photo table.
Use the .CopyFile function of the FSO using the new name as the destination.
fso.CopyFile "C:\MyFile1.txt", "C:\MyFile1_Backup.txt"

...and chant continually... "The FileSystemObject is my friend. I like the
FileSystemObject. The FileSystemObject makes me happy."

As it is, I am unintentionally appending the original graphics file rather
than the targeted graphics folder, or for lack of a better word, the upload
folder. I thought it would be easy to do either or both, but I have busted
my head on this for three days, its just crazy. I have a file I can post on
a webserver if needed for an example of the code in development.

:

Is the problem with the renaming/backing up of an existing file or the code
that you posted? You post doesn't make it clear as to which part you're
having problem's with.

:

I am trying to build an interface for users to browse to a photo file, select
the file and save it to a drectory folder. I don't want them to overwrite
existing filenames, so I have been attempting to automate the process wherein
Access VBA renames the file before saving it to the destination folder. I
have been working on it for two days, and still can't get it to work. Here
is a code snippet which shows the value inserted into a table as well as the
filename being saved to the folder:

FileCopy strFiles, strIMGPath & Dir(strFiles)

DoCmd.SetWarnings False
vPositionID = Nz(DMax("PositionID", "tblProduit_Photo", "[RefID]=" &
Forms!frm_Site_Info!ID)) + 1
DoCmd.RunSQL "INSERT INTO tblProduit_Photo ( File, RefID, PositionID )
SELECT '" & strFiles & "' AS Files, " & Forms!frm_Site_Info!ID & " AS Cpt, "
& vPositionID & " AS vPos;"
DoCmd.SetWarnings True