New Member with an easy (I think) question Parens-quotes-doubles

[jarhead8084]

I have inherited a database that stored pictures in large binary data format. I have been able to successfully extract these images to a file folder giving them the primary key as filename and then .jpg, all is good...but, I would like to make the filename as such - firstname_lastname_primary key.jpg - I modified the SQL to pull the first and last names but have pulled most of my hair out trying to figure out the parentheses, single and double quotes. IDWCARDNUM is the primary key, I want to have - IDWFNAME underscore IDWLNAME underscore IDWCARDNUM.jpg or essentially Tom_Jones_1001.jpg

This is the working line

mstream.SaveToFile "C:\Test\" & rs("IDWCARDNUM") & ".jpg", 2

Something like this but not...

mstream.SaveToFile "C:\Test\" & rs("IDWFNAME" & "_" & "IDWLNAME" & "_" & "IDWCARDNUM") & ".jpg", 2

Thank you all so much in advance, I have been away from Access for awhile, was never very good at quoting, need to study more and I am feeling the pain!

[Beetle]

Try this;

mstream.SaveToFile "C:\Test" & rs!IDWFNAME & "_" & rs!IDWLNAME & "_" & rs!IDWCARDNUM & ".jpg", 2

[jarhead8084]

Thanks for the quick response...sadly it says - write to file failed

[Beetle]

Test the file path results to make sure it's formatting correctly using a message box or the immediate window

Dim strPath As String

strpath = "C:\Test" & rs!IDWFNAME & "_" & rs!IDWLNAME & "_" & rs!IDWCARDNUM & ".jpg"

msgBox strPath

See if it's actually returning the file path you intend.

[isladogs]

Somewhere along the line the backslash after c:\Test got lost. Reinstate it.

[jarhead8084]

Different error msg - Item cannot be found in the collection corresponding to the requested name or ordinal. Which I think is the error I was getting with all of the different variations of parens single and double quotes that I was trying. Also tried with the original working line of code and got the same error???

Dim strPath As String
'strPath = "C:\Test" & rs!IDWFNAME & "_" & rs!IDWLNAME & "_" & rs!IDWCARDNUM & ".jpg"
strPath = "C:\Test\" & rs("IDWCARDNUM") & ".jpg"
MsgBox strPath

[Beetle]

@Colin

Thanks for pointing that out, i missed it. I must have mistakenly removed that in post #2

@jarhead

That may mean it can't find a field name you're trying to reference in the record set. Have you double checked that the field names are all correct?

[jarhead8084]

Ahh, I see, many thanks!!

[jarhead8084]

I feel certain that the field names are right - here is my SQL statement that preceeds

strsql = "SELECT IDWFNAME,IDWLNAME,IDWCARDNUM,IDWPhotofield1 FROM IDWSCHD WHERE IDWPhotofield1 is not null"

Which I changed from the original

strsql = "SELECT IDWCARDNUM,IDWPhotofield1 FROM IDWSCHD WHERE IDWPhotofield1 is not null"

[isladogs]

I hate to say the blindingly obvious but have you got two lines similar to this:

Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset(strSQL, OpenDynaset)

Also have you tried testing this:
mstream.SaveToFile "C:\Test" & rs!IDWCARDNUM & ".jpg", 2

[jarhead8084]

Yes this works - mstream.SaveToFile "C:\Test" & rs!IDWCARDNUM & ".jpg", 2

and...Here is the complete sub

Private Sub GetPhoto()
db = "C:\Test\SCHD.mdb"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set mstream = CreateObject("ADODB.Stream")
mstream.Type = 1
cn.Open "Provider = Microsoft.Jet.OLEDB.4.0; " & "Data Source =" & db
strsql = "SELECT IDWFNAME,IDWLNAME,IDWCARDNUM,IDWPhotofield1 FROM IDWSCHD WHERE IDWPhotofield1 is not null"

rs.Open strsql, cn
Do While Not rs.EOF
mstream.Open
mstream.Write rs("IDWPhotofield1")
mstream.SaveToFile "C:\Test\" & rs!IDWCARDNUM & ".jpg", 2
mstream.Close
rs.MoveNext
Loop
End Sub

[isladogs]

Just realised I also left out the backslash after Test in my last post !!!
As you did in the intro to your latest post - its fated

Try this as a simpler alternative using a DAO recordset:

Private Sub GetPhoto()

Dim rs As DAO.Recordset
strsql = "SELECT IDWFNAME,IDWLNAME,IDWCARDNUM,IDWPhotofield1 FROM IDWSCHD WHERE IDWPhotofield1 is not null"

Set rs=CurrentDB.OpenRecordset(strSQL,OpenDynaset)

With rs
     .MoveFirst
      DoUntil .EOF
     .Edit
      !IDWPhotofield="C:\Test\" & !IDWFNAME & "_" & !IDWLNAME & "_" & !IDWCARDNUM & ".jpg"
      .Update
      MoveNext
      Loop 
      .Close
End With

Set rs = Nothing

End Sub

Or much easier still, just use an UPDATE query instead of a recordset:

UPDATE IdwSchd SET IdwSchd.IdwPhotoField1 = "C:\Test\" & [IDWFNAME] & "_" & [IDWLNAME] & "_" & [IDWCARDNUM] & ".jpg"WHERE (((IdwSchd.IdwPhotoField1) Is Not Null));

This will be FAR faster than a recordset especially if you have a lot of records to update

[June7]

Saving calculated data usually unnecessary. If the path is built from existing data, just calc when needed.

[isladogs]

True and I thought about making the same point.
However, in this case, if the file path is fixed and will be referenced regularly, I think it's valid to store it using the simplest method possible.

[jarhead8084]

For anyone who is interested - this slight addition and code alteration to the sub accomplished what I was looking for...

Dim fnam As String
fnam = "C:\Test\" & rs!IDWFNAME & "_" & rs!IDWLNAME & "_" & rs!IDWCARDNUM & ".jpg"
mstream.SaveToFile fnam, 2

gotta love the simplicity