Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Attachment object to SQL Server 2008R2 Expand / Collapse
Author
Message
Posted Tuesday, August 27, 2013 1:16 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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



  Post Attachments 
Capture.JPG (9 views, 76.44 KB)
Post #1488918
Posted Wednesday, August 28, 2013 10:25 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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?
Post #1489314
Posted Wednesday, August 28, 2013 11:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:50 AM
Points: 144, Visits: 525
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!
Post #1489339
Posted Wednesday, August 28, 2013 2:23 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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?
Post #1489414
Posted Thursday, August 29, 2013 8:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:50 AM
Points: 144, Visits: 525
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!
Post #1489736
Posted Friday, August 30, 2013 2:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:17 PM
Points: 100, Visits: 794
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?
Post #1490031
Posted Friday, August 30, 2013 7:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:50 AM
Points: 144, Visits: 525
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!
Post #1490166
Posted Wednesday, March 19, 2014 11:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:54 AM
Points: 15, 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
Post #1552722
Posted Wednesday, March 19, 2014 11:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:50 AM
Points: 144, Visits: 525
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!
Post #1552745
Posted Wednesday, March 19, 2014 11:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:54 AM
Points: 15, 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
Post #1552747
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse