SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Attachment object to SQL Server 2008R2


Attachment object to SQL Server 2008R2

Author
Message
kwoznica
kwoznica
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1145 Visits: 481
Hello all,

How can the Attachment control be used in Access to store files to a Varbinary(max) column on a sql server 2008R2 database?

When I add the control and set the control source in access, clicking the control in form view results in no events occurring. If I just have a regular access database not using sql server as the backend the control works as expected.

I have found several online articles related to my issue but none of them use Access.

Attached is an image of the property sheet and below is my table definition. Please let me know your suggestions.


CREATE TABLE dbo.NonConfData
(
Id INT IDENTITY(100,1)
,CaseDate DATE NOT NULL Default GetDate()
,Creator INT NOT NULL
,ProducedBy INT NOT NULL
,DefectCategory INT NOT NULL
,Quantity Decimal(7,2) Default(1.00) NOT NULL
,DispositionAuthority VARCHAR (50)
,NonConfDetails NVARCHAR(MAX) NOT NULL
,InspectorDetails NVARCHAR(MAX) NOT NULL
,IRAttachments VARBINARY(MAX)
,CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH
(
PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO


Attachments
Capture.JPG (58 views, 76.00 KB)
kwoznica
kwoznica
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1145 Visits: 481
If using the Attachment object is an issue what is the recommended way to store image files on an Access form to a SqlServer 2008R2 backend?
WendellB
WendellB
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2116 Visits: 1832
One important thing that is missing in the description of your issue is the version of Access that you are using, and the format of your Access front-end database. Since you are trying to use the Attachment Control, I presume it is 2007 or later. But that control is designed to use the Attachment data type - see this thread where some of the issues are explored.

The attachment data type simply stores a link to one or more files stored somewhere outside of the .accdb format front-end. If you want to actually store the image in SQL Server, then you want to choose one of the SQL Server data types that can store the image and use either the Image control or one of the other options.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
kwoznica
kwoznica
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1145 Visits: 481
Wendel,

I am running access 2010. I have a column with datatype varbinary(max) in the sql server database. Based on the information you mention regarding the attachment control it looks like I will be unable to allow users to upload data to this specific field.
You mention the image object to store the data however we will need to store more than one file and it doesn't seem that the image object allows that.

Is there another way you can recommend for documenting where files are stored for relevant records?
WendellB
WendellB
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2116 Visits: 1832
The usual solution (aside from the attachment field in .accdb/e Access front-ends - where you don't really want to store that info if the front-end is deployed to the user PC) is to create a separate table of images that links to the related record using a foreign key. That lets you have an unlimited number of images. If you want to display those images, you can create an image control on the form and provide navigation buttons to scroll through the images. That does involve using VBA and DAO/ADO to scroll through the related record set. Another option is to use a subform that contains an image control.

Here are some references that may help you determine the best design in your situation:
Store images in a database
Displaying Images in a Form
Storing images in Access Databases
Storing Images and BLOB files in SQL Server
Linking a Picture on a Form
If you still have questions, please post back.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
grovelli-262555
grovelli-262555
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 888
Hi Wendell, about the third option Storing images in Access Databases:
In Mark Andrews' reply,
Set RSAttachments = rs.Fields("Attachments").Value
to me sets RSAttachments to be the value of the Attachments field so
how can he use
RSAttachments.Fields("FileName").Value
since that asks for the value of the FileName field which RSAttachments isn't set to?
WendellB
WendellB
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2116 Visits: 1832
I don't pretend to be even a novice at using attachments - we've never actually used them. From reading about them, I believe the attachments field type actually stores a series of file names and the actual files into what is known as a stream object, and there is a bunch of magic under the covers that keeps track of everything. The article Attach files and graphics to the records in your database gives some guidance on how to use them, but since we use SQL Server back-ends for data storage, as does Kwonzika, we don't consider them very useful.

If on the other hand you are using an Access .accdb back-end, then they might be a good choice. But aside from the LoadFromFile and SaveToFile methods there doesn't seem to be alot of how to use information available on attachments. And it's not obvious to me what the .Value property is actually storing - my guess is that it is the file name and some pointers to the magic backend where the file actually gets stored.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
elhandli
elhandli
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 11
I have a similar situation. I must migrate data from MS Access 2010 to SQL Server 2008.
The Access table contains a field with the Attachments data type.
This field type allows you to store multiple documents (PDFs) for a single record in a single cell.

The software we are migrating to uses SQL Server 2008 as the back end database.
The vendor suggests just creating a separate field for each attachment.
That is not a good solution for us.

Is there a field type in SQL Server 2008 that works like the Attachments field in Access 2010? I will manually add the attachments to the records, but would like to have the ability to add future attachments to existing records.

Any thoughts on how to proceed?
e
WendellB
WendellB
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2116 Visits: 1832
Unfortunately (or fortunately if you believe in adhering to database normalization rules), there is no data type in SQL Server 2008 (or in 2012) that behaves like the attachment object. So the only alternative is to create an Objects table and use a foreign key to relate them to the primary record. It should be possible to automate the process of converting the attachment data, but it does mean the front-end would need modification to work with SQL Server. As an aside, I'm surprised your vendor is recommending 2008 - it is approaching extended life support, which means there will be no further SPs.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
elhandli
elhandli
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 11
Thanks. That was my first hunch. Was reading about FILESTREAM, but it appears to only be for one file per field.
I will pitch the concept of storing just the path in SQL Server. I've read about the Pros and Cons of doing this.
I just inherited this project and, as is the case with most things, there is 'history and politics'. So rather than bore you with the details...let's just call SQL Server 2008 an absolute.
Thanks so much for the help.
e
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search