August 27, 2013 at 1:16 pm
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
August 28, 2013 at 10:25 am
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?
August 28, 2013 at 11:11 am
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!
August 28, 2013 at 2:23 pm
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?
August 29, 2013 at 8:07 am
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:
Storing images in Access Databases
Storing Images and BLOB files in SQL Server
If you still have questions, please post back.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
August 30, 2013 at 2:51 am
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?
August 30, 2013 at 7:50 am
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!
March 19, 2014 at 11:12 am
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
March 19, 2014 at 11:52 am
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!
March 19, 2014 at 11:59 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply