I work for a firm who have a few thousand clients and we are wanting to go through a process of checking with them that the data we send them is going to the email address they would like it to go to. However in some cases we don't currently hold an address so we are contacting them by phone to get the addresses over the next few weeks.
The way I've chosen to do this is to use the excellent link between Access and Outlook to send an email to the address we have on file (or have gotten) with a very light form they can optionally fill in to update the email address if desired.All seems well in testing this with 2 exceptions:
1) I have 2 email boxes in outlook, my "personal" one and a group mailbox. I'd like the emails access sends to come from the group mailbox but cannot see an option to control this.
2) We're likely to send the emails out in tranches - is there a way to have a single table the drives the email process but some how have it know that we have sent the emails out for certain clients such that when I send a second tranche, it doesn't re-send to clients that have already been contacted?
use of a query, or a report or it could involve changing the table structure.Say I have a many to one relationship, for simplicity we'll call the table on the 'many' side tblThing and the table on the 'one' side tblGroupofThings.When managing the records in these tables it is important that each Group of Things contains at least one Thing, but it isn't important that each Thing is part of a Group of Things.
Things and Groups of Things are constantly being added to the table. As I see it, the only way to manage this is from the Things table, or at least a form based on the Things table, as I am editing a foreign key for tblGroupofThings inside tblThings. However it would be easier for me to manage it from the Groups of Things end, so that my workflow goes as follows:
-I add a new Group of Things to tblGroupofThings, -I then 'put inside' that Group of Things, the Things that belong inside it (i.e. make the foreign key field in those Things point to the Group of Things.
Managing from the Things end means I have to start of with the new Groups of Things which are 'empty' at the moment, decide which things need to go in them then swtich to Things and remember which Things need to go into which Groups of Things.all the talk about putting records inside other records when I'm really talking about foreign keys. I know that's not know it really works but to the user that's how it should appear to work.
I downloaded a student template from microsoft and have been able to figure out most of it. I cannot see where to enter the emergency contact information. Also, when I try to see the student and guardian table/list, the guardian list comes up empty even though I entered this information when I entered the student info...
I have a need to store client emails in a database. In an ideal world the user would drag and drop the email over the attachment field and it would be attached. I've not been able to find any drag and drop features in Access 2013 so i am guessing this may not be possible?
It is possible to simply attach the email using the attachment field but i find this method slow as the user has to save the email to the drive and then attach it to the attachment field using the dialogue box.
I have an Access database update process that is run from the Win7 scheduler. I have attempted to instantiate or reference Outlook and send an email indicating the status of the process.
Outlook fails for some reason, but it works fine interactively regardless of whether Outlook is running or not.
I would like to send an email from Gmail server automatically at the end of every month. I'm currently sending email on button click. But is it possible to do this without user intervention.
I am trying to send out data collection emails to fill in my records but it will only let me select fields from single access tables, I want to be able to get them to fill out all the fields as entered in my forms, which contain sub-forms and therefore links to multiple tables.
Using Access and Outlook 2010. I have a database that tracks Contracts for our University. One of the fields it tracks is Expiration Date.
Is there a way to have Access prompt Outlook to send an email to my boss when the contract is a month or within a month from expiring?I'm assuming when the database is opened it would have to run a query for expiration dates expiring within one month of today's date (which i know how to do).
I just don't know if it's possible to generate an email or some kind of message with those records/contract numbers displayed? I should also note my boss is the only one who uses this database so even if the email notice isn't possible, is there a way to show a message of what contracts are expiring soon when he opens the DB?
I have a query that may have 6 - 7 different records for example each record contains a different email address, i need to get the first email then send and email to that address then go to the next record get that email address send an email to that email address until its gone through all the records...
I use this code to send an email, dont know how to loop through records and get the email...
Code: Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem Dim strPath As String Dim strFileName As String Set appOutLook = CreateObject("Outlook.Application")
I've setup a selection form that returns a specific list of email addresses in a query.
What I can't work out is how to press a button and have the email addresses populate the To: field in a blank Outlook email - I don't need any bells or whistles.
I'm writing a task tracking database to be used during staff meetings. Each task should be assigned a priority, and no two tasks should ever have the same priority. Easy enough. Here's where I could use some suggestions. If I have a new task, what would be easiest way to manage that insertion (assuming it has a higher priority than the lowest item), without having to renumber all the other items in the table?
Having had little luck in getting any concrete information about effective ways to manage ODBC data, and after days of long trials and errors, I thought at least I could write up a summary of what I've found to work well for ODBC sources and hopefully others may be able to contribute to this.Disclaimer: This is a far cry from being an authoritative and is woefully very subjective, being written by me and myself, using a MySQL server and Access 2003. I do hope that others who are able, can contribute more information to make this somehow more useful for those who would like to use Access as a front-end client.There are three principal issues that must be considered when you are using a ODBC data sources:1. Numbers of connections and different flavor of connections.2. Size of recordsets and network traffic.3. Binding forms to ODBC data sources.As a starting point, one should read the whitepaper on Jet and ODBC Connectivity. (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp ) This was for Jet 3.0, but should be valid for 4.0 as well. ODBC Driver and ConnectionsThe next thing is to understand what your ODBC driver is capable of. In a connection string, there is a parameter for "Options", which is usually a long integer. You should look at the ODBC driver's manual to ensure you have all options you need turned on. A good example would be to ensure that a certain data type is correctly mapped to Access's data type. In my case, I ensured that Big Integer (64-bit) were turned off because Access does not support this (and thankfully, I don't need it anyway). By far most important thing you want to look for is to ensure that the driver supports two things- Multiple statements and Active statements. Jet does try to pool all queries to the server into one connection whenever possible, but if the driver cannot support multiple statements over single connection, Jet will open another connection to submit a statement. Therefore, if you have a form that has a combobox, Jet will send two queries, one for form's recordsource and another for combobox's rowsource. In case of lack of support for multiple statement, Jet will need two connections. Even if your driver supports multiple statements, Jet may find it necessary to open a second connection if the driver cannot have more than one active statement. Active statement is when you fetch a big recordset and need to wait a bit for the full set to come over the wire. So back to that form with a combobox, if Jet find that the form's recordsource will take a while to be fetched, it will go ahead and open second connection to populate the combobox in order to allow for 'instantaneous' loading of a form from a user's POV.Wherever possible, keep numbers of connection to a minimum. As mentioned before, Jet will try to pool all statements into one connection wherever possible, but Jet cannot help you out if you issue a query using a new ADO connection, DAO ODBCDirect workspace, or create a recordset within VBA. If you want to avoid the additional overhead of another connection, you should use a stored query instead, and make sure it has a Connection string in its query property window set, so Jet knows that it uses ODBC data and pools it with its open connection to the ODBC server. The stored query can then be called within VBA without incurring another connection.DSN and DSN-less/ODBC and OLE DB connectionsThere are two ways to create connections, by using Data Source Name (DSN) or direct connection. To find right connection string, look at Carl Prothman's excellent website listing all possible connection strings (http://www.carlprothman.net/Default.aspx?tabid=81). Some people say connecting to DSN is better than without. Others say it's faster without DSN. One thing for sure is if you use DSN, you will need to distribute the DSN file to your users along with the finished database. I would recommend experimenting with both connection flavors before deciding which is better suited for your needs.To make a DSN connection DSN-less, look at Doug J Steele's example (http://www.accessmvp.com/DJSteele/DSNLessLinks.html) or perhaps this Add-in by Paul Litwin (http://www.mvps.org/access/modules/mdl0064.htm).Also, make sure you know whether you have different drivers available. I know for an example, some people prefer third-party drivers for Oracle over one supplied by Oracle themselves. Furthermore, some drivers are OLE DB which is supposedly better than ODBC (I say supposedly only because I am skeptical of Microsoft's promises of new and latest technology superseding a older technology, then dropping their claims and going back to older technology). Binding formsTo Jet's credit, it is quite intelligent in retrieving just enough rows to populate the bound form, and will continue to fill up the recordset periodically while a user is working on a row. However, Jet has some quirks where it can do something very stupid, such as asking for multiple full table scans. If you bind a form to ODBC table, Jet will do a full table scan. The simplest solution would be to add a WHERE clause to make the recordset smaller. But by far the better solution would be to set the recordsource's connection string to use ODBC instead of "(current database)". Jet will then query for keys, which it must have locally in order to manage a recordset, then afterward query a few rows at a time. If you can manage to keep keys smaller, all the better.Furthermore, if you want to use subform, do not use Master/Child linking fields. This makes Jet go ape-shit, issuing several queries to show tables and index which is quite unnecessary. Rather, leave the link blank, and set subform's recordsource with a WHERE clause to match the parent form's key, so Jet will only ask for rows that match the key only without asking for indexes and table status every time you move around.An additional benefit of making all queries for forms' recordsource an ODBC query is that you now have more control over how you handle those forms *with* Jet's intelligence. For example, you now can start transaction across multiple forms with subforms and commiting/rollbacking as you see fit, which would have not been possible using Access itself. I have been able to rollback the changes in two parent records and their related child records by issuing a SQL Pass-through query which simply says "Rollback;" and nothing was changed for any one of records, just as expected. If you have a combobox or listbox on a form, this will mean another query in order to fill in the rowsource. Ideally, you want to keep some tables local to front-end clients, especially for tables that will never change (e.g. a list of states or provinces for example). For tables that may be updated peridocially but is otherwise select-only, you need to decide whether you want to make it a dynaset or a snapshot. For a small set, snapshot is faster, but for a larger set, dynaset is faster. You will need to experiment with the rowsoource to ensure that the network traffic and time to load the rowsource is satisfactory. One problem is that Access won't accept an variable (at least I have been unable to do so) for a stored query's connection string, so if you need to change a connection string (e.g. you want to use different set of options, perhaps?), you would need to do this by hand, or at least write a function to loop through queries and updating the connection strings. Haven't tried that yet, but would imagine this is very possible.Keep an eye on SQL log when developingYou definitely will want to have the server write a log of what queries it has received from Jet to give you feedback in ensuring that Jet doesn't do anything stupid. This has told me far much more about Jet than working within Access environment. Unresolved issuesThere is only one thing I haven't yet worked on- Sharing a recordset for different controls. Suppose we have a form with a combobox and a subform, both which use same table as a rowsource and recordsource, respectively. In this case, Jet will issue two separate query to the server, even if they may use same recordset. If anyone has been able to show how to get such controls to share recordset, that would be cool.Also, I'm a bit worried about scattering connection strings all over the place, especially that it will contain a password. As I see no point in asking users to authenicate themselves every time they use a query with a ODBC connection string, given that variables can't be used in query's connection string. Would MDE make this less of a problem (I do not know if password still can be plucked out from a hex editor?). A possible solution is to synchronize Access's security with the server's security, because Jet will try to use Access's user & password for initial connection, and if a call to retrieve password was made along with a module at startup to 'fix connections' along with updating the connection string with the entered password, this may help somehow with keeping the password secret? Does anyone have more information on that?External LinkA useful FAQ (http://archives.postgresql.org/pgsql-interfaces/2000-07/msg00193.php) for linking Access with Postgresql which may be useful in giving you some ideas of how you can work with Access. If anyone wants to contribute to this, please do feel free to do so. If anyone finds anything that is dead-on wrong, please give me a good can of whoop-ass- I hate to lead blind into ditch, so to speak. :)
I have an estimating database, the "main" database is on my desktop, then it is also on a Notebook and Laptop and goes out with the bidders. At the end of the day, I want to sync all 3. I may enter info throughout the day on my desktop (which needs to sync up to the other 2) and vise versa. Using SharePoint is not an option as the Notebook and Laptop will not always have internet access.
I have a database for managing time related contracts.
I need to be able to run parameter queries but if I query the data by start date then and contracts which are current but started before the first date entered in the query (this is the same for end dates) are not shown.
I have a database where I record grades for my students. I would like to create a macro so that every time I record a grade as an "F", the student will receive an email that will contain an attachment of their scores. A lot of the information I want to include in this email is in several different tables. So to make it easier, I have created a query that lists the following:
Students name (SName) Student's email (SEmail) ID# of the form I use to record their grades (ID) ID# of the studen'ts work (WorkID) Date of their work (CreatedDate) Date I graded their work (AuditDate) Grade="F" (Accuracy)
How can I create something that will automate emailing of this information--to the individual student with only their information? I am using Access 2010. If I can't automatically send the emails, I would like to create a button in my audit screen that will send a report containing that student's information to them.
I am attempting to create a database for managing nonconforming product internally.
Different stages will need to be assigned to an individual within the organisation, is it possible to have a lookup value in my table which draws its information from active directory?
All the users are obviously in here and managed already, I dont want to have to create and manage a separate list of users just for my database.
Good afternoon All, I know there is a way to do what I need, but I am a bit rusty with my Access skills. Any help is greatly appreciated!
Here is what I would like to do as an end result... When my users enter a name on my form, I would like for the address and company information for that person automatically fill in. If they are not already in the contacts list, I would like for the record to be added. Any idea how I accomplish this?
So far I have the tblAccts (which would be the main information entered on the form), tblContacts (where the names, companies,and contact information is stored), and frmAcctOpen (the user's form for opening accounts).
It has been about 6+ years since I have really messed around with access, so please speak basic language in your answers.
Hi Guys. I have a form 'frmPurchaseOrders'. The user selects a Supplier from a combo. The form contains a control 'ContactID'. I want the user to be able to add a new Supplier Contact. I have added a cmd 'cmdNewContact'. When clicked I want the Supplier form to open on the relevant supplier. I added this code to the cmd:
On Error GoTo Err_cmdNewContact_Click
Dim strDocName As String
strDocName = "frmSuppliers"
DoCmd.OpenForm strDocName, , , [SupplierID] = [Forms]![frmPurchaseOrders]![tblSuppliers.SupplierID] 'I have 2 supplier ID's on the form DoCmd.Close acForm, "frmPurchaseOrders"
' Give CompanyName control focus. Forms![frmSuppliers]!CompanyName.SetFocus
Good afternoon All, I know there is a way to do what I need, but I am a bit rusty with my Access skills. Any help is greatly appreciated!
Here is what I would like to do as an end result... When my users enter a name on my form, I would like for the address and company information for that person automatically fill in. If they are not already in the contacts list, I would like for the record to be added. Any idea how I accomplish this?
So far I have the tblAccts (which would be the main information entered on the form), tblContacts (where the names, companies,and contact information is stored), and frmAcctOpen (the user's form for opening accounts).
It has been about 6+ years since I have really messed around with access, so please speak basic language in your answers.
I've written a query in SQL View. To be honest, its my first SQL Query EVER and I'm very happy that it works for half of what I need to do.
I'll paste the query here:
SELECT DISTINCT Abs(Avg([Handoff Tracking].[Internal Kickoff?])) AS [Avg Of Internal Kickoff?], Abs(Avg([Handoff Tracking].[E-Mailed prior to visit?])) AS [Avg Of E-Mailed prior to visit?], Abs(Avg([Handoff Tracking].[Call to Support?])) AS [Avg Of Call to Support?], Abs(Avg([Handoff Tracking].[Transfer to Support?])) AS [Avg Of Transfer to Support?], Abs(Avg([Handoff Tracking].[Recieve Post trip Info?])) AS [Avg Of Recieve Post trip Info?], Abs(Avg([Handoff Tracking].[Total Workstations])) AS [Avg Of Total Workstations], Abs(Avg([Handoff Tracking].[Failed workstations])) AS [Avg Of Failed workstations], Abs(Avg([Handoff Tracking].[Config File Issues?])) AS [Avg Of Config File Issues?], Abs(Avg([Handoff Tracking].[3rd Party App Issues?])) AS [Avg Of 3rd Party App Issues?], Abs(Avg([Handoff Tracking].[Welcome Pack Reviewed?])) AS [Avg Of Welcome Pack Reviewed?] FROM [Handoff Tracking];
As you probably see above it does the average of [table name].[field name] then outside THOSE brackets it does the absolute value of that averaged value (the numbers USED to show up as negative numbers.
This query is great becuase it checks the average value of ALL records regardless of contact, which is good becuase I need something to compare each contact to.
Now I need to get a query that will do the average value, for all fields PER contact!
What would be the string to narrow the range of the average equation down to a per contact basis. As far as relationships go I have 2 tables 1 for contacts.... and 1 for handoff tracking very simple DB structure wise. I just need to know how to do the same query as above, on a per user basis. Thanks for any input anyone might have :)
