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 , , , | Comments Off on Beginning a new Database

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 , | Comments Off on Manpower resource scheduling and communication with an Access Database for commercial contractors

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 , , , , , , , , , , , , | Comments Off on Populating a combo box with a new value

Specific Port in Connection String to SQL

When creating a project that will use SQL Server as the back end, the default port used is 1433. You need to have your firewall and router forwarding incoming requests on that port. However, if you need to use a different port, you need to specify it in your string. The way you do that is in the IP setting. Where you would type IP=99.999.999.99 for a specific port you type IP=99.999.999.99,5000. The comma after the IP address separates the IP from the Port assignment. The port can be assigned after the comma.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on Specific Port in Connection String to SQL

Access linked to SQL

Be sure to set a default value for bit fields in SQL. Access does not like it when you do not. Access will place a default value into the fields if they do not have one and as a result, all the records will be locked. The value that Access loads is not posted back to the SQL Server so Access has changed all the records but the records are still dirty. So all records will be locked. No updates can take place. The only solution if you do get to this point is to delete the problem field from SQL Server entirely. Later, after you see that Access has unlocked the records, you can return to SQL Server and add the field along with a default value.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged , | Comments Off on Access linked to SQL

64 Bit

Be aware that 64 bit operating systems along with 64 bit MS Access could spell trouble for your code. It will spell trouble for any libraries that you may hav e obtained along the way. There is a nearly total absence in the market of 64 bit versions of libraries. I would warn clients ahead of time if you can. Look through your client list and those that you know have unique libraries for whatever reason, contact them and let them know they will lose that function if they make the upgrade. The idea of 64 bit is wonderful. The reality of it is harsh.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged , , , | Comments Off on 64 Bit

Improve Db Performance in a Network Environ

Installing the following code into the first form that opens in a database will increase the database performance in a network environment, especially with Windows 7.

Option Compare Database
Â
Public Function PrintISAMStats()
    ‘ Print the values returned by the ISAMStats function
    ‘ to the Debug window.
  Â
    Debug.Print “Number of disk reads: “, DBEngine.ISAMStats(0)
    Debug.Print “Number of disk writes: “, DBEngine.ISAMStats(1)
    Debug.Print “Number of reads from cache: “, DBEngine.ISAMStats(2)
    Debug.Print “Number of reads from read-ahead cache: “, DBEngine.ISAMStats(3)
    Debug.Print “Number of locks placed: “, DBEngine.ISAMStats(4)
    Debug.Print “Number of release lock calls: “, DBEngine.ISAMStats(5)
    Debug.Print
End Function
Â
Public Function ResetISAMStats()
    ‘ This procedure resets the values returned by the
    ‘ ISAMStats function to zero.
  Â
    Dim intI As Integer
    ‘ Reset each value.
    For intI = 0 To 5
        DBEngine.ISAMStats intI, True
    Next
End Function
Â
Public Function UpdateISAM()
    DBEngine.SetOption dbMaxBufferSize, 50000
    DBEngine.SetOption dbMaxLocksPerFile, 500000
    ‘DBEngine.SetOption dbFlushTransactionTimeout, 5000
  Â
End Function
Â
Public Function runtest()
‘UpdateISAM
ResetISAMStats
Dim curtime As Date
curtime = Now
Â
DoCmd.OpenForm “frmprc10claims”
PrintISAMStats
Â
Debug.Print “Number of seconds:” & DateDiff(“s”, curtime, Now())
Â
Â
End Function

Posted in Access 2007 problems/solutions, Uncategorized | Tagged , , | Comments Off on Improve Db Performance in a Network Environ

Likesearches in large SQL tables

I’ve had several clients now that have requested a stored procedure that will return records based on key words entered by a user. There can be one or many key words and all have to match in order to match the item or product. I used to do this using a LIKE ‘%keyword1%’ AND LIKE ‘%keyword2%’ type of query using Dynamic SQL.

Using the LIKE operator like this on a large table can make for a long running query since no indexes can be used. A search query on an auto parts table with 1.2 million items took over 30 seconds to run for one of my clients. I developed a technique that would index each word in the item description in another table and use that for a search procedure. The average run time on that procedure is now 1.5 seconds.

In this article I’m going to use the AdventureWorks database to build such a table and show how to use it. Keep in mind that there isn’t much performance gain in this example since AdventureWorks had just over 500 products in it. ( I would never bother with this technique on a table that small.)

There are 2 tables that you have to create: KeyWordItems and KeyWordsToExclude. The first table has the primary key of the product or item table – in this case ProductID from Production.Product. The second table contains words that you want to ignore. This could be ‘a’, ‘an’, ‘the’ and so forth. Another type of word to ignore in most cases would be any single character word, which would exclude such things as ‘&’ or “#” if there are spaces on either side.

Here is the code to create and populate the KeyWordsToExclude table:

CREATE TABLE KeyWordsToExclude
( KeyWord VARCHAR(50) NOT NULL )
GO
ALTER TABLE KeyWordsToExclude
ADD CONSTRAINT PK_KeyWordsToExclude
PRIMARY KEY CLUSTERED (KeyWord)
GO

— Populate the KeyWordsToExclude table with whatever you don’t want to
— use.
INSERT INTO KeyWordsToExclude
( KeyWord )
SELECT ‘A’ UNION ALL
SELECT ‘AN’ UNION ALL
SELECT ‘THE’
GO
Here is the KeyWordItems table:

CREATE TABLE KeyWordItems
( KeyWord VARCHAR(50) NOT NULL
, ProductID INT NOT NULL )
GO

ALTER TABLE KeyWordItems
ADD CONSTRAINT PK_KeyWordItems
PRIMARY KEY CLUSTERED (KeyWord, ProductID)
GO
In order to populate the KeyWordItems table, you should have Jeff Moden’s table valued function – DelimitedSplit8KNEW. It is lightning fast and I’m including it here for simplicity (I hope Jeff doesn’t mind).

CREATE FUNCTION dbo.DelimitedSplit8KNEW
–===== Created by Jeff Moden (Prototype: Testing Still in Progress)
–===== Define I/O parameters
(
@pString VARCHAR(8000),
@pDelimiter CHAR(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), –10
E2(N) AS (SELECT 1 FROM E1 a, E1 b), –100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), –10,000
cteTally(N) AS (
SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4
)
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY t.N),
ItemValue = SUBSTRING(@pString,t.N+1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,t.N+1),0),DATALENGTH(@pString)+1)-t.N-1)
FROM cteTally t
WHERE t.N BETWEEN 0 AND DATALENGTH(@pString)
AND (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
;
GO
Now we come to actually populating the KeyWordItems table. There are a few quirks in the AdventureWorks product names. Slashes are used to separate some words – e.g. ‘LL Mountain Seat/Saddle’. Also some product names have commas after a word – e.g. ‘Mountain Bike Socks, M’. This type of punctuation is not likely to be entered by a user searching by product name and so needs to be ignored. In the code below there are 2 REPLACE functions that will replace a ‘/’ with a space (which makes it a separate word) and strip out commas.

SELECT
RecID = IDENTITY (INT, 1, 1)
, CAST(P.ProductID AS INT) AS ProductID, Split.ItemValue AS KeyWord
INTO #PW — ProductWords
FROM Production.Product P
CROSS APPLY (SELECT ItemValue FROM dbo.DelimitedSplit8KNEW(REPLACE(REPLACE(P.Name, ‘/’, ‘ ‘), ‘,’, ”), ‘ ‘)) AS Split
WHERE LEN(ItemValue) > 1

— Delete the words that are in KeyWordsToExclude (This could have been
— included
— above but done as a separate step to keep the SQL simple. In
— AdventureWorks there aren’t any product names that will be excluded.
DELETE PW
FROM #PW PW
INNER JOIN KeyWordsToExclude KWE ON
PW.KeyWord = KWE.KeyWord

— Delete any product key words with the same word twice. Again
— AdventureWorks product names don’t have the same word twice in them.
— Your data may differ.
DELETE PW
FROM #PW PW
INNER JOIN
(SELECT MIN(RecID) AS MinRecID, ProductID, KeyWord
FROM #PW
GROUP BY ProductID, KeyWord
HAVING COUNT(*) > 1
) AS X ON
PW.ProductID = X.ProductID
AND PW.KeyWord = X.KeyWord
AND PW.RecID X.MinRecID

— Populate the KeyWordItems table.
INSERT INTO KeyWordItems
( ProductID, KeyWord )
SELECT
ProductID, KeyWord
FROM #PW
It is VERY important to note that the SAME logic that is used to populate the KeyWordItems table must be used when handling the user input – i.e. the same punctuation handling, the same ignored words, the same restriction on no single letter words, etc. Otherwise you may end up where something that should match that won’t match.

Once the key words table has been created it will have to be maintined either in a procedure or trigger when an update occurs to the column you are using for the words (item description, product name, etc.). If any change is made to that column the procedure or trigger should delete and re-add the key words using the same logic that was used to initially populate it.

Now for some actual queries.

Here is a typical query for 2 key words – usually created in Dynamic SQL – that would search the Products table.

SELECT
ProductID, Name, Color, Size, ListPrice
FROM Production.Product
WHERE
Name LIKE ‘%nut%’
AND Name LIKE ‘%hex%’This would return something like:

Here are the actual stats from SET STATISTICS TIME ON and SET STATISTICS TIME ON which was set before running the query above:

Table ‘Product’. Scan count 1, logical reads 23, physical reads 3
, read-ahead reads 0, lob logical reads 0, lob physical reads 0
, lob read-ahead reads 0.

SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 270 ms.

The important point above is the table scan on the Product table. This is what the query plan for the above query looks like:

Here is the procedure that will do the search using the new key word tables:

CREATE PROCEDURE ItemSearch
@KeyWords VARCHAR(256)
AS

SET NOCOUNT ON;

— We need to know how many words the user entered for the search.
DECLARE
@KWCount INT

— Temp table of passed in key words
CREATE TABLE #KW
( RecID INT IDENTITY(1, 1)
, KeyWord VARCHAR(50))

— Same function and same logic used to populate KeyWordItems table.
INSERT INTO #KW
( KeyWord )
SELECT ItemValue
FROM dbo.DelimitedSplit8KNEW(REPLACE(REPLACE(@KeyWords,’/’,’ ‘),’,’,”),’ ‘) AS Split
WHERE NOT EXISTS
(SELECT 1 FROM KeyWordsToExclude KWE
WHERE KWE.KeyWord = Split.ItemValue)

— Delete any duplicate words. Same as was done for the KeyWordItems
— table.
DELETE KW
FROM #KW KW
INNER JOIN
(SELECT MIN(RecID) AS MinRecID, KeyWord
FROM #KW
GROUP BY KeyWord
HAVING COUNT(*) > 1
) AS X ON
KW.KeyWord = X.KeyWord
AND KW.RecID X.MinRecID

— Number of words in the temp table.
SELECT @KWCount = X.RecCount
FROM
(SELECT COUNT(*) AS RecCount FROM #KW
) AS X

— Create a little stub table from the temp table joined to the
— KeyWordItems table. Using this stub table avoids
— joining to the larger Product table multiple times and then
— aggregating the results. The GROUP BY also does a sort on ProductID
— so that this stub table and the main Product table are eligible
— for a MERGE JOIN. You may have to index it in order to get a
— MERGE in the query plan.

— The HAVING part means that the product name must match the #KW table
— the same number of times as the count of words. This produces the
— same effect as the AND operator in the original LIKE query.

SELECT
KWI.ProductID
INTO #KWP — Stub table of Products with the matching key words.
FROM #KW
INNER JOIN KeyWordItems KWI ON
#KW.KeyWord = KWI.KeyWord
GROUP BY KWI.ProductID
HAVING COUNT(*) = @KWCount

— Return the requested Products
SELECT
P.ProductID, P.Name, P.Color, P.Size, P.ListPrice
FROM #KWP KWP
INNER JOIN Production.Product P ON
KWP.ProductID = P.ProductID
GO
Now we run the stored procedure for the same key words:

EXEC ItemSearch
@KeyWords = ‘nut hex’
This produces the same results as the original LIKE query. Here are the stats for the procedure:

Table ‘Product’. Scan count 0, logical reads 78, physical reads 2
, read-ahead reads 0, lob logical reads 0, lob physical reads 0
, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 175 ms.

The important part of this is that there is no Scan count on the Product table. Here is what the actual query plan looks like:

Note that we get a Clustered Index Seek on the Product table instead of a scan.

As I mentioned before, since AdventureWorks only has just over 500 products, this technique doesn’t do much. When I used it against an item table of auto parts of over 1.2 million the performance differences was astounding.

This technique is most useful for searches where all criteria (and AND condition) must be met in order to qualify for a match. I hope someone finds this useful.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on Likesearches in large SQL tables

SQL Server and Visual Basic

Remember when casting values that there are some cases where a null value will result in an error and no conversion. Check your properties for your variables in VB and you fields in SQL.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on SQL Server and Visual Basic

SQL or Access Adding Records

Suggestions on how to let users enter new data into SQL or even Access.

1) Build them a real data-entry app (could be web-based, but doesn’t have to
be) that will check all the inputs before submitting the data to the tables
OR
2) Have them input whatever they want (Excel – that could be
an option).
Import into a staging table with the same field names, but all data types
set to varchar(), so that there is no validation. This way you gather
everything the users send you.
Then write an app that goes through the staging table and does all the
validations that you want, imports the good records and spits out a report
of rejected records that can be dealt with manually

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on SQL or Access Adding Records