In an effort to digitize our personnel files, I am currently building an Access database that tracks stores employee information, to include admin documents, leave requests, training docs etc. After attending an Advanced Access user class, I discovered that Access databases are limited to 2GB.
Because each document must be uploaded, loading them into the database will not be sustainable using the attachment feature in Access 2010.
My goal is to make the previously created forms that had been built to save the attachments within the Tables now save to a shared drive and create a hyperlink to those stored files within the table using VBA. I'm a Novice with VBA but have slowly started picking it up.
An example of the fields existing in my Admin Document Upload Form "AdminDocUpload frm" are:
Employee- combo box
Document Description- combo box
Doc Date - Date
Doc Upload - Attachment
Submit - button
My intent is for the VBA programming behind the "Submit Button" to format the save name of the doc by combining the Doc Date+Employee name+DocDescrip tion, save it to a designated location on the shared drive and create a hyperlink in the Access table. And complicating things further, the file format is PDF for all saved docs (Although I don't think I'll need to open the PDF's within Access.
Any help in where to start on this or where I can find some source code to start is appreciated. Thanks.
2 7231 zmbd 5,501
Recognized Expert Moderator Expert
We all feel your pain here!
Even if you were using a "mature" RDMS in the long run, storing large amounts of data would become a headache and eventually lead to data corruption that you couldn't easily recover from.
The search on this site is a tad difficult at times; however, you should do one on hyperlinks, go to the bottom of this page and click on the VBA Insights Sitemap, look at article #26 and #88 as that could save you some time fetching the file paths. In fact, you might just spend some time scoping out that entire list.
As for the actual code.... please understand that we can help you with specific problems (one question per thread please) as you encounter them.
Actually found some code and customized it a bit to work. Still looking for an answer on the best way to automate the generation of a hyperlink to the filename so I can touch the file from Reports or querries. - Private Sub AdminDocPath_DblClick(Cancel As Integer)
-
Dim fd As FileDialog
-
Set fd = Application.FileDialog(msoFileDialogFilePicker)
-
'the number of the button chosen
-
Dim FileChosen As Integer
-
FileChosen = fd.Show
-
'1) To set the caption of the dialog box,
-
' set the Title property
-
fd.Title = "Select Admin Document to Upload"
-
'2) Set the oddly named InitialFileName property to
-
' determine the initial folder selected
-
fd.InitialFileName = "C:\Users\....."
-
'3) Set the InitialView property to control how your files
-
' appear on screen (as a list, icons, etc.)
-
fd.InitialView = msoFileDialogViewSmallIcons
-
'4) To set the filters (you can have as many as you like)
-
' first clear any existing ones, then add them one by one
-
fd.Filters.Clear
-
fd.Filters.Add "PDF macros", "*.pdf"
-
fd.Filters.Add "Excel macros", "*.xlsm"
-
' if there's more than one filter, you can control which
-
' one is selected by default
-
fd.FilterIndex = 1
-
' if there's more than one filter, you can control which
-
' one is selected by default
-
fd.FilterIndex = 1
-
'5) Set the ButtonName property to control the text on
-
' the OK button (the ampersand means the following
-
' letter is underlined and choosable with the ALT key)
-
fd.ButtonName = "Choose PDF file"
-
If FileChosen <> -1 Then
-
'didn't choose anything (clicked on CANCEL)
-
MsgBox "Upload Cancelled"
-
Else
-
'display name and path of file chosen
-
MsgBox fd.SelectedItems(1)
-
End If
-
'store the selected file to a variable
-
strSelectedFile = fd.SelectedItems(1)
-
'parse out the filename from the path
-
strFilename = Right(strSelectedFile, Len(strSelectedFile) - InStrRev(strSelectedFile, "\"))
-
'build the destination
-
strDestination = "C:\Users\....." & strFilename
-
'copy the file to the new folder
-
FileCopy strSelectedFile, strDestination
-
'store file as part of the record
-
Me.AdminDocPath = strFilename
-
End Sub
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Rodrigue |
last post by:
I as a developer have an application that I can run from my box or from a
shared drive, but I cannot get anybody else to run it. They do have the same
version of the framework on their boxes, yet they are unable to run the
application. What do I need to do. The error is not a security exception
|
by: clusardi2k |
last post by:
Hello,
I have a shared drive on SGI, Linux, and Windows. The fact that I'm
using a shared drive may be mute information.
The problem is within the same program a second call to fopen does not
create a file if the file has been deleted.
I would like to use fopen for its pointer return value to solve this.
|
by: Brett |
last post by:
I have an assembly on a shared LAN drive. On my developer machine, I give
that assembly full trust from the .NET wizard. It works fine. I go to a
user machine on the LAN, map to the shared drive, give full trust on that
machine but the assembly doesn't fully work. No errors. The assembly post
data from MS Access to a website. The posting...
|
by: JeffW |
last post by:
Researched this, but none of the proposed solutions are working for me.
Am hoping for some insight.
Am trying to open a binary file that exists on a shared drive for
reading. Code lives inside a .NET web app and looks like this:
FileStream fs = new FileStream(_documentInfo.DocFile, FileMode.Open,
FileAccess.Read);
This statement...
|
by: c_shah |
last post by:
I am having a .NET Application. Install the application on a Share
drive
on the network. Any user WHO's machine is connected to network can
access the application by clicking on short cut on his desktop. Now the
application runs on
the local machine, or it runs on the Share Drive?
| |
by: Pamela via AccessMonster.com |
last post by:
Hi All;
I work for a company which is currently utlizing MS Access on a shared drive
where people from all over the country access. Because all are wishing to
view a single database maintained solely by us (in Florida), each region
cannot have their own individual database on their local servers (otherwise
we would have to update several...
|
by: fullyleaded |
last post by:
hello all and a big pre-thanks for help.
I am developing a simple message taking app that i would like anyone in the office to access. my intention is to put icons on their desktops which will allow them to open a simple access form (without opening access) where they can type some data. have the database built/stored on a shared drive and all...
|
by: =?Utf-8?B?SmltSGVhdmV5?= |
last post by:
I developed some web pages and stored those pages on a "removable drive".
All was right with the world. I then took the "removable drive" and moved it
to another machine but mapped that drive with the same drive letter on the
"home machine". Now when I bring up those web pages within VS 2005, I get
this error which refers me to KB article...
|
by: erniemack |
last post by:
Using comdlg32.dll to allow the user to reference a .jpg or .bmp file I want to have the default search to be the current folder plus \images. How can I pass that info to comdlg32.dll?
|
by: Alan Yim |
last post by:
Hi folks,
My company recently upgraded our Office suite from 2003 to 2010. The problem in particular is with an Access database that was originally designed in Access 2003. The code in question used work in 2003 (see below code).
Private Sub engSave_Click()
Dim strSql3 As String
'Archive order number and cost data for engine.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
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. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
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...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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...
| |