renaming queries.

[buggy2]

I am designing a form and in using it the users create new queries all of which are saved as "query1", and created by createquerydef. However I need to force the user to rename the query when they create it or it will result in an error when they create their next query. Is there a "save as" function i can use in access vb.

 

[Russell Hauf]

If you can post the code where you are creating the querydef, I'm sure someone will be able to help you.

Thank you,

Russell

 

[buggy2]

thanks here it is!

Private Sub Bttn_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database

Set db = CurrentDb

If opt_count.Value = True Then
'getstrcnt contains an sql string
strSQL = getstrcnt()
End If

Set qdf = db.CreateQueryDef("qryNew", strSQL)
DoCmd.OpenQuery "qryNew"

db.QueryDefs.Delete ("qryNew")

Set qdf = Nothing
Set db = Nothing

End Sub

 

[Russell Hauf]

I would just prompt the user for the name, and not delete the QueryDef, something like this:

Private Sub Bttn_Click()
    On Error GoTo HandleErr
    
    Dim qdf As DAO.QueryDef
    Dim db As DAO.Database
    Dim fOk As Boolean
    Dim strQryName As String
    
    Set db = CurrentDb
    
    If opt_count.Value = True Then 
        'getstrcnt contains an sql string 
        strSQL = getstrcnt() 
    End If 
    
    
    Do While Not fOk
        fOk = True
        strQryName = InputBox("Please name query") 'you'll be more creative here
        
        Set qdf = db.CreateQueryDef(strQryName, strSQL)
        DoCmd.OpenQuery strQryName
    Loop
    
    RefreshDatabaseWindow

ExitHere:
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
    
HandleErr:
    Select Case Err.Number
    ' The following error numbers are:
    ' 3012 - Object already exists (duplicate query name),
    ' 3125 - Not a valid name, and
    ' 7874 - Can't find object (query does not exist - due to invalid or duplicate name)
    Case 3012, 3125, 7874
        fOk = False
        Resume Next
    Case Else
        MsgBox Err.Description
        GoTo ExitHere
    End Select
End Sub

There are other ways to do it - just use the one that seems easiest to you. Step through the code and enter an invalid name (use an "!" in the name, for example).

HTH,

Russell

 

[buggy2]

Firstly thank you, that worked perfectly. Do you have any idea how to go about exporting the query made to excel?

 

[Russell Hauf]

Please try searching this board for that, as it has been asked many times. You can also look up TransferSpreadsheet in Access help.

If you can't find what you need after that, let me know and I'll see what I can do.

HTH,

Russell

 

[Mr.Access]

HI Russel,

Could you explain the point of creating querydefs? Is it nothing more than saving as SQL expression as a named query?

Just curious...

Thanks!

 

[Russell Hauf]

Yes, just a dynamic way to create a query.

 

[Mr.Access]

That's what I thought...

Thanks for the info!