Category Archives: SQL Server

All About Microsoft Access 2013 and Access 365


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.

Access 365 allows the rapid creation of small applications using the standard web technologies of HTML5, CSS and JavaScript in a simplified web development environment. One big feature is that Access Services can now store data to Microsoft SQL Server or an SQL Azure database.

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:

  • The Microsoft Access project (ADP)
  • The Access 97.mdb database format
  • PivotCharts and PivotTables
  • The dBASE database
  • Smart Tags (Action Tags)
  • Access 2003 Toolbars and menus
  • The Microsoft SQL Server database Upsizing Wizard
  • The Developer Source Code Control

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.

JavaScript API for Office

Access 365 has a new JavaScript API that provides programmatic access to Microsoft Office documents. The API includes application-specific data types, objects, functions and events.

The JavaScript API is a great improvement on the crude JavaScript design capabilities within HTML.

SharePoint apps

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.


Source link

Using PHP and MySQL to Develop a Simple CMS – Version 1


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:

  • cms.sql
  • updatePage.htm
  • updatePage.php
  • index.php


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.



2. USE cms;

3. CREATE table page (

4. pageID integer auto_increment,

5. contents text,

6. primary key (pageID)

7. );

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.


Source link

SEARCH Function and FIND Function in Microsoft Excel


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.

SEARCH Introduction

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.

FIND Introduction

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.

Main Differences

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.


Source link