sql query won't execute when using the AppAccess.Run method

I am using Access 2007 using the Access 2003 format.

I am trying to run an object on a database (back end db) from another datbase (front end db).  I can get it to run on the back end db but it will not execute the sql query that is in the code.

The front end db runs the code below to run the object in the back end db
 
Public Sub ProNameRun()
     Dim appAccess As Access.Application

    ' Create instance of Access Application object.
    Set appAccess = CreateObject("Access.Application")
   
    ' Open WizCode database in Microsoft Access window.
    appAccess.OpenCurrentDatabase "C:\Users\spalme01\My Documents\Access\Fac Chron Log\Tester\ChronLogDataCN.mdb", False
   
    ' Run Sub procedure.
    appAccess.Run "ProNameRun"
    Set appAccess = Nothing

End Sub

Here is the code for the sub procedure "ProNameRun" that is located in a Module in the back end db

Public Sub ProNameRun()
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim cnn As ADODB.Connection
    Dim vProName
    Set cnn = CurrentProject.Connection
     
    strSQL = "SELECT PrimaryKey, PrimaryLink, PIN, ProName, ConTermDate, ConEffDate, ConType, Retro, FacType, ActiveTerm, Search " _
    & "FROM tblTempParameters " _

    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic, adCmdText
       
    vProName = rst("ProName")
     
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   
    Dim pro As ADODB.Recordset
    Dim proSQL As String
    Dim cnpro As ADODB.Connection
    Set cnpro = CurrentProject.Connection
           
        proSQL = "INSERT INTO tblTempDemoInfo ( PrimaryKey, PrimaryLink, PIN, ProName, ConTermDate, ConEffDate, ConType, Retro, FacType ) " _
        & "SELECT PrimaryKey, PrimaryLink, PIN, ProName, ConTermDate, ConEffDate, ConType, Retro, FacType " _
        & "FROM tblCLDemoInfo " _
        & "WHERE ProName Like '" & vProName & "%" & "' AND ConTermDate is null"
         
    Set pro = New ADODB.Recordset
    pro.Open strSQL, cnpro, adOpenStatic, adLockOptimistic, adCmdText
           
    pro.Close
    Set pro = Nothing
    rst.Close
    Set rst = Nothing
         
 End Sub

The first part of the code gets the parameter(s) from the front end db needed for the where clause in the second part of the code.  

I know the object will run, but it won't execute the sql query, so no data is inserted into the "tblTempDemoInfo" table.
Scott PalmerData AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I wonder if tblTempParameters has data in it? In some cases, the developer will clear out temp tables when the database is closed, and that table may not be repopulated unless you actually open the db from the Access window.

Can you check the values in tblTempParameters?
0
Scott PalmerData AnalystAuthor Commented:
There is data in the table and even if I  leave out the Where statement it does not execute.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you sure there is data in tblCLDemoInfo? Also, I assume that tblCLDemoInfo is a table in the database you're automating?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, there's really no reason to open a Recordset to run an INSERT statement. For you second code block you could do this:

Dim cnpro As ADODB.Connection
Set cnpro = CurrentProject.Connection

cnpro.Execute "INSERT INTO tblTempDemoInfo ( PrimaryKey, PrimaryLink, PIN, ProName, ConTermDate, ConEffDate, ConType, Retro, FacType ) " _
        & "SELECT PrimaryKey, PrimaryLink, PIN, ProName, ConTermDate, ConEffDate, ConType, Retro, FacType " _
        & "FROM tblCLDemoInfo " _
        & "WHERE ProName Like '" & vProName & "%" & "' AND ConTermDate is null"

Open in new window

   Also, your WHERE clause seems to be malformed. It should be:

      & "WHERE ProName Like '" & vProName & "%' AND ConTermDate is null"

Finally, you may need to change the % to a *, depending on the type of table you're working with.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PalmerData AnalystAuthor Commented:
Thanks, working with the peices of code you posted I was able to find out the problem and fix it.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So why the B grade? If you need extra help you should ask, and not just grade down without giving us a chance to earn an A grade.
0
Scott PalmerData AnalystAuthor Commented:
OK, i will keep that in mind.

Scott
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.