Archive for June, 2012

June 27th, 2012

MS Access – VBA – Create a Query

The code below allows you to supply a query name and an SQL statement, and it will create the query for you.

'---------------------------------------------------------------------------------------
' Procedure : CreateQry
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Create a new query in the current database
' 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:
' ~~~~~~~~~~~~~~~~
' sQryName - Name of the query to create
' sSQL - SQL to use
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' CreateQry "qry_ClientList", "SELECT * FROM Clients ORDER BY ClientName"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Nov-07                 Initial Release
'---------------------------------------------------------------------------------------
Sub CreateQry(sQryName As String, sSQL As String)
    On Error Resume Next
    Dim db               As DAO.Database
    Dim qdf              As DAO.QueryDef
 
    Set db = CurrentDb
 
    With db
        'In the next line we try and delete the query
        'If it exist it will be deleted, otherwise it will raise an error but since
        'we set our error handler to resume next it will skip over it and continue
        'with the creation of the query.
        .QueryDefs.Delete (sQryName)                'Delete the query if it exists
        On Error GoTo Error_Handler             'Reinitiate our standard error handler
        Set qdf = .CreateQueryDef(sQryName, sSQL)   'Create the query
    End With
 
    db.QueryDefs.Refresh  'Refresh the query list to display the newly created query

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
 
Error_Handler:
    LogError Err.Number, Err.Description, _
             sModName & "/CreateQry", _
             , True
    Resume Error_Handler_Exit
End Sub

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access Queries, MS Access VBA Programming | CommentNo Comments »

Tags:
June 25th, 2012

MS Access – VBA – Convert Form SQL into Queries Automatically

When I develop, I always, initially, build my SQL statements directly within my form’s Record Source. Now, it is a best practice to always create an independant query and base form’s off of the query rather than use an embedded SQL Statement. This is all fine a dandy when your db has 5-10 forms, but when you are working on databases with 10s or 100s of forms, this can become quite a teadeous task to convert all of the SQL statments into queries and then reassign the newly created queries to their respective forms. So, I thought about it for a couple minutes and quickly realized that this could actually all be automated quite easily. As such, I developed the following procedure.

'---------------------------------------------------------------------------------------
' Procedure : ConvertSQL2QRY
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   :
' 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:
' ~~~~~~~~~~~~~~~~
'
'
' Usage:
' ~~~~~~
'
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-04-30                 Initial Release
'---------------------------------------------------------------------------------------
Sub ConvertSQL2QRY()
    On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim qdf              As DAO.QueryDef
'    Dim sSQL             As String
    Dim ctl              As Control
    Dim frm              As AccessObject
    Dim dbo              As AccessObject
    Dim dbp              As Object
    Dim sFrmName         As String
    Dim sFrmRS           As String
    Dim sQryRS As String
 
    Set db = CurrentDb
    Debug.Print "ConvertSQL2QRY Begin"
    Debug.Print "================================================================================"
 
    'Check Forms
    For Each frm In CurrentProject.AllForms
        sFrmName = frm.name
        DoCmd.OpenForm sFrmName, acDesign
        sFrmRS = Forms(sFrmName).RecordSource
        Debug.Print "Processing Form: " & sFrmName
        If Len(sFrmRS) > 0 And Left(sFrmRS, 4) <> "qry_" Then
            Debug.Print "   Converting Form: " & sFrmName
            If Left(sFrmRS, 7) = "SELECT " Then
                sQryRS = sFrmRS
            Else
                sQryRS = "SELECT [" & sFrmRS & "].* FROM [" & sFrmRS & "];"
            End If
            'Create a query based on the active RS and name it based on the form name for
            '   traceability
            CreateQry "qry_" & sFrmName, sQryRS
            'Change the form RS to use the newly created query
            Forms(sFrmName).RecordSource = "qry_" & sFrmName
        End If
        DoCmd.Close acForm, frm.name, acSaveYes
    Next frm
 
    Debug.Print "================================================================================"
    Debug.Print "ConvertSQL2QRY End"
 
Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Set ctl = Nothing
    Set frm = Nothing
    Set dbp = Nothing
    Set dbo = Nothing
    Exit Sub
 
Error_Handler:
    LogError Err.Number, Err.Description, _
             sModName & "/ConvertSQL2QRY", _
             , True
    Resume Error_Handler_Exit
End Sub

What does this procedure do? Well, it will go through all the forms in your database and create a query using the current record source and then reasign the newly created query as the form’s record source. I run this query just before deploying a databaase. It is also intelligent enough to only process those forms that don’t already have a query created, so it can be rerun whenever needed should I add new forms, etc…

Please note this procedure is dependent on my CreateQry procedure, so be sure to get it as well!

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access Forms, MS Access VBA Programming | CommentNo Comments »

Tags:
June 22nd, 2012

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

Access Solutions: Tips, Tricks and Secrets from Microsoft Access MVPsI 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

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryProduct Reviews | CommentNo Comments »

Tags:
June 22nd, 2012

VBA – Determine the state of a file, Is a file already open

Ever needed to determine if an Excel Workbook was already open, perhaps a Word document. The little function below can help you determine the state of a file.

'---------------------------------------------------------------------------------------
' Procedure : fGetFileState
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the current state of a given file
' 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:
' ~~~~~~~~~~~~~~~~
' sFile     - Full path and filename with extension to determine the state of
'
' Usage:
' ~~~~~~
' fGetFileState("C:\Databases\testme.xlsm")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Oct-09             Initial Release
'---------------------------------------------------------------------------------------
Function fGetFileState(sFile As String) As String
    On Error GoTo Error_Handler
    Dim iFileNo         As Integer
 
    iFileNo = FreeFile
    Open sFile For Input Lock Read As iFileNo
    Close iFileNo
 
Error_Handler_Exit:
    On Error Resume Next
    If fGetFileState = vbNullString Then fGetFileState = "File is not open"
    Exit Function
 
Error_Handler:
    Select Case Err.Number
        Case 53
            fGetFileState = "File does not exist"
        Case 70
            fGetFileState = "Permission denied - File is already open"
        Case 76
            fGetFileState = "Path not found"
        Case Else
            fGetFileState = "Unknow State"
    End Select
    GoTo Error_Handler_Exit
End Function

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | CommentNo Comments »

Tags:
June 20th, 2012

MS Access – VBA – Delete Captions from Table Fields

I used to use table field captions extensively until I started to run into a weird problem in which I couldn’t reassign a fieldname in a query with a new name.  No matter what I did, it always reverted to the field caption.  After a lot of testing, I eventually found the link.  Delete the link and the SQL Alias worked, put the caption back and again the Alias wouldn’t work?!

It has now become common practice for me, as well as many other profesional developers, to delete all the field captions from any database I work on to ensure the flexibility of ALIASes in my queries.  As such, I developed the procedure below to quickly delete the captions from all the tables within a database, instead of trying to do this manually.

'---------------------------------------------------------------------------------------
' Procedure : ClearAllCaptions
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Remove all captions from all the fields in all the non-system tables
'             because of issues that caption cause, mainly:
'               Captions prevent you from being able to assign a new name in a query
'               unless you do a calculation with it
' 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).
'
' Usage:
' ~~~~~~
' Call ClearAllCaptions
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Jun-19                 Initial Release
'---------------------------------------------------------------------------------------
Public Sub ClearAllCaptions()
    On Error GoTo Error_Handler
    Dim Db              As DAO.Database
    Dim sPropName       As String
    Dim fld             As Field
    Dim iTbls           As Integer
    Dim iNonSysTbls     As Integer
    Dim ifldCount       As Integer
 
    Set Db = CurrentDb
    sPropName = "Caption"
    iNonSysTbls = 0
    ifldCount = 0
 
    For iTbls = 0 To Db.TableDefs.Count - 1             'Loop through the table collection
        If (Db.TableDefs(iTbls).Attributes And dbSystemObject) = 0 Then    'Ensure the table isn't a system table
            'we don't want to mess around with them
            For Each fld In Db.TableDefs(iTbls).Fields  'Loop through the table fields
                fld.Properties.Delete (sPropName)   'Delete any captions
                ifldCount = ifldCount + 1
            Next fld
            iNonSysTbls = iNonSysTbls + 1
        End If
    Next iTbls
 
    If iTbls > 0 Then
        MsgBox "Out of a total of " & iTbls & " tables in the current database, " & _
               iNonSysTbls & " non-system tables " & _
               " were processed, in which a total of " & ifldCount & " fields " & _
               "had their '" & sPropName & "' property deleted."
    End If
 
Error_Handler_Exit:
    On Error Resume Next
    Set Db = Nothing
    Exit Sub
 
Error_Handler:
    If Err.Number = 3265 Then
        Resume Next
    Else
        MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: ClearAllCaptions" & vbCrLf & _
               "Error Description: " & Err.Description, _
               vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End If
End Sub

 

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access Tables, MS Access VBA Programming | CommentNo Comments »

Tags:
June 19th, 2012

Mr Lube / Monsieur Lub Review 2

After a little while to cool off from my last visit to Mr Lube, I thought I should perhaps give them a second shot.  Maybe my last visit was a typical…

So I went this morning to get my oil changed.

Pros of my most recent visit:

  • I was taken immediately
  • The technician were very kind and friendly
  • I was offered a coffee while I waited
  • They performed the oil change in about 15-20 minutes

Cons:

  • Items marked as completed on my invoice were never done
    • "Cabin Air Filter -> Appears OK", it was never examined (not that I would have changed it with them after my last experience)
    • "Wiper Blades -> Appears Ok", unless you can check wipers without lifting them up and actually looking at them, they were not validated
    • "Battery Test Passed", no one ever tested the battery
    • "Tire Pressure -> PSI OK" and "Tire Press Pneu", no one ever check the tire pressure
    • "Sealed Graissage Scelle", questionable as the technician was asking were the sealer was and was told by another employee to never mind

At the end of the day, I went there for an oil change, so the fact that my tire pressure wasn't actually check isn't the end of the world.  I just don't like it being indicated that it was performed when it wasn't and then it make me wonder about everything else they supposedly are doing (including the oil change itself).

Also, the price for a simple oil change was 51.24$+taxes which came out to 58.91$.  When you compare that to going to say Canadian Tire, where it would cost you 36.14$, there is a 22.77$ (63%) difference in price for the same service.  Actually at Canadian Tire you get a free inspection of your car at the same time.

You make up your own mind.  For an oil change it is one thing.  They are fast, courteous and you don't have to wait around.  Beyond that, I personally would go elsewhere.

 

Share and Enjoy

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryProduct Reviews | CommentNo Comments »

Tags:
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

  • Google Plus
  • Facebook
  • LinkedIn
  • Twitter
  • Email
  • Print

CategoryMS Access Forms, MS Access Samples | Comment2 Comments »

Tags: