MS Access | DEVelopers HUT

May 17th, 2013

MS Access – VBA – Find A String In Word Document

I recent was asked the following question in an UtterAccess forum and thought the solution could be useful to someone else down the line.

I have an access database with hyperlinks to many protected word documents (write protection only).
The target is : user enters a search string in access and selects specific protected document.

In the thread I developed 2 possible solutions: (1) Open the document and highlight the search term throughout the document, (2) Open the document and the start the Find dialog and allow the user the control of what they do from that point on.

Open the document and highlight the search term throughout the document

'---------------------------------------------------------------------------------------
' Procedure : OpenWordDocAndSearch
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Open the document and highlight the search term throughout the document
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFileName     : Fully qualified path and filename with extension of the word document
'                 to search through
' sSearchString : The search term to look for
'
' Usage:
' ~~~~~~
' OpenWordDocAndSearch "c:\demo\Test.docx", "The"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2013-May-15                 Initial Release
'---------------------------------------------------------------------------------------
Function OpenWordDocAndSearch(sFileName As String, sSearchString As String)
    On Error GoTo Error_Handler
    Dim oApp            As Object
    Dim oDoc            As Object
    Const wdYellow = 7
 
    On Error Resume Next
    Set oApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then    'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
 
    Set oDoc = oApp.Documents.Open(sFileName)
    oApp.Visible = True
    oDoc.Content.Find.HitHighlight FindText:=sSearchString
 
Error_Handler_Exit:
    On Error Resume Next
    Set oDoc = Nothing
    Set oApp = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenWordDoc" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Open the document and the start the Find dialog and allow the user the control of what they do from that point on

'---------------------------------------------------------------------------------------
' Procedure : OpenWordDocAndSearch
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Open the document and the start the Find dialog and allow the user the
'             control of what they do from that point on
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFileName     : Fully qualified path and filename with extension of the word document
'                 to search through
' sSearchString : The search term to look for
'
' Usage:
' ~~~~~~
' OpenWordDocAndSearch "c:\demo\Test.docx", "The"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2013-May-15                 Initial Release
'---------------------------------------------------------------------------------------
Function OpenWordDocAndSearch(sFileName As String, sSearchString As String)
    On Error GoTo Error_Handler
    Dim oApp            As Object
    Dim oDoc            As Object
    Dim dlgFind         As Object
    Const wdDialogEditFind = 112
 
    On Error Resume Next
    Set oApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then    'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
 
    Set oDoc = oApp.Documents.Open(sFileName)
    oApp.Visible = True
    Set dlgFind = oApp.Dialogs(wdDialogEditFind)
    With dlgFind
        .Find = sSearchString
        .Show
    End With
 
Error_Handler_Exit:
    On Error Resume Next
    Set dlgFind = Nothing
    Set oDoc = Nothing
    Set oApp = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenWordDocAndSearch" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Share and Enjoy

Category MS Access - Word Automation | Comment No Comments »

May 16th, 2013

MS Access – Database Created with the 32-bit version …

Have you ever developed a database using a 32-bit Access version and then tried to deploy it on a 64-bit version and receive the error: “This database created with the 32-bit version of Microsoft Access. Please open it with the 32-bit version of Microsoft Access”.

First let me state that it is not recommended to use the 64-bit of MS Access. So right from the start, this is a bad idea!

Regardless, what is the solution. Actually, it isn’t a very hard fix.

Make a copy of the 32-bit database and recompile on a 64-bit version of MS Access. So basically at the end of the day, you need to start maintaining 2 versions of your db: a 32-bit version and a 64-bit version. Once you copy over the 32-bit version to the 64-bit version, Decompile the database, Compact and Repair, Compile (address any issue that may arise such as missing references, etc…) and finally Compact and Repair. Then you can distribute your database or convert it to an mde/accde format…

So yes, as a developer, if you are going to be distributing a database to users who have a 64-bit version of MS Access, then you need to also have a 64-bit version of MS Access.

Share and Enjoy

Category MS Access General Information | Comment No Comments »

Tags: MS Access

March 19th, 2013

MS Access – Who is Logged In?

Another common need for most database administrators & developers is to be able to see who is logged into a database. A very common solution to this in the past has been to use the Jet UserRoster, see:

http://support.microsoft.com/kb/285822
http://support.microsoft.com/kb/q176670
http://www.utteraccess.com/forum/Who-s-Logged-Who-s-Con-t1897146.html
http://www.rogersaccesslibrary.com/misc/misc.htm (see: WhosOn97)

I personally find much more useful to create a table to log the database usage by the users and have a form open in hidden mode at the database startup to populate the table in question. There is also the added benefit that this method permit much more detailed information, and historical review (not possible with the UserRoster). The historical data can be very useful when determining users that did not disconnect properly (which can lead to corruption) from the database. The extra information can also be useful when troubleshooting problems.

So how can you create this simple log tracking form? Very easily and here is how:

First, we need to create a table to log the comings and goings within our database. Please note that you can add or remove the quantity (number of fields) you wish to keep track of. To do so you can use the following Data Definition Language (DDL) to create the corresponding MS Access table:

CREATE TABLE tbl_Db_UserTracking 
   (UTEntryID AUTOINCREMENT PRIMARY KEY,
   OSUserName VARCHAR(255) NOT NULL,
   ComputerName VARCHAR(255),
   ComputerIP VARCHAR(20),
   DbEntry datetime NOT NULL,
   DbExit datetime);

Then we need to create a login tracking form which will get open at startup in a hidden mode (so the user never actually sees it). Below is one example of this form. Since the form is never actually visible, you need not waste any time formatting it or performing any special layout… A bare minimum is required.
Then we need to setup a method to automatically launch the form at startup. Since all of my databases use an AutoExec macro to perform a number of steps (relink the table, check user permissions, stratup logoff forms, …) at starup it is only logical to merely add this to the function that the AutoExec macro calls.
Then we need to create a form for the administrator to use to review the data.

Share and Enjoy

Category MS Access, MS Access General Information | Comment No Comments »

Tags: Database Samples MS Access

January 20th, 2013

MS Access – Deploying an Access Database using CITRIX

This is a preliminary posting on the subject, one that I hope to add to over time.

We see many postings on various forums from people wanting to know more about running an Access database via CITRIX.

Firstly, since Access is not recommended to be used over a wireless connection, nor a WAN, CITRIX is a very interesting solution to giving access to such databases. That said, like everything in life, there are both pros and cons to such a deployment of your database.

Like with every MS Access database, to deploy it, you must first split the database into 2 components: front-end and back-end and then issue a copy of the front-end to each of your users. This can easily be done in CITRIX.

Typically, in an effort to minimize the impact of internal networks…, I like to try and have the back-end installed directly on the CITRIX server itself. By doing so, the database runs completely on the CITRIX server and there is no network impact, and the database also doesn’t take up valuable network bandwidth uselessly. Of course, as with any installation, a reliable backup system must be put in place and validated on a regular basis.

As a developer, I always need to be given an administrative console with the full version of Access installed so I can perform tests, and more importantly compile my application locally to ensure proper compatibility.

Where I have seen issue occur… Well, there can be a number of issues, but 2 specific issues seem to keep coming up recently.

Insufficient Server capacity – Since the CITRIX server is an application server, it is crucial to ensure sufficient RAM, CPU power,…. I recently had to identify the fact that the CITRIX server was maxing the CPU and thus the database application I designed was miserably slow. The IT department stated that the server was adequate so I added some custom code to log RAM and CPU usage to profile the server since they would not do so. I was able to clearly demonstrate that the CPU was saturated. Once they unloaded a few other applications from the server, all of a sudden my databases were running fine. The funny thing was that the database was throwing network error, nothing relating to CPU, or anything remotely leading one to believe it was a resource problem. Even more of a reason why the IT department was unwilling to believe my initial questions regarding their server. All of this to say, once you set up your db, ask the IT dept to profile the server CPU, RAM, Network just to ensure everything is optimal. it is so simple for them to do and can truly avoid weeks of headaches (for everyone). This is also something that could be checked from time to time.
Outdated/different server installations – Like any computer, the local setup can have an impact on application running on it. You can develop a superb database application and once deployed on the server it may not work. Why, simple because of a conflict caused by updates, difference in dll, exe, … This is why in my opinion (and some people argue this point) that the CITRIX server be kept up to date both its OS and Access/Office installation. Furthermore, it is crucial to compile your database on the local CITRIX server to ensure everything is proper.

That said, I must say that once things are setup properly, CITRIX can be very reliable and a very useful tool.

Share and Enjoy

Category MS Access General Information | Comment 2 Comments »

Tags: MS Access

December 21st, 2012

Database Project Questionnaire

I have been asked a few time over the years

what should a developer be asking a client when starting a project?

Sadly, if you are asking this question, you need to rethink your level of experience to tackle the project you are looking to take on as any experienced database developer has a good idea of what questions to ask.

Here are some starting questions. Obviously, however, the discussion with potential clients usually leads me to ask many more questions, but everything depends on the context of the conversation, the project,…

General IT Information:

PC OS (2000, XP, VISTA, Windows7, Mac, Linux, …)?
Do you have MS Access? Which version (2000, XP, 2003, 2007, 2010, Other, …)?
Will you be running the full version of MS Access, or runtime, or a combination of both?
How are you computers interconnected (LAN, WAN, Wireless, other …)?
Do you have a central server?

Database Current Specifications:

Database Format (2000, XP, 2003, 2007, 2010, Other)?
Number of Users (Simultaneous Vs. Total)?
Do you require security? If so, what type (single password, user login, other …)?
Does your database require linking/storing to external documents?
What are the Inputs and Output (in which formats)? Do you have any existing samples of each?
What resolution are we designing around?
What color scheme do you prefer (based on logo, …)?
What fonts are you wishing to use, or avoid?
Do you require remote access to the database (via Internet, from other offices, etc…)?
Do you require help with maintaining the database or will this be administered by your IT Dept.?
Will you require long term support for the database?
Do you need help with a backup strategy or will this be administered by your IT Dept.?

Work Mandate Specifications:

Work to be performed (On-site, Off-site, Combination)?
Will you require training?
Will you require documentation (help file, what format, etc…)?
Will you require importing existing data into the database (what format)?
Who will be the main point of contact? Do they have decision making authorization?

General Questions:

Current Issues/Needs/Wants? If they have a current app, what do you like, what don’t you like, what is missing?
How do you envision the database (design, functionalities)?
Timeline?
Budjet?

Although the above centers around MS Access, it can be generalized quite easily for any database project.

Share and Enjoy

Category General Technology Discussion, MS Access | Comment No Comments »

Tags: MS Access

December 21st, 2012

MS Access – Login Form

As of Access 2007, Microsoft stopped promoting User-Level Security (ULS), although it is still supported per sey. As such, most developers now require a new means to control access to their databases. Now ULS had it pros and cons. Although it was effective, sometimes it was simply overkill for simply controlling longins, and should you loose the mdw, or it becames corrupt, well lets just say you were in for a real treat and hence the critical importance of making regular backus.

Anyways, back to the subject at hand, what is one to do now to control access to a database using Access 2007 or later? Well, there is no point on me to try and reinvent the wheel on this. That said, this is a very common forum question and I usually refer people to the same list of links to learn from. So I thought it would make sense to post them on my site, in case someone Googled the subject. Here they are:

http://www.utteraccess.com/forum/Simple-Us…ml&hl=login
http://www.utteraccess.com/forum/Login-pas…ml&hl=login
http://www.utteraccess.com/forum/Security-…ml&hl=login
http://www.utteraccess.com/forum/db-Login-t201495.html&hl=login
http://scottgem.wordpress.com/2010/01/12/creating-login-security-using-access-vba/
http://www.accesssecurityblog.com/post/2011/04/02/Login-Security-using-Access-VBA.aspx
http://www.databasedev.co.uk/login.html

They are all different in complexity. So check them out, one by one, and find the security model to suit your needs and then you can simply implement it and tweak as needed.

I hope this helps.

Share and Enjoy

Category MS Access | Comment No Comments »

Tags: MS Access

December 4th, 2012

MS Access – Where to Start Learning – Database Tutorials

I always find it amazing that even with the internet, it can sometimes be very hard to sift through all the available material to find good tutorials and learning tools. Below are a few good starting points. Some are MS Access specific, but many are just database generalities whoses techniques can be used with any database. So if you are stating off, or merely brushing up, here are a few good links to consider:

For staters, their some of the best site are those of the various MS Access MVPs. I have compiled a seperate list of the website in the following post: http://www.devhut.net/category/mvp-websites/

Also, you will find a series a links below covering a variety of subject. I cannot take the credit for this listing, but thought it was so good that it should be posted for all to see:

As you can see, there are a number of articles from UtterAccess.com. This is one of the best, if not the best, one stop for Access related material. They have a large archive of articles, VBA functions, samples and they have one of the most active sets of forums. If there is one place to check out, http://www.utteraccess.com is that place!

Share and Enjoy

Category MS Access General Information | Comment No Comments »

Tags: MS Access

November 30th, 2012

MVP Sites

Some of the most valuable websites (especially regarding Access of course) are those of many of my fellow MVPs (and a few other exceptional individuals). So I thought it intelligent to try and compile them here. If you are looking for some of the very best online resources, these are the sites to start with! Packed with invaluable information, explanations, sample databases to quickly learn from, these sites are the ‘crème de la crème’!!!

AD Tejpal	http://www.rogersaccesslibrary.com/forum/tejpal-ad_forum45.html
Albert Kallal	http://www.kallal.ca/
Alex Dybenko	http://alex.dybenko.com/ http://accessblog.net/
Allen Browne	http://www.allenbrowne.com/tips.html
Armen Stein	http://www.jstreettech.com/downloads
Arvin Meyer	http://www.datastrat.com/ http://www.accessmvp.com/Arvin/
Bill Mosca	http://www.thatlldoit.com/
Bob Larson	http://www.btabdevelopment.com/ts/freetools
Boyd Trimmell (HiTech Coach)	http://www.hitechcoach.com/
Candice Tripp	http://www.candace-tripp.net/ http://www.utterangel.com/
Crystal Long (strive4peace)	http://www.accessmvp.com/Strive4Peace/ http://www.youtube.com/user/LearnAccessByCrystal/videos?view=0 http://www.rogersaccesslibrary.com/forum/long-crystal_forum71.html http://www.youtube.com/user/LearnByCrystal http://www.learnbycrystal.com/
Daniel Pineault	http://www.cardaconsultants.com/ http://www.devhut.net/
Dev Ashish	http://access.mvps.org/access/
Dirk Goldgar	http://www.datagnostics.com/tips.html
Douglas Steele	http://www.accessmvp.com/DJSteele/
Duane Hookom	http://www.access.hookom.net/
George Hepworth	http://www.gpcdata.com/
Gina Whipp	http://www.access-diva.com/tips.html
Glenn Lloyd	http://officetipsandmethods.com/
Helen Feddema	http://www.helenfeddema.com/
Jeff Conrad	http://www.accessmvp.com/JConrad/accessjunkie.html http://www.accessjunkie.com/Pages/default.aspx
John Viescas	http://viescas.com/category/tips/
Ken Snell	http://www.accessmvp.com/KDSnell/
Larry Linson	http://accdevel.tripod.com/ http://sp.ntpcug.org/accesssig/default.aspx
Luke Chung	http://www.fmsinc.com/free/
Martin Green	http://www.fontstuff.com/access/index.htm
Olaf Rabbachin	http://www.blogs.intuidev.com/category/AccessVBA.aspx
Ollie Stohr (freakazeud)	http://www.access-freak.com/
Patrick Wood	http://gainingaccess.net/
Roger Carlson	http://www.rogersaccesslibrary.com/ http://rogersaccessblog.blogspot.ca/
Sandra Daigle	http://www.accessmvp.com/SDaigle/ http://www.daiglenet.com/msaccess.htm
Scott Diamond	http://www.diamondassoc.com/
Stephen Lebans	http://www.lebans.com/
Steve Schapel	http://accesstips.datamanagementsolutions.biz/
Teresa Hennig	http://www.seattleaccess.org/
theDBguy	http://accessmvp.com/theDBguy/ http://thedbguy.blog.com/
Tom Wickerath	http://www.accessmvp.com/TWickerath/
Tony Toews	http://www.granite.ab.ca/access/tipsindex.htm
Tom van Stiphout	http://www.accessmvp.com/TomvanStiphout/
Paul Baldarelli	http://www.baldyweb.com

Share and Enjoy

Category MVP Websites | Comment No Comments »

Tags: MS Access

September 29th, 2012

MS Access – Persistent Connection in a Split Database

Anyone that knows anything about developing a database knows that best practice dictates that you split your database into 2 components: a Back-End containing the tables (your data) and a Front-End containing everything else (queries, forms, reports, macros, modules, …).

What many developers seem to overlook however is the importance of always creating a persistent connection between the Front-End and the Back-end.

In a standard split database, each time you run a query, open a form, run a report, the Front-End must first establish a connection with the back-end, then when you close that object the connection is dropped/closed. Then you open another object, it must, yet again, establish a new connection, … You get the idea. Each time, establishing the connection takes time. How much time depends on numerous factors, your network, is the database secured/ encrypted, …

Think of it this way, imagine you have a multi-question survey to call people with. Is it better to dial a number, wait for someone to pick up at the other end, explain who you are, ask your first question and then hang up. Then repeat the same steps for your second question. And then yet again for your third question, … OR does it not make more sense to dial a number, wait for someone to pick up at the other end, explain who you are, ask your first question, ask your second question, ask your third question, …, and only hang up once your are through with your survey! The proper answer to this question is pretty clear cut if you ask me. Well, it is the same for your database!

To minimize this impact on your database and improve performance it is critical, IMHO, to always establish a persistent connection as soon as your front-end load. This way the database need not create a new connection each time you choose to do something, it already exists, so it can simply use it.

How to Create a Persistent Back-End Connection
Nothing could be simpler!

In the Back-End

Create a new table (you can name it anything you’d like)
Create a couple text or number fields of your choosing to the newly created table
Add 2 or 3 simple records to the newly created table

In the Front-End

Link the newly created table from your Back-end
Create a new form based on this table
Part of your AutoExec Macro’s code, add a line to automatically launch the form at the startup of your database in hidden mode.

So what have we done? Quite simply, since, as I stated previously, opening any bound object forces the Front-End to establish a connection with the Back-End, we are simply opening this form so it will establish a connection back to our table. We set it in hidden mode so the end-user isn’t ever even aware that it is there, and so they do not accidentally close it.

For complicated, multi-Back-End databases, a separate persistent connection should be established with each Back-End file.

Share and Enjoy

Category MS Access, MS Access General Information, MS Access Tables | Comment 3 Comments »

Tags: MS Access MS Access Tables

September 15th, 2012

Windows Update KB2596856 / MS12-060 Breaks Microsoft Windows Common Control Library (MSCOMCTL.OCX)

On Tuesday, August 14, 2012, MS released a new update which in fact crippled any application which used the Microsoft Windows Common Control Library (MSCOMCTL.OCX)! So anyone using any of the following Microsoft Windows Common Controls:

Animation control
Button
Combo box
ComboBoxEx control
Date and time picker
Edit control
Flat scroll bar
Header control
Hot key control
Image list
IP address control
List box
List-view control
Month calendar control
Pager control
Progress bar
Property sheet
Rebar control
Rich edit control
Scroll bar
Static control
Status bar
SysLink control
Tab control
Task dialog
Toolbar
Tooltip
Trackbar
Tree-view control
Up-down control

There have been numerous disccusions on the matter, amongst them:

And numerous proposed solutions, amongst them:

That said, the true solution, which partially worked for me (it resolved my computer’s problem and got me back up and running but a serious compatibility issue with all my clients still remains! See below for more information) was finally release by MS (after the community had already done all the leg worked an figured it out on our own) and was posted at: http://support.microsoft.com/kb/2597986.

All of this is very nice, but there remain 2 major issues:

This solution relies on the end-user doing all the fixing, on every computer! Why has MS not released a hot fix that would automatically have fixed this?! MS screwed-ed big time and sadly has passed the puck on to everyone else, rather than addressing the issue.
Furthermore, even if you fix your computer, your application will no longer work on any computer that hasn’t had this update applied!!! Just a little problem if you ask me! So in fact their solution does not truly resolve the problem entirely as their remains a major compatibility issue at the end of the day.

Share and Enjoy

Category MS Access General Information | Comment No Comments »

Tags: MS Access

August 16th, 2012

MS Access – VBA – Rename attachment in SendObect Method

Everyone knows that the quickest and easiest method for sending an e-mail in MS Access is to use the SendObject method

DoCmd.SendObject acSendReport, "YourReportName", , "Someone@somewhere.com", _
                     , , "Subject", "E-mail Body"

This is all find an dandy, but there is one flaw, it uses the name of your MS Access report: “rpt_SalesForecast” or “rpt_SalesByRegion” access as the attachment name. This simply is not acceptable for most purposes. We want to have a name that reflects better what the report is about: “Sales forecast 2012″, “Average Sale for Montreal in July”.

I recently was in a discussion and theDBguy was kind enough to share a little trick to do exactly this without requiring a whole lot of extra coding and automation. I thought I’d share it for everyone to benefit from. Below is the code in question:

    Dim sExistingReportName As String
    Dim sAttachmentName As String
 
    'Input variables
    sExistingReportName = "YourMSAccessReportName"    'Name of the Access report Object to send
    sAttachmentName = "AttachmentNameToBeUsedInTheEmail"    'Name to be used for the attachment in the e-mail

    'The code to make it happen
    DoCmd.OpenReport sExistingReportName, acViewPreview, , , acHidden
    Reports(sExistingReportName).Caption = sAttachmentName    'by changing the report caption
                                                        'you effectively change the name
                                                        'used for the attachment in the
                                                        '.SendObject method
    DoCmd.SendObject acSendReport, sExistingReportName, , "Someone@somewhere.com", _
                     , , "Subject", "E-mail Body"
    DoCmd.Close acReport, sExistingReportName

Short, sweet and simple! Thank you theDBguy!

Share and Enjoy

Category MS Access Reports, MS Access VBA Programming | Comment No Comments »

Tags: MS Access MS Access Report MS Access VBA Programming

August 14th, 2012

MS Access, WANs, Wireless Networks and the Internet

A common question, and increasingly so, is relating to using MS Access over WANs (Wide Area Networks – which the Internet qualifies as).

The short answer: Keeping in mind the fact that MS Access uses the Jet/Ace database engine, MS Access simply does not, should not be used in any manner over WANs, wireless networks and/or the Internet.

The longer answer: As with most things in life, there are always ways to bend the rules… As such, there are techniques that permit one to use a database over WANs: the use of Terminal services, CITRIX, which the newer version of MS Access (2010 especially) developing your database as a Web database and publishing it to Office365.com, utilizing SharePoint services. Albert Kallal, an MS Access MVP, has a very detailed post about MS Access and WANs which covers much more in detail the subject, especially the technical reasons: http://www.kallal.ca/Wan/Wans.html.

That all said, even though there are certain work arounds (each having their own set of pros and cons), I always ask my clients the same basic question: ”What is the purpose of the database you are creating?”. If it is an internal business tools (used over a wired LAN network), then MS Access is definitely worth exploring further. If on the other hand, it is to be shared, over the Internet or corporate WAN, with lots of users/clients, etc. then it is time to look at web databases (which Access simply is not – at least not in the standard sense of the term). This is when you have to truly start looking into databases such as: MySQL (very common and cheap hosting is easy to find), PostgreSQL, SQL Server (harder to find host and more costly typically) and development languages such as: PHP, .Net, JAVA, and a multitude of others.

In summary:
In-house application used over a wired LAN connection-> MS Access is the apex in development tools! It is the fastest and probably easiest development tool used by millions, upon millions of users around the world!!!
An application to be run over a WAN, Wireless Network and/or the Internet -> Turn to more professional web application and development tools.

Share and Enjoy

Category MS Access General Information | Comment No Comments »

Tags: MS Access

June 22nd, 2012

Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs Review

I have one thought for you; If you are looking for a good book to learn from about common database developer needs, then this is a MUST buy! Truly, I wish this book had been out 10-15 years ago when I was learning and developing my first databases.

The subjects covered are corner stones of almost any database and any developer will find useful knowledge and techniques. Furthermore, coming from 2 of the best Access developers (Douglas Steele and Arvin Meyer), you know that the information is right on the money!

The approach is very unique, in that the book is divided into Tips and then each tip is subdivided into Objective; Scenario, Tables, Queries, Forms, Reports, Macros, Modules, Using This Tip & Additional Information. So they explain every aspect of what they are doing, why they are doing it and what needs to be done to make it all work. It is a simple step by step guide.

This book is not so much about how to use Access, but rather how to develop a database. Arvin and Douglas share their solutions on how to tackle common issues from start to finish. These are real-life issues that we all have to deal with and with complete solutions. Subjects such as Cascading Combo Boxes, Treeview & Listview controls, Drag and Drop, Tab Controls, Relinking your Back-End, and so much more…

Furthermore, unlike numerous other books currently collecting dust on my shelves in which a few chapters are useful but the bulk are not, Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs Review is good from cover to cover. Every tip is useful. Clearly written, easy to follow along, you can tell that Doug and Arvin have invested a lot of time and energy developing the content within.

The table of contents is:

Part I Tables 1.
Tip 1 Creating Tables for Web Applications.
Tip 2 Alternative to the Hyperlink Data Type.
Tip 3 Fill a Table with Numbers.

Part II Queries.
Tip 4 A Single Query to Do Both Updates and Insertions.
Tip 5 Using a Cartesian Product to Generate a Calendar.
Tip 6 Using a Multiselect List Box as a Query Parameter.

Part III Forms.
Tip 7 Cascading Combo Boxes.
Tip 8 Cascading Combo Boxes on Continuous Forms.
Tip 9 Paired List Boxes.
Tip 10 Marquees, Scrolling Messages, and Flashing Labels.
Tip 11 Custom Tab Controls.
Tip 12 Simulating Web-Style “Hover” Buttons.
Tip 13 Custom Form Navigation Controls.
Tip 14 Calendar Form.
Tip 15 Simulating Drag-and-Drop in Access Forms.
Tip 16 Providing Visual Feedback for Drag-and-Drop.
Tip 17 Control List Boxes with Drag-and-Drop.

Part IV Taking Advantage of Report Capabilities.
Tip 18 Page 1 of N for Groups.
Tip 19 Always Starting a Group on an Odd Page for Duplex Printing.
Tip 20 Dynamically Changing the Sort Order of a Report.
Tip 21 Week-at-a-Glance–Type Report.
Tip 22 Day-at-a-Glance-Type Report.

Part V Using VBA.
Tip 23 Useful String Functions.
Tip 24 Useful Functions.
Tip 25 Relinking Front-End Databases to Back-End Databases in the Same Folder.
Tip 26 SaveAsText and LoadFromText: Undocumented Backup and Anti-Corruption Tricks.
Tip 27 Reminders—Building Tickler Forms and Utilities.
Tip 28 Using Disconnected Recordsets.
Tip 29 Implementing Soundex.

Part VI Automating Applications and ActiveX Controls.
Tip 30 Charting Using Excel.
Tip 31 Using the TreeView Control.
Tip 32 Using the ListView Control.
Tip 33 Adding Images to the TreeView Control.
Tip 34 Using the TreeView and ListView Controls Together.

Part VII Access and the Web.
Tip 35 Building an Access Web Application.
Tip 36 Embedding a Web Control in a Form.
Tip 37 Building a Time Picker in a Web Form: An Introduction to Web Macros.
Tip 38 RSS Feeds.
Tip 39 Detecting Whether You’ve Got Internet Connectivity.

Part VIII Utilities.
Tip 40 Drilling Down to Data.
Tip 41 Utility for Renaming a Form’s Controls.
Tip 42 Document Management Using Access.
Tip 43 Ultra-Fast Searching.

At the end of the day, even an experienced developer such as myself has taken ALOT away from this book! I would recommend it to anyone, especially anyone starting out. It will make a great addition to my existing library. Well worth every penny!!!

To learn more or purchase the book, see:
wiley.com
amazon.ca
amazon.com

Share and Enjoy

Category Product Reviews | Comment No Comments »

Tags: MS Access Product Review

June 9th, 2012

MS Access – Sliding/Shutter Subform Example

I was looking for a simple way to reproduce a web style expandable sidebar (accordian subform, expandable subform, sliding subform, shutter subform, or whatever name you'd like to use to describe it in MS Access), instead of merely making a subform visible/invisible. The attached file, does exactly that and with a single change to a constant variable you can control the speed of the shutter/slidder.

This is a very crude example to illustrate the principle. You can use any subform you like, within any form you'd like. There are no requirements to make this work and all the code is native MS Acces code, no external references, or ActiveX Controls. The button used to expand and retract the subform can also be changed as you see fit, it is just a question of placing the brief code behind whatever button you select to use in your design.

Just another nifty method to add a little wow factor to a form.

Sliding/Shutter Subform Example Download

Share and Enjoy

Category MS Access Forms, MS Access Samples | Comment 2 Comments »

Tags: Database Samples MS Access MS Access Forms

April 16th, 2012

MS Access – Decompile a Database

Decompiling an Access Database

As a developer, it becomes necessary to decompile your database from time to time. The frequency depends on a multitude of factors, but at the very least, one should decompile one’s application prior to release. Decompilation is yet one more step a develpper has to take to ensure their database/code is clean and optimal for their end-users. Also, keep in mind that the best approach is to decompile the database on the development machine and then recompile on the end-user machine. This ensures that the database is compiled using the end-user’s libraries minimizing any surprises when put into production.

Before going any further on this subject, let me emphasize once more the importance of making a backup of your database prior to performing a decompile of your database!

One-time Decompile

The MSACCESS.EXE command line accepts several command line switches, one of which is to allow decompiling your database. There is no other means to decompile a database. So one merely needs to create a shortcut including the appropriate command line switch in order to decompile any given database. The basic syntax would be:

"FullPath\MSACCESS.EXE" "FullPathAndDbNameWithExtension" /decompile

Examples:

"C:\Program Files\Microsoft Office\Office\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile

"C:\Program Files (x86)\Microsoft Office\Office12\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile

Reusable decompilation method

On the other hand, it become tedious to create a shortcut for each database you create/manage and as such a more automated method may be a good idea to implement.

Using Windows Explorer (etc.) navigate your way to your MSACCESS.EXE and the copy the file.
Navigate to the %APPDATA%\ Microsoft\Windows\SendTo\ Folder
Right-Click within the folder and select Paste shortcut from the context menu
Rename the shortcut as you wish, for instance MSACCESS Decompile
Right-Click on the newly created shortcut and select Properties from the context menu
On the Shortcut tab, edit the Target control by adding /decompile to the existing value.
You should end up with something along the lines of:

"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" /decompile

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile

Click Ok
Close windows explorer.

Now whenever you wish to decompile a database you need only right-click on the Access database in Windows Explorer and select “Send To –> MSACCESS Decompile.

Special Note

Since after decompiling we always perform a compact of the database, you can perform both in one step if you’d like by appending a /compact to your /decompile command. This is not obligatory by any means. Personally, I prefer the granularity of performing each step myself, but should you wish to automate things a little more you’d do something along the lines of:

"C:\Program Files\Microsoft Office\Office\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile /compact

"C:\Program Files (x86)\Microsoft Office\Office12\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile /compact

"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" /decompile /compact

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile /compact

Do note that if you do implement this variation for the reusable decompilation method, you’d probably be best to also change the name attributed to the command in step 4. to MS Access Decompile and Compact

Proper Steps to Follow when Decompiling an Access Database

I found the following instructions from David Fenton, and thought they fit right into this subject.

Backup your database.
Compact your database.
Decompile your database (per either method listed above).
Close that instance of Access.
Open a new instance of Access and open the database you just decompiled, but BE SURE YOU BYPASS ALL STARTUP CODE (i.e., hold down the shift key). If you don’t do that, then you might as well go back to step 3 and try again, since if the startup code runs, your code will recompile before you are ready to do so.
Compact the decompiled database (and be sure you hold down the shift key so that it bypasses the startup code; see #5).
Open the VBE and on the Debug menu, choose COMPILE [name of project].
On the file menu, save the project.
Compact again.

Why are all these steps necessary?

Because you want to not just decompile the VBA, you want to make sure that all the data pages where the compiled p-code was stored are completely discarded before you recompile.

I also recommend:

In the VBE options, turn off COMPILE ON DEMAND
In the VBE, add the COMPILE button to your Toolbar.
Compile often with that button on the toolbar, after every two or three lines of code.

Last of all, read Michael Kaplan’s article on the subject to understand it better.

Share and Enjoy

Category MS Access, MS Access General Information, MS Access VBA Programming | Comment No Comments »

Tags: MS Access MS Access VBA Programming

February 14th, 2012

MS Access – Button Picture – doesn’t support this format … or the file size is too big

I came across the following post and wanted to see what the issue was since I routinely use Irfanview myself (it is a great, free no strings attached piece of software).

So I contacted the creator of Irfanview directly regarding the error, and he was able to pinpoint the exact nature of the problem. As it turns out, the ico files created by Irfanview are just fine. The issue is that MS Access would seem to expect a specific type of ico file (and doesn’t tell anyone this).

Irfanview creates a 24BPP (with no alpha layer) ico file while MS Access seems (based on my testing and returned information from Irfan) to expect a 32BPP with an alpha layer ico file.

So for anyone else ever seeing this this of error. An ico is not an ico in the world of MS Access. It is picky and requires a specific type of ico file. So just be sure of the type of file your are creating is a 32BPP with an alpha layer ico and you should have no problems using them as a picture for a button. Alternately, instead of having such issues using a ico file, use BMPs instead. I personally don’t like BMPs as they aren’t used in any other programming (always jpg, gif, png, ico) so I will stay with ico so I can reuse them easily where I please. It would be nice if MS would integrate common image files into the application, but that is out of my hands (if it were up to me many issues like this would have been resolve a long time ago!).

Hopefully this will help someone out.

Share and Enjoy

Category MS Access Forms, MS Access General Information | Comment No Comments »

Tags: MS Access MS Access Forms

January 21st, 2012

MS Access 2007-2010 Ribbon Development

A while back, ok several years ago, I took a look at the new (at the time it was new) ribbon. I was interested in porting my Custom Toolbars into the new and improved format. At the time I spent hours trying to digg up information on the subject.

To put things bluntly, MS messed up good on the implementation!

This is not to say that the ribbon is not a powerful tool and all the rest of that.

That said, it has been implemented in what I consider an incomplete form. MS provides no tool, to develop custom ribbons, no built-in callback routines…, no access to the ribbon built-in images, and so many more flaws I don’t even know where to start! It is a mess.

Then they still support old custom toolbars, but you have no way to edit, delete or work with them directly. You have to revert back to using 2003 or prior, or develop your VBA own code to interact with them.

The whole situation smells bad.

So what is one to do. First off, you have to realize, that the entire Office suite Ribbon is fully customizable and to work with it you can use a Custom UI Editor, EXCEPT for MS Access. That’s right, as usual, MS Access is in a category of its’ own. What a surprise!

Firstly, get ready to do a lot of reading, before you are even ready to tackle creating a ribbon. Here are a few places to start:

www.accessribbon.de
http://www.andypope.info/vba/ribboneditor.htm
http://msdn.microsoft.com/en-us/library/aa338202(v=office.12).aspx
http://msdn.microsoft.com/en-us/library/bb187398.aspx

http://www.rondebruin.nl/ribbon.htm

For MS Access, try http://www.ribboncreator.de/en/ The ribbon creator is a graphical interface to create the required XML and VBA code. Basically, what MicroSoft forgot to include in its’ own software!!! At the very least it will speed up development and you can tweak what it generates.

All in all, the ribbon, because of the way MS has choosen to implement a half baked technology, is a miserable failure (from the developer’s perspective)! Actually, even from the end-user’s perspective also. Where I used to be able to load 6 toolbars containing 50 icons (or more) where I had all the commands I needed at my finger tips, I now have 1 ribbon containing 10-20 commands. You end up spending your time flipping back and forth, all day, between tabs! It is never ending and teadious after a while. This is MS’ idea of empowering the end-user? Come on! But hey, it looks cool dude! This is the best that the best minds at MS could come up with; seriously!!!

That said, I believe MS has other plans for the ribbon, probably a way to introduce the end-user to the new layout, flow to be expected in future releases of their OS or other programs. Let use not forget the fact that MS is playing a major game of catchup with MAC.

Share and Enjoy

Category MS Access, MS Access General Information, MS Office | Comment 5 Comments »

Tags: MS Access MS Excel MS Word

December 15th, 2011

MS Access – Splitting and Deploying Your Database – Part 2

In my previous post entitled Splitting and Deploying Your Database, I elaborated on the critical importance of splitting your database before deploying it to your end-users. That said, many people post questions regarding securing their applications from their end-users to minimize their ability to mess around with the database, mainly fool around directly with the tables. In this post I will elaborate on a few possible step you can take as a developer to best secure your database from what I qualify of ‘dangerous’ users.

There are a few things that you can do to try and harden your database against your users. In 2 instances, you have the ability to take some steps using programming to secure your app. In the 3rd, you can deploy your application in a secured method. Let’s examine each.

Hide Object Pane & Disable SHIFT Bypass

As a developer, you can do your best to make it as difficult as possible for any user to gain access to any of your database objects (tables, queries, forms, reports, …) to pose a threat in the first place. To do this you need to:

Create an autoexec macro the uses RunCode to execute a VBA procedure at startup
In your startup procedure add in the following code

If SysCmd(acSysCmdRuntime) = False Then
    DoCmd.SelectObject acTable, , <strong>True</strong>
    DoCmd.RunCommand acCmdWindowHide
End If

This code will hide the Access object browser (the pane that lists all the database objects: tables, queries, forms, reports, …). If they can’t see them, then they can’t mess around with them!

Since any knowledgeable user knows that they can bypass any autoexec macros by holding the SHIFT key at startup, we need to disable the shift bypass capacity. Now there is no point in rehashing this subject, so please refer to: http://access.mvps.org/access/general/gen0040.htm

Convert to MDE/ACCDE

One more step you can take is to convert your database (mdb/accdb) to an (mde/accde) format. By doing so you lockout the user’s access to the all the VBA code. Furthermore, this creates a compiled version of the database which mean it should be optimized for use. Win, win. Just remember you cannot do development on an (mde/accde), so keep your original file (prior to conversion) for further development.

Deploy Your Database Using Runtime

The last thing you can do to restrict your users and limit their ability to run amuck in your database is to deploy your application using MS Access’s runtime version. Instead of giving your user the full blown MS Access application, only install the free runtime version. Unless, your user needs to do development in the database, there is no need for them to have the full version of MS Access. By doing this, your user will not be able to edit any of the database objects! They will only have access to whatever you have developed and given them access to. Also, note that when deploying using the runtime version ensure you put in place error handling throughout all of your VBA procedures, otherwise when an error is raised, the application will blatantly crash. You can freely download and install the runtime version of MS Access directly from Microsoft’s website at:

MS Access Runtime 2007 -> http://www.microsoft.com/download/en/details.aspx?id=4438
MS Access Runtime 2010 -> http://www.microsoft.com/download/en/details.aspx?id=10910

Share and Enjoy

Category MS Access, MS Access General Information | Comment No Comments »

Tags: MS Access

November 29th, 2011

MS Office – Executable File Versions

It can sometimes be necessary to determine the version of the program you are working with in VBA and I have been unable to find a comprehensive listing. Microsoft themselves have scattered this information over numerous pages?! Here is what I have compiled myself thus far (Office 2000 through Office 2010 SP1).

Application Name	Executable File	2000	2000 SP1	2000 SP2	2000 SP3	2002	2002 SP1	2002 SP2	2002 SP3	2003	2003 SP1	2003 SP2	2003 SP3	2007	2007 SP1	2007 SP2	2007 SP3	2010	2010 SP1
Microsoft Office	mso.dll					10.0.2627.01	10.0.3520.0	10.0.4330.0	10.0.6626.0	11.0.5614.0	11.0.6361.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6213.1000	12.0.6425.1000		14.0.4760.1000	14.0.6023.1000
Microsoft Access	msaccess.exe	9.0.2720	9.0.3821	9.0.4402	9.0.6926	10.0.2627.1	10.0.3409.0	10.0.4302.0	10.0.6501.0	11.0.5614.0	11.0.6361.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6423.1000	12.0.6606.1000	14.0.4750.1000	14.0.6024.1000
Microsoft Binder				9.0.2702	9.0.2702
Microsoft Excel	excel.exe	9.0.2720	9.0.3821	9.0.4402	9.0.6926	10.0.2614.0	10.0.3506.0	10.0.4302.0	10.0.6501.0	11.0.5612.0	11.0.6355.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6214.1000	12.0.6425.1000	12.0.6611.1000	14.0.4756.1000	14.0.6024.1000
Microsoft FrontPage	frontpg.exe	4.0.2.2720	4.0.2.3821	4.0.2.4426	4.0.2.6625	10.0.2623.0	10.0.3402.0	10.0.4128.0	10.0.6308.0	11.0.5516.0	11.0.6356.0	11.0.7969.0	11.0.8173.0
Microsoft Groove	groove.exe													12.0.4518.1014	12.0.6211.1000	12.0.6421.1000	12.0.6600.1000	14.0.4761.1000	14.1.6009.1000
Microsoft InfoPath	infopath.exe									11.0.5531.0	11.0.6357.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6214.1000	12.0.6413.1000	12.0.6606.1000	14.0.4763.1000	14.0.6009.1000
Microsoft OneNote	onenote.exe									11.0.5614.0	11.0.6360.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6415.1000	12.0.6606.1000	14.0.4763.1000	14.0.6022.1000
Microsoft Outlook	outlook.exe/outlib.dll	9.0.0.2711	9.0.0.3821	9.0.0.4527	9.0.0.6627	10.0.2627.1	10.0.3416.0	10.0.4024.0	10.0.6626.0	11.0.5510.0	11.0.6353.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6212.1000	12.0.6423.1000	12.0.6607.1000	14.0.4760.1000	14.0.6025.1000
Microsoft PowerPoint	powerpnt.exe	9.0.2716	9.0.3821	9.0.0.4527	9.0.6620	10.0.2623.0	10.0.3506.0	10.0.4205.0	10.0.6501.0	11.0.5529.0	11.0.6361.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6425.1000	12.0.6600.1000	14.0.4754.1000	14.0.6026.1000
Microsoft Project	winproj.exe					10.0.2915.0	10.0.8326.0			11.0.5614.0	11.0.6707.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6423.1000		14.0.4751.1000	14.0.6023.1000
Microsoft Publisher	mspub.exe					10.0.2621.0	10.0.3402.0	10.0.4016.0	10.0.6308.0	11.0.5525.0	11.0.6255.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6423.1000	12.0.6606.1000	14.0.4750.1000	14.0.6026.1000
Microsoft SharePoint Designer	spdesign.exe													12.0.4518.1014	12.0.6211.1000	12.0.6423.1000		14.0.4750.1000
Microsoft Visio	vision.exe/vislib.dll					10.0.525	10.1.2514	10.2.5110		11.0.3216.5614	11.0.4301.6360	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6423.1000		14.0.4756.1000	14.0.6022.1000
Microsoft Word	winword.exe	9.0.2720	9.0.3821	9.0.4402	9.0.6926	10.0.2627.0	10.0.3416.0	10.0.4219.0	10.0.6612.0	11.0.5604.0	11.0.6359.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6425.1000	12.0.6612.1000	14.0.4762.1000	14.0.6024.1000

You can also download a copy in csv format so you can import it into the program of your choice by clicking here.

Also, note that some of this information is impossible to find on any official website (but then again, why would I be writing such a post if it were!) so there could be mistakes. This is based on what I could find online and what I managed to piece together by looking over various computers. If you find mistakes, please use the contact form to send me an e-mail and I will make the necessary corrections promptly.

Here are a few of my references that I used to compile this list:
Office 2000
Office 2002 (Office XP)
Office 2003
Office 2007
General info on how to retrieve the Executable’s build number

Share and Enjoy

Category MS Access, MS Access General Information, MS Office | Comment No Comments »

Tags: MS Access MS Excel MS Word

November 7th, 2011

MS Access – VBA – Run Parameter Query in VBA

Once one delves into the wonderful world of MS Access & VBA programming, you quickly gets faced with the problem of executing queries and SQL statements from within VBA. No in general, this does not pose much of an issue and there are any number of great tutorials that cover the various way to handle this. For instance, Database Journal has a great article by Danny Lesandrini entitled Executing SQL Statements in VBA Code which covers all the methods available to a developer and explains the pros and cons of each.

That said, all of these articles cover but the basic idea of a simple SQL Statement. What happens if you want to execute a parameter query? How do you approach that. In fact, this isn’t to big an issue either, once you are shown how to handle it.

Firstly, let us very briefly cover the subject of a Parameter. What is a parameter? In a query, we can specify a criteria. In most instances, the criteria will be a static value. That said, it become necessary in many instances to make these criteria dynamic, and to do this there are different methods, but one common approach is to refer to a control on a form, thus allowing the end-user the ability to specify the criteria to apply to the query. This is what I am referring to in this post.

The problem arises that if you merely try to execute a query with a criteria to a form control, it will result in an error “Too Few Parameters”. The work around is that we much first make the database evaluates each parameter before running the query, to do so, we use the following code

    Dim db              As DAO.Database
    Dim qdf             As DAO.QueryDef
    Dim prm             As DAO.Parameter
    Dim rs              As DAO.Recordset
 
    Set db = CurrentDb
    Set qdf = db.QueryDefs("YourQueryName")
 
    For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm
 
    Set rs = qdf.OpenRecordset
 
    'Work with the recordset

    rs.Close 'Close the recordset
    'Cleanup after ourselves
    Set db = Nothing
    Set qdf = Nothing

So how does it work exactly? Well, we start off by specifying which query we are working with by setting the value of the qdf variable. Next, we llop through the parameters and evaluate each one. Finally, we execute the query. So simple, once you are shown the proper tachnique!

You may wish to look over the sample database found at Roger’s Access Library entitled Too few Parameters.

Share and Enjoy

Category MS Access VBA Programming | Comment No Comments »

Tags: MS Access MS Access Queries MS Access VBA Programming

November 4th, 2011

MS Access – Forms – Yes/No option Group with Null State

One of my greatest annoyances with Access is the fact that Microsoft in their infinite wisdom created a great field data type ‘Yes/No’ which you would think would be great for creating a Yes/No Option Group with. This is true if you want to have a default value, but what happens if you want to allow for a Null state, what is commonly referred to as a triple state field/Option Group, to allow for the case (a common situation in my experience) where you would like a Yes/No Option Group but leave it blank until the user actually makes a selection then the Yes/No data type no longer works?! You cannot have a Null value with a standard Yes/No Field.

So what is one to do?
Well, thankfully the solution is actually very simple. Change the data type from Yes/No to Number and change the Field Size to suit your particular needs. For instance, if like me, you wanted to assign -1 to Yes and 0 to No then you would have to change the Field Size to Integer.

Share and Enjoy

Category MS Access Forms | Comment 1 Comment »

Tags: MS Access MS Access Forms

October 4th, 2011

MS Access – AutoNumber Field

We tend to see numerous questions relating to MS Access Autonumbers. Specifically, regarding autonumber not following sequence, skipping sequence.

Regardless of how one might interpret what is written in the help files, AutoNumbers should never, ever, ever, be relied upon as a sequential number. Nor should they ever be used/displayed to the end-user. The simple fact of the matter is that AutoNumbers are merely unique identifiers for each record. Even when set to be incremental, the AutoNumber can be indexed even though no record was actually inserted into the table. This is not a bug, this is simply the way Access was developed.

So what does one do if they require a sequencial number to attribute to each record. The solution is actually very simple. Create a new field in your table and then you can used an equation such as =Dmax(…)+1 to generate the next number in your sequence. But at the end of the day, if you want a sequential number that will not jump sequence and can be faithfully relied upon, you have to create it yourself!

Share and Enjoy

Category MS Access, MS Access General Information, MS Access Tables | Comment No Comments »

Tags: MS Access MS Access Tables

July 21st, 2011

MS Access – Tables – Where to Create Relationships? In the Front-End or Back-End?

Now here is a question I had myself many years ago and could never find a proper answer to! And yet it is such a basic element and such an important one at that!!!

So when one creates a split database (which they all should be), where do you create the tables relationships exactly? Do you create the MS Access table relationship within the Front-End or the Back-End?

The short answer is, in the Back-End. Here are a few explanation from a couple fellow MVPs taken from a recent forum question.

The relationships need to be established in the backend. In fact, you can build a diagram in the frontend but for the backend tables, the referential integrity will not be enforced if the relationships aren’t in the backend — Bob Larson, Access MVP

If you create relationships in the front end the only thing it achieves is that it determines the default joins types when you create a query in design view. To ensure data integrity through enforced relationships they must be created in the back end. So, the recent advice you were given is wrong. You can if you wish create them in the front end in addition to, but certainly not in place of, those in the back end. — Ken Sheridan

What is important to understand here is the fact that you should always create your table relationships in the Back-End of your database. That said, as Ken stated it can be useful to merely recreate them within the Front-End as well to simplify Query building, but then this then incurs extra overhead when the database is modified (Now you have to update the relationships in both locations). The other time when you might create MS Access table relationships within the Front-End of your database is because you have lookup table, reference tables in the Front-End. In such a case, obviously you can’t create the necessary table relationships in the Back-End since the tables don’t exists there. As such, you’d create the necessary relationships directly within the Front-End to ensure referential integrity.

Share and Enjoy

Category MS Access General Information, MS Access Tables | Comment No Comments »

Tags: MS Access MS Access Tables

May 25th, 2011

MS Access – VBA – Hide Object Pane

Here is a simple bit of code that permits you to hide the MS Access’ main object browser, to stop nosy users from accessing tables, queries, etc…

DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdWindowHide

In conjunction with the code to determine whether the user is running the runtime or full version of Access (see MS Access – Determine if Runtime or Full Version
) you could insert a section of code such as:

If SysCmd(acSysCmdRuntime) = False Then
    DoCmd.SelectObject acTable, , True
    DoCmd.RunCommand acCmdWindowHide
End If

This would also be a good place to enable any custom command bars/ribbons and/or disable any built-in command bars/ribbons…

Share and Enjoy

Category MS Access Forms, MS Access Queries, MS Access Reports, MS Access Tables, MS Access VBA Programming | Comment No Comments »

Tags: MS Access MS Access Forms MS Access Queries MS Access Report MS Access Tables MS Access VBA Programming

April 30th, 2011

MS Access – JET SHOWPLAN

I came across an article about optimizing queries using a hidden JET tool call SHOWPLAN which basically generates a showplan.out file (which is merely a text file) which elaborates the method used by Access to execute the query. With this information it is possible to perform optimizations, determine which fields require indexing…

I'm not going to rewrite the article, if the subject interests you, then simply look at the source article.

That said, I did want to try and add a little more information for anyone trying to get Showplan to work on Windows 7 (possibly Windows Vista – untested). To be able to use ShowPlan one must create a registry entry to enable it. In the original atricle, they indicate that the base registry key is found at:

\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines

this may be the case for Windows XP, but if you are using Windows 7 (and I suspect Vista as well) you will not find this key. Instead look for:

\\HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines

The above mentioned article appears to have been moved to: Use Microsoft Jet's ShowPlan to write more efficient queries.

Share and Enjoy

Category MS Access General Information, MS Access Queries | Comment 1 Comment »

Tags: MS Access MS Access Queries ShowPlan

May 2013
M	T	W	T	F	S	S
« Apr
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Search

Pages

Recent Posts

Categories

Tags

Posts tagged ‘MS Access’

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Decompiling an Access Database

One-time Decompile

Reusable decompilation method

Special Note

Proper Steps to Follow when Decompiling an Access Database

Share and Enjoy

Share and Enjoy

Share and Enjoy

Hide Object Pane & Disable SHIFT Bypass

Convert to MDE/ACCDE

Deploy Your Database Using Runtime

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Recent Posts

Calendar

Archives