Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can you create a new SELECT query without using ADO or DAO 1

Status
Not open for further replies.

vpellerito

Technical User
Sep 12, 2006
30
US
By gathering info from a user-form, I create a SQL string to be passed as a query definition in ADO.
Creating the SQL string works fine but when it comes to appending the query as a database view, the code takes too long.

Is there a way to run/create a select query without using ADO or DAO?

The DoCmd.RunSQL and CurrentDB.Execute commands will only work for action queries so I know I can not use those.

Thanks.
Vince
 
Below is essentially the code.

Code:
Private Sub cmdRun_Click()
On Error GoTo HandleError
Dim strSQL As String
Const RecordErrorNum = 3021
Const SyntaxErrorNum = -2147217900
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim cat As ADOX.Catalog
Dim strQueryName As String
Dim intQueryCount As Long
Dim bolQueryExists As Boolean
intColCount = 0
intQueryCount = 0
bolQueryExists = True

DoCmd.Hourglass True
    Call SelectClause
    Call FromClause
    Call WhereClause
    
    strSQL = strSelectClause & strFromClause & strWhereClause & ";"

'Instantiate created objects
    Set cnn = CurrentProject.Connection
    Set cmd = New ADODB.Command
        cmd.CommandText = strSQL
    Set cat = New ADOX.Catalog
        cat.ActiveConnection = cnn

'check if query already exists            
        
'Create query
        cat.Views.Append strQueryName, cmd

'Open the query
        DoCmd.OpenQuery strQueryName, acViewNormal

strSQL has the SQL code which is pulling from several tables and with many outer joins. But like I said, the query does not run slow. It slows down when I append the new query to the database View objects.

Thanks.
Vince
 
It there a reason for using ADO rather than DAO?

Code:
'Needs reference to Microsoft DAO 3.x Object Library
Dim strSQL As String
Dim qdf As DAO.QueryDef

    strSQL = "Select * From tblTable"

    If DLookup("Name", "MSysObjects", "Name= 'tmpQry'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("tmpQry")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("tmpQry", strSQL)
    End If

Or do you mean that the query iteself runs slowly?
 
Well, I read elsewhere on this site that ADO was faster than DAO.
Is that not the case?
Vince
 
Thanks Remou. That thread was very helpful.
Vince
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top