Saving a Query via Access VBA code

Question

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.

Fionnuala · Accepted Answer · 2011-03-13 21:14:39Z

11

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"

edited Mar 13, 2011 at 21:14

answered Mar 13, 2011 at 0:51

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Patrícia Villela Over a year ago

It kinda worked. The statement validating the exintance of the object isn't running. It goes right through it and gives me an error that the object already exists. The problem is the crash that this error produces. If you can, give me another clue.

Fionnuala Over a year ago

A crash suggest your database is corrupt, I am afraid. To ensure that this is the problem, you can check with a new database. After that, it would be worth looking at granite.ab.ca/access/corruptmdbs.htm

Patrícia Villela Over a year ago

No, that's not what I meant. It's a crash in the code for not being able to handle the "Query already exists" error. The If Not IsNull line isn't working. But thank anyway... It really helped with my code.

Fionnuala Over a year ago

@Patrick I am very surprised. I tested quite carefully before posting. What is the error? You realize you cannot create a query if a query or table already exists with that name (forms etc are a different story) ? If a query already exists with that name, Type will be 5 and you can update the sql with new sql, but not create a query.

Patrícia Villela Over a year ago

Anyway, is there a way to destroy a query?

|

Collectives™ on Stack Overflow

Saving a Query via Access VBA code

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related