Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    1

    Unanswered: Run a simple Select Qry in VBA

    Hi,

    Every month, I receive a file from a vendor and perform a simple select query on it. I would like to automate this process in VBA, but seem to have trouble running the SELECT query through VBA. In essence, I would like the Select Query to run and results saved as a Select Query. Below is my code. Any help would be appreciated.

    Thanks!
    Code:
    Sub TestQRYRun()
    
        Dim mySQL As String
        Dim Cnn1 As ADODB.Connection
        Set Cnn1 = CurrentProject.Connection
        Dim myrecordset As New ADODB.Recordset
        
        myrecordset.ActiveConnection = Cnn1
    
        
        'build mySQL string from query's SQL statement
        
        mySQL = "SELECT [Jan Covered Lives].DIVISION, [Jan Covered Lives].[NUMBER OF SUBSCRIBERS], [divisionLookup2 Qry].Division"
        mySQL = mySQL + " FROM [divisionLookup2 Qry]"
        mySQL = mySQL + " INNER JOIN [Jan Covered Lives]"
        mySQL = mySQL + " ON [divisionLookup2 Qry].DIVTXTID = [Jan Covered Lives].DIVISION"
    
    
        myrecordset.Open mySQL
        
        ' Code goes wrong here
        
         DoCmd.OpenQuery myrecordset
        
        myrecordset.Close
        Set Cnn1 = Nothing
        
    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Dittoman99 View Post
    Code:
        ' Code goes wrong here
        
         DoCmd.OpenQuery myrecordset
    Not surprised! You can't do that.

    A recordset is essentially something called a cursor. It is a row-by-row window on your dataset. You can't open it as a query.

    All you need to do is take the contents of mySQL and save it as a new query. Then use DoCmd.OpenQuery supplying the name of your saved query.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •