First, I'm developing both the Front End and Back End in Access 2007, and I intend to distribute the application using the Access Run Time. The powers that be will not let me use SQL Server.
The database is required to house attachments. Initially, I had created a separate back end database to store the attachments, but the file would quickly grow beyond the 2GB limit - roughly within 3 weeks. Instead, I decided to save the "attachments" in folders (named after the parent records PK ID) on the network. Using VBA, I would populate a list, based on the parent record, from which the user could select the file, do what needs to be done, then save it.
Unforunately, we're running into data security issues - all of the attachments will have private personal information - SSNs, Loan Numbers, Account Numbers etc., so they need to be stored in a location that's not universally accessible. All of the users have different permissions based on their department, acting as a very basic security level (i.e., Department 1 users will not be able to view any parent records associted with departments 2 or 3).
So I need a way for my back end to take a file located in a separate directory not normally available to the Front End User, then move that file (or save a copy that will be overwritten when re-attached to the record) to a location that IS accessible to my Front End User.
Can I create/use modules stored on the back end?
My first thought is to let the back end store the record in a temporary attachment table that is deleted when the user is done with it, but I've noticed that Access has trouble reclaiming the space when the attachment is ultimately deleted once the user is done using it, and I'll run into my 2GB limit again.
Alternatively, I could create a new .accdb file each time the record's attachments need to be accessed, and then delete that database once it's done and the attachments are returned to the secure location, solving my limit problems.
I'm trying to store files on a linked SQL Server table, much the way I would using the Attachment data type in Access. (I couldn't use the Attachment data type, though, due to size restrictions - we're storing about a gig a week.)
Almost everyone says to store the attachments on a file system and save the location - I can't do that because of varying permissions. The files HAVE to be stored in the SQL Server table for security reasons.
So how do I do it? I've tried converting the files to binary and updating the recordset that way, but it always comes back as null. I'm not sure what I'm doing wrong.
Code: Public Function Test(strFile As String) As Boolean Dim rsAtts As dao.Recordset Dim ifilenum As Double Dim btAR() As Byte Set db = CurrentDb
Currently, I'm taking my unread emails, storing them in a table then sending any Excel files to a specified folder I've modified some code I found while surfing and it works great but I'd really like to send the attachment name and file path with the email recordset. It's been a long day so maybe it's super simple. Below is my code. Office 2010 Windows 8.1
PHP Code:
Function ReadInbox() Dim TempRst As DAO.Recordset Dim OlApp As Outlook.Application Dim Inbox As Outlook.MAPIFolder Dim Atmt As Outlook.Attachment Dim InboxItems As Outlook.Items
I currently have a code set up which sends an attachment from a drive on my computer to all the email addresses in a certain query. I would like to change the code so that I can add a button that will allow me to send an email with a specific attachment depending on their individual code in a table, e.g each person will receive a specific attachment. At the moment, the attachments are saved with the same file name as the person's specific code.The code I am currently using is:
Private Sub Command9_Click() Dim MyDb As DAO.Database Dim rsEmail As DAO.Recordset Dim qdf As DAO.QueryDef Dim prm As DAO.Parameter Dim strEmail As String Dim strMsg As String Dim oLook As Object
I'm working on a database for work that email's the current record after the form is completed. I have a attachment field in the table called Photo and some record's have a photo attached and other's don't. The code below works great with records that have a photo attached, but I get a path not found if I try to email a record that does not have a photo. I know that I need to put some form of code to check the photo field for a attachment, but I'm having a brain fade as to just what the code is.
Code: Private Sub eMail_Report_Click() Dim oFilesys, oTxtStream As Object Dim txtHTML As String Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem
I have a Database with a field that holds several attachments. I want to make a command button for a form that will open the attachment window that pops up when you double click on the attachment in the table. attach_fig7(1).gif.. I'd like the above window to open when I click the command button.
I am making a new database. And I want to confirm that I can do all the following in Access 2007 and how is best to do it.
1) A form where I can define:a unique Email Number starting at 1. a directory eg "C:UsersPaulDesktop101 - Brick House" an email address. a Recipient Name a Category for the email. Choosing either "Working" or "Complete". This would be part of the email subject.
2) a button which onclick lists all the files in the defined directory to a box on the form.
3) a button which onclick exports a report containing the list of files to PDF. Then attaches a zip of these files and the PDF report to an email in Outlook for revision before sending.
4)In Outlook the subject would be:
"The Directory Name" - "The Category" - "The Email Number" "101 - Brick House - Working - Email 1"
5)In Outlook the body would simply be the subject of the email followed by the filenames of the 2 attached files, ie the PDF report and the zip.
Dim dbs As DAO.Database Dim rsQuery As DAO.Recordset Set dbs = CurrentDb Set rsQuery = dbs.OpenRecordset("myQuery") Set rsImage = rsQuery.Fields("Images").Value While Not rsImage.EOF rsImage.Fields("FileData").SaveToFile "C:UsersmyNameDesktop" rsImage.MoveNext Wend
I am working on a database that will notify technicians if they have orders that have been placed on back order. The technician can enter two kinds of orders, one for a specific job and one to replenish the stock in his truck. My manager has asked that I create separate reports for each kind of order and e-mail them to the technician. Is there a way to program Access to send an e-mail with two separate attachments?
I would also like the e-mail to send without the user needing to click the Send button. Is that possible? The database will be sending twenty plus e-mails each time the button is pressed, and I would like to avoid needing the user to click the Send button that many times.
i want to crate a continuous form where i can add a button to export all of the attachments in that current record, i can create a folder and name it same as the selected record but how I can export all of the attachments in that record
Code: Dim strUserName As String Dim Location As String strUserName = Environ("UserName") Location = "C:Documents and Settings" & strUserName & "Desktop" & Me.ID If Dir(Location, vbDirectory) = "" Then MkDir Location
I have a database which has several records with at least 5 documents needing to be attached to each record. Using attachments or OLE would cause considerable bloat, so I opted for hyperlink fields for all the document types necessary to save with each record. However, now I am faced with an issue. It is easy enough to write code to open the files by following the hyperlinks, as well as write code to send an email, but how would I go about combining the two? I need to write a code that will attach these documents to an email, as attachments and not as hyperlinks. I know with excel documents, I could follow the hyperlink and send the document by email through excel, but I also have images converted to pdf's that need to be sent as attachments.
I have some code that attaches any files that are in my attachment field on the current record in to a email this is great but I m starting to realise that this is take up way to much space as it hold a copy in the database and can t afford to go down the sql sever route so what I have done is added some new fields called path1, path2 ,path3 ,path4 and path5 now the user can add the files location to these text box instead of add them via the attachment field also I can now limit number attachments to each record
Code: Private Sub cmdEmail2_Click() Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem Dim OutlookAttach As Outlook.Attachment Set appOutLook = CreateObject("Outlook.Application")
[Code] ....
Some how I need to change this so it checks fields path1 path2 path3 path4 path5 on the current record and see if anything is entered in these textbox's and follow the paths and attach the files to email
How to make command button . How to add a file path to a text box
1. add new field to your table e.g "path1" 2. add the new field to your form 3 create new command button call it addpath 4 add code below to click on event
Code: Private Sub addpath_Click() Dim fDialog As Office.FileDialog Dim varFile As Variant ' Clear listbox contents. ' Me.Path1.Value = "" ' Set up the File Dialog. '
[Code] ....
This will now save file location path to the path1 text box. To open file path
1.add another command button call it "pathopen" 2. add code below on click event
Code: Private Sub pathopen_Click() Application.FollowHyperlink Me.Path1 End Sub
How to add attachments to a email using a sub form were I store the attachment paths
E.g I have a main form called frmteaminfomer with various fields and continuous subform called attachmentssubform in the sub form I have a field call txtaddress . I have some code that I can pick a file then put its file path into txtaddress field and also have a check box call add to email. so what I trying to do is loop through all the records in the subform and if the check box is true add the files as attachment on the email.
I have a query which returns a list of reports I have created this session.
I want a button that when I click it runs the query and then stores the results into a variable.
I looked online and found a pretty good connection code and loop code to get the data but I'm having a hard time getting it to store into a variable.
What I want to do next is using code print the reports that were listed in the query.
That Means I need to be able to take that variable and pop the information off it, storing it into another variable (or if i can use an array just use that) and use that to concatenate into a command to print that report.
I am having problems with the syntax of this though. I think I'm just missing some key elements.
I have a button that adds pictures/files to a network location. So people from all over the company can do this and all pictures/files are stored in one location.
I also have a delete button, but a waning pops up and the file remains on the network.
PHP Code:
Run-time error '424': object required
Code: My.Computer.FileSystem.DeleteFile (Path)
where Path is a string, files location on network.
I have a front end and a back end access files.My user save the back end on a shared network folder like:a.b.comfolderName$..I wish to simulate this path in my local pc in order to set the linked table links.My problem is that I can not modify my pc name to be "a.b.com".How can I simulate this in my windows 7 pc ..so I can set the linked table values to "a.b.comfolderName$" ?
I need to make sure that all users on the network can access a specific network drive folder.
Most users don't have a problem but some users have the network drive mapped differently so when running the feature required their system can't find the file at that specified location.
I have the location of the files hard wired into the VBA code.
E.g. Z:INVDATACOMPANIESCompanyAASSETS Pty LtdPrecedents 2015STAGE 3A - COLLS-DEMS
The location is outside of the project address.
I need to be able to make sure the address of the folder is the same for all users.
I have a picture stored on a network file share that populates each time a particular form is opened. Every so often we lose our connection (which is a different issue altogether). Right now the error I get after much grinding away, is "Bad file name or number"
Is there a quicker way to check for the connection before it spends 45 seconds trying to find the whole path?
Code: Dim vFolderPath As String, dirFile As String, strFile As String vFolderPath = Nz(DLookup("FolderName", "tblCodes-FolderControl", "FolderKey = '" & "Profile" & "'")) dirFile = vFolderPath & Dir(vFolderPath & ctrl_people_id & " *", vbDirectory) strFile = dirFile & "profile_pic.*" 'Debug.Print dirFile On Error Resume Next If Dir(strFile) <> vbNullString Then Me.[ctrl_ImageFrame].Picture = dirFile & "" & Dir(strFile) Else Me!ctrl_ImageFrame.Picture = "X:~stuffprofile_icon.png" End If
Small piece of code that can send / recieve small text-strings over a network. I finally got it to work, but forgot to think ahead . Right now it only works in a formular but i really need to be a function with input / output.
My problem is that i am using the "Withevents" to call the Ostrosoft Winsock network module and "Withevents" does not work in functions.
How to build this into a function ?
Code: Option Compare Database Option Explicit Dim sBuffer As String Dim spage As String Dim WithEvents wsTCP As OSWINSCK.Winsock
My database is used by a few users on a shared drive in "read-only" mode so they can't change any data.I set a scheduled task to copy an updated version of the database (which I edit) every morning.
The problem is that when the users forget to close the database file I can't overwrite it.For this purpose I wrote a small code that quits the application at 00:00 using Application.Quit.The code WORKS when I test it on my computer, but every morning when I try to open the file I see that it is opened by another user since I also open it as "read-only" - meaning the code didn't work...getting the code to work properly OR get a better solution to be able to overwrite the file even though it's opened by other users.
After several days of searching, I haven't been able to find any threads related to this. I'm making a search form that queries a pdf library table. Once a search query is entered, the user is able to open the files from a results form. Currently, I am entering the filenames from the network directory manually into the pdf library table.
For example, I manually enter the following information into the tblPDFLibrary table that contains the fields:
Date added, Filename, File Path, Series, Class, Title.
I was wondering if there's a way to have Access query all the filenames in the directory for me. In other words, if the directory contains the filenames: AccountsPDF, InventoryPDF, CustomerPDF. Can I have Access query all the filenames within the directory and automatically add all the file names (AccountsPDF, InventoryPDF, CustomerPDF) to the Filename field within the tblPDFLibrary table? If I am able to do this, I can code the other fields to populate information because the filename contains all the other field information (except for file path but I can program it insert the UNC path).
I have a database that runs a macro/report every 2 hours, however our network has been up and down lately. This causes the macro to fail when trying to log into the system because the table that is storing your username is no longer in the database. When I check, all tables, linked tables, forms, reports, macros and modules are all gone. I find the only way to get them back is to close the database and reopen.
What I want is a way to reconnect the objects without closing and reopening.
I have designed a database which I intend to split for multi-users to access from one front end icon on a shared folder at work. I have designed a form bound to cmr record table and on it a subform to another table where cmrs activities will be saved. The form has buttons to and blank controls.
User can create a new activity entry by typing into the blank controls and pressing the save button which saves to the activity table. the edit button extracts a selected records details (selected on the subform) from the activity table and populates the blank field for a user to edit and then hit save to save changes. The delete button deletes a selected record from the activity table. I intend to have multi-users either accessing, viewing and a possibly editing the same customer at the same time. The simultaneous viewing is essential but the simultaneous editing, though not desired is inevitably going to occur.
What I would like to know is:
1. Can you lock an individual record in a table or does the whole table have to be locked. E.g If Colleague 1 is editing Cmr A's record in Table1 can he lock it so Colleague 2 can view and edit Cmr B's record in Table1
2.Can Colleague 1 access/read Cmr A's record in Table1 to retrieve details toe the form controls if Colleague 2 is viewing or editing Cmr A's record in Table1
3. If record lock is possible, how can I initiate it in my example code below.Edit activity record Code
Code: Private Sub Edit_A_Click() 'Get Data to text box control With Me.R_P_Data_P_Subfrm.Form Me.txtrID = !rID Me.txtrID.Tag = !rID Me.txtrefNo = !refNo Me.cmbrpc = !rPC
I need to generate 1 email with 3 attachments from an Access Db. These attachments are canned reports that are generated each week with fresh data.
I've done several searches and found a lot of good information here. Based on what I've read, I decided to output the 3 reports to a folder in My Documents and then automate Outlook to send the message.
I've used the output function to create the 3 files. No Problem, works well.
Then I found this code for automating Outlook. (Pasted below) I can get it to work (following either step 7 or step 8 below) but only if I include the attachment path in the SendMessage command.
Assuming the full paths are: C:My DocumentsReport1.snp C:My DocumentsReport2.snp C:My DocumentsReport3.snp
how do I modify the code to automatically attach all 3 files?
Any ideas? As always, thanks for taking the time to help, BeckieO
Sub SendMessage(Optional AttachmentPath) Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookRecip As Outlook.Recipient Dim objOutlookAttach As Outlook.Attachment
' Create the Outlook session. Set objOutlook = CreateObject("Outlook.Application")
' Create the message. Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg ' Add the To recipient(s) to the message. Set objOutlookRecip = .Recipients.Add("Henny Penny") objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message. Set objOutlookRecip = .Recipients.Add("Lucky Ducky") objOutlookRecip.Type = olCC
' Set the Subject, Body, and Importance of the message. .Subject = "This is an Automation test with Microsoft Outlook" .Body = "Last test - I promise." & vbCrLf & vbCrLf .Importance = olImportanceHigh 'High importance
' Add attachments to the message. If Not IsMissing(AttachmentPath) Then Set objOutlookAttach = .Attachments.Add(AttachmentPath) End If
' Resolve each Recipient's name. For Each objOutlookRecip In .Recipients objOutlookRecip.Resolve If Not objOutlookRecip.Resolve Then objOutlookMsg.Display End If Next .Send
End With Set objOutlookMsg = Nothing Set objOutlook = Nothing End Sub
7. To test this procedure, type the following line in the Immediate window, and then press ENTER: SendMessage "C:My DocumentsCustomers.txt"
8. To send the message without specifying an attachment, omit the argument when calling the procedure, as follows:SendMessage
