VBA returning Error 462 when running code more than once

Question

Hello,

I have researched this for hours and cannot find a solution.  In Excel VBA, I am trying to populate a text box in a userform in Access which is used when running a make table query.  My code works great the first time I run it, but returns Error 462 if run again without closing the Excel worksheet.  Can someone please help me with this code so I can move on with my life?  I appreciate any help I can get.

Public Sub GetPlansQuery(planidtorun As String)

Dim appAccess As Object

Dim strDatabasePath As String

strDatabasePath = USERS_ACCESSDATABASEFILEPATH & USERS_ACCESSDATABASE

    On Error GoTo ErrHandler:

    Set appAccess = CreateObject("Access.Application")

    With appAccess

        appAccess.OpenCurrentDatabase (strDatabasePath)

        appAccess.DoCmd.SetWarnings False

        appAccess.DoCmd.OpenForm ("Form1")

        [Forms]![Form1]![Text0].Text = planidtorun   'THIS IS WHERE THE CODE RETURNS THE ERROR

        appAccess.DoCmd.OpenQuery "Aqry_1a2_GetPlans"

        appAccess.DoCmd.Close acForm, ("Form1"), acSaveNo

        appAccess.DoCmd.SetWarnings True

    End With

My_Exit:

On Error Resume Next

If Not appAccess Is Nothing Then

    appAccess.CloseCurrentDatabase

    appAccess.Quit

End If

Exit Sub

'

ErrHandler:

MsgBox "Error " & Err.Number & ": " & Err.Description

Resume My_Exit:

End Sub

Answer 1

The only problem I see with your code is that the line Db.Close should be above the line Set Db = Nothing.

The SQL statement and the Db.Execute line look OK to me. It is clearly a make-table query (you did mention that in your first post).

Answer 2

Since Forms!Form1!Text0 does not refer to appAccess, it probable creates a second instance of Access that remains in memory. Try changing the line

        [Forms]![Form1]![Text0].Text = planidtorun

to

        appAccess.Forms!Form1!Text0 = planidtorun

Answer 3

Thank you again HansV.  I will have to give this a try tomorrow.  I will let you know how it goes.

Answer 4

In Access, open the query Aqry_1a2_GetPlans in design view, then switch to SQL view.

Copy the SQL text of the query.

Paste it into Notepad.

Replace the line breaks in the SQL text with spaces.

If the SQL text contains double quotes ", replace them with single quotes (apostrophes) '.

Place a double quote " in front of and also behind the entire string.

You now have a long string of the form

"SELECT ... FROM ... INTO ..."

Somewhere in this string, there will be   [Forms]![Form1]![Text0]

Replace this with  '" & planidtorun & "'

In Excel, replace the code to start Access etc. with code to open the database in DAO, and to execute the SQL string.

Answer 5

Thank you HansV!  This was probably the only version that I did not try.  LOL

It works great!

Now, if I could only figure out how to avoid the form altogether and pass the parameter directly into the query, I would be making my boss very happy.