Rob Kraft's Software Development Blog

Software Development Insights

Archive for the ‘Access’ Category

Type Mismatch Error in VBA for Microsoft Access Parameters Collection

Posted by robkraft on January 8, 2021

I ran into a strange problem today with an project that I took over where code in Microsoft Access VBA is being converted. The original database, with untouched source code, has the following lines of code to cycle over all of the parameters in a querydef. The code below has been working for 15 years:

Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs(strQuery)

For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)

However, our current Access database project (*.mdb) that has the exact same code, but we have modified “something” now gets a “Type Mismatch” error number 13 when we try to “For Each” over the results of qdf.Parameters.

If I open a watch on qdf.Parameters I can view the parameters without a problem. There is only one parameter and I even confirmed that all properties of the parameter are identical when they run in the old version of our Access database app.

I could not figure out why the code no longer works. Even the query that runs is identical. However, I was able to fix the code to work again for all cases by replacing the For Each block with the following:

Dim i As Integer
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i).Value = Eval(qdf.Parameters(i).Name)
Next i

Posted in Access, Coding | Leave a Comment »

Tracking Microsoft Access Code Changes in GitHub; And Searches with Visual Studio

Posted by robkraft on December 27, 2020

I will admit up front that this approach is not as awesome as the post title may make it sound, but I do find it to be very useful and fairly simple, just a little tedious.

As a consultant, I occasionally get the Access database project that needs some fixes. Of course we can often easily make copies of the .mdb or .accdb files regularly, but as a programmer I want more. I want to be able to see the history of my code changes over time so that I can identify where changes had unintended consequences. I do this by exporting all of the source code out of the Microsoft Access database into flat files, then importing the flat files into a git repo. I have a bit of code below that I use for this, which I found elsewhere on the Internet and modified a little.

I create a new module and place the code below in that module. Then I execute the subroutine. Following that I import or update the files in my git repo. The approach works well but the export does not include macros, and it does not include properties and events on forms and reports, nor table structures. Still, I have found it useful. I also use this approach to pull up all the files I exported in Microsoft Visual Studio which has search tools I like better than those built into the Access database.

I run the code below about once per day to track the latest changes I made, then apply the updates to the git repo.

Public Sub ExportAllCode()

    Dim c As Variant
    Dim Sfx As String

    For Each c In Application.VBE.VBProjects(1).VBComponents
        Select Case c.Type
            Case 2 'vbext_ct_ClassModule, vbext_ct_Document
                Sfx = ".cls"
            Case 100 'vbext_ct_MSForm
                Sfx = ".frm"
            Case 1 'vbext_ct_StdModule
                Sfx = ".bas"
            Case Else
                Sfx = ""
        End Select

        If Sfx <> "" Then
            c.Export _
                FileName:=CurrentProject.Path & "\" & _
                c.Name & Sfx
        End If
    Next c

End Sub

Posted in Access, Coding | Leave a Comment »