CODEWITHSUNDEEP
ms-accessvba
Patrícia Villela
Patrícia Villela

Reputation: 839

Saving a Query via Access VBA code

I need to "physically" create an SQL query via VBA code. I know how to execute a query in VBA, but I need to save it in the menu. If I could I would post a picture. I'll try to make you imagine.

In the main screen of MS Access there's a bar on the left.

Just to make it clear, "Consulta" is Query in Portuguese.

If you didn't understand me, please excuse my lack of explanation. I'll be pleased to explain again.

Upvotes: 2

Views: 17890

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

I think you want:

If Not IsNull(DLookup("Type", "MSYSObjects", "Name='MyNewQuery'")) Then
    MsgBox "An object already exists with this name"
Else
    CurrentDb.CreateQueryDef "MyNewQuery", "SELECT * FROM Table1"
End If

EDIT re comments

Sub UpdateQuery(QueryName, SQL)
    ''Using a query name and sql string, if the query does not exist, ...
    If IsNull(DLookup("Name", "MsysObjects", "Name='" & QueryName & "'")) Then
        ''create it, ...
        CurrentDb.CreateQueryDef QueryName, SQL
    Else
        ''Other wise, update the sql.
        CurrentDb.QueryDefs(QueryName).SQL = SQL
    End If

End Sub

Note that deleting a query that does not exists will cause an error.

 DoCmd.DeleteObject acQuery, "NewQuery"

Upvotes: 11

Related Questions