G
Guest
Any one knows what the syntax for select/update/insert of an attachment field
in access 2007?
Does it work like a blob?
Any help is welcome
in access 2007?
Does it work like a blob?
Any help is welcome
6
'69 Camaro
Hi, Ofer.
No. It's a multi-valued field, so it has a one-to-many relationship
described partially in the MSysComplexColumns system table, with the balance
described in the table that contains the Attachment field.
The syntax for a SELECT query is:
SELECT AttachmentFieldName
FROM MyTable;
When the query is run, a paperclip will be displayed instead of the column's
name as the caption. A paperclip and the number of attachments stored in
that field will also be displayed for every record in the table.
How exceptionally useful this opaqueness is.
One cannot create an UPDATE or INSERT query where one of the columns
contains an Attachment field type, or any other multi-valued columns for
that matter. The intended method of adding an attachment or altering an
existing attachment in a record is to use the Attachments dialog window in
the table, or an Attachment control manually bound to an Attachment field on
a form or report.
How exceptionally convenient this manual technique is.
The user interface in Access 2007 has been designed for Excel users' ease of
use. For Excel users, working with the Attachments dialog window and the
Choose File dialog window to add attachments manually for potentially
hundreds or thousands of records, that's easy. For database users and
developers, that's ridiculous.
If manually adding to or updating the attachments for multiple records is
too tedious, you'll have to write a VBA procedure to accomplish this, even
though the Access development team claims Access 2007 is designed to remove
the need for code. Use the LoadFromFile method of ADODB Library's Stream
object. For more information, please see the following Web pages:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthloadfromfile.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjstream.asp
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
No. It's a multi-valued field, so it has a one-to-many relationship
described partially in the MSysComplexColumns system table, with the balance
described in the table that contains the Attachment field.
The syntax for a SELECT query is:
SELECT AttachmentFieldName
FROM MyTable;
When the query is run, a paperclip will be displayed instead of the column's
name as the caption. A paperclip and the number of attachments stored in
that field will also be displayed for every record in the table.
How exceptionally useful this opaqueness is.
One cannot create an UPDATE or INSERT query where one of the columns
contains an Attachment field type, or any other multi-valued columns for
that matter. The intended method of adding an attachment or altering an
existing attachment in a record is to use the Attachments dialog window in
the table, or an Attachment control manually bound to an Attachment field on
a form or report.
How exceptionally convenient this manual technique is.
The user interface in Access 2007 has been designed for Excel users' ease of
use. For Excel users, working with the Attachments dialog window and the
Choose File dialog window to add attachments manually for potentially
hundreds or thousands of records, that's easy. For database users and
developers, that's ridiculous.
If manually adding to or updating the attachments for multiple records is
too tedious, you'll have to write a VBA procedure to accomplish this, even
though the Access development team claims Access 2007 is designed to remove
the need for code. Use the LoadFromFile method of ADODB Library's Stream
object. For more information, please see the following Web pages:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthloadfromfile.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjstream.asp
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
A
Allen Browne
The Dev Team's blog - http://blogs.msdn.com/access/ - also provided this
approach for adding an attachment programmatically:
Private Sub cmdAddImage_Click(frm As Form)
Dim rsEmployees As DAO.Recordset2
Dim rsPictures As DAO.Recordset2
' Get the parent recordset
Set rsEmployees = frm.Recordset
'Put the parent record into edit mode
rsEmployees.Edit
'Get the attachment recordset
Set rsPictures = rsEmployees.Fields("AttachmentCell").Value
'Set first attachment to loaded picture
rsPictures.Edit
rsPictures.Fields("FileData").LoadFromFile ("C:\FileName.jpg")
rsPictures.Update
MsgBox "Picture added"
' Update the parent record
rsEmployees.Update
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
message
approach for adding an attachment programmatically:
Private Sub cmdAddImage_Click(frm As Form)
Dim rsEmployees As DAO.Recordset2
Dim rsPictures As DAO.Recordset2
' Get the parent recordset
Set rsEmployees = frm.Recordset
'Put the parent record into edit mode
rsEmployees.Edit
'Get the attachment recordset
Set rsPictures = rsEmployees.Fields("AttachmentCell").Value
'Set first attachment to loaded picture
rsPictures.Edit
rsPictures.Fields("FileData").LoadFromFile ("C:\FileName.jpg")
rsPictures.Update
MsgBox "Picture added"
' Update the parent record
rsEmployees.Update
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
message
6
'69 Camaro
Thanks, Allen. I didn't know about the new Recordset2 object.
I've been trying to get this code to work, but I must have a different
version of the Beta 2 than Erik had when he was creating the demonstration
for his blog last month. I notice that in the query with the
Issues.Attachments.FileName column, all of his file names show up, but mine
are all blank (and uneditable in the query datasheet, but the Attachments
dialog window is available for managing attachments should I want to edit
that column instead). However, the pop-up menu filter shows the different
variations of the file name that should be displayed in that column (Equals
"test.xml," Does Not Equal "test.xml," et cetera), so it knows what's there,
but it's playing "I've got a secret" and hiding it from me.
Also, when the code gets to this line:
rsEmployees.Edit
.. . . I get the error message that the object is read-only. This form is
based on a query of the table, so perhaps that has something to do with my
error. I've done some experimenting so far and have managed to get a "no
current record" when I base another form on just the table and move to any
record displayed on the form before pushing the button, but I'll have to get
back to this later today when I have more time.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
I've been trying to get this code to work, but I must have a different
version of the Beta 2 than Erik had when he was creating the demonstration
for his blog last month. I notice that in the query with the
Issues.Attachments.FileName column, all of his file names show up, but mine
are all blank (and uneditable in the query datasheet, but the Attachments
dialog window is available for managing attachments should I want to edit
that column instead). However, the pop-up menu filter shows the different
variations of the file name that should be displayed in that column (Equals
"test.xml," Does Not Equal "test.xml," et cetera), so it knows what's there,
but it's playing "I've got a secret" and hiding it from me.
Also, when the code gets to this line:
rsEmployees.Edit
.. . . I get the error message that the object is read-only. This form is
based on a query of the table, so perhaps that has something to do with my
error. I've done some experimenting so far and have managed to get a "no
current record" when I base another form on just the table and move to any
record displayed on the form before pushing the button, but I'll have to get
back to this later today when I have more time.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
A
Allen Browne
Hi Gunny.
I haven't tried working Erik's example on the specific recordset of the
form, but I have had fun with creating a self-joined table of clients.
Created a tblClient that has some records who are persons (individuals) and
others that are corporate entities (companies, choirs, committees, ...). It
has a ClientID (autonumber), and a MemberOf (multi-valued Long), so one
client can be a member of several others.
This code makes client 7 a member of client 3 (in addition to any other
clients he may belong to):
Function AddData()
Dim strSql As String
Dim rsMain As DAO.Recordset
Dim rsSub As DAO.Recordset
strSql = "SELECT tblClient.* FROM tblClient WHERE tblClient.ClientID =
7;"
Set rsMain = DBEngine(0)(0).OpenRecordset(strSql)
Set rsSub = rsMain!MemberOf.Value
rsMain.Edit
rsSub.AddNew
rsSub!Value = 3
rsSub.Update
rsMain.Update
rsSub.Close
rsMain.Close
End Function
The basic idea is of a recordset within a recordset, i.e. the multi-value
field is exposed as a recordset within each record of the main recordset.
Now I just have to figure out how to get these things nested further, so we
can have recursion.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
message
I haven't tried working Erik's example on the specific recordset of the
form, but I have had fun with creating a self-joined table of clients.
Created a tblClient that has some records who are persons (individuals) and
others that are corporate entities (companies, choirs, committees, ...). It
has a ClientID (autonumber), and a MemberOf (multi-valued Long), so one
client can be a member of several others.
This code makes client 7 a member of client 3 (in addition to any other
clients he may belong to):
Function AddData()
Dim strSql As String
Dim rsMain As DAO.Recordset
Dim rsSub As DAO.Recordset
strSql = "SELECT tblClient.* FROM tblClient WHERE tblClient.ClientID =
7;"
Set rsMain = DBEngine(0)(0).OpenRecordset(strSql)
Set rsSub = rsMain!MemberOf.Value
rsMain.Edit
rsSub.AddNew
rsSub!Value = 3
rsSub.Update
rsMain.Update
rsSub.Close
rsMain.Close
End Function
The basic idea is of a recordset within a recordset, i.e. the multi-value
field is exposed as a recordset within each record of the main recordset.
Now I just have to figure out how to get these things nested further, so we
can have recursion.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
message
6
'69 Camaro
Hi, Allen.
Most excellent! That's a concept that most of us can easily deal with,
without having to learn a whole new object model. (Not that the Recordset2
class looks hard.)
That can be pretty sticky. Good luck with that!
To Ofer:
After working on Erik's code when I had more time last night, I realized
that I got the "no current record" error because I was attempting to edit an
empty Recordset. Doh! Erik's code worked for him because he already had
attachments added to that record before running the code. After looking at
it some more, I realized that it would be better to have an "add attachment"
function and an "edit attachment" function, so I created two separate
procedures. One is the cmdAddFile( ) sub for adding new attachments, and
the other is the EditFileAtch( ) sub for editing (or adding) attachments.
Both procedures use the new Recordset2 and Field2 classes.
In the examples I've listed below, the column with the Attachment data type
is named Atch (which I probably should have passed as a parameter to make it
generic, but here they are anyway). For editing existing attachments, an
ordinal number is passed to EditFileAtch( ) to indicate which attachment is
to be replaced with another. The ordinal number can be a number that's
higher than the current number of attachments, in which case, a new
attachment will be added.
A possible stumbling block is that when an attachment is edited and a new
value assigned, the order of the attachments might change as Jet
re-alphabetizes them. So if multiple attachments are being edited in a
loop, keep in mind that the index of a particular item before the loop may
easily change to another index, so be careful not to edit the wrong
attachment.
On other notes, while I was experimenting, I tried the form's RecordsetClone
class. This doesn't work in the Beta 2 version. And, while you now have
working procedures that will add or update Attachments in a record whether
you're using a bound form or not -- thanks for the example, Allen! -- I
wouldn't recommend creating multi-valued fields for a relational database.
However, I realize that Sharepoint lists have this capability and one may
not have any choice but to use these procedures to add to or edit a record
for an existing Sharepoint list.
And if you're one of those people who only uses the ADO library, then you
may use the Stream object to load the file into the field. As for the code
to do so, you're on your own.
As promised, the two procedures follow:
' Requires the Microsoft Office 2007 Access database engine Object Library,
ACEDAO.DLL.
Private Sub cmdAddFile(frm As Form, sFileName As String)
On Error GoTo ErrHandler
Dim rsEmployees As Recordset2
Dim rsPictures As Recordset2
Dim fld As Field2
Dim fOpenedRecSet As Boolean
Set rsEmployees = frm.Recordset ' Set the parent recordset.
' Set the attachment recordset.
Set rsPictures = rsEmployees.Fields("Atch").Value
fOpenedRecSet = True
Set fld = rsPictures.Fields("FileData") ' Field2 type to load file
into.
rsEmployees.Edit ' Put the parent record into edit mode.
rsPictures.AddNew
fld.LoadFromFile sFileName
rsPictures.Update
MsgBox "File added"
rsEmployees.Update
CleanUp:
If (fOpenedRecSet) Then
rsPictures.Close
fOpenedRecSet = False
End If
Set fld = Nothing
Set rsPictures = Nothing
Set rsEmployees = Nothing
Exit Sub
ErrHandler:
If (Err.Number = 3820) Then
MsgBox "Sorry. That's a duplicate of a file name " & _
vbCrLf & "that has already been added to this record.", _
vbInformation + vbOKOnly, "Duplicate!"
Else
MsgBox "Error in cmdAddFile( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & "Error #" & _
Err.Number & vbCrLf & Err.Description, vbOKOnly, "Error!"
End If
Err.Clear
GoTo CleanUp
End Sub ' cmdAddFile( )
Private Sub EditFileAtch(frm As Form, num As Long, sFileName As String)
On Error GoTo ErrHandler
Dim recSetMain As Recordset2
Dim recSetAtch As Recordset2
Dim fld As Field2
Dim fOpenedRecSet As Boolean
Set recSetMain = frm.Recordset ' Set the parent recordset.
'----------------------------------------------------------------------
' Set the attachment recordset.
'----------------------------------------------------------------------
Set recSetAtch = recSetMain.Fields("Atch").Value
fOpenedRecSet = True
Set fld = recSetAtch.Fields("FileData") ' Field2 type to load file
into.
recSetMain.Edit ' Put the parent record into edit mode.
'----------------------------------------------------------------------
' Prevent a bad ordinal number from having been passed.
'----------------------------------------------------------------------
If (num < 1) Then
num = 1
End If
'----------------------------------------------------------------------
' Get accurate record count & move to approp. record.
'----------------------------------------------------------------------
If (Not (recSetAtch.BOF) And Not (recSetAtch.EOF)) Then
recSetAtch.MoveLast
recSetAtch.MoveFirst
recSetAtch.Move (num - 1)
End If
'----------------------------------------------------------------------
' Determine whether there are already attachments.
'----------------------------------------------------------------------
If ((recSetAtch.RecordCount > 0) And (num <= recSetAtch.RecordCount))
Then
recSetAtch.Edit ' Replace existing file.
Else
recSetAtch.AddNew ' Add new file.
End If
fld.LoadFromFile sFileName
recSetAtch.Update
MsgBox "File added"
recSetMain.Update
CleanUp:
If (fOpenedRecSet) Then
recSetAtch.Close
fOpenedRecSet = False
End If
Set fld = Nothing
Set recSetAtch = Nothing
Set recSetMain = Nothing
Exit Sub
ErrHandler:
If (Err.Number = 3820) Then
MsgBox "Sorry. That's a duplicate of a file name " & _
vbCrLf & "that has already been added to this record.", _
vbInformation + vbOKOnly, "Duplicate!"
Else
MsgBox "Error in EditFileAtch( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & "Error #" & _
Err.Number & vbCrLf & Err.Description, vbOKOnly, "Error!"
End If
Err.Clear
GoTo CleanUp
End Sub ' EditFileAtch( )
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
Most excellent! That's a concept that most of us can easily deal with,
without having to learn a whole new object model. (Not that the Recordset2
class looks hard.)
Now I just have to figure out how to get these things nested further, so
we can have recursion.
That can be pretty sticky. Good luck with that!
To Ofer:
After working on Erik's code when I had more time last night, I realized
that I got the "no current record" error because I was attempting to edit an
empty Recordset. Doh! Erik's code worked for him because he already had
attachments added to that record before running the code. After looking at
it some more, I realized that it would be better to have an "add attachment"
function and an "edit attachment" function, so I created two separate
procedures. One is the cmdAddFile( ) sub for adding new attachments, and
the other is the EditFileAtch( ) sub for editing (or adding) attachments.
Both procedures use the new Recordset2 and Field2 classes.
In the examples I've listed below, the column with the Attachment data type
is named Atch (which I probably should have passed as a parameter to make it
generic, but here they are anyway). For editing existing attachments, an
ordinal number is passed to EditFileAtch( ) to indicate which attachment is
to be replaced with another. The ordinal number can be a number that's
higher than the current number of attachments, in which case, a new
attachment will be added.
A possible stumbling block is that when an attachment is edited and a new
value assigned, the order of the attachments might change as Jet
re-alphabetizes them. So if multiple attachments are being edited in a
loop, keep in mind that the index of a particular item before the loop may
easily change to another index, so be careful not to edit the wrong
attachment.
On other notes, while I was experimenting, I tried the form's RecordsetClone
class. This doesn't work in the Beta 2 version. And, while you now have
working procedures that will add or update Attachments in a record whether
you're using a bound form or not -- thanks for the example, Allen! -- I
wouldn't recommend creating multi-valued fields for a relational database.
However, I realize that Sharepoint lists have this capability and one may
not have any choice but to use these procedures to add to or edit a record
for an existing Sharepoint list.
And if you're one of those people who only uses the ADO library, then you
may use the Stream object to load the file into the field. As for the code
to do so, you're on your own.
As promised, the two procedures follow:
' Requires the Microsoft Office 2007 Access database engine Object Library,
ACEDAO.DLL.
Private Sub cmdAddFile(frm As Form, sFileName As String)
On Error GoTo ErrHandler
Dim rsEmployees As Recordset2
Dim rsPictures As Recordset2
Dim fld As Field2
Dim fOpenedRecSet As Boolean
Set rsEmployees = frm.Recordset ' Set the parent recordset.
' Set the attachment recordset.
Set rsPictures = rsEmployees.Fields("Atch").Value
fOpenedRecSet = True
Set fld = rsPictures.Fields("FileData") ' Field2 type to load file
into.
rsEmployees.Edit ' Put the parent record into edit mode.
rsPictures.AddNew
fld.LoadFromFile sFileName
rsPictures.Update
MsgBox "File added"
rsEmployees.Update
CleanUp:
If (fOpenedRecSet) Then
rsPictures.Close
fOpenedRecSet = False
End If
Set fld = Nothing
Set rsPictures = Nothing
Set rsEmployees = Nothing
Exit Sub
ErrHandler:
If (Err.Number = 3820) Then
MsgBox "Sorry. That's a duplicate of a file name " & _
vbCrLf & "that has already been added to this record.", _
vbInformation + vbOKOnly, "Duplicate!"
Else
MsgBox "Error in cmdAddFile( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & "Error #" & _
Err.Number & vbCrLf & Err.Description, vbOKOnly, "Error!"
End If
Err.Clear
GoTo CleanUp
End Sub ' cmdAddFile( )
Private Sub EditFileAtch(frm As Form, num As Long, sFileName As String)
On Error GoTo ErrHandler
Dim recSetMain As Recordset2
Dim recSetAtch As Recordset2
Dim fld As Field2
Dim fOpenedRecSet As Boolean
Set recSetMain = frm.Recordset ' Set the parent recordset.
'----------------------------------------------------------------------
' Set the attachment recordset.
'----------------------------------------------------------------------
Set recSetAtch = recSetMain.Fields("Atch").Value
fOpenedRecSet = True
Set fld = recSetAtch.Fields("FileData") ' Field2 type to load file
into.
recSetMain.Edit ' Put the parent record into edit mode.
'----------------------------------------------------------------------
' Prevent a bad ordinal number from having been passed.
'----------------------------------------------------------------------
If (num < 1) Then
num = 1
End If
'----------------------------------------------------------------------
' Get accurate record count & move to approp. record.
'----------------------------------------------------------------------
If (Not (recSetAtch.BOF) And Not (recSetAtch.EOF)) Then
recSetAtch.MoveLast
recSetAtch.MoveFirst
recSetAtch.Move (num - 1)
End If
'----------------------------------------------------------------------
' Determine whether there are already attachments.
'----------------------------------------------------------------------
If ((recSetAtch.RecordCount > 0) And (num <= recSetAtch.RecordCount))
Then
recSetAtch.Edit ' Replace existing file.
Else
recSetAtch.AddNew ' Add new file.
End If
fld.LoadFromFile sFileName
recSetAtch.Update
MsgBox "File added"
recSetMain.Update
CleanUp:
If (fOpenedRecSet) Then
recSetAtch.Close
fOpenedRecSet = False
End If
Set fld = Nothing
Set recSetAtch = Nothing
Set recSetMain = Nothing
Exit Sub
ErrHandler:
If (Err.Number = 3820) Then
MsgBox "Sorry. That's a duplicate of a file name " & _
vbCrLf & "that has already been added to this record.", _
vbInformation + vbOKOnly, "Duplicate!"
Else
MsgBox "Error in EditFileAtch( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & "Error #" & _
Err.Number & vbCrLf & Err.Description, vbOKOnly, "Error!"
End If
Err.Clear
GoTo CleanUp
End Sub ' EditFileAtch( )
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.