tag:blogger.com,1999:blog-5581586040583096402024-02-20T14:26:17.561-05:00InfoCore AdvisorDiscussions on Database Design, Data Management issue, Microsoft Access tips, and Small Business applications.Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-558158604058309640.post-36675996401889007242014-01-30T11:25:00.001-05:002014-01-30T11:26:28.922-05:00Using Arrays in Access<strong>Testing if a value is in an Arrays using Functions</strong><br />
<br />
A client of mine recently had a situation where they were granting scholarships based on a particular major. They needed a program that would "check their work" and either identify students who qualified or verified that recipient's were correct.<br />
<br />
Although there were a number of ways to complete this task, I ended up using an Array and figured this would make a good example for the blog.<br />
<br />
An Array is used in many programming languages and refers to a set of numbers or objects that will follow a specific pattern. The set of values for this array was the list of major codes. There really wasn't anything specific that related these majors as they were picked by a committee based on many different factors. I could have stored these values in a table, but they will change year to year and are really only needed for this one function.<br />
<br />
I'll go through the code here:<br />
<br />
1. First we create a function name ScholarshipMajor. I will use this function in Queries and pass to it one argument [Major]. We define Major as a string value.<br />
<em><span style="color: blue;">Public Function ScholarshipMajor(Major As String)</span></em><br />
<em><span style="color: blue;"></span></em><br />
<span style="color: black;">2. Next we define our Array. I name our Array ValidPlans and define it as Variant. This way I'm not limited to the number of majors I can list it in.</span><br />
<span style="color: blue;"><em>Dim ValidPlans As Variant</em></span><br />
<br />
3. Now we have to load values into our array. I've shortened the list of majors to make writing this easier. The original list had 67 majors. We are going to use the "Split" function to load our values into the Array. The Split function asks for the string and the delimiter as its 2 arguments. The values are all included between one set of " " and we put the delimiter here in " " as well. We are using a comma delimiter. <br />
<span style="color: blue;"><em>ValidPlans = Split("ARVA,ARTH,ARTT,DHAR,DHEN,",")</em></span><br />
<span style="color: blue;"><span style="color: black;">At this point, we need to break from this function and write a second one.</span></span><br />
<br />
<span style="color: blue;"><span style="color: black;">4. Now that we have an Array that lists all valid majors for this scholarship, we need to write a function to test if the passing value (major) is in that array. The function here can be used to test if any text value is in any array. You just have to pass the function the array and the value to test for. The "As Boolean" will default our function to return a -1 or 0 for True or False.</span></span><br />
<span style="color: blue;"><span style="color: black;"></span></span><br />
<span style="color: blue;"><span style="color: black;"> </span></span><br />
<span style="color: blue;"><em>Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean<br /> IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)<br />End Function</em></span><br />
5. With this function to test if a value is in an array, we can finish our original function. Here we will write an IF statement using our newly created IsInArray function. We will pass that function the major to test for and the array we created. If IsInArray returns as True (-1) then this function will return True. Otherwise both functions will return false.<br />
<br />
<em><span style="color: blue;">If IsInArray(Major, ValidPlans) Then<br /> ScholarshipMajor = True<br /> Else<br /> ScholarshipMajor = False<br />End If</span></em><br />
<br />
This could have been built into one function but I've split out the test part to make it more usable to other functions without re-writing the code over and over.<br />
<br />
I've placed all the code in order below.<br />
<br />
***************************************************<br />
<span style="color: blue;"><em>Public Function ScholarshipMajor(Major As String)</em></span><br />
<span style="color: blue;"><em>Dim ValidPlans As Variant</em></span><br />
<span style="color: blue;"><em>ValidPlans = Split("ARVA,ARTH,ARTT,DHAR,DHEN,",")<br /><br />If IsInArray(Major, ValidPlans) Then<br /> ScholarshipMajor = True<br /> Else<br /> ScholarshipMajor = False<br />End If</em></span><br />
<br />
<span style="color: blue;"><em>End Function</em></span><br />
<span style="color: blue;"><em></em></span><br />
<span style="color: blue;"><em> </em></span><br />
<span style="color: blue;"><em>Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean<br /> IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)<br />End Function</em></span>Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com0tag:blogger.com,1999:blog-558158604058309640.post-25844752660779964702013-07-28T12:58:00.000-04:002013-07-29T15:22:55.538-04:00Use queries to do your work<span style="color: black; font-family: Arial, Helvetica, sans-serif;">Many people think of queries as just a way to ask the database a question and get a response. The typical query is a select query. This is one in which you are selecting a sub set of data from a table, or multiple tables, and either filtering rows and/or columns to create a new recordset.</span><br />
<br />
<span style="font-family: Arial;">Queries can be so much more than just select queries. Here I will go over 3 types of queries that you can use to </span><span style="color: black; font-family: Arial, Helvetica, sans-serif;">modify the data in your database. The queries DO NOT return a set of data, but instead can add, edit, and/or delete data in your database.</span><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br />
<span style="color: black;"></span></span><br />
<strong><span style="color: black; font-family: Arial, Helvetica, sans-serif;"> INSERT (Access refers to this as an Append query)</span></strong><br />
<br />
<span style="font-family: "Verdana", "sans-serif"; line-height: 150%;"><span style="color: black; font-family: Arial, Helvetica, sans-serif;">An Insert Query is used to insert a new record, by listing values that match existing fields in a table.</span></span><br />
<div class="MsoNormal" style="line-height: 150%; margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -0.25in;">
<span style="font-family: "Verdana", "sans-serif"; line-height: 150%;"><span style="color: black; font-family: Arial, Helvetica, sans-serif;">This example would insert my data into the Customer table in the database:</span></span></div>
<div class="MsoNormal" style="line-height: 150%; margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -0.25in;">
<br /></div>
<div class="MsoNormal" style="line-height: 150%; margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -0.25in;">
<span style="font-family: "Verdana", "sans-serif"; line-height: 150%;"><span style="color: black; font-family: Arial, Helvetica, sans-serif;">INSERT INTO Customer (FirstName, LastName, Address, State, City, PostalCode) VALUES (‘Rob', 'Buonocore', ’130 Mapple Ave', 'NJ', ‘Dumont', 07628); </span></span><span style="color: black; font-family: Arial, Helvetica, sans-serif;"> </span></div>
<ul>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">The fields do not need to be in the same order as they are in the table.</span></li>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">You do not need to enter data into every field unless the field is set up as required on the table side.</span></li>
</ul>
<br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;">The above example is a bit silly since you would rarely have the opportunity to hard code values into an insert query. So to show a more practical application, you can use the same query to refer to fields on a form. Then call the query from a VBA statement or Macro and it will insert the values that you entered into fields on a form, directly into your table.</span><br />
<br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;"> INSERT INTO Customer (FirstName, LastName, Address, State, City, PostalCode) VALUES </span><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;"> (Forms!myForm.Fname, Forms!myForm.Lname, Forms!myForm.Address, Forms!myForm.State, </span><span style="color: black; font-family: Arial, Helvetica, sans-serif;">Forms!myForm.City, Forms!myForm.Zip); </span><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;"></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">In this example, the user would enter data into unbound controls on a form and the query would pick up those values and insert them into your table.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">You can also use an insert query to append many records at one time. Usually this is appending all the records from one table or from a query. I often use this type of query to append all the ID numbers of a group into another table to record that I performed some action on them (for example, sent them a letter). I'll also use this type of query to perform archiving of records from one table into a master table.</span><span style="color: black; font-family: Arial, Helvetica, sans-serif;">Rules:</span><br />
<ul>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">Inserted data must have the same data type as the field Or be convertible to the same data type</span></li>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">An inserted value can not violate a check constraint</span></li>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">If a column is omitted the field is assigned:</span></li>
<ul>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">Default value if one is set</span></li>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">NULL</span></li>
</ul>
</ul>
<span style="font-family: Arial, Helvetica, sans-serif;"><br />
<span style="color: black;"></span></span><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;"></span><br />
<strong><span style="color: black; font-family: Arial, Helvetica, sans-serif;"> UPDATE</span></strong><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;">An UPDATE query is used to edit existing data in the database. It can be used to update one specific row in a table or all rows in a table. This kind of query can use the WHERE clause in order to select the specific records to be updated. Not entering any criteria will cause all rows to be updated.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br />
<span style="color: black;"></span></span><br />
<span style="background-color: white; color: black; font-family: Arial, Helvetica, sans-serif;"> UPDATE Customer SET FirstName = 'Robert', </span><br />
<span style="background-color: white; color: black; font-family: Arial, Helvetica, sans-serif;"> WHERE FirstName = 'Rob';</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br />
<span style="color: black;"></span></span><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;">UPDATE Rules:</span> <br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br />
<span style="color: black;"></span></span><br />
<ul>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">You must specify the Table to update, the Columns to update, and their new values.</span></li>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">Optional search condition that specifies rows to update</span></li>
</ul>
<br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;"><strong> DELETE</strong></span><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;">Use a DELETE query to remove/delete:</span><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;"></span><br />
<ul>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">A specific row in a table</span></li>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">All rows in a table</span></li>
</ul>
<br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;">You can use the WHERE clause to select a specific record to delete or a sub set of records to delete. If you do not enter a WHERE clause it will delete ALL the records in the table. </span><br />
<br />
<span style="color: black;">DELETE *</span><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;">FROM Customer</span><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;">WHERE State ="NJ";</span><br />
<br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;">Rules:</span><br />
<ul>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">You must select the table to delete from.</span></li>
<li><span style="color: black; font-family: Arial, Helvetica, sans-serif;">You can not delete certain fields. You must delete the entire row.</span></li>
</ul>
<br />
<span style="font-family: Arial;">Each one of these queries has so many more possible ways that they can be used than is presented here. The point is to recognize that these types of queries exist and to begin thinking of queries as more than just selecting records and columns.</span><br />
<br />
<span style="font-family: Arial;">Robby</span><br />
<br />
<span style="color: black;"></span><br />Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com0tag:blogger.com,1999:blog-558158604058309640.post-31372010324847853382011-05-04T23:59:00.006-04:002011-05-10T19:01:36.985-04:00Where does an Access Database Application fit into my Organization<div>Many people don't understand Microsoft Access and have a hard time seeing how it can help them. They hear the word "database" and think "large system", "programming", "servers", and get overwhelmed. When they have data to store, they revert back to Excel spreadsheets and workbooks or just putting it in Word. True, its safe and easy and I can see it there right away. Incorporating an Access database into your organization doesn't have to conjur up negative feelings. Hopefully, this post will help you answer the question, "Where does an Access Database fit into my Organization?"</div><br />
<div></div>Many data challenges that companies face are not large enough or important enough to merit a solution that cost $100,000 or more. Yet just because the challenge isn't large enough to merit this investment, it doesn't mean that it doesn't exist. Access can meet this in between range of data issues and challenges. <br />
<br />
<div></div><strong>Storing data in Excel</strong><br />
As I mentioned earlier, and in other blog posts (see <a href="http://infocoreblog.blogspot.com/2011/01/beyond-spreadsheet.html">Beyond the Spreadsheet</a> post), many people default to storing data in Excel. Its is easy to get started with and usually requires no investment. One downside to getting started quickly is that users rarely take the time to think about the data that is being stored, the amount, or how the data will be used. One thing to make clear is that Excel is not a database. It is not relational and thus does not have all of the advantages that a database provides.<br />
<br />
Another thing to consider is that storing data in Excel is not secure and drives IT guys crazy. The average cost of creating an Excel application is $500. Excel is used because it is simple. People know it and can use it very quickly.<br />
<br />
<strong>Using an Access Database Application</strong><br />
Access is the most popular database in the world. It can solve a range of problems (see <a href="http://infocoreblog.blogspot.com/2011/02/top-10-reasons-to-use-ms-access.html">Top 10 Reasons to use Access</a> post) and can still be deployed fairly quickly. True that it takes some knowledge of Access. Access provides a great Return on Investment. It serves as a great tool for prototyping. The average cost of an Access solution is:<br />
<br />
Access Individual user system - $3,000<br />
Access Multi-User system - $10,000<br />
Access Department Level system - $25,000<br />
<br />
<strong>Using an Server Database Application</strong><br />
<br />
Larger than Access is a server side database. Examples of server side databases are SQL Server and Oracle. These are major systems with major investments. They are the databases that, mentioned earlier, are not useful for many small problems. They require hardware, software, database adminstrators, security concerns, conectivity concerns, and the list goes on and on. The avgerage costs for a server side database are:<br />
<br />
SQL Server - $500,000<br />
Oracle - $2,000,000<br />
<br />
There are instance where an Access database can evolve into a server side application. This is another advantage of Access as it allowed you to protype a system, determine the details, and work out the solution at a smaller investment, thus making the upgrade to SQL Server must more efficient and ecconomical.<br />
<br />
Most small businesses are never going to have the funds for ability to create true server side databases unless they outsource and rent server space somewhere.<br />
<br />
<br />
So in short, Access should be used when you are ready to graduate from using Excel and really commit to building an Application, but aren't ready to make a giant investment into the solution. <br />
Robby<br />
<br />
<div></div>Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com0tag:blogger.com,1999:blog-558158604058309640.post-52977764351145050862011-04-06T00:01:00.013-04:002011-04-06T00:01:03.130-04:00Things to do before you get buildingI recently started doing some work with Filemaker Pro as a database development tool. For those of us who are "born and bred" Access developers, this is like turning to the dark side. However, software prejudices aside, I was struck by how the stuff you do before you get into the actual building of a database are very much the same. The logic of a well designed relational database goes deeper than the software used to develop it. So, what are some of these steps? </br><br /><br /><span style="font-family:arial;color:#3333ff;"><strong>1. Think about the data: </strong></span>What things will the database be about? A good way to start is simply to write a list in good old English (or any other language you happen to be fluent in). <br /><ul><br /><li>Watch for the nouns you use: Clients, employees, invoices, appointments. These are the data entities your database will incorporate. </li><br /><li>Ask yourself what you want to know about these things. Sometimes these bits of information will be the attributes (aka fields) describing the data entity (e.g. name of client, date of invoice) but sometimes they may give rise to a new entity. A good example of this would be the payment of an invoice. If you simply want to indicate whether an invoice has been paid (a verb), you can simply attach an attribute to the entity "invoice" (which will probably become a "yes/no" field in your invoice table). But if there are things you want to know about THE PAYMENT (a noun), such as the amount, date, and method of payment, then the payment itself becomes a new data entity that will have, as one of its attributes, a reference to the invoice entity. </li><br /><li>Create a simple Entity diagram: As you turn your narrative into a graphich representation, you will clarify the entitites involved and their relationships to each other. There are many free tools available for download to assist in this process. </li></ul><br /><p></br><br /></br><br /><strong><span style="font-family:arial;color:#3333ff;">2. What does the system need to do or produce? </span></strong></p><br /><ul><br /><li>Initially, try to limit this to the core functions: those things that are the main reasons for the database. </li><br /><li>Look for any gaps in your data entity analysis. When thinking about a function that needs to take place, or a report that needs to be produced, we often discover that we have not properly included the necessary data in our design. </li><br /><li>Start to envision what forms will need to be developed. Create a list of forms with key functions for each (Record retrieval, data addition/edit/deletion) </li></ul><br /></br><br /></br><br /><p><strong><span style="font-family:arial;color:#3333ff;">3. Consider the installation environment and constraints: </span></strong></p><br /><ul><br /><li>If this is to be an Access databasse, does the intended user have Access installed? If so, what version? If not, how many user licenses are needed? (Developers can also consider packaging a solution as a runtime version to avoid the need to purchase Access. However, this introduces other issues.) </li><br /><li>Where will the database reside: on a file server or a computer? </li><br /><li>Will it be shared? If so, by how many people and how will they connect to the host location? </li><br /><li>Are all users the same? Will you need to restrict certain data or functions for different users? </li><br /><li>How secure is the environment?</li></ul><br /><p></br><br /></br><br /><strong><span style="font-family:arial;color:#3333ff;">4. Develop a naming convention: </span></strong>This may seem superficial but having a logical way of naming your database objects will make it easier to follow the flow of the system and to make modifications later on. Although Access has gotten better at updating references to tables and queries if you change the name later on, problems still come up when you do this. Experienced users/developers probably have a system that works well for them. We have used some of the following conventions: </p><br /><ul><br /><li>The names of tables that relate to data entities start with "T_" and names for those that are used to look-up values used in data entry begin with "LU_" </li><br /><li>Don't leave spaces in the names of objects. For readability, use underscores or hyphens. This is more important if you intend to do programming in VBA. </li><br /><li>Try to group related objects by using with similiar names; employee_bio, employee_addresses, employee_names. </li></ul></br><br /></br><br />As with building a house, working out the design concepts is the first step in assuring a successful outcome. <br /></br><br /><p>Joe</p>Joe Buonocorehttp://www.blogger.com/profile/08717259471981376444noreply@blogger.com1tag:blogger.com,1999:blog-558158604058309640.post-84001785512373536252011-03-15T00:01:00.001-04:002011-03-15T00:01:00.099-04:00How secure is Microsoft Access? Part 2<div>In our last post, we started to discuss the how secure Microsoft Access is. I provided some quick and easy ways to keep out preying eyes and that can be implemented right away. Please look back at "How secure is Microsoft Access? Part 1" to see these.<br />
<br />
In this post, I'm going to go into a few more complex methods for securing your Access database.<br />
<br />
<span style="color: blue;"><strong>I. Splitting your tables from the client database.</strong></span> A common practice when devleoping an Access database is to create a "front-end" and a "back-end". The front-end is the "program". It is the database that will store all your forms, reports, queries, etc. It will NOT contain the tables. The tables will be stored in a second database or "back-end". The front-end will access these tables as linked tables. This setup is used for a number of beneficial reasons, but you can utilize this to really secure your data. <br />
<br />
1. After you create your back-end database and the tables, set a database password on the file. Then when you go into the front-end and link to these tables, you will be prompted for a password. The password will be stored and now users who don't know the password will only be able to access the tables by going into the front-end.<br />
<br />
2. With a little VBA code, you can set up your database to link to the tables every time you open the database. If the user authenticates correctly, then the tables will be linked. If the user does not, the tables will not be relinked. Thus they can not be accessed at all. <br />
<br />
3. You don't have to have just one set of tables. You can have mutliple Access files each storing certain related tables. Then, the authentication procedure that you built, can determine which tables need to be linked and which do not. Thus each user will ONLY have access to those tables that they have been granted access to.<br />
<br />
<span style="color: blue;"><strong>II. Turn your front-end into an MDE file - </strong></span><span style="color: black;">Once you have finished developing your database and before you distribute it users, it is good practice to turn the front-end file into an MDE file (ACCDE file in Access 2007 and later). By creating an MDE file, you restricted the user from going into design view of Forms and Reports, and from viewing VBA code. This not only will protect your code, but it will hide sensitive information like the path to the data files and passwords needed to access them. </span><br />
<br />
On a side note, an MDE file will also perform better than an MDB file.<br />
<br />
<span style="color: blue;"><strong>III. Piggy back on existing security - </strong></span><span style="color: black;">If your database is going to reside on a network file server, you probably already have network security in place to access those shared drives. You can set Access up to piggy back on top of this existing security. In order for me to gain access to the shared drives at InfoCore, I have to sign into our network with a user login and password (lets say user name rbuonocore). This user name and password is stored in the network management system and not in Access. When I open up Access, you can write code to "pull" the network user name of the person signed in. In this case, when I open up my database, it will determine that the person signed into the network is rbuonocore. I can then build my security in Access around permissions granted to rbuonocore. I don't have to worry about authenticating or storing passwords because my network management system has already done that for me. T</span>his same concept can be used with normal Windows logins as well. <br />
<br />
So...How secure is Microsoft Access? Although not as secure as a full blown database management system, a very secure system can be created by using all these listed methods in combination; one that protects access to the system, access to the data, and access to functional areas inside the database.<br />
<br />
-Robby</div>Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com0tag:blogger.com,1999:blog-558158604058309640.post-81089033919230789662011-03-01T00:01:00.008-05:002011-03-01T00:24:39.254-05:00How secure is Microsoft Access? Part 1<div>One complaint I hear from many IT professionals who don't like Microsoft Access is that it really isn't very secure. Although Access is not as secure as some larger Database Management Systems, there are a number of things you can do, that when used in combination, can really help to secure your database. In this post, I'll present a few simple tasks that you can do that will keep out preying eyes. These methods are quick and easy and can be implemented right away.<br />
<br />
<strong><span style="color: blue;">I. Encrypting your database.</span></strong> Encrypting a database compacts the database file and helps protect it from being read by a word processor. Encrypting is particularly useful when you transmit a database electronically (FTP or Email), or when your store it on a flash drive or compact disc. Remember though that most databases will grow very fast and thus transporting them becomes very difficult anyway. Also, many email programs (Outlook included) don't like to receive emails with Access files attached. They basically view them as threats and it's difficult to receive. One quick way around this is to zip the file first and then send it. <span style="color: red;"></span><br />
<br />
<span style="color: red;">WARNING:</span> Encrypting will slow down the performance of your database.<br />
<br />
<strong><span style="color: blue;">II. Hide your objects and database window.</span></strong> This is a very simple method of securing objects (tables, forms, reports) from the casual user. Through the Start up properties, you can set your database to not show the database window at startup. You can also select settings that will stop the user from un-hiding the database window. Then if a user gets into the database window they won't see the tables, etc. that would get them into the information. <span style="color: red;"></span><br />
<br />
<span style="color: red;">WARNING: </span>This is not a very secure method. It only stops the casual person from accidently opening these objects. Anyone who know a little about Access will know how to unhide the objects, but it is still good practice to do.<br />
<br />
<strong><span style="color: blue;">III. Create a database password.</span></strong> As with most Office applications, Access provides the ability to set a database password in order to open it. This again is a simple means of protecting your data from others and limits access to the system to a select few that know the password. In this case, there is only one password. All authorized users must know the password in order to access the system. Although this is a good means of keep unauthorized users out of the system, it doesn't help much when certain authorized users only need some of the data.<br />
<br />
<span style="color: red;">WARNING:</span> There are programs out on the market that can break Access passwords very easily. I have a number of them that I use to help people when they forget their own passwords that they set.<br />
<br />
<br />
<strong><span style="color: blue;">IV. Create user sign-on.</span> </strong>Access has its own built in user level security. You can create users, provide individual passwords, have the user authenticate when they open the database, and restrict certain users to certain objects. I have always found that this is easy to get started with but not robust enough and have always opted for developing my own security for my databases. In doing so, you can not only restrict certain users to certain objects, but you can also limit the data they see. If Joe is the salesmen for the NY area, then I can limit Joe's view of the customer table to only NY customers. </div><br />
<div></div><br />
<div>These are some very simple methods that can help to secure your database. In our next blog post, I'll discuss a few more technical methods that can really lock things down.<br />
<br />
-Robby</div>Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com0tag:blogger.com,1999:blog-558158604058309640.post-57971653474596872292011-02-01T00:00:00.001-05:002011-02-27T16:57:47.747-05:00Top 10 Reasons to use MS AccessMicrosoft Access is the most popular database program in the world for a number of reasons. One of the biggest is that non-IT professionals can use it to develop solutions that are cost-effective and that address a wide range of data management issues. Access essentially brings Visual Basic, SQL Server, and Crystal reports all down into one nice package that almost anyone can start using. <br />
<br />
Here are the Top 10 reasons why I like MS Access....<br />
<br />
<strong><span style="color: #073763;">10. Great for projects with little or no budget</span></strong><br />
<span style="color: #073763;"><br />
</span><br />
Solutions can be built quickly and, if done in house, for almost no dollar cost. Many small businesses already own a version of Microsoft Access and just aren't using it. Either in house our through a consultant, applications can be built quickly and at low cost that solve many issues and have an immediate effect on your business.<br />
<br />
<br />
<strong><span style="color: #073763;">9. Allows for Rapid Application Development</span></strong><br />
<br />
Building in Access is a great Agile development tool. A non-IT professional can quickly start building an application and will quickly see how Access can address their data challenges. Access is also a great environment for prototyping. <br />
<br />
Many databases built in Access solve a specific problem and may be short term solutions. They last for a while and then disappear as the issue is resolved.<br />
<br />
Other databases start out to address one issue but evolve to address many other needs. These may eventually outgrow Access but, when its time to upscale from your Access database to a SQL server database, you are well prepared because you have your Access database to serve as a prototype.<br />
<br />
<br />
<strong><span style="color: #073763;">8. Easily Integrates with Microsoft Office</span></strong><br />
<br />
One of the best features of working with Microsoft Access is its ability to integrate with other MS Office products. You can get the most out of Office when you let each product do what it was intended to do; MS Word was created to do Word Processing, Excel, for number crunching and graphs, and Powerpoint for presentations. Data can be easily moved between Excel and Access, letting you use the best features of both. Access queries can easily provide a data source for a Word Mail Merge document. The integration of all the Microsoft Office components creates an environment that exceeds the sum of the parts. <br />
<br />
<br />
<strong><span style="color: #073763;">7. Great for Data Entry</span></strong><br />
<br />
Through the use of Forms, Access allows for very customized and easy to use forms that are great for data entry. The combination of these allow for the creation of a very user friendly interface to interact with your database. Business rules can easily be built in and through the use of combo boxes and list boxes, data entry can be simplified and uniform.<br />
<div><br />
<br />
<strong><span style="background-color: white; color: #073763;">6. Interface with other Database formats</span></strong></div><div></div>Access should be described as a tool more than just a program. You can build a solution in Access and yet link to data in other applications such as dBase, Paradox, FoxPro, as well as SQL Server, Oracle, and DB2 through ODBC connections.<br />
<br />
You can even link to other Access databases. This ability basically solves any space limitations you may worry about. Tables can be stored in as many databases as you need; each allowing for up to 2 gig of data. Then the tables are linked back together in your front end application. Basically, the only limitation to the size of your database will be the size of your hard drive or file server.<br />
<br />
<br />
<strong><span style="color: #073763;">5. Query Designer</span></strong><br />
<br />
The query design tool in Access is excellent. Dragging and dropping fields into your query couldn't be easier. It is laid out nicely and allows even the most novice query writer to begin building useful queries. As you get more experienced, you can easily switch to the SQL view to write more complex queries. <br />
<br />
Even experienced SQL writers will prefer to use the query designer just for the ease of it...I know I do.<br />
<br />
<br />
<strong><span style="color: #073763;">4. Report Generator</span></strong><br />
<br />
Now that you were able to get your data organized and in one place, you'll want reports. Bigger database systems like Oracle and SQL require a separate system to run reports. Access brings a reporting tool right into the same package. Reports can be built directly against tables or on top of pre-existing queries. Experienced and inexperienced users, through the help of wizards, can build very fancy reports quickly and easily. There are even some preformatted reports to help with labels. <br />
<br />
<br />
<strong><span style="color: #073763;">3. No need for a server or extra hardware</span></strong><br />
<br />
Unlike, Oracle or SQL, MS Access does not require the use of a server. The MS Access program and Jet Database Engine run on each machine. This means, instead of a server doing the work, each machine using the database will handle the processing. <br />
<br />
Very often, we will build our application to utilize an existing file server. The data will be stored in an Access Database on the file server and each user will have their own version of the program (the Forms, Queries, Reports, etc) on their own desktops. If the company doesn't have an existing file server, we often just dedicate one of the workstations as the server and let the datatables reside there. Another possible solution is investing in a Network Attached Storage device. These devices are small and cheep and can take the place of a file server nicely. <br />
<br />
<br />
<strong><span style="color: blue;"><span style="color: #073763;">2. Multiple users</span> </span></strong><br />
<br />
Although Access has advantages for the single user, sharing the the database with multiple people all at the same time is where you will really see the biggest impact. MS Access will handle all the record locking and worrying about who made what changes to which records. Unlike working with Excel, you'll never get that annoying message that someone already has your spreadsheet open. Access was designed to be used by many users (by default 255 can use it at one time). Through the use of forms and some coding, security can be set up so that certain users can only view certain data. Of course you can get very complex with security as well and get into all sorts of levels with Read/View only or Read/Write levels.<br />
<br />
<br />
<strong><span style="color: #073763;">1. Programming with Macro's and VBA</span></strong><br />
<br />
One of my favorite aspects of Access is that it allows you to program using Macro's or VBA code. When I first started using Access (back in Access 2.0), Macro's gave me my first taste of programming. While building Forms, I discovered how to make buttons work through the use of macro's. I could program buttons to open new Forms, run reports, run queries, etc. They are easy to use and give users a good taste of how the database can be transformed into an application.<br />
<br />
As if Macro's weren't enough, Access also gives you the option to program in VBA. Once you become more experienced you will realize how powerful VBA can be and how many more options it gives you. VBA is a version of Visual Basic. It is the code that controls the application and allows all the pieces of your database to work together as one, seamless, application.<br />
<br />
<br />
<br />
These are just a few of the reasons I like using Access. While looking back at these, I can’t help but realize a recurring theme in most of them. Access provides all the tools necessary for a very inexperienced user to create a new database and start building while at the same time providing experienced users a great environment to build more complex systems.<br />
<br />
How great is a program that really allows you to grow your skills all within the same application?<br />
<br />
RobbyRobert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com0tag:blogger.com,1999:blog-558158604058309640.post-60967022721327961162011-01-03T10:30:00.006-05:002011-02-27T16:58:08.673-05:00Beyond the Spreadsheet<div>Lists! Some people love them; others hate them. No matter which category you’re in, lists are a fact of life since we all have stuff we need to keep track of: customers, invoices, things that have happened, things to do. For those of us of a certain age, our first impulse might be a card file (Remember when every desk had a Rolodex?). In today’s computer world, many people may use a word processing program for simple lists but for more complicated records, a spreadsheet such as Microsoft Excel is often the program of choice.<br />
<br />
Spreadsheets are great for many purposes, especially those that used to be accomplished on accounting paper and involve lots of mathematical calculations and they have evolved to include many data management features. In addition, the program is often included in the basic software found on many computers (Excel is included in the most basic versions of Microsoft Office). However, the basic structure of spreadsheets make them difficult to manage for more than moderately complex information. This is because a spreadsheet relates data spatially, based on their physical location, not logically, based on the information being stored. Many people have become frustrated as their once managable spreadsheet became more and more complex and they find themselves “impaled” on the grid of columns and rows.<br />
<br />
Database programs, such as Microsoft Access, offer a solution to this problem since they are, at their core, designed to store and relate sets of data. Because data are related logically, rather than spatially, a “relational database” can be a very powerful tool for handling very complex information systems. The key to success with a database is to decide at the beginning, what “sets” of information need to be stored.<br />
<br />
Let’s say we want to store information on customers and their invoices, using a mental image of multiple card files. We would establish a file with a card for every customer. On that card, we would record the basic information about the customer. We could also record information about each invoice (date, amount, whether it was paid etc.). There would, however, be a physical limit to how many invoices we could record, even if we wrote really, really small and used the back of the card. Instead, we create a separate file with a card for each invoice. In addition to the basic information, we would also record which customer was being invoiced. In this system, there would be no limit to the number of invoices we could record for a customer.<br />
<br />
But a database is more than just a place to store information. It is a system for controlling how information is entered (and by whom) that improves accuracy, efficiency and security. It is also a “magic window” into your data. Using our card file example, we could easily retrieve a customer (based on business name, contact name, phone number etc.) and automatically pull information on all related invoices by telling it which customer ID to look for. (We don’t need to tell the system that the invoices are the 13th to 27th cards in the file!)<br />
<br />
Although they don’t present the user with a familiar looking, ready made system of columns and rows to store information, a database does provide a more robust system for storing, retrieving and reporting information. Programs such as Microsoft Access include many simple to use wizards that help you get started and basic instruction is available from a variety of sources. The expense of purchasing an additional piece of software and the up-front time spent in setting up the data structure, are more than recouped by gains in efficiency and flexibility for any information processing that is an ongoing part of your business operation.<br />
<br />
<a href="http://www.infocore.us/pages/images/joe_small.jpg"><img alt="" border="0" src="http://www.infocore.us/pages/images/joe_small.jpg" style="cursor: hand; float: left; height: 88px; margin: 0px 10px 10px 0px; width: 81px;" /></a><a href="http://www.infocore.us/pages/about.htm">Joe</a></div>Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com0tag:blogger.com,1999:blog-558158604058309640.post-33346232366182237352010-11-19T16:37:00.019-05:002010-12-16T23:05:51.785-05:00Using D-Lookup function<a href="http://www.infocore.us/pages/images/Rob_small1_000.JPG"></a><span style="font-family:arial;font-size:85%;"><strong>Access 2007,2003,2002,2000,97<br /></strong></span><div></div><div></div><div></div><div>One of our readers recently asked a question. He was recording meter readings on specific dates in an Access table. In a report, he needed to run a calculation that multiplied the meter reading by the rate in effect for that particular day. He contacted us questioning the "correct" way to build this into his database. </div><div></div><br /><div>The simplest option was to store the rate in the same table as the readings. Instead of having just "Date, Meter Reading" in the table, he would have "Date, Meter Reading, Rate". Then in his query or report, he could just multiply the two values. </div><div></div><br /><div>This approach does have some drawbacks. One, it requires the user to know the current rate and it requires unneeded data entry. It is also leaves your data open to errors and inconsistencies, as it requires each user to enter the rates correctly. Another drawback, perhaps not so likely in this case, is that you can't easily change the rate value. If you found out that you had entered the wrong rate on a particular date, you'd have to go change it for ALL meter readings on that date. </div><br /><div></div><div>The second approach is to store the rate in a separate table. Being a database programmer, this is the approach I would use. It requires building a separate table, lets say "T_Rate" which would include a few fields "Date_Active, Date_InActive, Rate". When running your reports, you would have to go retrieve the rate for the time period that includes your meter reading date. </div><div></div><br /><div>There are a number of advantages in this approach. One, it requires less data entry by the user and cuts back on the possibility that the user enters the wrong rate. It also ensures that all users are using the same rate for a particular day. If you realize you got the rate wrong, you only have to change it in one place. </div><div></div><br /><div>This approach does pose one major problem...how do I get my query to retrieve the correct rate. The problem is that you can't just join your meter readings table to the rate table because there is no key field to link on (unless you enter a new reading for every day of the year even if it didn't change). This is a great use for the D-Lookup function. </div><br /><div></div><div>In your query, you can build in a field that uses the D-lookup function to retrieve your correct rate for a specific day. In simple terms, the function will take your meter reading date, go to the T_Rate table, find the correct rate, and return it to your query. </div><br /><div></div><div>The syntax for the D-Lookup is: </div><br /><div></div><div><span style="font-family:arial;color:#3333ff;"><strong>DLookup("[Field Name]", "Table Name", "Criteria" )</strong></span></div><div><span style="font-family:arial;color:#3333ff;"><strong></strong></span></div><div></div><div></div><div></div><div>In our example, it would look like </div><div></div><div><span style="font-family:arial;font-size:85%;"></span></div><div><span style="font-family:arial;font-size:85%;"></span></div><div><span style="font-family:arial;font-size:85%;color:#3333ff;"><strong></strong></span></div><div><span style="font-family:arial;color:#3333ff;"><strong>DLookup("[Rate]", "T_Rate", "#" & [MeterDate] & “# Between [Date_Active] and [Date_InActive]")<br /></strong></span><br />The hardest part about using DLookup is getting the criteria code correct but once you do, it works very well in many situations.<br /><br /><a href="http://www.infocore.us/pages/about.htm">Robby</a></div><div> </div>Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com2tag:blogger.com,1999:blog-558158604058309640.post-34278083449190743612010-11-01T00:00:00.003-04:002011-02-27T16:58:25.461-05:00Spice up Your Access Reports<a href="http://www.infocore.us/pages/images/Rob_small1_000.JPG"><img alt="" border="0" src="http://www.infocore.us/pages/images/Rob_small1_000.JPG" style="cursor: hand; float: left; height: 121px; margin: 0px 10px 10px 0px; width: 103px;" /></a> Access 2007,2003,2002,2000,97<br />
<br />
One nice feature of Access Reports is the availability of separate format properties for the Page Header/Footer, Report Header/Footer and the Detail section. If you add Groupings to the report, Group Header/Footers also become available. Each level has it’s own properties and you can format the sections to be different from the others. But what about individual records within the detail section?<br />
<br />
As you are probably aware, the detail section repeats itself for every record in the report using whatever property settings you have specified in the report design view. However, there may be times when you wish to vary the format on a record-by-record basis based on the specific values in that record. This can be done with just a little bit of code, either as a macro instruction or a VBA expression.<br />
<br />
For example, imagine a report that shows all upcoming campus events, their maximum capacity, and their current number of reservations. You would like to highlight the events that have reached their maximum capacity by having the event name print in red instead of black. You can accomplish this through OnFormat property of the detail section.<br />
<br />
“A Format event occurs for each section in a report, but before Microsoft Access formats the section for previewing or printing.”<br />
<br />
Using our example above, I’ve created a Report named Report1. This report has three fields in the detail section. These fields are txtMaximum, txtReservation, and txtEvent.<br />
<br />
1.Open Report1 in design view.<br />
2.Right Click on the Detail section and select Properties.<br />
3.In the Report Detail properties box, select the Events tab.<br />
4.In the "On Format" event of the events tab, click on the ellipsis (...) button to the right and choose Macro Builder. Then click OK. When prompted, name the macro, FormatMacro.<br />
5.Make sure you have a column named “Condition”. If you do not see this, click on the View Menu and then click on Conditions.<br />
6.In the first row, enter a Condition “[txtReservation]>=[txtMaximum]” and an Action of SetValue. SetValue has two Arguments; Item and Expression. Set the Item Argument to [Reports]![Report1]![txtEvent].[ForeColor] and set the Expression Argument to 125. This will change the font color to Red if the condition is true. Unfortunately you do need to know the number of the color are you are looking to use. On tip for figuring out the number is to set the color from the report design view using the color pallet on the toolbar. Then check the properties of the control to see what number it was set too. After a while you will begin to remember the colors.<br />
<br />
<br />
7.In the second row enter an ellipsis (…) in the condition line and enter StopMacro in the Action column. This tells the macro to exit the macro if, in fact, the first condition is true.<br />
8.In the third row leave the condition blank and enter SetValue in the action column. Set the Item Argument to [Reports]![Report1]![txtEvent].[ForeColor] and set the Expression Argument to 0. This will change the font color to black if the first condition is not met.<br />
9.Save your Macro and run your report. The Event name should be red if the reservations numbers are equal to or greater than the maximum.<br />
This can also be done using a Visual Basic expression. I prefer using the VBA code because it allows greater control and more options. If you choose to use an expression instead of a macro, you will need write an IF statement. The IF statement is a very powerful tool and if you are just getting started, using it will really make you feel like a programmer.The IF statement syntax is IF (something) THEN (do something) ELSE (do something else) END IF.<br />
<br />
Follow steps 1-4 the same as above, but in step five click on Code Builder instead of Macro Builder. Once the code window opens type the following IF statement.<br />
<br />
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)<br />
If [txtReservation]>=[txtMaximum] then<br />
Me.txtEvent.ForeColor = vbRed<br />
Else<br />
Me.txtEvent.ForeColor= vbBlack<br />
End if<br />
End Sub<br />
<br />
There are a few advantages to using the VBA code instead of macros. One is that you can use vbRed and vbBlack instead of having to figure out the color number. As you get more comfortable with the IF statement and VBA, you will be able to branch off and use ELSE IF and/or Select CASE to utilize multiple conditions and colors.<br />
<br />
Good Luck!Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com0tag:blogger.com,1999:blog-558158604058309640.post-20772085282729881522009-03-13T12:40:00.004-04:002011-02-02T22:02:39.426-05:00What can a customized database solution do for you?A database provides a “home” for all your business’ information. If you are like many busy people, you have lots of important information stored in a variety of ways: multiple spreadsheets, documents, handwritten lists and sticky notes. Finding what you need, when you need it, can be a problem. Often the same information is recorded in more than one place. How can you be sure you are looking at the most current and accurate data? A well designed database relies on the old adage “a place for everything and everything in its place”. (Think of how your mother thought your room should look!) Not only does this allow you to quickly and easily retrieve any information you need but, by linking the different “data entities” (e.g. customers, job orders, invoices, payments etc.), you get exactly what you need for the job at hand.<br />
<br />
However, a database system is more than just a repository for data. It is a tool for efficiently viewing and managing information and, since business run on information, for effectively managing your business processes. By providing a structured set of user-friendly windows, a database system: <br />
<div><div><div><br />
<ul><li>standardizes and speeds data entry through the use of list boxes and drop down menus that can be dynamically filtered,</li>
<li>assures that data entry is complete and accurate according to your specifications,</li>
<li>maintains consistent practices and procedures,</li>
<li>and allows multi-tasking and easy navigation through the system via customized toolbars and linkages.</li>
</ul><br />
Do you ever have to stop what you are doing to look something up? Perhaps a product code or a tax rate for a specific location? Built-in directories can put needed information at your fingertips. Frequently used reference lists can appear as pop-up windows, or drop-down menus throughout the system, allowing you to select items with a mouse click. Large directories can often be easily linked to your database from a source file.<br />
<br />
Of course, recording data is only half of the process. Getting information out, when and how you need it, is the other part of the equation. Routine reports and operating documents (e.g. work orders, daily schedules etc.) can be generated on-demand with the click of a button. In addition, ad-hoc reports can be generated based on a variety of selection criteria and output formats. And for those of you who want to use your data in other applications (e.g. a mail merge or spreadsheet), you can select the data you need and export it Word, Excel or other programs.<br />
<br />
Finally, a database system helps protect your information. While defense against “hackers” and other outside threats is primarily the domain of firewalls and anti-virus software, a database adds an additional layer of security, specific to the management of your data. Access to the database and to specific system functions can be password restricted and a user’s ability to view, change, add and/or delete information can be specified for each function in the database. In addition, the database can track user activity to document, among other things, when data was changed and by which user.<br />
<br />
In summary, your business can “do more, more easily.” A well designed database facilitates your existing functions, allows you to go beyond what is possible with a manual system and grows with your company. Let InfoCore be your partner in making this a reality for your business!<br />
<br />
Joe Buonocore<br />
<a href="http://www.infocore.us/pages/images/joe_small.jpg"><img alt="" border="0" src="http://www.infocore.us/pages/images/joe_small.jpg" style="float: left; height: 143px; margin: 0px 10px 10px 0px; width: 111px;" /></a></div></div></div>Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com0tag:blogger.com,1999:blog-558158604058309640.post-32559752508323358572008-11-12T16:45:00.003-05:002011-02-27T16:58:40.744-05:00Keep your data "your" dataInfoCore's mission is to help you manage your data, keeping it well organized and easily accessible. But what about keeping your data safe and making sure that your data stays YOUR data and isn't either lost or even worse, stolen. The loss of data could have a huge impact on your company. Here are some quick tips that even the least techy person could do to make their data safe. 1. Make sure you back up your data.<br />
<br />
There are two things to keep in mind when backing up your data. One is that you want to do it at a regular schedule. The second is that you would like your backups stored at a different location. Once such company, <a href="http://www.datadepositbox.com/?pid=02a4044d159920050a8b3956486d0842">DataDepositBox</a> makes instant online backups whenever your files change. Instead of scheduling your backups once at night, this service actively monitor the directories that you set and makes backups of the files that change as they change. Simply set it and forget it. You'll never have to worry about your backup again. The best part is, that you can retrieve these files from any computer with internet access and the files are stored office site at a secure location. <br />
<br />
2. Install a firewall<br />
<br />
Although this does sound scary, its really very easy to do these days. Most routers come with a firewall built in. Make sure that your cable or DSL modem is connected to an access point or a wireless router and not directly to your computer. <br />
<br />
3. Update your anti-virus software<br />
<br />
Make sure you pay for updates and select your virus software to automatically check for updates. Remember, those creating virus, ad-ware, mal-ware, and the such aren't taking breaks and neither can you. Installing your anti-virus software once and then not updating it just wont do these days. Make sure your anti-virus software comes with some kind of spy-ware/ad-ware scanning as well. These spy-ware program can be even more detrimentally than a virus as it most likely will leak your data instead of just destroying it.<br />
<br />
4. Don't let your data just get up and walk away.<br />
<br />
One of the most common types of data theft is from stolen laptops. Laptops are becoming more and more common and its scary how much data you probably keep on your laptop. After all, you need that data everywhere you go. If your laptop is stolen, that data is in the hands of someone else. One good feature is making sure your laptop is set up with a user login. It won't stop a good thief, but it will make your data more secure from just anyone happening upon your computer and taking a look at your data. When setting up a login and password, make sure your password is secure enough. Use a combination of letters and numbers and don't use your birth date. The use of the word "password" as your password also isn't as tricky as you might think!<br />
<br />
Keeping your data securely "your" data is a constant task. By following these basic steps you will have a good start at protecting yourself against system crashes, corrupted files, or computer crimes.Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com0tag:blogger.com,1999:blog-558158604058309640.post-13725774997050696922008-10-20T22:40:00.001-04:002008-10-20T22:40:30.149-04:00Simple way to add Email Capability to your Access DatabaseSo you have an Access database and are beginning to store records in it. You quickly realize the power that Access can give you but you also quickly hit a wall in your learning curve. You know there must be more you can do, but don’t know how to do it. Well why not add some email capabilities to your database? Using the SendObject command is an easy way to get this done.<br /><br />If you’ve really just begun to use Access then using a macro is probably your best bet. You can then run your macro from a command button on your form. Form my example; I will use a form called AnyForm. AnyForm is a form that is bound to either a table or query. It doesn’t really matter is the form properties are set to single form or continuous. The table or query must include a field to hold email and name. My field is called txtEmail and txtName. <br /><br />Use the toolbox and add a command button to the detail section of the form. Name this command button cmdEmail, and set the caption property to Email. In the “On Click” event of the events tab, click on the ellipse (…) button to the right and choose Macro Builder. Then click OK. When prompted, name the macro, EmailMacro. In the Action column of the macro, use the drop down and select SendObject.<br /><br />The SendObject action has a number of arguments; Object Type, Object Name, Output Format, To, CC, BCC, Subject, Message Text, Edit Message, and Template File. You can use this action to email an object (table, query, report, etc.) as an attachment, but you can also leave it blank. For my example we will leave Object Type, Name and Format blank. We will program the To argument to be filled in automatically from your form. We can do this by typing in the To argument, [Forms]![AnyForm]![txtEmail]. For the Subject argument type in “Message from (your name)”. Message Text can be filled in the same way, but to personalize it, type the following “Dear “&[Forms]![AnyForm]![txtName]. Change the Edit Message property to Yes. If you leave it as no, the email will send without you being able to edit it. By setting the property to Yes, we will be able preview and add more text to our message before we send it.<br /><br />Save and close your macro. Now open your form in Form View and click on your email command button. This event will open your default mail client, open a new mail message and populate the To, Subject, and the first line of your email! <br /><br />If you have some experience with Access and prefer to use VBA, you can do this as well. For you VBA users, instead of selecting Macro Builder, choose Code Builder. The syntax would then be:<br /><br />DoCmd.SendObject (Object Type, Object Name, Output Format, To, CC, BCC, Subject, Message Text, Edit Message, Template File)Robert Buonocorehttp://www.blogger.com/profile/14950603502629460560noreply@blogger.com0