Connecting to Microsoft Access to import CCB API data

How many times have you said or experienced the following statement: If only I could get info into Microsoft Access from CCB, then I could really shine and do my best work?

Not all churches use databases such as NoSQL, MongoDB, MySQL, SQL, Oracle, etc.  I know I lost a few of you with the aforementioned technical database alphabet soup, but hang in there with me for a moment.

Nevertheless, depending on the size and technical expertise on staff, churches often are quite familiar with working in Excel and Microsoft Access to produce their weekly, monthly, quarterly and annual reports.

See there, I’m reeling it back in to world’s you know: Excel and Microsoft Access.  Ok, Microsoft Access may be a stretch, but Excel shouldn’t.  But I digress.

Where most churches run into trouble is when their data lives in a Church Management Software that doesn’t provide native functionality to easily retrieve the data they most desperately desire.

Have you ever needed data outside of CCB to massage and manipulate to your liking only to find out that it’s available via the API and not native CCB functionality?

Well, today’s the day you can rejoice (although every day is a day to rejoice in the Lord). 🙂

Today’s tutorial will specifically focus on churches that use Microsoft Access as their reporting tool of choice, typically finance teams.

I’ll show you how to use PHP to connect to Microsoft access, bridging the gap between CCB’s API and Microsoft Access data.  Let’s get started!

PHP environment needed for this tutorial

Before we begin, make sure you have access to a web environment that allows you to execute PHP. If you’re not familiar with setting up or working with PHP, might I suggest you visit the Getting Started page and scroll to the bottom to learn about using a public web server or a local web server.

If you don’t have access to a website with PHP, then complete this tutorial using Apache Friends’ XAMPP PHP development environment on your local laptop or desktop for Windows, Mac, or Linux.  It’s quite easy to setup.  Just read, point, click and follow the instructions.  The  Apache Friends site even provides video walkthroughs of the complete installation and operation.

Moving right ahead, we’ll cover how to add (insert), retrieve (select), modify (update), remove (delete) data to a Microsoft Access database table.  If you’ve had some experience with database querying, then you’ll be right at home.  No worries if you have no clue as to what a query is, because you read and learn more about SQL queries.

Using PHP’s ODBC and PDO to connect to Microsoft Access

Now we’re ready to get the basic building blocks in place to connect to Microsoft Access database using PHP.

We’ll focus on two options when using PHP to connect to Microsoft Access: ODBC and PDO.  I won’t go into the specifics, but just know in short that both allow you to make a database connection.  You’ll have to read up on the history for each.

In addition, please be sure that the PDO ODBC is present as an extension and uncommented (i.e., extension=php_pdo_odbc.dll) in the php.ini file.

We’ll use PDO for this tutorial although I’ll provide a ODBC example too at the very end.

How to create a Microsoft Access database connection

Just as we did with the MySQL and backup CCB data tutorials, proper database credentials need to be setup as variables.

The Microsoft Access database’s username (dbUsername) and password (dbPassword), and the database path (dbPath), where the database is located, all need to be defined as variables.  Also, make sure to assign each their respective values as shown below in the code example.

Next, we’ll add a bit of error checking to verify that the database path to file (dbPath) exists using the built-in PHP file_exists method.  Simply check that the file path to the database doesn’t exist.

Should it not, then we’ll use the built-in PHP die method to kill or halt any other logic from executing. Should the path to the database file exist, then it’s time to make the long awaited database connection using PDO and the database credentials previously defined.

Define the dbConnect variable and assign it a new PDO object as shown below.  And there you have it.  This is how you use PHP’s PDO to create a Microsoft Access database connection.

Let’s move on to query statements to add, view, modify and delete Microsoft Access database table data.

<?php  
 
$dbUsername="root";  
$dbPassword="d8P@55w0rd";  

$dbPath="c:/db/path/name.mdb";  //path to database file

//before we get to far, check to see that $dbName does exist on the server/desktop/laptop where Microsoft Access is running
if(!file_exists($dbPath)){
	die("Oops!  No access database file found at the following path: <b>$dbPath</b>.")
}

//create a new PDO object
$dbConnect = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb,*.accdb)}; DBQ=$dbPath; Uid=$dbUsername; Pwd=$dbPassword;");

?>

Microsoft Access insert query PHP file

To enter data into a Microsoft Access database’s table, use the insert query statement as shown below.

In this example, define the insertQuery variable and create an insert query statement to enter FirstName, LastName and Email into a database table named tblPersons.  Basically, this is a table for tracking individuals.

Now use an if else statement to test whether or not the insert query successfully created a record in the tblPersons table.

If the logic of $dbConnect->query($insertQuery) is successful, then we echo the following message: successfully inserted.  $dbConnect->query($insertQuery) is connecting to the database and executing the insertQuery statement.

If the logic of $dbConnect->query($insertQuery) is NOT successful, then we return the $dbConnect->errorInfo() and echo the specific dbError variable error array values.

<?PHP

//insert query statement
$insertQuery = "INSERT INTO tblPersons (FirstName,LastName,Email) VALUES ('alvin','brown','info@ccbtutorials.com')";

if($dbConnect->query($insertQuery)){

	echo ' successfully inserted';

} else {
	$dbError = $dbConnect->errorInfo();
	echo 'Error: ('.$dbError[0].') -- '.$dbError[2];
}

?>

Microsoft Access select query PHP file

To view Microsoft Access database’s table data, use the select query statement as shown below.

In this example, define the selectQuery variable and create a select query statement to retrieve the idFirstName, LastName and Email from the database table named tblPersons.

Now, execute the selectQuery variable’s select query statement by defining the selectResults as variable and assigning it the following: $dbConnect->query($selectQuery).

To display all records from the tblPersons table, use the while statement, passing it the $data = $selectResults->fetch(), to fetch results and echo the data array values for idFirstName, LastName and Email.

In short, the while loop or statement iterates or loops through from the first to the last record of a table if data records are available.

<?PHP

//select query statement
$selectQuery="SELECT id, FirstName, LastName, Email FROM tblPersons"; 

$selectResults = $dbConnect->query($selectQuery);
while ($data = $selectResults->fetch()) {
	
	echo $data['id'].'-'.$data['FirstName'].' '.$data['LastName'].' - '.$data['email'];

}

?>

Microsoft Access update query PHP file

To update Microsoft Access database’s table data, use the update query statement as shown below.  The update query resembles and acts very much like the insert query statement although the update query statement and syntax itself is quite a bit different.

<?PHP

//update query statement
$updateQuery = "UPDATE tblPersons SET LastName='browning' where id='123'";

if($dbConnect->query($updateQuery)){

	echo ' successfully updated';

} else {
	$dbError = $dbConnect->errorInfo();
	echo 'Error: ('.$dbError[0].') -- '.$dbError[2];
}

?>

Microsoft Access delete query PHP file

To delete Microsoft Access database’s table data, use the delete query statement as shown below. The delete query resembles and acts very much like the insert and update query statements although the delete query statement and syntax itself is quite a bit different.

<?PHP

//delete query statement
$deleteQuery = "DELETE FROM tblPersons WHERE id='123'";

if($dbConnect->query($deleteQuery)){

	echo ' successfully deleted';

} else {
	$dbError = $dbConnect->errorInfo();
	echo 'Error: ('.$dbError[0].') -- '.$dbError[2];
}

?>

Using ODBC to connect to Microsoft Access

In addition to PDO, ODBC is yet another way to connect to Microsoft.  The code example below resembles that of PDO in structure, yet the PDO syntax has been replaced by ODBC specific syntax.  I’m not going to explain it, so use it at your own risk. 🙂

<?php  
 
$dbName="name of db here or path to db file";  
$dbUsername="root";  
$dbPassword="d8P@55w0rd";  

$selectQuery="SELECT id, FirstName, LastName, Email FROM tblPersons";  

$maConnect = odbc_connect($dbName, $dbUsername, $dbPassword);  

$results = odbc_exec($maConnect, $selectQuery);   
   
while( odbc_fetch_row( $results )){  
	$id  = odbc_result($results,"id");
	$FirstName  = odbc_result($results,"FirstName");
	$LastName  = odbc_result($results,"LastName");
	$Email  = odbc_result($results,"Email");

	echo "<p>".$id." - ".$FirstName." ".$LastName." - ".$Email . "</p>";  
 }  
   
odbc_free_result($results);  
odbc_close($maConnect);  

?>

Well, that’ll do it for this tutorial.  Yes, I realize there was no integration of CCB and Microsoft Access.  However, there are previous tutorials that show you how to retrieve your CCB data.

I encourage you to take an educated guess at integrating your CCB data and Microsoft Access.  Use previous tutorials with the knowledge you’ve gain from this tutorial about connecting and assessing Microsoft Access, and you should be well on your way.

If not, no worries, I’ll provide a tutorial in the coming weeks to show you how to get your data from CCB using the API and into a Microsoft Access database.  See you around, rather see you next week.

Related Posts

Subscribe and receive the following...

  • Inside CCB tips and tricks
  • Instant CCB tutorial alerts and updates
  • CCB How To's, Videos, Webinars and more...