tag:blogger.com,1999:blog-76570791038363078162024-03-13T12:20:42.124-04:00MS Access ShareEfficient Design: Data Accessibility, Reporting, Business Intelligence, UseabilityAnonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.comBlogger18125tag:blogger.com,1999:blog-7657079103836307816.post-11704004457805724362012-07-21T12:20:00.000-04:002012-07-21T16:12:00.209-04:00MS Access Designing and Deploying for Efficiency<div style="text-align: left;">
This week a coworker and I were discussing Access design practices that can be used to improve performance and security. I got to thinking about the things he suggested and that I've picked up over time (from Microsoft and other developers) and realized that I could benefit from a review and, in some cases an update of those practices. So for anyone that might be helped by it, here's what I sifted out:</div>
<div style="text-align: left;">
</div>
<strong>Split your application into front-end/back-end architecture:</strong><br />
<br />
A split architecture has long been suggested and I have used it and found it improves performance, minimizes collisions and corruption, and makes support and program enhancements so much easier.<br />
Because the front-end, with forms, reports, queries and VBA, is on the users local desktop network traffic and load time is reduced. Support is easier and more efficient because the tables are stored in a separate database. When you make any form, report, query and/or vba modifications you simply replace the users front-end file without affecting their data. Of course in the, hopefully, rare event that you find it necessary to adjust a table struture, you can do it with users offline as before.<br />
<br />
How to split an application:<br />
<br />
There are a couple of ways to accomplish a split application but the easiest is to use the wizard Microsoft has included. From the "Database Tools" menu option, select "Access Database" in the "Move Data" section:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-XALuj0vt3oY/UAq1FVz-3nI/AAAAAAAAAHE/SEn23LncW9o/s1600/SplitAccess.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-XALuj0vt3oY/UAq1FVz-3nI/AAAAAAAAAHE/SEn23LncW9o/s1600/SplitAccess.png" /></a></div>
<br />
Your tables will be exported to the location you select (using UNC rather than mapped paths is recommended) with links included from the front end. If you move your back-end database to a different location, you will need to use the "Linked Table Manager" (under the "External Data" menu option) to re-link your tables. Now if you give users on the network a copy of your front-end and they have access to the back end location, they will be set to go.<br />
<br />
<strong>Establish a Persistent Connection:</strong><br />
<br />
In addition to the above, by keeping your back-end linked tables open with a persistent connection you improve speed. This avoids creating a new lock file (ldb) on the database each time a tables is used. <br />
<br />
Here's a quote from Microsoft with simple instructions:<br />
<br />
"You can greatly enhance performance, when opening the main database and opening tables and forms, by forcing the linked database to remain open. To do this, create an empty table in the linked database, and link the table in the main database. Then, use the <b class="ui">OpenRecordset</b> method to open the linked table. This prevents the Microsoft Jet database engine from repeatedly opening and closing the linked database and from creating and deleting the associated .ldb file."<br />
<br />
<strong>Save Database as an accde:</strong><br />
<br />
An Access 2010 database saved as an ACCDE makes it an executable file, gives a smaller file size and locks your database (first disable shift key startup and make sure you have a back up). To save the Access 2010 database in ACCDE format, first save a back up for development, open the database and on "File" menu, click "Save & Publish," select ACCDE.<br />
<br />
<strong>Password Protect VBA Code:</strong><br />
<br />
To keep zealous, curious users from accessing (and possibly changing) your VBA code, password protect it. Here's how to do it: While in the VBA editor, click on "Tools" on the Menu Bar, select your database name properties, select the "Protection" tab, select "Lock project for viewing," fill in the "Password" and "Confirm Password" fields. Please BE SURE YOU HAVE SAVED YOUR PASSWORD SOMEWHERE!!<br />
<br />
<strong>MS Access 2010 Runtime:</strong><br />
<br />
Microsoft has kindly provided us with a free runtime (no it does not expire) that we can distribute to users that do not have Access 2010 installed on their desktops. Imagine, they don't have to buy Office! The application should be tested in runtime mode before deploying to verify that all needed features are working since the runtime disables some features. To test in runtime mode, add the /runtime switch to the run line of your shortcut, or rename the file extension from accdb to accdr (thank you Antoine). If you find any lack you can program in the needed functionality before deploying.<br />
<br />
To choose the correct runtime, right click COMPUTER to see if your operating system is 32 or 64 bit. System requirements for MS Access 2010 Runtime are: <br />
Windows 7, Windows Server 2003 R2 (32-Bit x86), Windows Server 2003 R2 x64 editions, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 1, Windows XP Service Pack 3<br />
<br />
Only the 32-bit Access 2010 Runtime is supported on Windows XP Service Pack 3 (ask me how I know!).<br />
<br />
<a href="http://www.microsoft.com/en-us/download/details.aspx?id=10910">http://www.microsoft.com/en-us/download/details.aspx?id=10910</a><br />
<br />
<strong>Package and Deploy Solution Wizard:</strong><br />
<br />
Depending on your environment and needs, there are times when the Package and Deploy Solution Wizard comes in handy. It provides a simple way to bundle and deploy Microsoft Access database applications. The wizard guides you through the steps necessary to bundle stand-alone Access applications into Setup packages. You can also include the Access 2010 Runtime and create a shortcut in your package if helpful. <br />
<br />
<a href="http://office.microsoft.com/en-us/access-help/package-solution-wizard-overview-HA010294985.aspx">http://office.microsoft.com/en-us/access-help/package-solution-wizard-overview-HA010294985.aspx</a><br />
<br />
<strong>Don't forget to add error handling to your VBA code:</strong><br />
<br />
You can get quite extensive with error handling but at the least, it's good practice to let the user know something happened and move forward without offering the user the opportunity to "END" or "DEBUG."<br />
<br />
At the least include something simple like this:<br />
<br />
<div style="margin: 0px 0px 0px 36pt;">
<span style="color: black; font-family: inherit; font-size: x-small;"><span style="font-size: 11pt;">On Error GoTo Err_Traping</span></span></div>
<span style="color: black; font-family: inherit;"></span><br />
<div style="margin: 0px 0px 0px 36pt; text-indent: 36pt;">
<span style="color: black; font-family: inherit; font-size: x-small;"><span style="font-size: 11pt;">YOUR EVENT CODE</span></span></div>
<span style="color: black; font-family: inherit;"></span><br />
<div style="margin: 0px 0px 0px 36pt;">
<span style="color: black; font-family: inherit; font-size: x-small;"><span style="font-size: 11pt;">Err_Trapping:</span></span></div>
<span style="color: black; font-family: inherit;"></span><br />
<div style="margin: 0px 0px 0px 36pt;">
<span style="color: black; font-family: inherit; font-size: x-small;"><span style="font-size: 11pt;"> MsgBox err.Description</span></span></div>
<div style="margin: 0px 0px 0px 36pt;">
<span style="color: black; font-family: inherit; font-size: x-small;"><span style="font-size: 11pt;">Exit Sub '(or resume next)</span></span></div>
<br />
<strong>Additional Resources:</strong><br />
<br />
<a href="http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx">http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx</a><br />
<br />
<a href="http://www.opengatesw.net/ms-access-tutorials/Access-Articles/MSAccess-Deployment-Best-Practices.htm">http://www.opengatesw.net/ms-access-tutorials/Access-Articles/MSAccess-Deployment-Best-Practices.htm</a><br />
<a href="http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/6d2da932-05bc-4dac-b3f5-7ce10fb6cabc">http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/6d2da932-05bc-4dac-b3f5-7ce10fb6cabc</a><br />
<a href="http://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html">http://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html</a><br />
<a href="http://msdn.microsoft.com/en-us/library/office/ee358847(v=office.12).aspx">http://msdn.microsoft.com/en-us/library/office/ee358847(v=office.12).aspx</a><br />
<br />
Have I missed anything? Leave a comment, let me know if you disagree with any of my conclusions or add some additional suggestions!Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com0tag:blogger.com,1999:blog-7657079103836307816.post-11914860864816436042012-06-14T21:04:00.000-04:002012-06-17T08:12:39.059-04:00ActiveX Controls to Enhance MS Access FunctionalityActiveX is a Microsoft technology that offers components to insert into programs or web pages, similar to JAVA applets. Here's Microsoft's description of ActiveX Controls (<a href="http://support.microsoft.com/kb/154544">http://support.microsoft.com/kb/154544</a>): <br />
<br />
<em>ActiveX controls, formerly known as OLE controls or OCX controls, are components (or objects) that you can insert into a Web page or other program so that you can reuse packaged functionality that someone else programmed. For example, the ActiveX controls that are included with Internet Explorer enable you to enhance your Web pages</em> <em>with sophisticated formatting features and animation.</em> <br />
<br />
<em>ActiveX is an open integration platform that provides developers, users, and Web producers a fast and easy way to create integrated programs and content for the Internet and Intranets. Using ActiveX, you can easily insert multimedia effects, interactive objects, and sophisticated programs into a Web page, creating a user experience comparable to that of high quality multimedia CD-ROM titles.</em><br />
<br />
In other words, ActiveX controls are mini-programs that can be inserted into, say an MS Access form, to add functionality. File extensions of controls are dll (direct link library) or OCX (object linking and embedding). There are many pre-designed ActiveX controls available for free or little cost. Here are a few examples:<br />
<br />
<strong>Adobe PDF Viewer Control</strong><br />
This control allows you to embed and control the view of PDF's directly in your form. <br />
<br />
<strong>Barcode Control</strong><br />
This type of ActiveX control can be inserted into a report or form and bound to data field(s).<br />
<br />
<strong>File Download Controls</strong><br />
You can download files directly from the internet to the hard drive, with a progress notification with this type of control.<br />
<br />
<strong>Document Management Controls</strong><br />
Microsoft Office Document Imaging 2003 (MODI) adds programmability features to the document scanning and viewing tools. This is a great tool but no longer included with MS Access 2010 (<a href="http://support.microsoft.com/kb/982760">http://support.microsoft.com/kb/982760</a>).<br />
<br />
Before using a custom control, it must be registered in your install of MS Access. To do this, open the visual basic editor (Ctrl-G), select "Tools," "References" and "Browse" to your dll or ocx file, and select it. If you have registered the control correctly, in a form or report design mode, select "Design" and "ActiveX Controls" you will see and be able add the control. <br />
<br />
ActiveX Controls can be controlled by modifying the controls "Properties," or writing event handlers in VBA depending on the control. <br />
<br />
One example of how I have used a custom control is in a data entry application to view embedded PDF's. I added hot-key enabled command buttons on my form that have onclick event procedures to zoom or page through the PDF. I thus give the keyer the ability to quickly, (without using the mouse) control the navigation and viewability of the PDF and thus speed up data entry. Of course we could open the PDF in a separate viewer but we would then have to use the mouse to control the zooming and paging. When you have repetitive data entry, this little option saves time and ultimately, money. <br />
<br />
If you have the need to add functionality to your application, an internet search will expose many available controls.Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com0tag:blogger.com,1999:blog-7657079103836307816.post-23213657712418227532012-05-29T13:25:00.000-04:002012-05-30T07:52:29.566-04:00MS Access - A Cost Effective, Powerful Business ToolMS Access is a powerful low-cost business tool that offers excellent ROI and fills many and varied business needs. <br />
<br />
As the most popular database program in the world, MS Access allows the creation of very sophisticated multi-user applications with a shared back-end data source, simple desktop data manipulation, or adhoc reporting solutions from a variety of data sources. <br />
<br />
I have not found a data source that I could not link MS Access to including SQL Server, Oracle, MySQL, MS Excel, Word, dBase, etc. Because of this, an Access developer has the ability to develop and deploy real-time front-end solutions that report critical business intelligence from one or more of these sources. These solutions can either be one-offs or custom menu-driven applications that offer the selection of multiple criteria, grouping, sorting and visual representation. <br />
<br />
With the Access Query Designer, complex multi-table queries can be created. Queries can also use VBA or user defined functions directly in queries for sophisticated analysis and updates. <br />
<br />
The reporting ability in MS Access ranges from simple data querying to complex grouped, sorted and summarized reports with charts, graphs and custom VBA functions. Include sub-reports to further show multi-table relationships if needed. Here's a few examples:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-0jg4KkrqHUY/T8Tbcg5-vOI/AAAAAAAAAFw/oUHa3LYyLew/s1600/DeepSouthReport.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="http://4.bp.blogspot.com/-0jg4KkrqHUY/T8Tbcg5-vOI/AAAAAAAAAFw/oUHa3LYyLew/s400/DeepSouthReport.jpg" width="302" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<a href="http://1.bp.blogspot.com/-9zAluaZN1p8/T8TbqFRux_I/AAAAAAAAAF4/CblE3fBUHJ0/s1600/PortfolioReport.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="297" src="http://1.bp.blogspot.com/-9zAluaZN1p8/T8TbqFRux_I/AAAAAAAAAF4/CblE3fBUHJ0/s400/PortfolioReport.jpg" width="400" /></a><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-eK7d2o-t3gE/T8UMhKCithI/AAAAAAAAAGY/NzxOtOAwqoU/s1600/AdhocReporting.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://1.bp.blogspot.com/-eK7d2o-t3gE/T8UMhKCithI/AAAAAAAAAGY/NzxOtOAwqoU/s320/AdhocReporting.png" width="290" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
MS Access 2010 also includes the ability to design and deploy database applications to web hosted Sharepoint sites. These can be totally browser based or desktop front-ends linked to Sharepoint lists. Though there are size limitations, this allows the flexibility to have remote users share, add or update all or some business data. Note the example of Sharepoint hosted reporting design:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-CDKeoOO_IRw/T8TlnqofMEI/AAAAAAAAAGE/rIfCWeXUjsg/s1600/SudiaReportOptions.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="http://2.bp.blogspot.com/-CDKeoOO_IRw/T8TlnqofMEI/AAAAAAAAAGE/rIfCWeXUjsg/s400/SudiaReportOptions.jpg" width="400" /></a></div>
<h4>
<br />Bottom Line Cost Advantage</h4>
MS Access offers a low-cost solution for handling data and automating business processes. Microsoft even makes available a free runtime that alleviates the cost of buying Access for every end user. <br />
For many business needs, MS Access is a cost-effective and powerful tool!Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com2tag:blogger.com,1999:blog-7657079103836307816.post-89214152829597657892012-05-02T10:43:00.000-04:002012-05-31T17:23:41.700-04:00MS Access Key From ImageI wanted to share an additional option to my earlier blog regarding Sharepoint Attachments. This design shows the flexibility that can be built into an MS Access Web Database. <br />
<br />
Here it is:<br />
<br />
In my web database (as described in my blog "MS Access SharePoint Attachment Field):<br />
<br />
Add CLIENT forms that have an unbound Image
Control, place TIF images in a consistent folder on the users computer (I would
automate the daily image distribution). <br />
<br />
Run my custom PopulateImages() function that adds
the image names to a field in the table. <br />
<br />
In the OnCurrent event of the form,
set the unbound image control's "Picture" to the image. <br />
<br />
As my tables are still
Sharepoint lists, I still have the administrative advantages of a web database. This is more
reasonable for higher daily volumes (remember it took and hour to add 150 images
to the attachment field). It also gives the advantage of distributing any
updates to users automatically whenever they login. Users can use the free MS
Access 2010 runtime. Either browser based or client based can be available from
the same application. <br />
<br />
Private Sub Form_Current()<br />
Dim MImage<br />
MImage
= "C:\folder\NewImages\" & Me.ImageName<br />
Me.ImageNew.Picture =
MImage<br />
End Sub<br />
<br />
What do you think?Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com0tag:blogger.com,1999:blog-7657079103836307816.post-17238974442286310032012-04-29T14:51:00.001-04:002012-05-03T07:14:18.787-04:00MS Access to Oracle ODBC ConnectionI recently had an adhoc reporting project using MS Access querying Oracle tables via ODBC. Thought I would share the process for anyone that might need it.<br />
<br />
Though I could make the connection read/write, for safety and because I didn't need to make any changes to the Oracle data, a read-only connection was made. Here's the basic overview:<br />
<br />
Oracle provides a free local client (Instant Client) and ODBC driver that is used for the connection. It can be downloaded from: <br />
<br />
<a href="http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html">http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html</a><br />
<br />
It's important to select the correct driver - either 32 or 64 bit. The Oracle documentation doesn't clearly explain but keep in mind that you want the driver version to match your Oracle server version. I used the 32-bit version. After downloading and installing the Oracle "Instant Client" (make sure to download the separate ODBC driver that is part of the Instant Client), you must setup your system PATH and TNS_ADMIN variable as follows:
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-zT51QPqnQic/T52LYAjituI/AAAAAAAAAFE/kMvvC1-SK6E/s1600/Systemvars.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://2.bp.blogspot.com/-zT51QPqnQic/T52LYAjituI/AAAAAAAAAFE/kMvvC1-SK6E/s320/Systemvars.jpg" width="288" /></a></div>
<br />
• In Windows explorer LEFT pane, right click on “My Computer” icon and click “Properties”<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-PB8l1qcnE_Q/T52NDWNF0UI/AAAAAAAAAFM/ScD06pbZkqY/s1600/SysVars2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://2.bp.blogspot.com/-PB8l1qcnE_Q/T52NDWNF0UI/AAAAAAAAAFM/ScD06pbZkqY/s320/SysVars2.jpg" width="268" /></a></div>
<br />
<br />
• Click “Advanced system settings” tab<br />
<br />
• Click Environment variables button:<br />
<br />
• In the “System Variables” panel select “Path” variable and click “Edit” button:<br />
<br />
• Now find a variable called TNS_ADMIN — it could be in either System or User panel (I chose System so that all users on this PC would have access), if you have no such variable, create it in the User panel by clicking “New”:<br />
<br />
• Variable Name: TNS_ADMIN
<br />
<br />
• Variable Value: C:\InstantClient (or whatever folder you designated in the install above)<br />
<br />
• Click Ok<br />
<br />
• Now click “Ok” on the Environment Variables screen and click “Ok” on the System Properties window to close it<br />
<br />
• Run C:\InstantClient\odbc_install to install the Oracle ODBC driver.<br />
<br />
• Run the ODBC Data Source Administrator to configure your connection. Note: The default ODBC <br />
Data Source Administrator is 64-bit so if you are running the 32-bit ODBC driver, you will have to specifically run the above file (on my machine it was found at (C:\Windows\SysWOW64\odbcad32.exe).<br />
<br />
• Select “System DSN” and configure as shown:<br />
<br />
• Click “Test Connection” to confirm set-up. You will be asked for a TNS Service Name, User ID and password which your Oracle administrator can give you.
If all is correct you should see a successful connection message.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-inKlPidTgcA/T52Nr8O1cOI/AAAAAAAAAFU/hSHm3AZAedg/s1600/Success.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-inKlPidTgcA/T52Nr8O1cOI/AAAAAAAAAFU/hSHm3AZAedg/s1600/Success.jpg" /></a></div>
<br />
<br />
Once connected, I was able to design and run all my reports without issue.Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com0tag:blogger.com,1999:blog-7657079103836307816.post-35397208281353252872012-04-29T08:50:00.000-04:002012-04-29T21:07:19.810-04:00MS Access SharePoint Attachment FieldI recently tested a project that is a good candidate for MS Access on Sharepoint. It is an ongoing project keying records daily from scanned images and would require at least 3 keyers daily. The advantages are obvious: ability to work remotely, centralized administration, and flexibility to distribute or redistribute work flow. <br />
<br />
I developed forms with needed validation rules for each unique image that we are keying from. Within the table design (and then forms) I included an attachment field. When an attachment field is added to a form, the first image will show. As I will only add one image per record, this works perfectly for our purpose.<br />
<br />
For management tools, I designed a separate, client application that links to the Sharepoint lists (tables). I run daily processes and updates from this "management" application. I needed a way to add all new images in a batch process daily.
My images arrive as tifs since this is the default and best format on scanning images. However, this format is not "web enabled" and needed to be converted to jpg's to render in the browser. After batch converting the tif's to jpg's, I batch add them to the table with the following two-step vba functions:
First, I populate a field in the table (list) with the name of the image (having all new images in a folder):
<br />
<br />
Public Function PopulateImages() <br />
Dim strFile As String<br />
Dim strFolder As String<br />
Dim strSQL As String<br />
<br />
strFolder = "C:\Folder\NewImages\"<br />
strFile = Dir$(strFolder & "*.jpg")<br />
<br />
Do While Len(strFile) > 0<br />
strSQL = "INSERT INTO TicketsTbl (ImageName) " & "VALUES(" & Chr$(34) & strFile & Chr$(34) & ")"<br />
CurrentDb.Execute strSQL, dbFailOnError<br />
strFile = Dir$()<br />
Loop <br />
End Function<br />
<br />
Then I add the attachments:
<br />
<br />
Sub AddAttachments() <br />
Dim mfile <br />
Set db = CurrentDb<br />
Dim rsTickets As DAO.Recordset<br />
<br />
Set rsTickets = db.OpenRecordset("TicketsTbl")<br />
<br />
If Not (rsTickets.EOF And rsTickets.BOF) Then<br />
rsTickets.MoveFirst<br />
Do Until rsTickets.EOF<br />
rsTickets.Edit<br />
Set rsPictures = rsTickets.Fields("Field1").Value<br />
mfile = rsTickets.Fields("ImageName")<br />
mfile = "C:\Folder\1F\" & mfile<br />
<br />
rsPictures.AddNew<br />
rsPictures.Fields("FileData").LoadFromFile mfile<br />
rsPictures.Update<br />
<br />
rsTickets.Update<br />
rsTickets.MoveNext<br />
<br />
Loop<br />
End If<br />
<br />
Set rsTickets = Nothing<br />
Set db = Nothing<br />
<br />
End Sub
<br />
<br />
NOTE: I set the process running and checked back later and found that adding 150 jpg attachments took one hour. Not real speedy and an obvious consideration that will make this process unreasonable for some projects.
<br />
<br />
I have the ability to run counts, do quality control and give feedback to keyers based on the login information that Sharepoint always captures. The built in fields are "SharePointCreatedDate", "Author", "SharePointModifiedDate" and "SharePointEditor." I always add similar fields to my desktop applications but these are included in any SharePoint list.
<br />
<br />
I found no fully-developed instructions to this process on the web (though I started with a basic outline of the add attachment function from Microsoft) so I'm hopeful that this information will be helpful to some of you. Would love to hear your feedback.Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com0tag:blogger.com,1999:blog-7657079103836307816.post-34749850889682024742011-11-06T10:08:00.000-05:002011-12-19T07:22:59.198-05:00Sharepoint Access Services Reporting FlexibilityI have been struggling with offering my users a flexible and powerful reporting environment in their Access Services application. In a desktop MS Access database, to give users the ability to select more than one item to apply to a single filtering criteria you can either create a custom interface/fuction with VBA or use a multi-select combobox (or listbox) with VBA. Unfortunately, VBA is not available in Access Services. Microsoft has replaced VBA with expanded Macro capabilities that, while pretty powerful, is not as flexible or powerful as VBA. Nor is a multi-select combobox available in the same way in a web database. I had to think outside the box. <br /><br />Here's what I came up with:<br /><br />In our database we have a table with contacts and a related table with lists that the contacts belong to. I created a list lookup table with the values the users can select to filter their records with the fields: SelectList(s) (Y/N), Lists, ListID. There are 55 possible lists in the lookup table. Then I designed a report selection form, placed a subform on it with checkboxes for each list selection. After selecting the list(s) the user desires and clicking "Select Lists" a macro is run that populates TempVars with the for selected list(s) ListID number. <br /><br />I designed a Query that links the Contacts table to an interim ContactLists table that then links to the SelectMultiLists table. I run a datamacro that sets the selected records to YES for the TempVars ListID. I included the SelectList field in the query and set it's criteria to "True" so that only the related records that the user has checked in the subform appear in the results. <br /><br />Back on the Report Selection form, after the user clicks the yes/no option to select their criteria, they select the report (I have 4 different report formats including labels and a spreadsheet layout appropriate for export to Excel) they want to run and a macro is run with additional If/Else/Endif structure that additionally filters the report selected. <br /><br />In this way I was able to offer reports on multiple lists and have included 3 additional criteria (state, district, active). <br /><br />I reviewed the templates Microsoft offers for Web Databases, and the Northwinds Web template and I believe what I have designed has more flexibility/options for reporting all in Sharepoint (don't have to move to the client).<br /><br />Would love to hear your feedback if you have worked with Access Services and can offer better or additional reporting suggestions.Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com4tag:blogger.com,1999:blog-7657079103836307816.post-37579518224087635552011-09-25T17:54:00.000-04:002011-09-25T21:51:08.546-04:00My First Access Services ProjectAccess Services is Microsoft's answer to running MS Access applications on the web, in Sharepoint. The obvious advantage is that a user with login credentials can access their data from any web browser. I have completed and published my first MS Access Services project. It has been tested on an iphone/ipad and it runs just like on a PC (not quite as much fun on an iphone because of the screen size but great to have the option in a pinch). I don't know about you but I can think of lots of business needs that this might fill. Microsoft Access is the most widely used desktop database system in the world. Sharepoint is the perfect intranet environment and adding Access Services extends that usefulness.<br /><br />Challenges that had to be overcome:<br /><br />1) Office 2010 was the first release of Access Services so finding a lot of good examples and forum support is a challenge. A Google search on any MS Access features or questions returns beaucoup of resources - such is not yet the case with Access Services. Microsoft recommends http://www.utteraccess.com/forum/Access-Services-Web-Data-f74.html and I have found others like http://accessexperts.net/blog/category/access-web-database, http://channel9.msdn.com and http://dmoffat.wordpress.com. Though there are many that try to offer support, most prefice their comments with "I haven't tried this yet, but ..." It's a start, we are all learning and any effort these non-paid folks make to help should definately be appreciated. I've found posts by Albert Kallal are very comprehensive and informative.<br /><br />2) I heard a lot of buzz about the fact that you must either have Sharepoint hosted in house of pay for the hosting service. Some complained that it was too expensive. In my opinion, this is not an issue. Any serious business application on the web must be securely hosted somewhere and Sharepoint offers that security and flexibility at a reasonable price for businesses. For $49/month accesshosting.com offers 5 users and handles our needs. That is not concurrent users, concurrent users aren't limited. That 5 users is user logins. So I configured administrator, contributor and viewer users with 3 user logins to spare. Accesshosting includes a daily backup in the hosting price but will only restore back one day. This is not an adequate backup/restore flexibility for most businesses but you can upgrade backup/restore options or plan your own backup.<br /><br />2) Optimizing the design for speed. As we are running this in a web browser with multiple and varied internet speeds, making it as efficient as possible is important to it being a useable tool. My project contained about 14,000 records in the largest table and initially was too slow to load. The solution was to open the main page with a subset of the data (A-C for example) and offer the user a box to search for the record(s) they want and then query the next small subset of data. Doing this in all forms made a huge different in speed.<br /><br />3) Offering multiple report formats and filtering options. I have 10 reports including formatted labels, each report/label has 3 or more filtering options. I host with Accesshosting.com and they include SQL Reporting Services in the hosting. In this way, my users are able to generate their reports and print or export in multiple formats including PDF, Excel, CSV and more. Does the trick.<br /><br />I'm excited about the results and looking for my next project!Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com2tag:blogger.com,1999:blog-7657079103836307816.post-38091802320768819372010-07-04T10:10:00.001-04:002010-07-04T10:30:56.901-04:00MS Access 2010 - Sharepoint HostingI have taken the giant step of upgradng to Microsoft Office 2010 and I love it. I did not upgrade to 2007 because I chose to wait for the second iteration of this major upgrade. For MS Access 2010, the major improvement and, in my opinion, move in the right direction, is the new Access Services that makes it possible to develop an application totally for the web and hosted on a MS Sharepoint server. Data is moving to the web, we know it and if we are smart, we will get ready to support it.<br /><br />Here's how it works: In Access the developer must select "Web Database" option when starting the project. All forms, reports, Queries and events are developed with web compatibility. New macro functionality has been added to extend the capabilities. Access 2010 allows forms and reports to be published to web sites using what is called "access web services" that runs on Sharepoint software. These web based forms and reports run in any standard browser. Before publishing your project to the Sharepoint server, it is checked for compatibility and errors reported.<br /><br />Many have voiced the concern that having to run in Sharepoint creates an expense and complication that will impede this usefulness. Honestly, I had this concern at first but here's what I decided: Any URL will have to be hosted somewhere and security, user-access, multi-user conflicts in tables, etc., will need to be handled somehow. Sharepoint does it effortlessly to the developer and there are many reasonably-priced hosting services out there. You can find 30-day free trials to host and demo sites to customers if needed as well. I have used www.accesshosting.com for such a demo and been happy.Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com12tag:blogger.com,1999:blog-7657079103836307816.post-58686987348197563282010-04-08T10:16:00.001-04:002010-04-08T10:45:32.430-04:00Go Where the Work Takes YouI have been awarded several MS Access database development projects of late that I would like to share: <br /><br />I recently completed an application for a wonderful European style bakery in Atlanta that is growing daily. The chef needed a way to control the process of receiving and tracking daily customer's orders, generating the baker's daily production reports with breakdown of dough types, dough volume required, and invoicing of customers. I worked closely with the client and we have developed a great tool that will make the daily process more efficient, easier to manage and less time consuming. Really enjoyed this project.<br /><br />I am working on modifications to an application used by a local micro brewery to manage their process. This is a challenging project as I am wading through some very different, and totally without comments, code. To further complicate matters, the original programmer and I seem to approach the process differently. An occasional challenge can be good and keep you on your toes. <br /><br />The third project is for an association that promotes a food product. Though I am modifying and adding to an existing application, I and the original programmer seem to approach the process similarly. I can more quickly understand where he is going. <br /><br />The moral of the story is: Go where the work takes you. Be flexible enough to see a need and accept the challenge.Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com6tag:blogger.com,1999:blog-7657079103836307816.post-52596393948542156542009-12-16T08:50:00.001-05:002009-12-16T08:55:02.329-05:00We Are a Google Favorite Place!It's been almost a year since I posted (shame on me). Still here after 15+ years. We have been staying busy with a large project collecting police accident reports and validating the VINs. Been promised more to come and glad of it.<br /><br />We we're excited to receive a Google "FAVORITE PLACE" decal this week. Shows that the hard work I've been doing to promote our services on the web have had benefit. Google sent out 100,000 of the decals to those businesses that had enough clicks from their Local Business listings.<br /><br />Thanks Google!Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com8tag:blogger.com,1999:blog-7657079103836307816.post-5103444652257478862009-01-09T07:40:00.000-05:002009-04-02T08:15:36.154-04:00Lost a BidWe were not selected to continue in the bid process this week. A large telecommunications company located in our area gave us the honor of being included in their RFP list. We gave it our best shot but one of the things they looked at was company growth and receivables. Unfortunately our growth is flat and our receivables have gone down. We were also in the upper-middle tier on our pricing. There were some unknown variables inherent in the project that caused us to bid a bit higher. They gave about a week in a half to prepare the bid. <br /><br />It's a tough economic market and this company had previously started this project with a subsidiary company of a very large corporation. They went bust, leaving them with a failed project to start over. The moral of the story is: size may matter but it doesn't guarantee success. <br /><br />We have a strong history of successfully starting, continuing and completing projects. We have never let a customer down and had hoped that this would be considered. We are also proud of the people that have worked with us over the years. Some in our office, some from their homes. Local mothers for the most part that want to help with the family budget. <br /><br />C'est la vie! If at first you don't succeed, try try again.Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com9tag:blogger.com,1999:blog-7657079103836307816.post-34772154648707386652008-11-01T06:31:00.000-04:002008-11-04T07:11:15.621-05:00Being Prepared When the Opportunity ArisesSo I had been awarded a job capturing names and addresses from Kids Club applications for Burger King Corporation. Burger King had 6,000+ franchisees at the time and each was a distribution point for these applications. This was huge! I was given estimates of up to 20,000 applications per week. I got to work setting up an office in my garage, hiring and training people. We put six workstations upstairs networked into a Novell server. I also designed the data entry screens that we would need to key into and conversion/upload processes we would use. I wrote it in, are you ready, dBase III. This was a DOS based database language (the mother of all database systems today). It was actually very effective. I designed it as a multi-user back-end system with distributed front ends running on the local PC's. It was fast, safe and, as I said, efficient. We used it successfully for many years. My mantra was, "If it ain't broke, don't fix it." Over the years I upgraded dBase versions, finally moving into OOP dBase for Windows with SQL. I currently use MS Access, VBA and SQL. You might say I was forced into it by the business world, but I have reached an expert level and am very happy with that decision. I design screens that give me an edge in speed and accuracy. I listen to input from the people that are using the screens and constantly tweek and improve the screens and processes.<br /><br />Okay, we're ready, where's the work? We waited impatiently, checking our details and loose ends. We had set up a PO Box at the post office and were checking each morning. After a couple of days with no work, Bob the Postmaster called and said "You've got some mail here. I think you better come see this." We were excited, and thought, "Oh boy, maybe we have enough to get one or two of these girls working for a day or two. So we went to the post office, and you could have bowled us over when we saw our mail. We were brought into the back mail room, and as we walked in we saw very large orange plastic bags piled up everywhere. Bob the Postmaster walked us right toward them and said "Here's your mail." "OH MY GOODNESS! That's our mail?" "Yes, ma'am." <br /><br />After we picked ourselves up off the floor, we went home and got the truck and started carrying those big bags back to the garage. We filled up the downstairs of the garage with them. On opening the bags we found a mishmash of applications, complaint letters, letters from crazy people, little gifts from children, photos of Burger Kings loyal children customers, etc. A large part of the job was sorting and separating that mail. I can picture Rhonda, our mail sorter person, sitting with trays in her lap as she sorted through the bags. We also added several Value Added Services to our responsibilities on this job such as answering the white mail and housing a customer complaint phone line. <br /><br />Our 20,000 weekly estimate turned into 60,000 in reality, but we geared up and got it done. Needless to say, we got all six people started plus more and kept them working for over 12 years on that job. We eventually rented a local office space, as the job warranted. Burger King franchisees considered this their most effective marketing to date. We only stopped doing it after they awarded their marketing to a different company that chose to use their own in-house vendor. We have recently noticed that Burger King is collecting all the applicants directly online. Karen was the account director for the Burger King Kids Club and who we answered to. She is my idea of a great manager. She was no push over and could be tough and tenaciously accomplish her goals while being reasonable and fair at the same time. She gave us a glowing reference that will ever be appreciated. <br /><br />They were good years, and we appreciate the opportunities that we were given and the great contacts we made. We kept ourselves working and created in-office and at-home jobs for some good people. At one time we had twelve in-house employees and 40 local people typing at home as sub-contractors. We continue to use some of those same people today and enjoy running into the others around town. A good case for always being fair and kind.<br /><br />Have you had any memorable job experiences you can share?Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com3tag:blogger.com,1999:blog-7657079103836307816.post-20520292420265610082008-09-24T21:14:00.000-04:002008-11-01T06:30:49.886-04:00Starting a Data Entry CompanyOkay - ages since I blogged but here goes.<br /><br />I left off the Day of Small Things when I lost the job keying insurance forms for a doctor's clinic. From there I again went and found employment out of the house and had to have my son's stay in after school. I was traveling about an hour to work and my husband often made it to school to pick up the kids before me. I started out in a temporary position (Kelly Girls) as a specification typist for a design/engineer company. When a position opened up for an Administrative Assistant for one of the principles, I got it. I moved from there into IT support when a new network was installed (all the new high tech stuff). I enjoyed the job until the company started having financial trouble and a lack of jobs. They began laying off and soon closed up the office and moved the operation to Boston. Again I found myself looking for work. Only this time I had a little severance money. My husband and I thought it out: what could I do to make our lives easier, to be there for our sons? We decided that I would get some schooling in computer networking and get certified as a Novell Network Engineer. I took the classes, passed the seven tests and got my certification. Now maybe I can get something with more flexibility and closer to home. Sorry Charlie, I hunted for a job for a long time and it seems that I was more marketable as a secretary. However, I got a call from someone who knew someone I knew that needed a network installed in their office. It was a very large printing company - American Signature. They had just procured a job managing and processing a Kids Club for a major fast food company. I managed to present them with a network plan and a design for their data entry keying screens and data processes. <br /><br />This was my real start. They decided not to do the data entry and processing in-house themselves and let me bid on it. I got the job! We had to get to work. To be continued....Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com14tag:blogger.com,1999:blog-7657079103836307816.post-85953346704982119352008-08-10T13:43:00.000-04:002008-11-01T08:28:51.942-04:00Don't Despise the Day of Small ThingsIt's been a while but I am back to continue the story of my work from home saga. I left off with the purchase of an IBM Mag card machine and I was starting to solicit work. As I mentioned, we left notes on bulletin boards at the local colleges, told everyone we met, and ran an ad in the paper.<br /><br />I started to get calls from students that needed their thesis and other papers typed. The going rate was $.35 per page but I was glad to be doing something to make some money while at home with the kids. Many times the work came in the afternoon and they needed it done by the morning. I would work late into the night to get it done. That was actually okay for a while because the kids were in bed. Needless to say, that can get old and sometimes did.<br /><br />Shortly after my start up venture I received a call from a doctor that needed his insurance forms keyed and filed. This doctor specialized in geriatric Medicare and Medicaid patients. I went into his office for a meeting and he explained the job. We arrived at a deal and I happily went home to figure out how I would accomplish it. It was about this time that I upgraded my equipment to a PC. This PC was far from the desktop PC's we are using today. It was a SANYO computer, had 64 KB (not MB) of memory, did not have a hard drive, had a 5 1/4" floppy drive that the operating system (a predecessor of DOS called CPM) and all programs would run off. It came a "suite" of software applications that included WordStar, DataStar and a spreadsheet that I have forgotten the name of. I knew I could key them with WordStart but it was a great deal of work, besides I was only making 35 cents per filing. At that rate I might make $5.00 per week. I starting playing with DataStar to see if I could automate it and save time and eventually came up with a good system. I worked for this doctor for a good while and only lost the job when he stopped practicing.<br /><br />What I learned from this was that if you are diligent, don't turn down work, don't think too much of yourself to take a lower paying job, and use your brain; you can usually figure out a way to be more productive, give a good product and compete with others if there is a fair playing field. I add the fair playing field because there are some occasions when it is not possible to be competitive. As an example: I gave a bid on some work last year and the customer came back to tell me that I was a few cents per record more than their bid from China. I do not consider that a fair playing field. As I'm sure most readers understand, China has different labor laws (I am happy to have fair labor laws) and lower overhead than we do. What may level out that playing field is that English is our first language, there is the ability to more closely manage the job, we are in closer proximity and when the customer calls we personally answer the phone and understand their job. There is also some expense, loss of control and risk involved in sending the work to an out-of-country service provider, either in hard copy or electronically.<br /><br />That's all the <a href="http://www.dataplus-svc.com/">data entry </a>story for today. To be continued with the next post . . .Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com4tag:blogger.com,1999:blog-7657079103836307816.post-70472694211705932912008-06-25T08:26:00.000-04:002008-11-01T08:27:24.116-04:00Have Kids - Help them Adjust to your Work at HomeSo you finally have a job working at home. Just what you've been hoping for. You are set up and the kids are outside playing, you sit down to type and:<br /><br />Here comes little Jimmy crying. Big brother hit me and won't let me play with him. Whaw, whaw, will you play with me. What am I goin to do. <br /><br />Well, Mom, it will take a little patience and creativity to help your children adjust and understand your new schedule. The first thing you can do is consider their schedule and try to work around it. If they are in school during the day, your in like Flint. But what about the summer when they are around the house and bored. Try to plan activities that will keep them busy (a little TV is okay but we all know too much is a bad thing). Do they have some hobbies that they can be involved in during the day, day camp during the summer, can you see them playing in the yard while you are working inside. I actually designed our house so that I could see the kids while I was on my PC. If the kids are loosing it, fighting and crying, take a break, go get an ice cream. Explain to them how their understanding with your new job will help them also. Maybe it will make it possible for the family to make a trip to Disneyland. Now before they demand your attention they may think about that trip to Disneyland. <br /><br />Can you work in the evening when Dad is home and spending some quality time with his children? <br /><br />Sometimes you will find it difficult but hang in there and be patient. It will get easier with time.<br /><br /><a href="http://www.dataplus-svc.com/Employment.htm">Data Entry Services - Database Programming</a><br /><br />Do you stay at home, work at home or just want to be at home? Visit <a href="http://www.thelightkeeper.com/">The Light Keeper.com</a>, a site dedicated to those who Keep the Light at Home! You'll find articles, ideas, links, resources, information, tips - everything you need to help Keep the Light in your home. Sign up for our free newsletter, too!<br /><br /><a href="http://technorati.com/faves?sub=addfavbtn&add=http://www.workathomeencouragement.blogspot.com"><img src="http://static.technorati.com/pix/fave/tech-fav-1.png" alt="Add to Technorati Favorites" /></a>Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com3tag:blogger.com,1999:blog-7657079103836307816.post-15481734320628216752008-06-25T08:09:00.000-04:002009-04-02T08:18:09.456-04:00Managing Your TimeThe NUMBER ONE reason home workers fail is bad time management. A lot of the contractors that we have used over the years are moms so I will present this from that perspective.<br /><br />Strategies:<br /><br />Get up early. If you rise 30 - 45 minutes before the rest of the family you will a few minutes to collect your thoughts, have a cup of coffee and start the day prepared.<br /><br />Have a plan: what needs to be done today. Do you have 5 hours of data entry plus need to pick up the kids and go to ballet lessons. Write down on a note pad what you are going to accomplish and when. This will also help you see if what you are expecting of yourself is realistic. Don't forget to leave a few minutes for personal time and the little chores that will creep in. You might have some items that need to be done through out the week that are secondary. Do most important thing first. Use discipline and stick to the Plan!<br /><br />Group your running around: If you have to drop the kids at practice and pick them up in an hour, do your grocery shopping. <br /><br />Get enough rest and relaxation time: Don't fall into the habit of not getting enough rest - it will catch up with you. Ask me how I know. Set some time to go for a walk with your family or get an ice cream. It will keep you balanced and your family will not resent your job (so much).<br /><br /><a href="http://www.dataplus-svc.com/Employment.htm">DataPlus Data Entry Services</a>Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com3tag:blogger.com,1999:blog-7657079103836307816.post-11883714166122185812008-06-25T07:41:00.000-04:002008-11-01T07:46:25.071-04:00How to Loose a JobI have had many employees, home contractors and job applicants over the the years. I would like to share some of my observations and experiences with them. <br /><br />EMPLOYEES<br /><br />PERFECT: She shows up for work on time and dependably, will do anything asked in fact looks for ways to be helpful to me and her coworkers. She recognizes that the company must be profitable in order to be viable and keep her employed and works toward that end. She is pleasant to be around. She will be with me as long as I can keep her.<br /><br />OKAY: She shows up late occassionally (currently we have a casual flex schedule that works well for us but, in the past, some jobs required a regular schedule), grumbles about what projects she is assigned or complains that somebody else has an easier project, but when all else fails, she does her work fine. I consider her a good employee but guess who is staying if I have to make a choice?<br /><br />NOT SO MUCH: Complains, not dependable, sloppy work, arguments with coworkers, etc., etc. She's gone as quickly as possible. <br /><br />CONTRACTOR<br /><br />Pretty much as above but not under my supervision. <br /><br />PERFECT: The ideal home worker is dependable, never falls off the map, cares about doing a good job and takes responsibility for the quality of her work. She will also not complain about assignments and makes her self available for whatever needs to be done. I will try my best to keep this person busy and happy.<br /><br />NOT SO MUCH: The problem home worker pretty much likes the idea of working at home and would like a check but isn't really into the work stuff. She may be unavailable when needed and will be gone ASAP. She makes excuses about missing deadlines and begs more time (because she hasn't managed her time well). You would be amazed at home many of these we have hired, trained, set-up and supported over the years. Turned out I was the one working at their home - not them.<br /><br />APPLICANTS<br /><br />PERFECT: Applicant 1 shows up on time, neatly (but casually dressed for us is okay), passes the typing test without a problem, asks about what our needs are and sincerely considers how it fits with her situation. This person is my ideal.<br /><br />NOT SO MUCH: Applicant 2 may show up late and not call, gives me a headache with her perfume or cigarette smell, may have a child with her that cries and demands while we are trying to talk, can't pass the typing test (hasn't practiced as I always suggest), is unorganized, asks all about what the job will do for her and tells me all about her requirements for a work at home job. Has no idea (nor does she care) about the companies needs to be viable.<br /><br />Guess who got the job!<br /><br /><a href="http://www.dataplus-svc.com/Employment.htm">DataPlus Data Entry Services</a>Anonymoushttp://www.blogger.com/profile/08436982049701752461noreply@blogger.com1