MS Access | DEVelopers HUT

May 20th, 2013

MS Access – VBA – Reset the Table Lookup Display Control Property to Text Box

I recently took over a database in which the previous dba had set a number of table fields to act as lookup displaying combo boxes. This is a horrible thing to do, IMHO, and since users should never directly have access to tables, and should be working through forms, there is absolutely no reason for this in the first place. Now not wanting to have to go through hundreds of tables and countless fields resetting this property manually, I decided to write a simple function to perform this cleanup for me. Hopefully this might help someone else out with this issue.

'---------------------------------------------------------------------------------------
' Procedure : RemoveAllTableLookupCboLst
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Loops through all the tables in a database and removes/resets the Lookup
'             Display Control property to reset it to a simple textbox rather than a
'             combo box or listbox.  Never needed as users should never access tables
'             directly!
' 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 RemoveAllTableLookupCboLst
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2013-May-17                 Initial Release
'---------------------------------------------------------------------------------------
Function RemoveAllTableLookupCboLst() As String
    On Error GoTo Error_Handler
    Dim db              As DAO.Database
    Dim td              As DAO.TableDefs
    Dim t               As DAO.TableDef
    Dim fld             As Field
 
    Set db = CurrentDb()
    Set td = db.TableDefs
    On Error Resume Next
    For Each t In td    'Loop through each table
        If Left(t.Name, 4) <> "MSys" Then    'Don't mess with system tables!
            For Each fld In t.Fields    'loop through each field
                fld.Properties("DisplayControl") = acTextBox
            Next fld
        End If
    Next t
 
Error_Handler_Exit:
    On Error Resume Next
    Set td = Nothing
    Set db = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: RemoveAllTableLookupCboLst" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Share and Enjoy

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

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 »

Tags: MS Access MS Access VBA Programming MS Word VBA

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 26th, 2013

MS Access – Bypassing the Outlook E-mail Security Warning

Yet again today, I was asked in a forum the infamous question regarding how to disable the security warning that pops up when trying to automate e-mails. So I thought it was time to make this post on my site to help everyone that has this question.

Anyone that had the pleasure of trying to automate sending e-mails using automation (SendObject method, Outlook Automation) had the pleasure of the following security pop-up.

Microsoft Office Outlook Security Warning

So basically, Microsoft has thrown a stick in developer’s wheels in the hopes of stoping malicious software from propagating unbenounced to the user.

So the question remains, what is a developer to do to make a seamless e-mail tool? Well, as usual, there are a couple possible solutions:

Solution 1 :: Click-Yes
One option would be to download and install a piece of software such as ClickYes. The problem with this is that it can effectively make you vulnerable to propagating malicious software… since it effectively will always authorize any access, whether the request comes from your app, or not. This also requires installation on every computer that will require sending e-mails automatically. In a controlled environment, this can be done, but for general users, this will not be a viable option.

Solution 2 :: Outlook Redemption
Another popular option is to use Outlook Redemption to send your e-mail. The downside here is that it require registering a COM library and then recoding your e-mail automation routine. The positive aspect here is that there are lots of examples available online!

Solution 3 :: Swicth E-mail Techniques
In my opionion, this is the easiest and best solution. Simply stop sending e-mails through Outlook altogether. The major downside here is that since you don’t send the e-mails through Outlook, you will not have a copy saved in your Sent Items. The pros is that it works! It works seamlessly! You could switch over to CDO mailing techniques, use BLAT, use WinSock, … Tony Toew’s covers a number of possible alternate techniques on his site http://www.granite.ab.ca/access/email.htm.

So at the end of the day, there are several solutions available to get around this problem.

Share and Enjoy

Category MS Access - Outlook Automation, MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | Comment No Comments »

Tags: MS Access VBA Programming MS Excel VBA MS Word VBA

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

February 27th, 2013

MS Access – VBA – Determine a Filename without the Extension

Once again, in my programming career, I have on numerous occasions needed to extract the filename from a fully qualified path/filename.extension, but just the filename without the extension. Below, is a very simple function to do so.

'---------------------------------------------------------------------------------------
' Procedure : GetFileNameWOExt
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the filename without the extension from a path\filename input
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strFileWPath - string of a path and filename (ie: "c:\temp\test.xls")
'
' Returns:
' ~~~~~~~~
' test
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Feb-24                 Initial Release
'---------------------------------------------------------------------------------------
Function GetFileNameWOExt(ByVal strFileWPath As String)
On Error GoTo Error_Handler
 
    GetFileNameWOExt = Right(strFileWPath, Len(strFileWPath) - InStrRev(strFileWPath, "\"))
    GetFileNameWOExt = Left(GetFileNameWOExt, InStr(GetFileNameWOExt, ".") - 1)
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: GetFileNameWOExt" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Share and Enjoy

Category MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | Comment No Comments »

Tags: External Files MS Access VBA Programming MS Excel VBA MS Word VBA

February 26th, 2013

MS Access – VBA – Determine a File’s Extension

Another simple question, with a simple answer! At some point, you will need to extract, determine, the extension of a file. Below is a straightforward function to do so.

'---------------------------------------------------------------------------------------
' Procedure : GetFileExt
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the file extension from a path\filename input
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strFileWPath - string of a path and filename (ie: "c:\temp\test.xls")
'
' Returns:
' ~~~~~~~~
' xls
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Feb-24                 Initial Release
'---------------------------------------------------------------------------------------
Function GetFileExt(ByVal strFileWPath As String)
On Error GoTo Error_Handler
 
    GetFileExt = Right(strFileWPath, Len(strFileWPath) - InStrRev(strFileWPath, "."))
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: GetFileExt" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Share and Enjoy

Category MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | Comment No Comments »

Tags: External Files MS Access VBA Programming MS Excel VBA MS Word VBA

February 23rd, 2013

MS Access – VBA – Extract the Nth Term from a String

The heading says it all. Below is another straightforward function to extract the Nth element/term from a input string. I have offset the Element number so it is not 0 based, but rather 1 based. This to make it intuitive to use.

'---------------------------------------------------------------------------------------
' Procedure : ExtractNthTerm
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Extract the nth term form a string
' 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:
' ~~~~~~~~~~~~~~~~
' sString   : Full string to extract the term from
' sDelim    : Separating delimiter character
' iTermNo   : No of the term to extract
'
' Usage:
' ~~~~~~
' ExtractNthTerm("William is a great guy.", " ", 4)  -> will return great
' ExtractNthTerm("apple,pear,orange,mango,lemon", ",", 3)  -> will retun orange
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Feb-7                Initial Release
'---------------------------------------------------------------------------------------
Function ExtractNthTerm(sString As String, sDelim As String, iTermNo As Integer) As String
    On Error GoTo Error_Handler
 
    aTerms = Split(sString, sDelim)
    ExtractNthTerm = aTerms(iTermNo - 1)
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    If Err.Number = 9 Then
'        MsgBox "You have requested a Term No that exceed the supplied String no of term." & vbCrLf & vbCrLf & _
'               "You have requested term " & iTermNo & " and there only appears to be " & UBound(aTerms) + 1 & _
'               " in the supplied string (" & sString & ").", vbCritical + vbOKOnly
        ExtractNthTerm = ""
    Else
        MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: " & sModName & "/ExtractNthTerm" & vbCrLf & _
               "Error Description: " & Err.Description, _
               vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Function

Share and Enjoy

Category MS Access VBA Programming | Comment No Comments »

Tags: MS Access VBA Programming MS Excel VBA MS Word VBA String Manipulation VBA

February 19th, 2013

MS Access – VBA – Web Scrapping

I see frequently asked on various forum questions regarding pulling information off of websites. The concept is relatively straight forward, load a page, grab the content, parse what you need… You can use various need, get into textual parsing, DOM manipulation and extraction, …

There are a number of approaches that can be used, such as: Internet Explorer automation, MSXML2.XMLHTTP, …

As usual, the information on how to do this is sparse, and sometimes hard to find, especially when you don’t even know the proper terms to search for in the first place. Below are a few links to help you get on your way.

http://www.jpsoftwaretech.com/an-exploration-of-ie-browser-methods-part-i/
http://www.jpsoftwaretech.com/an-exploration-of-ie-browser-methods-part-ii/
http://www.jpsoftwaretech.com/an-exploration-of-ie-browser-methods-part-iii/
http://www.jpsoftwaretech.com/vba/msxml-object-library-routines/
http://www.jpsoftwaretech.com/website-parsingretrieval-using-xml/
http://www.accessmvp.com/djsteele/smartaccess.html – Scroll down to the bottom and look at the article entitled: “April 2006: Surfs up!”

Share and Enjoy

Category MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | Comment No Comments »

Tags: MS Access VBA Programming MS Excel VBA MS Word VBA VBA

February 8th, 2013

MS Access – VBA – Exports All Tables to Another Database

I recently needed to export all the tables from a secured mdb into a new accdb I was creating. Now, you can export each table, 1 by 1, manually, but the becomes tiresome and teadeous quite quickly. Instead, I wrote the following simple function to do the work for me.

All it does is loop through the table definitions to identify each table in the database, and as it does, I export that table.

'---------------------------------------------------------------------------------------
' Procedure : ExpAllTbls
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Exports all the tables from the currentdb into the specified db.
'             Can be useful when in a secured db to quickly export all the tables
'             into a new db.  Could easily be expanded to export any other db object
'             (query, form, report, ...)
' 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:
' ~~~~~~~~~~~~~~~~
' sDestDb   - Fully qualified path and filename with extension of the database in which
'             to import the exported tables
'
' Usage:
' ~~~~~~
' ExpAllTbls("C:\Databases\dummytransfer.accdb")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2013-Feb-08                 Initial Release
'---------------------------------------------------------------------------------------
Function ExpAllTbls(sDestDb As String)
On Error GoTo Error_Handler
    Dim db As DAO.Database
    Dim td As DAO.TableDefs
 
    Set db = CurrentDb()
    Set td = db.TableDefs
    For Each t In td    'loop through all the fields of the tables
        'Ignore any system tables
        If Left(t.Name, 4) = "MSys" Then GoTo Continue
        DoCmd.CopyObject sDestDb, t.Name, acTable, t.Name
Continue:
    Next
 
Error_Handler_Exit:
    On Error Resume Next
    Set td = Nothing
    Set db = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ExpAllTbls" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Share and Enjoy

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

Tags: MS Access Tables MS Access VBA Programming

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 9th, 2012

MS Access Sample- Export Data to Excel and/or Word

Over the years, I have answered numerous questions regarding how to export a records, or records to either MS Excel or MS Word. I already have 2 posts on the subject:

but thought a concrete example would help illustrate things even further.

Here are a few screenshots of the sample.

Now in this sample I am covering Excel and Word automation, not built-in commands, such as:

DoCmd.TransferSpreadsheet
DoCmd.TransferText
DoCmd.OutputTo
DoCmd.RunCommand acCmdExportRTF

These are well documented, and plenty of examples can be found online. Instead, I concentrate on demonstrating a few possible ways to export using late binding and word and excel automation, permitting much more control on the final product (font, colors, layout, page orientation and so much more) and no need for any external reference libraries.

Note: for this sample to work, all the supporting files (excel and word) must be in the same folder as the database itself (although this very easy to change in the VBA code provided).

Fill In Excel Or Word Access Demo

Share and Enjoy

Category MS Access - Excel Automation, MS Access - Word Automation, MS Access Samples | Comment No Comments »

Tags: MS Access VBA Programming

December 7th, 2012

MS Access – VBA – Set the Report Ribbon Property Using VBA

With the replacement of the command bars with the ribbon in Access 2007, I have started to always use a standardized ribbon for all my reports. Now, I typically used 2 template reports which I have configured my typical layout, colors, header & footer formatting, etc. and of course defined the Ribbon Name. But like everyone else, sometimes, I start a new report from scratch, modify an existing one and forget to add in the Ribbon Name property and thus the user would not have displayed the ribbon allowing the to print, export or merely close the report; forcing them to shutdown the database just to be able to exit/close the report. So I needed a simple means to ensure all my reports included this ribbon.

I already have a DeployMe function that I run everytime prior to deploying a new version of any database I create to my client’s which sets certain properties, ensures things like auto compact is disabled, auto correct is disables, and much more, so I simply wanted to create a function the I could add to this routine. Below is what I came up with. A simple loop that open each report one by one, sets the RibbonName property and then closes and saves the change. Short, sweet and simple. Best, it work.

 '---------------------------------------------------------------------------------------
' Procedure : SetReportRibbon
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Loop through all the reports in the current database and assign the
'             specified ribbon as the report's ribbon
' 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 SetReportRibbon
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Dec-07                 Initial Release
'---------------------------------------------------------------------------------------
Function SetReportRibbon()
On Error GoTo Error_Handler
    'Ensure that all the reports are set to use the ReportUtilities ribbon
    Dim DbO             As AccessObject
    Dim DbP             As Object
    Dim Rpts            As String
 
    Set DbP = Application.CurrentProject
 
    For Each DbO In DbP.AllReports
        DoCmd.OpenReport DbO.Name, acViewDesign
        If Reports(DbO.Name).Report.RibbonName = "" Then
            Reports(DbO.Name).Report.RibbonName = "ReportUtilities"
        End If
        DoCmd.Close acReport, DbO.Name, acSaveYes
    Next DbO
 
Error_Handler_Exit:
    On Error Resume Next
    Set DbP = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: " & sModName & "/SetReportRibbon" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Share and Enjoy

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

Tags: MS Access Report MS Access Ribbon MS Access VBA Programming

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 21st, 2012

Why a copyright notice in the VBA procedures

I received a nice e-mail today from Jack Stockton:

Why such a big copyright notice in your sample code?
There is really nothing that is special and not available form many other sources.
It is one thing to put your name in the code, but it is hardly copyright material.

For one thing, when I started the site years ago, there were not as many sources of information as today.
I doubt that you will find everything found here in one, easily searchable location.
A lot of code available on certain other sites has been copied from sites such as this one. Even more of a reason to have a notice. If people actually respected where content came from, it wouldn’t be as necessary.
Although there are pretty basic functions that you can find easily by searching, there are also some that you will not find elsewhere and have taken considerable amounts of time to figure out… With well over 200 posts on a variety of MS Access subjects, I doubt it is all easily found elsewhere. Perhaps sites that merely repost other website’s content.
This header also ensure, that one can easily remember the source.
You’ll notice that I put no restriction on usage whatsoever (none of this personal use only…), so I think asking to keep the source where you got the info from isn’t so much to ask.

This is a site I developed on my spare time to try and help out others, free of charge (I assume the cost of the website, hosting, … and don’t have advertising, pop-ups, malware, thrid-party tracking cookies, …). If asking someone to keep a header in a module (that realistically no other person will ever see but the developer) is such an inconvenience…! Really, this is what you consider a problem worthy of an e-mail. Life must be quite boring for you.

It is exactly this type of e-mail, when simply trying to help others, that makes me be on the verge of just shutting down my site altogether. Amazing how in one broad statement you can degrade an entire site, hundreds of hour of work… What is the expression: “If you don’t have anything nice to say, don’t say anything at all.”. You could learn something from that saying. Actually, you could learn a lot from it!!!

Thanks for the support Jack. Instead of criticizing others, feel free to create your own site and do better, it could only help the Access community at large! But then again it is easier to criticize for no real reason.

Actually Jack, I think you’ve just put the nail in this site’s coffin (once it expires, I will not be renewing for another 10 years). Amazing the thanks that one gets trying to help others. Some people have a lot to learn in life.

Share and Enjoy

Category MS Access | Comment 6 Comments »

October 19th, 2012

MS Access – VBA – Requery a Form While Remaining on the Same Record

Have you ever wanted to requery a form after a user inserts a new record or modifies an existing record, to perhaps re-order things, but wanted to stay on the same record that you currently are on once the requery was done?

The fact of the matter is that it truly isn’t very complex to do. Below is some straight forward code to do so and you’d need only add it to a Form’s After Insert event or a control’s After Update event.

    Dim rs              As DAO.Recordset
    Dim pk              As Long
 
    pk = Me.PrimaryKeyFieldName
    Me.Requery
    Set rs = Me.RecordsetClone
    rs.FindFirst "[PrimaryKeyFieldName]=" & pk
    Me.Bookmark = rs.Bookmark
    Set rs = Nothing

Now there is nothing wrong with the code above, but instead of putting such code inside each and every form’s After Insert event and every control’s After Update event, I thought to myself that I should be able to create a simple, re-useable function that I could call, and achieve the same desired effect. Below is that function.

'---------------------------------------------------------------------------------------
' Procedure : FrmRequery
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Requery the form to apply the chosen ordering,
'               but ensure we remain on the current record after the requery
' 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:
' ~~~~~~~~~~~~~~~~
' frm       : The form to requery
' sPkField  : The primary key field of that form
'
' Usage:
' ~~~~~~
' Call FrmRequery(Me, "Id")
' Call FrmRequery(Me, "ContactId")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-19                 Initial Release
'---------------------------------------------------------------------------------------
Sub FrmRequery(frm As Form, sPkField As String)
    On Error GoTo Error_Handler
    Dim rs              As DAO.Recordset
    Dim pk              As Long
 
    pk = frm(sPkField)
    frm.Requery
    Set rs = frm.RecordsetClone
    rs.FindFirst "[" & sPkField & "]=" & pk
    frm.Bookmark = rs.Bookmark
 
Error_Handler_Exit:
    On Error Resume Next
    Set rs = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: FrmRequery" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

The beauty of the above function is that you can copy it into a standard module, and then call it with a single line of code in as many events as you choose. You could even build an event expression, thus not requiring any VBA events, if you so wished to.

Share and Enjoy

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

Tags: MS Access Forms MS Access VBA Programming

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 27th, 2012

MS Access – VBA – Export Database Objects to Another Database

I while back, I wanted to unsecure a database. Instead of messing around with accounts…. I simply decided to export everything, all the database objects: tables, queries, forms, reports, macros, modules into a new unsecured database. Now you can right-click on each object, one at a time, select export, browse to find the database, click ok, ok… but this is simply a complete waste of time.

Don’t ask me why you can’t, using multiple selected objects, perform an export?! this to me is the type of oversight made by MS’ development team, but this is another discussion altogether.

The good news is that we can easily accomplish a complete export using the power of VBA and a few very simple lines of code!

'---------------------------------------------------------------------------------------
' Procedure : ExpObj2ExtDb
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Export all the database object to another 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:
' ~~~~~~~~~~~~~~~~
' sExtDb    : Fully qualified path and filename of the database to export the objects
'             to.
'
' Usage:
' ~~~~~~
' ExpObj2ExtDb "c:\databases\dbtest.accdb"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2008-Sep-27                 Initial Release
'---------------------------------------------------------------------------------------
Public Sub ExpObj2ExtDb(sExtDb As String)
    On Error GoTo Error_Handler
    Dim qdf             As QueryDef
    Dim tdf             As TableDef
    Dim obj             As AccessObject
 
    ' Forms.
    For Each obj In CurrentProject.AllForms
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acForm, obj.Name, obj.Name, False
    Next obj
 
    ' Macros.
    For Each obj In CurrentProject.AllMacros
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acMacro, obj.Name, obj.Name, False
    Next obj
 
    ' Modules.
    For Each obj In CurrentProject.AllModules
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acModule, obj.Name, obj.Name, False
    Next obj
 
    ' Queries.
    For Each qdf In CurrentDb.QueryDefs
        If Left(qdf.Name, 1) <> "~" Then    'Ignore/Skip system generated queries
            DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                                   acQuery, qdf.Name, qdf.Name, False
        End If
    Next qdf
 
    ' Reports.
    For Each obj In CurrentProject.AllReports
        DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                               acReport, obj.Name, obj.Name, False
    Next obj
 
    ' Tables.
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then    'Ignore/Skip system tables
            DoCmd.TransferDatabase acExport, "Microsoft Access", sExtDb, _
                                   acTable, tdf.Name, tdf.Name, False
        End If
    Next tdf
 
Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set tdf = Nothing
    Set obj = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: " & sModName & "/ExpObj2ExtDb" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Voilà, nothing to it (once you do it once).

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 Forms MS Access Queries MS Access Report MS Access Tables MS Access VBA Programming

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

September 10th, 2012

MS Access – VBA – Change Database Password

Ever wanted to change the password of a database, perhaps the back-end to your database from a click of a button rather than opening the file… It can be done quite easily using the simple little function below!

'---------------------------------------------------------------------------------------
' Procedure : Set_DBPassword
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Change the password of any given 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:
' ~~~~~~~~~~~~~~~~
' sDBName   : Full path and file name with extension of the database to modify the pwd of
' sOldPwd   : Existing database pwd - use "" if db is unprotect
' sNewPwd   : New pwd to assign - Optional, leave out if you which to remove the
'             existing pwd
'
' Usage:
' ~~~~~~
'Set a pwd on a db which never had one
' Set_DBPassword "C:\Users\Daniel\Desktop\db1.accdb","", "test"
'Clear the password on a db which previous had one
' Set_DBPassword "C:\Users\Daniel\Desktop\db1.accdb","test" 'Clear the password
'Change the pwd of a pwd protected db
' Set_DBPassword "C:\Users\Daniel\Desktop\db1.accdb","test", "test2"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Sep-10                 Initial Release
'---------------------------------------------------------------------------------------
Public Sub Set_DBPassword(sDBName As String, sOldPwd As String, Optional sNewPwd As String = "")
    On Error GoTo Error_Handler
    Dim db              As DAO.Database
 
    'Password can be a maximum of 20 characters long
    If Len(sNewPwd) > 20 Then
        MsgBox "Your password is too long and must be 20 characters or less." & _
               "  Please try again with a new password", vbCritical + vbOKOnly
        GoTo Error_Handler_Exit
    End If
 
    'Could verify pwd strength
    'Could verify ascii characters

    Set db = OpenDatabase(sDBName, True, False, ";PWD=" & sOldPwd)    'open the database in exclusive mode
    db.NewPassword sOldPwd, sNewPwd    'change the password

Error_Handler_Exit:
    On Error Resume Next
    db.Close    'close the database
    Set db = Nothing
    Exit Sub
 
Error_Handler:
    'err 3704 - not able to open exclusively at this time, someone using the db
    'err 3031 - sOldPwd supplied was incorrect
    'err 3024 - couldn't locate the database file
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Set_DBPassword" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Remeber however, if you alter the password to your back-end file, you must then delete and recreate your linked table since the existing ones will no longer work.

Share and Enjoy

Category MS Access VBA Programming | Comment No Comments »

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

July 13th, 2012

VBA – List of files in a specified Folder or Directory

Ever needed to extract a list of files within a folder/directory? Ever needed to iterate through the files within a given folder/ directory? Below is a simple illustration of how you can do it using the Dir() function. Many examples commonly utilize the FileSystem object, when in reality there is no need to use an external library to do this simple task! As they say Keep It Simple Stupid (the KISS philosophy).

'---------------------------------------------------------------------------------------
' Procedure : fListDirFiles
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return a list of files in a given directory
' 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:
' ~~~~~~~~~~~~~~~~
' sPath     : Full path of folder to examine with trailing \
' sFilter   : specific file extension to limmit search to, leave blank to list all files
'
' Usage:
' ~~~~~~
' fListDirFiles("C:\Users\Daniel\Documents\") 'List all the files
' fListDirFiles("C:\Users\Daniel\Documents\","xls") 'Only list Excel files
' fListDirFiles("C:\Users\Daniel\Documents\","doc") 'Only list Word files
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Jul-13             Initial Release
'---------------------------------------------------------------------------------------
Function fListDirFiles(sPath As String, Optional sFilter As String = "*")
On Error GoTo Error_Handler
    Dim sFile As String
 
    sFile = Dir(sPath & "*." & sFilter)
        Do While sFile <> vbNullString
            If sFile <> "." And sFile <> ".." Then
                    Debug.Print sFile & " was found"
                    'Do something with the found file
            End If
            sFile = Dir 'Loop through the next file that was found
        Loop
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: fListDirFiles" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Share and Enjoy

Category MS Access VBA Programming, MS Excel VBA Programming, MS Office, MS Word VBA Programming | Comment No Comments »

Tags: External Files MS Access VBA Programming MS Excel VBA MS Word VBA VBA

Search

Pages

Recent Posts

Categories

Tags

Archive for ‘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

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

Recent Posts

Calendar

Archives