Upload file and save it in database (Access 2003)

Hi folks,

I have created a database to store information on equipments. During
the first level of registration, there is a form that i need the user
to fill up details on the equipment testing. i have done this one.Now
what i need is to enable the users to upload files and save it into the
corresponding table.

Example: 3. A laser inventory form has been completed for each 3b or
4 laser and submitted to the Laser Safety Officer (LSO)? which requires
users to answer whether Passed or Not Applicable in a combo box.
If the answer is "Passed", i need the users to upload the "Laser
inventory form".
If the answer is "Not Applicable", then there is no action, users
simply move on to another control on the same form.

My second question is what fields and what data type must i must create
in the underlying table"SafetyLevel1"?

Appreciate if someone could offer help on this plus specific details
(coding and where to attach it--which event). I really need it urgently
coz the deadline is just around the corner. Please help.

Great thanks.

re: Upload file and save it in database (Access 2003)

On 12 Dec 2006 18:32:49 -0800, "kev" <kevi.mel@gmail.comwrote:

Your questions are confusing to me.
You write "...enable the users to upload files and save it into the
corresponding table..."
What do you mean - upload files. I'm assuming you are speaking of an
Access application. "upload files" is more commonly used term in the
context of a web application. On the LAN (where Access operates) we
don't "upload" files. Perhaps we enter a filename in a textbox or find
it using a File>Open dialog.

Then later you write"i need the users to upload the "Laser
inventory form""
So now we're uploading a form rather than a file? What does that mean:
"uploading a form"?

Use precise language, and we might be able to help.

-Tom.

re: Upload file and save it in database (Access 2003)

Hi Tom,

I am sorry for the confusing mail. What i need is for the users to
search/find for a file (perhaps saved in their own hard disk) and
later save it in the corresponding table"SafetyLevel1".
What i meant by file is Microsoft Word or Excel files.

I use the wrong word it's not form it's a file.
Hope you understood my request and thanks in advance for your help.

Tom van Stiphout wrote:

re: Upload file and save it in database (Access 2003)

On 12 Dec 2006 22:14:44 -0800, "kev" <kevi.mel@gmail.comwrote:

It is unusual to save a file in a table. Is that really what you want
to do? Why?
More commonly files would be copied to a central network location,
accessible to all users. The table would only contain a string with
the path to the file.

Allowing users to search/find a file is typically done using a
File/Open dialog. Check this out:
http://www.mvps.org/access/api/api0001.htm
and bookmark that site!

-Tom.

re: Upload file and save it in database (Access 2003)

Howdy,

I think I know what you are after, so I hope this helps:

1/ Use the common dialog control to allow a user to hunt for a file.

2/ once you know what the path/filename is then you can insert it into
the database.

The type of field that you require is a dbLongBinary field, also called
an OLE field by Access.

The most efficient way to load data into a dbLongBinary field is to use
"BLOB" type loading. I have included the code below to both load a
file, as a BLOB, into a field, and also in turn to extract a file from
a BLOB record back to a file.

The word of warning that goes with this is that in order to see the
contents of the file you must first extract it from the record and back
to a file, then open the file/document. You are unable to read the file
directly when it is a BLOB.

The advantage of BLOB's is that they are very efficient when storing
data, much more so than using simple OLE types which eat a lot of space
in the DB.

I hope this helps, code is below

Cheers

The Frog

This is the code to extract a BLOB back to being a file...

Sub BlobToFile(fld As ADODB.Field, filename As String, _
Optional ChunkSize As Long = 8192)
Dim fnum As Integer, bytesLeft As Long, bytes As Long
Dim tmp() As Byte

' Raise an error if the field doesn't support GetChunk.
If (fld.Attributes And adFldLong) = 0 Then
Err.Raise 1001, , "Field doesn't support the GetChunk method."
End If
' Open the file;, delete it firstoverwrite it if necessary.' Delete
the
' file if it exists already, then create a new one.
If Dir$(filename) <"" Then Kill filename

fnum = FreeFile
Open filename For Binary As fnum
' Read the field's contents, and write it the data to the file.
bytesLeft = fld.ActualSize

Do While bytesLeft
bytes = bytesLeft
If bytes ChunkSize Then bytes = ChunkSize
tmp = fld.GetChunk(bytes)
Put #fnum, , tmp
bytesLeft = bytesLeft - bytes
Loop

Close #fnum
End Sub


and this is the code to take a file and make it a BLOB and store it
somewhere in the DB

Sub FileToBlob(fld As ADODB.Field, filename As String, Optional
ChunkSize As Long = 8192)

Dim fnum As Integer, bytesLeft As Long, bytes As Long
Dim tmp() As Byte

' Raise an error if the field doesn't support GetChunk.
If (fld.Attributes And adFldLong) = 0 Then
Err.Raise 1001, , "Field doesn't support the GetChunk method."
End If

' Open the file; raise an error if the file doesn't exist.
If Dir$(filename) = "" Then Err.Raise 53, , "File not found"

fnum = FreeFile
Open filename For Binary As fnum

' Read the file in chunks, and append data to the field.
bytesLeft = LOF(fnum)
Do While bytesLeft
bytes = bytesLeft
If bytes ChunkSize Then bytes = ChunkSize
ReDim tmp(1 To bytes) As Byte
Get #1, , tmp
fld.AppendChunk tmp
bytesLeft = bytesLeft - bytes
Loop

Close #fnum

End Sub


you need to make a reference to the ADO and ADOX libraries before this
will work. (Tools/references -tick the box next to one of the ADO
versions (preferabley the latest one) and also the ADOX)