?
Solved

DAO.QueryDef

Posted on 2009-07-09
15
Medium Priority
?
626 Views
Last Modified: 2013-11-27
I'm getting a debug error on the line qdf.SQL = strSQL stating:

Microsoft Visual Basic
Run-time error '3129':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

Suggestions?

For addtional information refer to Q/A "Inner Join Query based on Input Parameter for Table Names"
Public Sub InnerJoin()
 
    Dim qdf As DAO.QueryDef
    Dim strSQL As String, strQueryName As String
        
strSQL = "SELECT " & strTblBefore & ".PPN AS BeforePPN," & strTblBefore & ".PPS AS BeforePPS," & strTblBefore & _
".PPNStatusEffDate AS BeforePPNStatusEffDate," & strTblBefore & ".JON AS BeforeJON," & strTblBefore & _
".JobTitle AS BeforeJobTitle," & strTblBefore & ".BillingCriteria AS BeforeBillCriteria," & strTblBefore & _
".ManHours AS BeforeManHrs," & strTblBefore & ".Budget AS BeforeBudget," & strTblBefore & _
".APSUnitN AS BeforeAPSUnitN," & strTblBefore & ".APSWBSN AS BeforeAPSWBSN," & strTblBefore & _
".APSDir AS BeforeAPSDir," & strTblBefore & ".APSMgr AS BeforeAPSMgr," & strTblBefore & _
".POwner AS BeforePOwner," & strTblBefore & ".CompCharge AS BeforeCompCharge," & strTblBefore & _
".ContractLbr AS BeforeContractLbr," & strTblBefore & ".AuthorizedName AS BeforeAuthName," & strTblBefore & _
".PropN AS BeforePropN," & strTblBefore & ".PLN AS BeforePLN," & strTblBefore & ".TCS AS BeforeTCS," & strTblBefore & _
".CP AS BeforeCP," & strTblBefore & ".PWPReq AS BeforePWPReq," & strTblBefore & ".SLNucQA AS BeforeSLNucQA," & strTblBefore & _
".SLProjMgr AS BeforeSLProjMgr," & strTblBefore & ".SLProgMgr AS BeforeSLProgMgr," & strTblAfter & _
".PPN AS AfterPPN," & strTblAfter & ".PPS AS AfterPPS," & strTblAfter & ".PPNStatusEffDate AS AfterPPNStatusEffDate," & strTblAfter & _
".JON AS AfterJON," & strTblAfter & ".JobTitle AS AfterJobTitle," & strTblAfter & ".BillingCriteria AS AfterBillCriteria," & strTblAfter & _
".ManHours AS AfterManHrs," & strTblAfter & ".Budget AS AfterBudget," & strTblAfter & ".APSUnitN AS AfterAPSUnitN," & strTblAfter & _
".APSWBSN AS AfterAPSWBSN," & strTblAfter & ".APSDir AS AfterAPSDir," & strTblAfter & ".APSMgr AS AfterAPSMgr," & strTblAfter & _
".POwner AS AfterPOwner," & strTblAfter & ".CompCharge AS AfterCompCharge," & strTblAfter & ".ContractLbr AS AfterContractLbr," & _
strTblAfter & ".AuthorizedName AS AfterAuthName," & strTblAfter & ".PropN AS AfterPropN," & strTblAfter & ".PLN AS AfterPLN," & _
strTblAfter & ".TCS AS AfterTCS," & strTblAfter & ".CP AS AfterCP," & strTblAfter & ".PWPReq AS AfterPWPReq," & strTblAfter & _
".SLNucQA AS AfterSLNucQA," & strTblAfter & ".SLProjMgr AS AfterSLProjMgr," & strTblAfter & ".SLProgMgr AS AfterSLProgMgr," & _
"FROM " & strTblBefore & " INNER JOIN " & strTblAfter & " ON " & strTblBefore & ".PPN = " & strTblAfter & ".PPN," & "WHERE " & strTblBefore & ".PPS AS BeforePPS," <> "X" And strTblAfter & ".PPS AS AfterPPS," <> "X"
strQueryName = "qryInnerJoinBeforeAfter"
Set qdf = CurrentDb.QueryDefs("qryInnerJoinBeforeAfter")
qdf.SQL = strSQL
Set qdf = Nothing
 
 Debug.Print strSQL
    
End Sub

Open in new window

0
Comment
Question by:dlmaz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +3
15 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24819061
There is something very strange about your SQL string. When I dimension the strTblBefore and strTblAfter as strings and give them values, then put Debug.Print strSQL after strSQL = "SELECT " & ..., I get True instead of a string.
Public Sub InnerJoin()
 
    Dim qdf As DAO.QueryDef
    Dim strSQL As String, strQueryName As String
    Dim strTblBefore As String, strTblAfter As String
    strTblBefore = "strTblBefore"
    strTblAfter = "strTblAfter"
strSQL = "SELECT " & strTblBefore & ".PPN AS BeforePPN," & strTblBefore & ".PPS AS BeforePPS," & strTblBefore & _
".PPNStatusEffDate AS BeforePPNStatusEffDate," & strTblBefore & ".JON AS BeforeJON," & strTblBefore & _
".JobTitle AS BeforeJobTitle," & strTblBefore & ".BillingCriteria AS BeforeBillCriteria," & strTblBefore & _
".ManHours AS BeforeManHrs," & strTblBefore & ".Budget AS BeforeBudget," & strTblBefore & _
".APSUnitN AS BeforeAPSUnitN," & strTblBefore & ".APSWBSN AS BeforeAPSWBSN," & strTblBefore & _
".APSDir AS BeforeAPSDir," & strTblBefore & ".APSMgr AS BeforeAPSMgr," & strTblBefore & _
".POwner AS BeforePOwner," & strTblBefore & ".CompCharge AS BeforeCompCharge," & strTblBefore & _
".ContractLbr AS BeforeContractLbr," & strTblBefore & ".AuthorizedName AS BeforeAuthName," & strTblBefore & _
".PropN AS BeforePropN," & strTblBefore & ".PLN AS BeforePLN," & strTblBefore & ".TCS AS BeforeTCS," & strTblBefore & _
".CP AS BeforeCP," & strTblBefore & ".PWPReq AS BeforePWPReq," & strTblBefore & ".SLNucQA AS BeforeSLNucQA," & strTblBefore & _
".SLProjMgr AS BeforeSLProjMgr," & strTblBefore & ".SLProgMgr AS BeforeSLProgMgr," & strTblAfter & _
".PPN AS AfterPPN," & strTblAfter & ".PPS AS AfterPPS," & strTblAfter & ".PPNStatusEffDate AS AfterPPNStatusEffDate," & strTblAfter & _
".JON AS AfterJON," & strTblAfter & ".JobTitle AS AfterJobTitle," & strTblAfter & ".BillingCriteria AS AfterBillCriteria," & strTblAfter & _
".ManHours AS AfterManHrs," & strTblAfter & ".Budget AS AfterBudget," & strTblAfter & ".APSUnitN AS AfterAPSUnitN," & strTblAfter & _
".APSWBSN AS AfterAPSWBSN," & strTblAfter & ".APSDir AS AfterAPSDir," & strTblAfter & ".APSMgr AS AfterAPSMgr," & strTblAfter & _
".POwner AS AfterPOwner," & strTblAfter & ".CompCharge AS AfterCompCharge," & strTblAfter & ".ContractLbr AS AfterContractLbr," & _
strTblAfter & ".AuthorizedName AS AfterAuthName," & strTblAfter & ".PropN AS AfterPropN," & strTblAfter & ".PLN AS AfterPLN," & _
strTblAfter & ".TCS AS AfterTCS," & strTblAfter & ".CP AS AfterCP," & strTblAfter & ".PWPReq AS AfterPWPReq," & strTblAfter & _
".SLNucQA AS AfterSLNucQA," & strTblAfter & ".SLProjMgr AS AfterSLProjMgr," & strTblAfter & ".SLProgMgr AS AfterSLProgMgr," & _
"FROM " & strTblBefore & " INNER JOIN " & strTblAfter & " ON " & strTblBefore & ".PPN = " & strTblAfter & ".PPN," & "WHERE " & strTblBefore & ".PPS AS BeforePPS," <> "X" And strTblAfter & ".PPS AS AfterPPS," <> "X"
 
'======== ADDED ========
Debug.Print strSQL
'=======================
 
strQueryName = "qryInnerJoinBeforeAfter"
Set qdf = CurrentDb.QueryDefs("qryInnerJoinBeforeAfter")
qdf.Sql = strSQL
Set qdf = Nothing
 
 Debug.Print strSQL
    
End Sub

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24819145
try this

strSQL = "SELECT " & strTblBefore & ".PPN AS BeforePPN," & strTblBefore & ".PPS AS BeforePPS," & strTblBefore & _
".PPNStatusEffDate AS BeforePPNStatusEffDate," & strTblBefore & ".JON AS BeforeJON," & strTblBefore & _
".JobTitle AS BeforeJobTitle," & strTblBefore & ".BillingCriteria AS BeforeBillCriteria," & strTblBefore & _
".ManHours AS BeforeManHrs," & strTblBefore & ".Budget AS BeforeBudget," & strTblBefore & _
".APSUnitN AS BeforeAPSUnitN," & strTblBefore & ".APSWBSN AS BeforeAPSWBSN," & strTblBefore & _
".APSDir AS BeforeAPSDir," & strTblBefore & ".APSMgr AS BeforeAPSMgr," & strTblBefore & _
".POwner AS BeforePOwner," & strTblBefore & ".CompCharge AS BeforeCompCharge," & strTblBefore & _
".ContractLbr AS BeforeContractLbr," & strTblBefore & ".AuthorizedName AS BeforeAuthName," & strTblBefore & _
".PropN AS BeforePropN," & strTblBefore & ".PLN AS BeforePLN," & strTblBefore & ".TCS AS BeforeTCS," & strTblBefore & _
".CP AS BeforeCP," & strTblBefore & ".PWPReq AS BeforePWPReq," & strTblBefore & ".SLNucQA AS BeforeSLNucQA," & strTblBefore & _
".SLProjMgr AS BeforeSLProjMgr," & strTblBefore & ".SLProgMgr AS BeforeSLProgMgr," & strTblAfter & _
".PPN AS AfterPPN," & strTblAfter & ".PPS AS AfterPPS," & strTblAfter & ".PPNStatusEffDate AS AfterPPNStatusEffDate," & strTblAfter & _
".JON AS AfterJON," & strTblAfter & ".JobTitle AS AfterJobTitle," & strTblAfter & ".BillingCriteria AS AfterBillCriteria," & strTblAfter & _
".ManHours AS AfterManHrs," & strTblAfter & ".Budget AS AfterBudget," & strTblAfter & ".APSUnitN AS AfterAPSUnitN," & strTblAfter & _
".APSWBSN AS AfterAPSWBSN," & strTblAfter & ".APSDir AS AfterAPSDir," & strTblAfter & ".APSMgr AS AfterAPSMgr," & strTblAfter & _
".POwner AS AfterPOwner," & strTblAfter & ".CompCharge AS AfterCompCharge," & strTblAfter & ".ContractLbr AS AfterContractLbr," & _
strTblAfter & ".AuthorizedName AS AfterAuthName," & strTblAfter & ".PropN AS AfterPropN," & strTblAfter & ".PLN AS AfterPLN," & _
strTblAfter & ".TCS AS AfterTCS," & strTblAfter & ".CP AS AfterCP," & strTblAfter & ".PWPReq AS AfterPWPReq," & strTblAfter & _
".SLNucQA AS AfterSLNucQA," & strTblAfter & ".SLProjMgr AS AfterSLProjMgr," & strTblAfter & ".SLProgMgr AS AfterSLProgMgr " & _
"FROM " & strTblBefore & " INNER JOIN " & strTblAfter & " ON " & strTblBefore & ".PPN = " & strTblAfter & ".PPN" & " WHERE " & strTblBefore & ".PPS <> ""X"" And " & strTblAfter & ".PPS <> ""X"""

strQueryName = "qryInnerJoinBeforeAfter"
Set qdf = CurrentDb.QueryDefs("qryInnerJoinBeforeAfter")
qdf.SQL = strSQL
Set qdf = Nothing

 Debug.Print strSQL
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24819224
For starters, your syntax with the line continuation characters (Underscores) is incorrect.

I've fixed a few lines for you to show you the correct syntax (Underscore at the end, ampersand at the beginning of the next line):
 
strSQL = "SELECT " & strTblBefore & ".PPN AS BeforePPN," & strTblBefore & ".PPS AS BeforePPS," & strTblBefore _
 & ".PPNStatusEffDate AS BeforePPNStatusEffDate," & strTblBefore & ".JON AS BeforeJON," & strTblBefore _
 & ".JobTitle AS BeforeJobTitle," & strTblBefore & ".BillingCriteria AS BeforeBillCriteria," & strTblBefore _
 & ".ManHours AS BeforeManHrs," & strTblBefore & ".Budget AS BeforeBudget," & strTblBefore _
 & ".APSUnitN AS BeforeAPSUnitN," & strTblBefore & ".APSWBSN AS BeforeAPSWBSN," & strTblBefore

Also, you need to define strTblBefore and strTblAfter (as shown in the previous post)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 24819236
Sorry cap,

I didn't refresh the screen.

cap's syntax works as well.
 
(Ignore my previous post)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24819446
dlmaz,

For the record, it's nothing to do with the line continuation characters. I'm set in my ways of doing things, so at first glance your syntax looked wrong to me (that part of the syntax in the original post is definitely correct - my remark to the contrary was wrong).

This was the original end of your sql string:
".PPS AS BeforePPS," <> "X" And strTblAfter & ".PPS AS AfterPPS," <> "X"

Cap made a great catch on this one. The change he made was very subtle:


strTblBefore & ".PPS <> ""X"" And " & strTblAfter & ".PPS <> ""X"""

He rearranged the quotes a bit dropping the quotes before the <> and adding quotes around the X's. This embedded the X in the SQL string.

Before that, the SQL string was effectively a boolean expression that checked two conditions:
1. The bulk of your SQL string <> 'X'
2. "someTableName.PPS AS AfterPPS," <> "X"

Both of these conditions are true since neither of these strings was literally 'X'. That's why the SQL string evaluated to TRUE, per the first comment.

No points for this comment please - it was an excellent find on cap's part, but worthy of an explanation.
0
 

Author Comment

by:dlmaz
ID: 24819456
Thank you for the quick responses, however after using the code as provided by capricorn1, I now get this error:

Run-time error '3075':
Syntax error (missing operator) in query expression '.PPN'

What I am trying to accomplish is to get an inner join query to run based upon input on the table names that are created by the user (see note below).

Please, note the SQL is based upon the following:

Public Function CreateTable()

    Dim sSql As String
    Dim sTable
   
    sTable = InputBox("Enter Table Name", "Table Creation")
    If Trim$(Nz(sTable, "")) = "" Then
        MsgBox "Must specify table name"
    Else
        sSql = "SELECT PPN, PPS, PPNStatusEffDate, JON, JobTitle, BillingCriteria, ManHours, Budget, APSUnitN, APSWBSN, APSDir, APSMgr, POwner, CompCharge, ContractLbr, AuthorizedName, PropN, PLN, TCS, CP, PWPReq, SLNucQA, SLProjMgr, SLProgMgr INTO [" & sTable & "] FROM qryBeforeAfter"

        Debug.Print sSql

        CurrentDb.Execute sSql, dbFailOnError
    End If
End Function

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24819497

dlmaz,

in your query that you build, you have specified
" & strTblBefore & ".PPN AS BeforePPN

and
" & strTblAfter & ".PPN AS AfterPPN
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24819597
Just to mention - readability is a wonderful thing in development.
It helps you support your own application, which helps you make your application's quality better.
While I don't believe in it at the expense of performance or functionality, when there's nothing to lose then readability every time! ;-)
A couple of table aliases and it's much simpler.
strSQL = "SELECT B.PPN AS BeforePPN, B.PPS AS BeforePPS," & _
"B.PPNStatusEffDate AS BeforePPNStatusEffDate, B.JON AS BeforeJON," & _
"B.JobTitle AS BeforeJobTitle, B.BillingCriteria AS BeforeBillCriteria," & _
"B.ManHours AS BeforeManHrs, B.Budget AS BeforeBudget," & _
"B.APSUnitN AS BeforeAPSUnitN, B.APSWBSN AS BeforeAPSWBSN," & _
"B.APSDir AS BeforeAPSDir, B.APSMgr AS BeforeAPSMgr," & _
"B.POwner AS BeforePOwner, B.CompCharge AS BeforeCompCharge," & _
"B.ContractLbr AS BeforeContractLbr, B.AuthorizedName AS BeforeAuthName," & _
"B.PropN AS BeforePropN, B.PLN AS BeforePLN, B.TCS AS BeforeTCS," & _
"B.CP AS BeforeCP, B.PWPReq AS BeforePWPReq, B.SLNucQA AS BeforeSLNucQA," & _
"B.SLProjMgr AS BeforeSLProjMgr, B.SLProgMgr AS BeforeSLProgMgr," & _
"A.PPN AS AfterPPN, A.PPS AS AfterPPS, A.PPNStatusEffDate AS AfterPPNStatusEffDate," & _
"A.JON AS AfterJON, A.JobTitle AS AfterJobTitle, A.BillingCriteria AS AfterBillCriteria," & _
"A.ManHours AS AfterManHrs, A.Budget AS AfterBudget, A.APSUnitN AS AfterAPSUnitN," & _
"A.APSWBSN AS AfterAPSWBSN, A.APSDir AS AfterAPSDir, A.APSMgr AS AfterAPSMgr," & _
"A.POwner AS AfterPOwner, A.CompCharge AS AfterCompCharge, A.ContractLbr AS AfterContractLbr," & _
"A.AuthorizedName AS AfterAuthName, A.PropN AS AfterPropN, A.PLN AS AfterPLN," & _
"A.TCS AS AfterTCS, A.CP AS AfterCP, A.PWPReq AS AfterPWPReq," & _
"A.SLNucQA AS AfterSLNucQA, A.SLProjMgr AS AfterSLProjMgr, A.SLProgMgr AS AfterSLProgMgr " & _
"FROM " & strTblBefore & " B INNER JOIN " & strTblAfter & " A ON B.PPN = A.PPN WHERE B.PPS <> 'X' And A.PPS <> 'X'"
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24823908
Miriam:  I've had & at the end of a line on countless occasions.
0
 

Author Comment

by:dlmaz
ID: 24863248
Please, excuse the delayed response but I've had other issues at work that took priority over this until now. Reviewing the comments since my last post, I still haven't been able to resolve this issue.

Now, there was one module created to allow the user to name the table being created from essentially a Make Table query.

The second module that I'm working on will run the Inner Join query based upon the 2 table names supplied by the user.

For example, the user specifies a table name of Smith and another table name of Jones. When the Inner Join query is run, it should ask the user which table name will be considered tblBefore? and which table name will be considered tblAfter?

Hopefully, this will help clarify what it is I am trying to accomplish. If it's still not clear or if the code used so far will not accomplish this, please, feel free to advise me. Thank you!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24864866
I'm not entirely clear. Which part are you still struggling with? Asking the user? Using their entries? The SQL itself should be OK - no?
0
 

Author Comment

by:dlmaz
ID: 24869520
Using the attached, I'm still getting a Run-time error 'syntax error (missing operator) in query expression '.PPN'.'

Also, I discovered yesterday afternoon that when the SQL did work with input on the table names, the query is not using the data from those tables anyway.

I just don't seem to get it!

I don't know what I'm doing wrong or if I'm making it more complicated than it should be.

ublic Sub InnerJoin()
 
    Dim qdf As DAO.QueryDef
    Dim strSQL As String, strQueryName As String
        
strSQL = "SELECT " & strTblBefore & ".PPN AS BeforePPN," & strTblBefore & ".PPS AS BeforePPS," & strTblBefore & _
".PPNStatusEffDate AS BeforePPNStatusEffDate," & strTblBefore & ".JON AS BeforeJON," & strTblBefore & _
".JobTitle AS BeforeJobTitle," & strTblBefore & ".BillingCriteria AS BeforeBillCriteria," & strTblBefore & _
".ManHours AS BeforeManHrs," & strTblBefore & ".Budget AS BeforeBudget," & strTblBefore & _
".APSUnitN AS BeforeAPSUnitN," & strTblBefore & ".APSWBSN AS BeforeAPSWBSN," & strTblBefore & _
".APSDir AS BeforeAPSDir," & strTblBefore & ".APSMgr AS BeforeAPSMgr," & strTblBefore & _
".POwner AS BeforePOwner," & strTblBefore & ".CompCharge AS BeforeCompCharge," & strTblBefore & _
".ContractLbr AS BeforeContractLbr," & strTblBefore & ".AuthorizedName AS BeforeAuthName," & strTblBefore & _
".PropN AS BeforePropN," & strTblBefore & ".PLN AS BeforePLN," & strTblBefore & ".TCS AS BeforeTCS," & strTblBefore & _
".CP AS BeforeCP," & strTblBefore & ".PWPReq AS BeforePWPReq," & strTblBefore & ".SLNucQA AS BeforeSLNucQA," & strTblBefore & _
".SLProjMgr AS BeforeSLProjMgr," & strTblBefore & ".SLProgMgr AS BeforeSLProgMgr," & strTblAfter & _
".PPN AS AfterPPN," & strTblAfter & ".PPS AS AfterPPS," & strTblAfter & ".PPNStatusEffDate AS AfterPPNStatusEffDate," & strTblAfter & _
".JON AS AfterJON," & strTblAfter & ".JobTitle AS AfterJobTitle," & strTblAfter & ".BillingCriteria AS AfterBillCriteria," & strTblAfter & _
".ManHours AS AfterManHrs," & strTblAfter & ".Budget AS AfterBudget," & strTblAfter & ".APSUnitN AS AfterAPSUnitN," & strTblAfter & _
".APSWBSN AS AfterAPSWBSN," & strTblAfter & ".APSDir AS AfterAPSDir," & strTblAfter & ".APSMgr AS AfterAPSMgr," & strTblAfter & _
".POwner AS AfterPOwner," & strTblAfter & ".CompCharge AS AfterCompCharge," & strTblAfter & ".ContractLbr AS AfterContractLbr," & _
strTblAfter & ".AuthorizedName AS AfterAuthName," & strTblAfter & ".PropN AS AfterPropN," & strTblAfter & ".PLN AS AfterPLN," & _
strTblAfter & ".TCS AS AfterTCS," & strTblAfter & ".CP AS AfterCP," & strTblAfter & ".PWPReq AS AfterPWPReq," & strTblAfter & _
".SLNucQA AS AfterSLNucQA," & strTblAfter & ".SLProjMgr AS AfterSLProjMgr," & strTblAfter & ".SLProgMgr AS AfterSLProgMgr " & _
"FROM " & strTblBefore & " INNER JOIN " & strTblAfter & " ON " & strTblBefore & ".PPN = " & strTblAfter & ".PPN" & " WHERE " & strTblBefore & ".PPS <> ""X"" And " & strTblAfter & ".PPS <> ""X"""
 
strQueryName = "qryInnerJoinBeforeAfter"
Set qdf = CurrentDb.QueryDefs("qryInnerJoinBeforeAfter")
qdf.SQL = strSQL
Set qdf = Nothing
 
 Debug.Print strSQL
 
    
End Sub

Open in new window

0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24869756
Replace lines 27 through 29 with the code in the snippet.
-Chuck
' turn off error handling
On Error Resume Next
    With CurrentDb
        ' delete any the query if it exists
        .QueryDefs.Delete strQueryName
        DoEvents
' turn error handling back on
On Error GoTo 0
        ' create a new query
        Set qdf = .CreateQueryDef(strQueryName)
        DoEvents
        ' set the sql of the new query
        qdf.Sql = strSQL
        DoEvents
        ' run the query,
        qdf.Execute
        DoEvents
        ' delete the query
        .QueryDefs.Delete strQueryName
        DoEvents
    End With

Open in new window

0
 

Author Comment

by:dlmaz
ID: 24869949
Thank you for the quick response but now I get Run-time error '3065'; Cannot execute a select query. The query itself "qryInnerJoinBeforeAfter" is an inner join query....?
0
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 2000 total points
ID: 24870079
Sorry, I used some code that runs an action query. Remove these four lines from the code I previously posted and that should eliminate the error.
        ' run the query,
        qdf.Execute
        DoEvents
        ' delete the query
        .QueryDefs.Delete strQueryName
        DoEvents

Open in new window

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question