Archive for ‘MS Access Queries’

June 30th, 2011

MS Access – Sub-Query – Invalid Bracketing

Here is about the most frustrating error message you can get in Access (Okay, there are many others that are just as problematic…)

MS Access Invalid Bracketing Error

I myself had encountered this errors many times in the past and understood the reason (improper SQL statement), but never understood why after performing the necessary correction to the query to make it work, that it would break again?! As of late, I came accross an excellent explanation made by fellow MVP, Dirk Goldgar.

This is a well-known and annoying problem. Access has a preferred, non-standard SQL syntax for derived tables, and if you give it half a chance, it will transform your SQL into that syntax. The standard syntax is:

(SELECT … FROM …) As T

Jet’s preferred, but non-standard syntax is:

[SELECT ... FROM ...;]. As T

.. or some variation thereof. As you see, the parentheses have been changed to square brackets, and a dot (.) has been added after the closing bracket.

Now, this doesn’t matter if the subquery doesn’t include any square brackets of its own — around field or table names, for example. But sometime those are required because of names that use nonstandard characters, and also the Access query designer automatically surrounds everything with brackets “for safety’s sake.” So in these cases, the transformed query becomes syntactically invalid, because Access can’t parse brackets inside of brackets. Once this happens, if the SQL has to be reparsed, an error is raised.

The first workaround for this problem is to ensure that no brackets are present around field names or table names in the SQL of the subquery. If you can do that, then there will be no problem if Access decides to rewrite your SQL in its own quirky way.

If you can’t do that, then you need to design the query in SQL view, and try never to open the query in design view. If you do open it in design view, you must not then save the query, unless you first switch to design view and fix the SQL again.

This is particularly a problem in rowsources for combo and list boxes, since clicking the build button on the RowSource property line automatically opens the query in design view. Flip to SQL view, fix it if necessary, and do your work there.

We have asked Microsoft to fix this bug in the query designer — so far, to no effect.

Feel free to checkout the original post at: http://answers.microsoft.com/en-us/office/forum/office_2003-access/sub-query-headaches/b1e09348-209f-4a2f-acc1-fe7191ae9591

So what have we learnt, yet again? The importance of following naming conventions. In my particular case, like the OP in the original post, I am working with an old database that was developed by someone else and am now trying to fix. So I will have to live with this nuissance until I can do a cleanup of table field names, etc. But the entire issue can easily be entirely avoided by following a simple naming convention.

Lastly, thank you Dirk for the excellent, plain English explanation!

CategoryMS Access Queries | CommentNo Comments »

Tags:
June 13th, 2011

MS Access – VBA – Delete all the Queries

Similarily to deleting tables, if you have ever needed to delete all the queries from a database, it can be a long tedeous task as Access does not allow multiple Object selection. So you have to delete each query, one by one!!! Hence, why I created the simple little procedure below.

'---------------------------------------------------------------------------------------
' Procedure : DeleteAllQueries
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Deletes all the queries from the active 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).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-Jun-10                 Initial Release
'---------------------------------------------------------------------------------------
Function DeleteAllQueries()
On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim qdf              As DAO.QueryDef
 
    DoCmd.SetWarnings False
    Set db = CurrentDb
    For Each qdf In db.QueryDefs
        DoCmd.DeleteObject acQuery, qdf.Name
    Next
 
Error_Handler_Exit:
    DoCmd.SetWarnings True
    Set qdf = Nothing
    Set db = Nothing
    Exit Function
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: DeleteAllQueries" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

CategoryMS Access Queries, MS Access VBA Programming | Comment1 Comment »

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

CategoryMS Access Forms, MS Access Queries, MS Access Reports, MS Access Tables, MS Access VBA Programming | CommentNo Comments »

Tags:
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 this to work using 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

CategoryMS Access General Information, MS Access Queries | CommentNo Comments »

Tags:
April 11th, 2011

MS Access – VBA – Append/Insert Record into Another External Database

So how can one take data from one database and insert it into another database?

There are 2 methods that come to mind and both are quick and easy to implement.

 

Specifying the Database to Append to in Your SQL Statement

The first method is to modify your query to include the external database as a reference. A basic Insert query would look something along the lines of:

INSERT INTO TableName ( Field1, Field2, ...)
VALUES ( Value1, Value2, ...);

but did you know you can also include, as part of your SQL statement, the database to append the data into?! So we could easily modify the SQL statement like:

INSERT INTO TableName ( Field1, Field2, ...) In 'FullPathAndDbNameWithExtension'
VALUES ( Value1, Value2, ...);

Here’s a concrete example:

INSERT INTO Temp1( FirstName, LastName) In 'C:\Databases\Clients.mdb' 
VALUES ( "Don", "Hilman");

 

Using Linked Tables

Another approach is to merely create a linked table to your secondary database and then run a normal append query on your linked table!

Either way, as you can clearly see for yourself, it is not a hard thing to insert data into a table contained within another external database!

CategoryMS Access Queries, MS Access Tables | CommentNo Comments »

Tags:
April 8th, 2011

MS Access – VBA – Determine Where a Field Is Used

I recently had to make a minor change to a poorly designed, but large database and had to determine where certain fields were being used so I could go make the necessary changes. In this case, I had to review hundreds of MS Access objects, so a manual approach was just not acceptable. As such, I created a VBA to let the computer do the checking for me and report back. Below is the fruits of my labor.

'---------------------------------------------------------------------------------------
' Procedure : FindFieldUsedWhere
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Locate where a field is used within queries, forms and reports
' 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:
' ~~~~~~~~~~~~~~~~
' sFieldName    : Field Name to search for in the various Access objects
'
' Usage:
' ~~~~~~
' FindFieldUsedWhere("Type A")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-04-08                 Initial Release
'---------------------------------------------------------------------------------------
Function FindFieldUsedWhere(sFieldName As String)
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
 
    Set db = CurrentDb
    Debug.Print "FindFieldUsedWhere Begin"
    Debug.Print "Searching for '" & sFieldName & "'"
    Debug.Print "================================================================================"
 
    'Check Queries
    For Each qdf In db.QueryDefs
        'qdf.Name    'The current query's name
        'qdf.SQL     'The current query's SQL statement
        sSQL = qdf.SQL
        If InStr(sSQL, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Query: " & qdf.Name
        End If
    Next
 
    'Check Forms
    For Each frm In CurrentProject.AllForms
        DoCmd.OpenForm frm.Name, acDesign
        If InStr(Forms(frm.Name).RecordSource, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Form: " & frm.Name
        End If
        'Loop throught the Form Controls
        For Each ctl In Forms(frm.Name).Form.Controls
            Select Case ctl.ControlType
                Case acComboBox
                    If Len(ctl.Tag) > 0 Then
                        If InStr(ctl.Tag, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                        End If
                        If InStr(ctl.ControlSource, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                        End If
                    End If
                Case acTextBox, acCheckBox
                    If InStr(ctl.ControlSource, sFieldName) Then
                        'The Query is a Make Table Query and has our TableName we are looking for
                        Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                    End If
            End Select
        Next ctl
        DoCmd.Close acForm, frm.Name, acSaveNo
    Next frm
 
    'Check Reports
    Set DbP = Application.CurrentProject
    For Each DbO In DbP.AllReports
        DoCmd.OpenReport DbO.Name, acDesign
        If InStr(Reports(DbO.Name).RecordSource, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Report: " & DbO.Name
        End If
        'Loop throught the Report Controls
        For Each ctl In Reports(DbO.Name).Report.Controls
            Select Case ctl.ControlType
                Case acComboBox
                    If Len(ctl.Tag) > 0 Then
                        If InStr(ctl.Tag, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                        End If
                        If InStr(ctl.ControlSource, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                        End If
                    End If
                Case acTextBox, acCheckBox
                    If InStr(ctl.ControlSource, sFieldName) Then
                        'The Query is a Make Table Query and has our TableName we are looking for
                        Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                    End If
            End Select
        Next ctl
        DoCmd.Close acReport, DbO.Name, acSaveNo
    Next DbO
 
    Debug.Print "================================================================================"
    Debug.Print "FindFieldUsedWhere 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 Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FindFieldUsedWhere" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, "An Error has Occured
    Resume Error_Handler_Exit
End Function

This is my first draft and I haven’t looked at optimizing my code. It was more about getting results than developing a procedure to be reused often. Hopefully it can help someone else in the same boat as I found myself! Simply execute the procedure and it will return a listing of what Access objects use the specified field in the immediate window of the VBE console.

CategoryMS Access Forms, MS Access Queries, MS Access Reports, MS Access VBA Programming | Comment1 Comment »

Tags:
November 8th, 2010

MS Access – VBA – Run a Query in Another Database

So how can you run a query in another database?

Well, that depends! It depends on whether you simply need to run an action query or if you actually wish to get the results returned to you.

One method is to use DAO programming to access the remote db and simply execute the query. This implies that you are simply wanting to run an action query.

Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("FullPathAndFileNameOfThe2ndDb")
db.Execute "TheQueryNameYouWishToExecute", dbFailOnError
Set db = Nothing

Another method which will actually return the results of a SELECT query… is to add the 2nd database as a reference in your 1st database and then call a function which open you query. You can find a sample database illustrating this technique at http://www.access-programmers.co.uk/forums/showthread.php?t=156716 (in the second post by MStef).

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

June 12th, 2010

MS Access – Listing of Database Objects

It can be useful to have a listing of all the objects in the database. For instance, a listing of all the table or queries… This can easily be achieved using a query which uses as its source a hidden system table named ‘MSysObjects’. The basic query SQL statment is as follows:

SELECT MsysObjects.Name AS [List OF TABLES]
FROM MsysObjects
WHERE (((MsysObjects.Name) NOT LIKE "~*" AND (MsysObjects.Name) NOT LIKE "MSys*") AND ((MsysObjects.TYPE)=1)) ORDER BY MsysObjects.Name;

You need only change the value of the (MsysObjects.Type)=1 part of the query expression to change what listing is returned. Below are the various values that can be used to return the various objects available in Access:

Object Type Value
Tables (Local) 1
Tables (Linked using ODBC) 4
Tables (Linked) 6
Queries 5
Forms -32768
Reports -32764
Macros -32766
Modules -32761

CategoryMS Access Forms, MS Access General Information, MS Access Queries, MS Access Reports, MS Access Tables | Comment3 Comments »

June 12th, 2010

MS Access VBA – Looping through records

One very frequent ‘action’ that programmers need to do is to loop through records. This could be a Table or Query … The basic concept is illustrated below using DAO. Although this can be done using ADO as well, I use DAO as it is native to Access and thus most efficient.

Sub LoopRecExample()
On Error GoTo Error_Handler
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim iCount      As Integer
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("TableName") 'open the recordset for use (table, Query, SQL Statement)
    
    With rs
        If .RecordCount <> 0 Then 'Ensure that there are actually records to work with
            'The next 2 line will determine the number of returned records
            rs.MoveLast 'This is required otherwise you may not get the right count
            iCount = rs.RecordCount 'Determine the number of returned records
            
            Do While Not .BOF
                'Do something with the recordset/Your Code Goes Here
                .MovePrevious
            Loop
        End If
    End With
 
    rs.Close 'Close the recordset

Error_Handler_Exit:
    On Error Resume Next
    'Cleanup after ourselves
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: LoopRecExample" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

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

June 10th, 2010

MS Access Queries – Using Built-in Functions in Queries

For new user a quick word of caution to try and avoid one potential headache when using built-in functions within a query.

For instance:
Let say you with to use an update query to standardize the text in a field so that it is ‘Proper Cased’ (ie: capitalize the first character of each word – Good for Names, Address Info,… ), you would use the StrConv() function.

Now in vba you would do something like:
StrConv(“joHn HOLMEs”, vbProperCase)

However, if you were to use the same in the QBE when building your query for a field named “client_name”, you would see that Access would automatically place the vbProperCase between quotes, like:
StrConv([client_name], “vbProperCase”)
and if you ran your update query like this, you would end up with blanked fields. Yes, you would actually lose your data. I know, because I once made the mistake.

So what is the solution? It is quite simple actually! The QBE does not seem to have access to the built-in vba constant values. As such, instead of using the built-in vba constant values, you need to replace them by their literal values. In this case, 3. So our code would become:
StrConv([client_name], 3)

CategoryMS Access Queries | CommentNo Comments »

June 10th, 2010

MS Access VBA – Create a Query

The following function can be use VBA to create a query on the fly.

'---------------------------------------------------------------------------------------
' 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:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: CreateQry" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

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

June 10th, 2010

MS Access VBA – Edit a Query’s SQL Statement

Have you ever needed to change/alter/update/redefine a query’s underlying SQL statement using VBA? It really isn’t very hard. Below is a simple function which illustrates exactly how to achieve this!

'---------------------------------------------------------------------------------------
' Procedure : RedefQry
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Redefine a query's SQL using VBA
' 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 redefine the SQL of
' sSQL      : New SQL statement to use to define the query
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' ? RedefQry("qry_ClientList","SELECT * FROM Clients ORDER BY ClientName")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Nov-07                 Initial Release
'---------------------------------------------------------------------------------------
Sub RedefQry(sQryName As String, sSQL As String)
On Error GoTo Error_Handler
    Dim qdf         As DAO.QueryDef
 
    Set qdf = CurrentDb.QueryDefs(sQryName)
    qdf.SQL = sSQL  'Redefine the Query's SQL

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: RedefQry" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

CategoryMS Access Queries, MS Access VBA Programming | Comment1 Comment »

June 10th, 2010

MS Access VBA – Determine if a Query Exists

The following procedure checks to see if a query exists in the current database.

'---------------------------------------------------------------------------------------
' Procedure : DoesQryExist
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine if the specified query exists or not 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:
' ~~~~~~~~~~~~~~~~
' sQueryName: Name of the query to check the existance of
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' DoesQryExist("Query1")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Feb-02             Initial Release
'---------------------------------------------------------------------------------------
Function DoesQryExist(sQueryName As String) As Boolean
   Dim db   As DAO.Database
   Dim qdf  As QueryDef
 
On Error GoTo Error_Handler
   'Initialize our variable
   DoesQryExist = False
 
   Set db = CurrentDb()
   Set qdf = db.QueryDefs(sQueryName)
 
   DoesQryExist = True  'If we made it to here without triggering an error
                        'the query exists

Error_Handler_Exit:
   On Error Resume Next
   Set qdf = Nothing
   Set db = Nothing
   Exit Function
 
Error_Handler:
   If Err.Number = 3265 Then
      'If we are here it is because the query could not be found
   Else
      MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
      Err.Number & vbCrLf & "Error Source: DoesQryExist" & vbCrLf & "Error Description: " & _
      Err.Description, vbCritical, "An Error has Occured!"
   End If
   Resume Error_Handler_Exit
End Function

Another alternate method to using this function would be to loop through all the QueryDefs to see if the specified name matched any query in the list, but I believe this above listed function is better.

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

June 10th, 2010

MS Access VBA – List Query Fields

The following function will return a listing of all the fields/columns that are shown in a query.

'---------------------------------------------------------------------------------------
' Procedure : listQueryFields
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return a listing of all the fields (column names) of a give Query
' Copyright : The following code may be used as you please, but may not be resold, as
'             long as the header (Author, Website & Copyright) remains with the code.
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strQryName - Name of the query to list the fields of.
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2007-June-01            Initial Release
'---------------------------------------------------------------------------------------
Function listQueryFields(strQryName As String) As String
On Error GoTo listQueryFields_Error
    Dim db As DAO.Database
    Dim qryfld As DAO.QueryDef
    Dim fld As Field
 
    Set db = CurrentDb()
    Set qryfld = db.QueryDefs(strQryName)
    For Each fld In qryfld.Fields    'loop through all the fields of the Query
        Debug.Print fld.Name
    Next
 
Error_Handler_Exit:   
    Set qryfld = Nothing
    Set db = Nothing
    Exit Function
 
listQueryFields_Error:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: listQueryFields" & vbCrLf & _
    "Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

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

June 10th, 2010

MS Access VBA – List of Database Queries

The following VBA Function can be used to produce a list of all the queries within a given MS Access database.

'---------------------------------------------------------------------------------------
' Procedure : ListDbQrys
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Returns a ';' separated string containing the names of all the queries
'             within the database (use Split() to convert the string to an array)
' 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).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2007-Nov                Initial Release
'---------------------------------------------------------------------------------------
Function ListDbQrys() As String
On Error GoTo Error_Handler
 
    Dim DbO     As AccessObject
    Dim DbCD     As Object
    Dim Qrys    As String
 
    Set DbCD = Application.CurrentData
 
    For Each DbO In DbCD.AllQueries
        Qrys = Qrys & ";" & DbO.Name
    Next DbO
    Qrys = Right(Qrys, Len(Qrys) - 1) 'Truncate initial ;

    ListDbQrys = Qrys
 
Error_Handler_Exit:
    Set DbCD = Nothing
    Set DbO = Nothing 
    Exit Function
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: ListDbQrys" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

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

June 10th, 2010

MS Access – List of Database Queries

The following SQL statement can be used to produce a list of all the queries within a given MS Access database.

SELECT MsysObjects.Name AS [List OF Queries]
FROM MsysObjects
WHERE (((MsysObjects.Name) NOT LIKE "~*" AND (MsysObjects.Name) NOT LIKE "MSys*") AND ((MsysObjects.TYPE)=5))
ORDER BY MsysObjects.Name;

CategoryMS Access Queries | CommentNo Comments »