Background: Frontend MS Access 2010/ VBA; Backend MS SQL Server 2008. Learning Access VBA and SQL
I have an Access application that needs to have the backend DB store an attachment. The Access code is written in VBA. How can this be accomplished? I would like to do it much like a native Access program. Please see attachment.
The best I could find was to create the field in SQL as a varbinary(MAX).
Thanks in advance.
4  2889  twinnyfo 3,653
Recognized Expert Moderator Specialist
From what I understand about SQL Server, it does not support attachment type fields in the same way as Access. It might be wisest to try to save the name and location of the file in a text field and then use that field as a pointer to open the file with its native program.
 jforbes 1,107
Recognized Expert Top Contributor
There are two basic schools of thought when it comes to Attachments and SQL Server.
One is what Twinyfo is talking about, by saving the URL to the Attachment, typically in a NVARCHAR(255). This is probably the most common and definitely the easiest way. Then you can launch the URL with something like http://allenbrowne.com/func-GoHyperlink.html or something like this: - Function OpenAttachment(ByVal sTargetAndLocation As String) As Boolean
-
Dim dReturn As Integer
-
OpenAttachment = False
-
dReturn = Shell("explorer.exe " & sTargetAndLocation, vbNormalFocus)
-
If dReturn >= 0 Then OpenAttachment = True
-
End Function
The other is to save the Attachment into a SQL field and this one is much trickier. Basically, it is using a BLOB/VARBINARY(MAX) or a hybrid type Blob (FileTables and FileStream if you want to Google them) to store the file into the SQL Server Database. I've done this in the past by using VB.NET's Filestream object to store into a VARBINARY(MAX), it was slick but it wasn't easy to figure out. It would be extremely difficult to replicate that code in MS-Access, so I really wouldn't recommend it unless it is a hard requirement. Oh, lastly, to use the Attachment you need to stream the Object back out of the Database into a physical file before it can be read by an external application.
The benefits of storing in SQL is that you wont have to worry about Windows Security on the physical file and you can have everything in one backup so you have little chance of loosing an Attachment. If you can live without these benefits, I would recommend saving the Attachment on a server somewhere and storing the URL in the Database.
I'm sure you'll find other opinions on this subject. Good luck.
twinnyfo 3,653
Recognized Expert Moderator Specialist
@jforbes,
Great explanation for our friend!
@CLSkcab,
Let us know what you decide and if there are any hitches as you go along.
It is too complex for me at this time and it is a low priority item. Thanks anyway.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: S_p_ike |
last post by:
Hi all,
I have a problem with mail with attachments.
To send e-mail with attachments from a form, I use Setec Astronomy's libmail
class (v 1.4) (available here http://www.phpclasses.org/)
Everything is ok but if I use a specific e-mail address (say address X) in
the field To, the e-mail arrives without attachments. (even if I use two
addresses for field To, of which one is address X, the mail to address X
arrives without attachments,...
|
by: Trond A. S. Andersen |
last post by:
Hi all,
I'm working on a project in which i need to send mail
attachments in the form of Excel documents from a .NET C#
application.
I've written a class which creates the Excel document
using the automation features provided by the
Excel.Application component. The Excel workbooks/documents
are stored locally before I call my SendExcelDocument()
method in order to attatch each document to an instance of
|
by: nabil m |
last post by:
hi i have 5 checkboxes i would like to when the user click on 1 or multiple
checkbox i would like to email 1 or multiple files attachments to them
ex: mailMsg.Attachments.Add(myAttachment+i);
but getting error: Specified cast is not valid.
thank you in advance
my code below:
MailAttachment myAttachment0 = new MailAttachment
(this.Server.MapPath("../serverForms/Auto_Form_01.pdf"),
|
by: Jozef |
last post by:
Hello,
I have an Access XP database that has attachments to a BE database that has
a password. The attachments work fine, until I run some code that modifies
a BE table (adding a field). I do this by running code similar the
following;
Public varDb as DAO.database
sub ConnectBEDatabase(strLocation as string)
Set varDb = OpenDatabase(strLocation, False, False, ";pwd=" &
|
by: Brad |
last post by:
I have a web .Net app which sends emails with attachments. After the email
is sent I clean up aftermyself and delete the attachments from disk. In
upgrading to .Net 2 I changed the email logic from using system.web.mail to
system.net.mail (of course vs is telling me system.web.mail is obsolete).
In .Net 1.1, the files deleted with no problem but in .Net 2 when I attempt
to delete I receive
The process cannot access the file ...'...
| | |
by: mike11d11 |
last post by:
If someone could help me, I need to be able to send attachments from my
access database that I have created. This database runs queries then
generates a report off the queries from underlying tables and emails
the reports as HTML format in the body, I then need to have this same
process go out to my C:\ drive and pick up a couple files and add it to
the same email as attachments. If someone could please give me a
sample code that would...
|
by: Steve Holden |
last post by:
I'm having some trouble getting attachments right for all recipients,
and it seems like Apple's mail.app is the pickiest client at the moment.
It doesn't handle attachments that both Thunderbird and Outlook find
perfectly acceptable.
Since the code I'm using is currently ugly and embedded, before I trim
it down for posting could anyone who's successfully generated emails
with attachments received by mail.app let me know if they had any...
|
by: Gianni Mariani |
last post by:
Attached example CPP files makes it easier to post code and extract code
from posts. It's unimaginable at this time where virtually any news
reader is capable of dealing with attachments to stick with such old
antiquated rules.
It's time for a change. I reccomend that the faq-5.4 bullet be changed:
http://www.parashift.com/c++-faq-lite/how-to-post.html#faq-5.4
from:
|
by: budyerr |
last post by:
All, I am trying to build a email submission form using asp.net. I
currently have a web form page that will upload to my webhosting
server, attach to email then delete the file after sending. This
works great with one attachment. I am requiring that a file be attach
before submitting. Now I am trying to add the ability to add multiple
attachments and I am able to create this however, it will error out if
all of the attachment is not...
|
by: lonelykeyboard |
last post by:
The database I have created needs a feature in which certain projects can be attached with certain specified PDF's.
I have tried creating the following loop in order to search through a table column (table is named attachments) the column (which is titled project number and is just right of ID or in column(1)by a generated project number on my form (pnumber.value)
If a match exists between my table (Field(1))and the generated number...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
