I’m super excited to share my TEDx Talk. I gave this talk at TEDxHickory in March, 2015. Please share with your friends and give me some feedback.
I’m super excited to share my TEDx Talk. I gave this talk at TEDxHickory in March, 2015. Please share with your friends and give me some feedback.
Microsoft Access 2013 is the software that has progressed over the ages from Access 1.1 in 1992 until now.
Office Professional 2013 Software (which includes Word, Excel, PowerPoint, Outlook and Access) will allow you to install and run Access 2013 as usual.
The Office 365 version is paid by monthly subscription and provides Web services to Office 2013. It includes the Office Professional 2013 software as well as Access 365 and Access 2013.
Access 365, besides the name “Access”, has little to nothing in common with Access 2013 or previous versions of Access.
What is Access 365 all about?
Access 365 is a tool for creating web apps that run within SharePoint 2013. The old .ACCDB format has been abandoned. New applications should use the Access 365 model, or stick with Access 2010.
The use of SQL Server overcomes the 2 Gigabyte size limitation of the previous Access databases. SQL Server also provides all the security and features essential for any strategic company business system.
And What about Access 2013?
The Access 2013 desktop version is much unchanged from previous versions – it still supports the Front-End and Back-End database model and Visual Basic for Applications (VBA).
But Access 2013 no longer supports:
Apps for Office
There is a new feature of Access 365 – Apps for Office. This technology enables Office to be used as a service on the Cloud or Web. The webpage can be hosted in an Office application – that is Excel, Word, Outlook, PowerPoint or Project.
An app is usually published to the Office Store for use.
Access 365 provides a simple way for SharePoint to host the Front-End of an app and have the data management capabilities of SQL Server on the Web. This simplifies web development.
Note that Visual Basic for Applications (VBA) code is not compatible with SharePoint Access applications.
Access 365 Macro Editor
Templates can be used to create sophisticated looking apps – and without the need for code. However business rules and data manipulation can be implemented with a new macro language. The macros do not have the power of VBA, but are adequate for simple logic.
Microsoft’s Strategic Direction
Microsoft Access was never meant for corporate company databases. Many companies evolved the Access database software into their core administration systems. But with the limitations of network traffic, peak loads, reliability, rollback recovery, security, etc, etc – these systems eventually hit a brick wall.
The direction that Access 365 is now taking reverts to the original intention of Microsoft Access. That is, providing a rapid development environment for small Line of Business (LOB) applications.
Microsoft Access with VBA is at the end of the road. For mission critical administration systems, the best development platform is Visual Basic.Net with SQL Server.
In this article I’ll try to describe how to develop a very simple Content Management System (CMS). I’ve chosen PHP as the server-side scripting language and MySQL as the database management system purely because I think they are fairly easy to use and they do the job very well.
I won’t spend any time describing CMSs, what they are, or why you should or should not use them as there are plenty of excellent articles on this site that describe them perfectly well. I’ll just explain one way of developing one.
This CMS consists of a single web page (index.php) that can have its contents updated by use of a standard form (updatePage.htm). The contents entered via the form are stored in a database, and are accessed and displayed by the web page. Although this CMS is too simple to be of any real use, it could be used as the starting point for a real life CMS solution. In subsequent articles I’ll look at various ways to extend the CMS to make it more useful.
There are four files in this project:
This file creates a database called cms, and creates a table in that database called page. It also loads some initial data into the table. You only need to use this file once.
This web page contains a simple form that can be used to enter the contents displayed by index.php.
This is the form handler – the script that processes the data (entered in updatePage.htm) and inserts it into the database table (page).
This is the web page that displays the data held in the database table.
1. CREATE DATABASE cms;
2. USE cms;
3. CREATE table page (
4. pageID integer auto_increment,
5. contents text,
6. primary key (pageID)
8. insert into page (pageID, contents) values (‘1’, ‘dummy text’);
Line 1 creates a database called cms in the MySQL database management system.
Line 2 tells MySQL to use the database for the subsequent commands.
Line 3 creates a table in the database.
Line 4 creates a column called pageID, which will contain integers, and which will be automatically incremented as new records are added to the table. As we only have one web page (index.php) in our imaginary website, we will only have one record and therefore one integer: 1. If we added additional pages to the table, they would be automatically numbered (2, 3, 4, etc).
Line 5 creates a second column called contents, which will contain text. This is where the editable contents displayed by index.php will be stored.
Line 6 sets pageID as the primary key, which you can think of as a reference for the table. As we only have one table, which will contain only one record, we won’t make any use of the key. I’ve included it though because it’s good practice to do so.
Line 7 simply closes the bit of code that was started in line 3.
Line 8 inserts some initial data into the table: 1 as the first (and only) pageID, and ‘dummy text’ as the contents of the first record.
(Note that for display considerations, I’ve inserted spaces into the HTML tag names, otherwise they would be processed as HTML code.)
3. Really Simple CMS
6. Really Simple CMS
8. Enter page content:
This is just standard HTML, which probably doesn’t really need explaining. All it does is present a form, the contents of which are sent to updatePage.php when the ‘Update Page’ button is clicked.
This is the form handler, that’s to say, the script that processes the data entered into the form (in updatePage.htm).
Line 1 signifies the start of a PHP script.
Line 2 requests the contents that were posted from the form. We could have written $contents=$_POST[‘contents’]; instead if we had wanted to.
Line 3 connects to the MySQL database server, setting up the host name, which I’ve assumed to be localhost, the database user, which I’ve assumed to be root, and the password needed to connect to the database. I have no idea what this would be for your system so I’ve just written the word password.
Line 4 updates the page table in the CMS database with the new contents.
Line 5 closes the database connection.
Line 6 closes the PHP script.
3. Home Page
5. Home Page
This is the web page that displays the contents from the database. It’s called index.php rather than index.htm because the web page contains PHP code. If the page was called index.htm, the PHP preprocessor, which is part of the web server, would not know that the page contained PHP code, and would therefore not try to process the script part of the page (lines 6 to 13). This would cause the script itself to be displayed in the browser rather than the HTML generated by the script.
Most of the lines in this web page are pretty straight forward and don’t need explaining. Lines 6 to 13 contain the PHP script that extracts the contents from the database and displays (echos) it in the browser.
Installing/Running the CMS
To use the CMS you need to copy the files onto your web server into the area allocated for web pages. Your web server needs to support PHP and MySQL; if it doesn’t, the CMS won’t work.
You also need to use the correct database connection names and passwords (those used in the mysql_connect lines in the PHP scripts).
Exactly how you run the cms.sql file to set up the database and database table will vary from web server to web server so it’s difficult to give precise instructions here. If you have a phpMyAdmin icon or something similar in your web servers control/administration panel you should be able to use that.
Once you’ve set up the database and table, you can simply browse to the updatePage.htm web page and update the database contents. You can then browse to the index.php page to view the updates.
There are two very similar functions in Excel to look for data inside of cells matching parameters that you dictate: SEARCH and FIND. There are so similar, in fact, that one wonders why have two separate functions that perform virtually the identical results and are identical in the construct of the formula. This article will discuss he one, basic difference.
The SEARCH function is a way to find a character or string within another cell, and it will return the value associated with the starting place. In other words, if you are trying to figure out where a character is within the cell that contains a word, sentence or other type of information, you could use the SEARCH function. The format for this function is:
If, for example, the word “alphabet” was in cell C2, and your model needed the location of the letter “a” in that cell, you would use the formula =SEARCH(“a”,C2,1), and the result would be 1. To continue this simplistic example, if you were seeking the location of “b” in the word, the formula would be =SEARCH(“b”,C2,1), and the result would be 6. You can also use search on strings of characters. If, for example, cell F2 contains 1023-#555-A123, the formula =SEARCH(“A12”,F2,1) would yield the 11 as an answer.
The FIND function is another way to find a character or string within another cell, and it will return the value associated with the starting place, just like the SEARCH function. The format for this function is:
Using the same example as before, the location of the letter “a” in cell C2 would be discovered using =FIND(“a”,C2,1), and the result would be 1. Looking for “b” in cell C2 would be accomplished be =FIND(“b”,C2,1), resulting in the number 6. Finally, continuing on the similarity path, if cell F2 contains 1023-#555-A123 (as before), the formula =FIND(“A12”,F2,1) would yield the 11 as an answer. As you can see, up to this point, both methods would give you the same results.
Note: You probably quickly recognized that there are two a’s in the word located in cell C2. By stating the starting point in each of the formulas as 1, we will pick up the first instance of the letter “a”. If we needed to choose the next instance, we could merely have the “start_num” part of the formula to be 2, thus skipping the first instance of the letter and resulting in an answer of 5.
The main difference between the SEARCH function and the FIND function is that FIND is case sensitive and SEARCH is not. Thus, if you used the formula =SEARCH(“A”,C2,1) (note the capital “A”), the result would still be 1, as in the case before. If you were to use the formula =FIND(“A”,C2,1), you would get #VALUE!. FIND is case sensitive and there is no “A” in the word “alphabet”.
Another difference is that SEARCH allows for the use of wildcards whereas FIND does not. In this context, a question mark will look for an exact phrase or series of characters in a cell, and an asterisk will look for the beginning of the series of characters right before the asterisk. For example, the formula =SEARCH(“a?p”,C2,1) in our alphabet example would yield an answer of 1, as it is looking for an exact grouping of the letter “a” with anything next to it with a “p” immediately after. As this is in the beginning of the word, the value returned is 1. Continuing with the alphabet example, the formula =SEARCH(“h*t”,C2,1) would yield a value of 4. In this instance, the wildcard “*” can represent any number of characters in between the “h” and the “t” as long as there is a string beginning and ending with the two letters you use in the formula. If the formula was =SEARCH(“h*q”,C2,1), you would get #VALUE!.
In short, these two formulas are very similar, and unless you need confirmation of an exact character or string of characters, you would likely err on the side of using SEARCH. Instances where this may not be the case might involve searches involving specific SKUs or names of employees. In my experience, SEARCH has been more helpful in specific financial modeling exercises, but it is helpful to understand the differences in usage and results as you work through your own modeling projects.
Fresh website content for your visitors can be of real benefit when attempting to generate repeat traffic. Most webmasters, however, just don’t have enough spare time to frequently update or rebuild their pages manually. If your web site hosting company provides free access to PHP and MySQL, this article will show you how to combine those two open source tools and replace a portion of your websites’ static content with frequently changing dynamic content.
Why do you need dynamic content for your website?
Static pages on a website eventually become “stale” and visitor traffic can fall significantly over time. The drop in traffic can be attributed to these primary factors:
1) The reluctance of search engines to include and display your potentially “out of date” pages in their search results,
2) The finite number of other subject related websites that would be willing to link to your information on a specific topic, and
3) Visitors that learn to view your static website with a “been there, done that” attitude.
Creating and maintaining a web site requires a significant investment in time and resources. Loosing repeat visitors diminishes the value of your investment. Without repeat traffic it is virtually impossible for a website to be a continuing success.
How can you add dynamic content without having to purchase expensive software?
One proven (and easy to implement) method of creating dynamic content for your website is by rotating information on key, higher traffic web pages using PHP with a MySQL database. Rotating content can take the form of a series of rotating articles, a rotating group of product listings, or even a simple “thought for the day”. What is important is that your clients and visiting search engines find new and interesting information each time they visit your website.
As an example of dynamic content creation, we will build a system that rotates information about a group of products on the main page of a hypothetical retail sales web site that markets widgets. Our goal is to present information about a different type or model of widget available for purchase whenever a consumer visits the shopping web site.
Step One: Create a content table to hold your widget data.
There are a couple of options for storing the data to be displayed in your dynamic content rotation. The first option would be to create a new database, or perhaps simply add a table in an existing product database that will hold the information that you wish to display.
Let’s take five theoretical widget products and design a table as follows:
| item | product |
| 1 | Plastic Widgets |
| 2 | Metal Widgets |
| 3 | Wooden Widgets |
| 4 | Rubber Widgets |
| 5 | Stone Widgets |
1-a) Create your table with the following SQL statement:
CREATE TABLE `content_table` (
`item` int(4) NOT NULL auto_increment,
`product` varchar(10) NOT NULL default ”,
KEY `item` (`item`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
This table contains two fields. The first is an item number and the second is a description field that will hold the product name and features. Note: You can add fields to your actual table including: an image URL field, shopping cart direct purchase URL field, product page field, etc.
1-b) Insert the example data into your new table as follows:
INSERT INTO `content_table ` VALUES (1, ‘ Plastic Widgets’);
INSERT INTO `content_table ` VALUES (2, ‘ Metal Widgets’);
INSERT INTO `content_table ` VALUES (3, ‘ Wooden Widgets’);
INSERT INTO `content_table ` VALUES (4, ‘ Rubber Widgets’);
INSERT INTO `content_table ` VALUES (5, ‘ Stone Widgets’);
Once you have completed these two steps you will have a table compete with data to be shown on your website.
Another option would be to utilize your existing product table. If there are hundreds of different models and styles of widgets already in one of the tables in your database, you could utilize the same structure we are learning now to connect directly to that table and display the already existing data.
Step two: Working with your new table:
For this tutorial, we will utilize one of the most effective mechanisms and perhaps the easiest to incorporate. This is the use of a random number generator for deciding which item will be shown.
To create a random number generator using PHP you must first calculate the total number of possible items that you want the system to choose from. In this example we had five items so the maximum number of choices will be 5. The reason we need this number is to limit the random numbers being delivered. If we have five items, we want the number generator to only give us a result of between 1 and 5.
We must now create a variable for our PHP code that will hold our new randomly generated item number as follows:
$mynumber = rand(1, 5);
This little snippet of code will act as the mechanism to “select” a widget product item at random from the five provided in the content table that we created.
If we created 100 different items for your dynamic display instead of just five, you would simply change the “rand (1, 5)” part of the code to reflect the different maximum number. In this case we would change it to “rand (1, 100)” so that the random number generator gives us back a number somewhere between one and one hundred.
We are now ready to extract the randomly selected item’s information from your table so that it can be displayed on your webpage.
You can now connect to your database and query your table to find the data for the item that matches the random number you created, as follows:
$query_content = “SELECT * FROM content_table WHERE item = $mynumber “;
Step three: Displaying your data:
When displaying your data it is important to maintain consistency in presentation size. It is preferable to create a table of specified dimensions (such as “width=400”) and display your results within this table. In this way the page proportions do not have to change with each new item (which can be very confusing for visitors).
Simply display the results just as if these where any other MySQL query using the echo command:
echo $query_content [‘ product ‘];
Every time your page is loaded a different widget product will be selected at random for display on that page.
What else can you do with your dynamic content?
The only limits are within your imagination. By adding a title and meta description tags to your content table, you can alternate the title and search engine description for that page. You can also utilize this system to promote affiliate programs or sponsorship opportunities by rotating affiliate links and banners.
The proper use of dynamic content can bring your website back into favor with search engines and encourage your visitors to return frequently to see what is new.
For small and large database projects, Microsoft Access works wonderfully. If you have a database with hundreds of thousands of records, maybe even connecting to other databases remotely, Access will work just fine. Security isn’t too much of a problem (no Access database is truly secure), and with a wide range of options for publishing and reporting on your data, in many cases Access is as good as it gets.
That is, until you try to put it online. That’s where converting your Access database to MySQL comes in.
While Microsoft Access is great as a desktop solution, it weakens greatly when transferred online. The limits on users and speed still exist, and it’s not built to work well on platforms other than Microsoft. Unfortunately for Microsoft Access, most web servers run a form of Unix, not Windows.
MySQL is a relational database system designed specifically for use online and housed on the most common servers online. It’s fast, robust, clean, free, and fairly intuitive to work with as long as you have a very solid understanding of all things SQL. Converting to MySQL from Microsoft Access isn’t without it’s set of problems, however there are a few things you can do to make sure that this conversion goes smoothly.
1. Organize and clean your Microsoft Access database before converting to MySQL! This can’t be stressed enough and it’s pretty much the sole reason your conversion from Access to MySQL will fail or succeed. Think of upgrading from Access to MySQL like a complicated high-wire act. If one person has trouble going across that wire, chances are making him ride a bicycle across it while balancing a ball on his nose will only end up in disaster … and a broken bicycle. Re-design your Microsoft Access database if you have to, but be absolutely sure that your database is designed flawlessly.
2. Set up your MySQL database. Just as making sure your Access database is designed flawlessly, your MySQL database must be designed just as flawlessly. MySQL has different data types than Microsoft Access does – you won’t find memo fields, for instance – so be sure that your existing data will fit into the structure you build for MySQL. Otherwise, all you’re going to get is a mess of mish-mashed data.
3. Export your Microsoft Access data to text files. Fortunately, MySQL has a very handy interface called phpMyAdmin. This allows you to interact directly with your tables through a somewhat graphical UI. All you need to have are tables and fields set up that match your data, and you can click, “Import Data from Text File” through phpMyAdmin. Browse to the text file, import it, and congratulations, your data is online!
While there is certainly more details to be learned regarding the process of converting from Microsoft Access to MySQL, always keep in mind the fundamentals. Databases that are designed perfectly will circumvent 95% of the problems that generally befall poorly designed ones. Converting Access to MySQL isn’t terribly difficult or time consuming, but it can be very tricky. Back up your data, make sure you’ve designed databases properly on both sides, and your data could be up in no time. Then, it’s on to PHP!