Beginning a new Database

I’m about to begin the development of a database for the Navy (Military Naval Base Client in Maryland). The purpose of the database is to track inventory on the military base.

I’ve already had interviews with the client and gotten a statement of work.

So where to begin?

Microsoft Access Database: Using a database template

Well my first step is to take a database template I’ve developed for myself and copy/paste it into a ‘navy’ folder on my C: drive. This gets me the typical navigation most of my databases use (a main menu), a report criteria screen, a split database with relinking features, and an administration screen for managing users and their permissions.  Here’s the Main Menu I often use that’s in my template:

main-menu-example

If you’d like a copy of this template, contact me at jjaeger@datacontrolllc.com

I realize this is sort of ‘cheating’ because you probably don’t have a similar template to start with. So then what’s the next step?

I then make a list of the objects the user desires. The forms and the reports. Also the import and export processes they desire.

For this Inventory database for instance, the user desires barcode scanners as inputs in addition to typical data entry. They also want barcode labels as outputs.

Microsoft Access Database Scoping a Project
Here’s a list of the primary objects this client desires:

Repository for documents and drawings that can be linked to items
·         Work Orders
·         PMI’s
·         Assets
§  Purchase Order’s
§  Purchase card
§  Service orders
§  Receiving
§  Parts Inventory
·         Asset List/Repairs
·         Facilities Daily Log
·         Facilities Equipment
·         Test Equipment
·         Keys Log
·         Required Documents
·         System Administration
·         Flexible reporting
–          Bar code scanning capability (vendor suggested scanner)
–          Automatic email notification, using MS Outlook, ie. sending work orders to technicians

Microsoft Access database task list

From the above list of objects I’ve created a task list. I’ll then use the task list throughout the project to communicate with my client.

navy-inventory-database

Microsoft Access Database table creation process
From the list of objects above I then begin cranking out data entry forms. The first step is to build a table that lists all of the necessary forms. Please note that in my discussions with the client to scope the project and get to the statement of work, we were mocking up each of the above objects. In this particular project, they mostly handed me hard copies of what they were currently doing, such as work orders.

Microsoft Access Database Form Creation Process
Most of the data entry forms will be single form view with record filtering/finding at the tops of the forms. Most of the drop down lists will lead to lookup tables and maintenance forms for same. During this process, I need to make a decision on whether or not to use an ‘ID’ field for the maintenance table and then have the processing form/table I’m building use the ID field as well. Or I can simply list the values in the lookup table without an ID and the processing form/table will use the same name of the field.

Next Steps

So the exercises above will lead me to a rough draft of all fields/tables/processing forms/maintenance forms.  I will usually have uncovered a number of issues that I need to bring to the attention of my client after these steps.  In my next blog I’ll go into more detail on some of the form creation issues I typically run into.

Posted in Access Code Examples, Inventory Database, Microsoft Access Solutions, Military Database, Navy Database, Uncategorized | Tagged , , , | Leave a comment

Manpower resource scheduling and communication with an Access Database for commercial contractors

I recently wrapped up database development for a commercial mason, Masonomics, here in Richmond, VA.  Masonomics is a large masonry outfit that works on a variety of government, educational, and commercial projects across Virginia.   They regularly run 10 or more projects simultaneously and employ 300 folks on those projects.

Figuring out where manpower resources should be tomorrow is a big deal to these guys.  Maybe it’s a big deal to your organization too?

So we built an Access database that tracks current projects and foremen and the desired manpower resources for those projects.  We store and manage the cell phone numbers for the foremen and the employees/subcontractors that Masonomics employs.  We use those cell numbers to send texts to the employees letting them know where to be the next days.

At the end of the day, around 5:00PM, it is a superintendent’s responsibility to look at all the projects he/she is responsible for and manage the manpower resources for those commercial masonry projects throughout Virginia.  That may mean moving masons, laborers, construction machinery operators, etc from one job to another.  This Access database we developed does that.

When the project superintendent moves a manpower resource from one Virginia masonry project to another project, the manpower resource (employee) being moved receives a text message for his assignment tomorrow.  Furthermore the foremen for both the sending and receiving project receive text messages making them aware of the move.

This saves Masonomics management an immense number of evening work hours they used to spending making phone calls.  You can visualize all of these employees across Virginia instantly knowing where they should report tomorrow without 300 phone calls having to be made!

Lastly, every day the entire company is updated on whom is to be at which Virginia construction project.  In the morning, the foremen for the projects are responsible for logging into their mobile phone web page where they see their currently assigned resources.  They mark the necessary resources as (U) Unexcused Absent, (E) Excused Absent, and (T) Tardy.  That information then updates the Access Database (through a SQL Server/Web connection) and the superintendents for Masonomics may then see at a glance where they are having manpower issues as early as 8:00AM.

Please give us a call if you’d like to discuss a similar application for your company!

Commercial mason contracting access database text mobile SQL integration development

Posted in Access Code Examples, construction database, Legal Access Database, Manufacturing Access Database, Microsoft Access Solutions | Tagged , | Leave a comment

Populating a combo box with a new value

I really am not a fan of the code examples out there for adding an item to a drop down list.  I’d like to give you a simple example of how to approach.

So here’s the scenario:

You’ve got a drop down (cboItemID) of Items (tbl_Items) based on an item table with a key field of itemid.

You want to enter an item into the drop down list that’s not in the tbl_Items table. So you want to add the item to the item table, and then the drop down will accept the new item.

Here’s how I did it:

1 – Set drop down (combo box) LimitToList property to ‘Yes’

2 – Set the drop down On NotInList property to [event procedure] and enter the following code:

Private Sub cboItemID_NotInList(NewData As String, Response As Integer)

    Dim intReply As Integer

    intReply = MsgBox(NewData & " is not in the list of Items. Do you wish to add it?", vbYesNo, "Add Item?")

    If intReply = 6 Then
        CurrentDb.Execute "INSERT INTO tbl_Items ( Item_Description ) SELECT '" & NewData & "' AS Item_Description"
        Response = acDataErrAdded
    Else
        cboItemID = Null
        Response = acDataErrContinue
    End If
 
End Sub

 

Combo box notinlist limit to list property

Please note that I made the assumption that cboItemID is a 2 column combo box with ItemID as the first column and Item_Description as the second Column.

If you have any question on this code, please feel free to email me or give me a call. I’m happy to help out.

Jack Jaeger
Data Control
jjaeger@datacontrolllc.com
804 928 4111

Posted in Access Code Examples, Legal Access Database, Manufacturing Access Database, Microsoft Access Solutions | Tagged , , , , , , , , , , , , | Leave a comment

Are You Going to Access Day?

If you have been reading this blog, or if you are a talented up and coming developer who wants to attend a workshop with some of the Access geniuses of today, then you may be interested by an event called Access Day being held in Irvine, California.

Hosted by Armen Stein, an Access MVP, the event is a one day conference about everything Access related.  There will be developers, book authors, and even some of the programming team from Microsoft behind Access.

For more details, visit www.accessday.org

Posted in Announcements/News | Leave a comment

Importing an Excel Spreadsheet Into Access

Often times, it is far easier to input raw data into a spreadsheet.  Because of the table like format, copying and pasting can be done quickly and efficiently.  Add to that the ability to copy formulas down thousands of rows with a quick double-click (if you know where), and you can create the data much faster than you might be able to in Access.

However, for tracking that data, analysing it, and reporting it, Microsoft Access is the much better tool.  Beyond that, Access allows for relationships between data, which comes in very handy for analyzing when specific products were added to specific orders for a specific customer.  Tracking that in Excel is possible, but would be a nightmare to maintain.

So, what do you do with that data that is so easily entered into Excel when you need the analyzing power of Access?  Really, you have two options here.  One option is to link to the Excel spreadsheet. The other is to import the Excel spreadsheet.

Linking to an Excel spreadsheet from within Access is a viable option, but is not one I recommend on a normal basis.  The problem for me is that you are splitting up the data into multiple sources.  If that excel spreadsheet were to get moved or deleted, the data would be unusable and anything in the Access database that is depending on it would stop working correctly.  Plus, Excel doesn’t have all the bells and whistles of an Access table, such as preventing duplicate data from being entered into a field that is being used as an index.  In Access, you can, and normally would, define that Product 1 is named “Sample.”  In this case, you do not want another product to have a number 1 and perhaps you do not want any other product to be named “Sample.”  You can do this easily from within Access, but not in a linked Excel spreadsheet.

The better option in most cases is to import the spreadsheet and store the data within an Access table.  You can then set that table to have the indexes in place to make sure duplicates are not imported into the table.  When an import attempts to do so, Access will throw off a warning and the data will not be imported.

Importing data from Excel into Access is a fairly simple process, if you only want to do it one time.  At the top of the screen, using 2007+, in the External Data Ribbon, in the Import & Link section, you will see the button for Excel.  If you read my post on splitting a database, this should be familiar to you, except in that case you were linking from Access.  The Excel button will run you through a wizard, where you will find the correct Excel spreadsheet and be able to make sure that the fields being imported are of the correct data type (i.e. dates are not being classified as text and so on).

A word of caution, before you begin the import process.  Blank cells do not mean empty cells.  The import wizard will import any fields that it believes are populated.  If you ever entered information into cell H2 in the Excel spreadsheet, even if you erased it later, Access will import the entire H column as a field.  In order to avoid this, before you run the import, select the entire column by left clicking the column letter at the top, right click the column letter, and select “Delete.”  This will delete the entire column, but the columns will rename themselves, so in the sample above, H would be deleted, I would become H, J would become I and so forth.  This works for multiple columns, just select the first column, hold the shift key, and select the last column.  It also works for rows.

Importing an Excel spreadsheet can also be done programmatically through VBA code, but I will be posting that next week, so stay tuned!

Posted in Uncategorized | Leave a comment

Did You Know? Using Access as a CMS

Content management systems (CMS) are getting a lot of hype these days.  While there are still many websites being created in the traditional sense, utilizing HTML for static pages, more and more websites are utilizing dynamic content driven by a database.  More often than not, these cms based websites utilize an open source database such as MySQL just because of the fact that it is free and relatively easy to set up.  However, a cms can actually use any sort of internet friendly database including SQL Server.

However, there are three parts to a good cms.  There is the database, of course, there is the website that retrieves the information from that database, and then there is the management console.  Usually, this management console is built in a subdirectory of the website using the same computing language as the website itself.  However, all that is really necessary is a way to communicate with the database and add/edit the information present to make the website display the most up to date data.

In essence, Microsoft Access is already set up to do this.  All you need is to connect your Access database to the database being used for your website, and you are all set.  Creating forms and queries will certainly help you find, edit or add information easier, but just utilizing the tables themselves can put the information onto the internet.

This doesn’t have to be for the entirety of the website.  It can be for portions or even a single page.  As long as you know which tables your website is pulling from, you can edit the information whenever you would like.  Some of the other functionality of a cms may be harder to add in, but it is all possible.  Plus, if set up correctly, you could make changes to your website on your desktop while you do not have internet connection and just set the database to send that information up to your server at a later time.

Recently, we did utilized this technique for creating pages that held information about the various services we offer and the clients we have helped.  All of it is stored in a SQL Server database and managed through a local Access interface.

So, in essence, you can use Microsoft Access to control your website.

Did you know that?

Posted in Did you know | Leave a comment

Do You Need SQL Server?

In a previous post, I spoke of splitting your database into a front end and a backend.  In that example, I used the idea of separating one Access database into two Access databases, however that is not the only option you have.which will be explained in a lat

The truth is that Access can be a front end interface for a number of types of database backends.  By utilizing cons called ODBC connections, which will be explained in a later post, you can link your Access database to just about any database on your computer, on your network, or accessible via the internet.

Recently, using this same technique to have a SQL Server database act as a backend.  Like Access, it is a Microsoft product, but where Access was built for the consumer and small business needs, SQL Server was built to support the needs of commercial enterprises and large scale databases.  It is also a much different experience to maintain a sql server database so it should only be undertaken if needed.  Thus, the question becomes:

Do you need SQL Server?

The biggest advantage of SQL Server is size.  An Access database is limited to 2 gb of disk space.  Once it reaches that maximum, you will be unable to add any more data.  This can be a great amount of data and depending on how much data you are storing and how often you are adding to it, this may be way more than enough.  Plus, this is only the limit for a single Access database.  Your front end interface can be linked to multiple back end data files.  Indeed, even many corporate clients have chosen to go this route rather than make the switch to SQL Server.  If you have reached the 2 gb limit, but it has taken 5 years or more of data entry to get to that point, you may want to just move your current data into an archive database and begin anew.  If, however, you are finding that you can reach that amount in a month, it may be time to upgrade your backend to SQL Server.

SQL Server measures its maximum space differently.  While Access is limited by the amount of data that can be stored in a single Access database, SQL Server is limited by the amount of data that the installation of SQL Server can hold, spread across all of the databases on the machine.  This amount differs with each version of SQL Server and the most recent version, 2012, this amount is 524,272 terabytes, or the equivalent of 262 million Access databases.

The next biggest advantage of SQL Server is accessibility.  SQL Server is designed to be web friendly.  It can have its own port associated with it and has internal security settings to let users have access to the database.  While similar measures can be set up within Access, they are significantly harder to put into place and not nearly as effective as those within SQL Server.

This accessibility also makes SQL Server ideal for tying a database into a web application.  While not usually suitable for open source content management systems, such as WordPress, it still can be used with many other cms systems (a topic for the future).  It can also be helpful for setting up e-commerce solutions and allowing you to view the data within your already set up Access interface.

If size or accessibility matter to you, then SQL Server may be the right way to go.  Next week, I will go into the process of creating a SQL Server database and how to upgrade your current Access back end.

Posted in SQL Server Information | Leave a comment

Valentines Day Code

In honor of Valentines Day, here is how a guy would calculate how much to spend on Valentine’s Day

Function fncHowMuchMoneyToSpendOnDate(iYearsTogether As Integer, ynMarried As Boolean, ynTryingToImpressHer As Boolean) As Variant

    Dim curMoneyForFlowers As Currency
    Dim curMoneyForChocolates As Currency
    Dim curMoneyForDinner As Currency
    
    Select Case ynTryingToImpressHer
        Case True
            Dim iMultiplier As Integer
            
            If iYearsTogether <= 1 Then
                iMultiplier = 2
            ElseIf ynMarried = True And iYearsTogether >= 9 Then
                iMultiplier = 10
            Else
                iMultiplier = iYearsTogether + 1
            End If
            
            curMoneyForFlowers = 50
            curMoneyForChocolates = 25
            curMoneyForDinner = 100
            
            fncHowMuchMoneyToSpendOnDate = (curMoneyForFlowers + curMoneyForChocolates + curMoneyForDinner) * iMultiplier
        
        Case False
            
            fncHowMuchMoneyToSpendOnDate = "As little as possible"
    
    End Select

End Function
Posted in Uncategorized | Leave a comment

Did you know? Mail Merge in Word

Mail merges in Microsoft Word are an old tool that a majority of business men and women have used in the past.  If you are sending out letters to your customers, clients, partners, suppliers, or just about anyone else, a mail merge will let you create letters with their names, addresses, contact information, and a number of other fields filled in.  You just design the word document, put the mail merge fields where you want them to go, and type in a list of people it should go to.

Did you know, however, that you can link a mail merge in Word to an existing Microsoft Access database?  If you use an Access Database to manage your contacts or customers, or even if you have a Access powered POS system that tracks orders and billing information, you can set up a mail merge to draw directly from that data.

In Word, when you are putting in the list of recipients, you are presented with the choice of using an existing list.  This list can be in a number of formats, including a Microsoft Access Database.  You just browse to that Access Database, select it, and choose the table that holds all of your customer contact information.  If your database developer used proper naming conventions, it should be a relatively easy task to determine which table that information is stored in.  From there, your mail merge will work just like normal.

You can use this same process to create custom labels, invoices (if you are not doing it through your database already), order forms, and more.

Did you know that?

Posted in Did you know | Leave a comment

Support Ending for Office 2003

On April 8th, just over 2 months from now, Microsoft will officially stop providing support for both Windows XP and Office 2003.  What does that mean for you and your business?

According to statcounter.com, as of today, 17.78% of people worldwide, and 10.92% of Americans, are still using Windows XP.  While the statistics for Office 2003 usage are unknown, I still see a few clients utilizing it.  What they all have in common is that on April 8th, they will all be in a lot of trouble if anything were to go wrong.

Why is Office 2003 still popular?

Office 2003 was the last version of Microsoft Office before Microsoft made the switch to the new ribbon menu system.  It was also the last before Microsoft introduced many new file formats for its various products including .docx for Word, .xlsx for Excel, and .accdb for Access to name a few.

While the new file formats caused some grief for users of 2003, due to compatibility issues, Microsoft released a compatibility pack soon after 2007’s release.  However, the switch to the new ribbon menu was enough to scare off many potential new users.  The new menu was so vastly different from the older menu, and unlike anything else Microsoft users had seen before, that it did not appeal to the company’s biggest client segment, commercial users.  Companies did not want to spend the time and money to train their employees on how to use the new version of the software.  Office 2003 was stable and it worked, so why switch?

The Era of Windows XP

Windows XP can probably be considered Microsoft’s most significant and popular operating system since it first released Windows 3.1.  While Windows 95 was the first OS to show us the new look of operating systems and what we now see as the standard Desktop, it was Windows XP that added a dimension of efficiently used power behind it.  While Windows Vista added power, it did so by utilizing a large majority of the computer’s memory to do so.  Windows XP was efficient and fast.  It let businesses run the software they needed to at speeds that optimized efficiency.

11 years later, it is still the number 2 operating system in popularity.  Only the versatile Windows 7 was able to pass it, and that took a while.  Windows Vista can be considered as much of a dud as Microsoft ever released and Windows 8 has failed to gain ground, likely too different and touch-screen oriented for a market of mostly commercial and enterprise users who use a keyboard and mouse the majority of the time.

What does the end of support mean?

It is a given fact that hackers are constantly trying to find ways into other systems, exploiting holes in the operating system that allow them entry.  Viruses, bugs, worms, and other tools are used to gain entry and to mess up the system.  Bugs can also occur naturally when the environment a program operates in has changed.  Microsoft is constantly working to try and patch up these holes, before they can be exploited and to correct the bugs that already exist.

Once support ends, these patches will no longer be made.  The holes will continue to exist and the security concerns can be exploited.  While it does not mean that the programs cannot continue to be used after the end of support, it does mean that the risk of something happening afterwards will begin to grow exponentially and that risk will need to be measured against the monetary and time cost of upgrading to the more recent technologies.

Posted in Announcements/News | Leave a comment