Opening a query window USING VBA

[darinmc]

Hi
I know how to open a query using one that is pre made.

docmd.openquery "The Query's name", acViewNormal, acReadOnly

How could i do it using an sql statement in VBA

---------------------------------
e.g

Dim sql as string

sql = "select * from tblBackup"

????
docmd.openquery sql .....

Thx
Darin

 

[genomon]

What you will be opening is a recordset based on SQL or a QueryDef object. Possibly create a make-table query, run it, then open the table if you need to see the data in datasheet view?

Always remember that you're unique. Just like everyone else.

 

[genomon]

Scared up an example from my stuff:

If ObjectExists("Table", "tbl_flat_out_340_header") Then
    DoCmd.DeleteObject acTable, "tbl_flat_out_340_header"
End If
DoCmd.OpenQuery "qryCreate340Header"

Where qryCreate340Header is a make-table query.
Deleteing the table object first ensures that no
pop-up dialog will appear.
Hope this helps!






Always remember that you're unique. Just like everyone else.

 

[JerryKlmns]

darinmc

Dedicated forum for Access VBA is forum705, for future use

 

[mikej336]

I assume you are talking about Access.

Public Sub DoSQL()

Dim SQL As String

SQL = "select * from tblBackup"

DoCmd.RunSQL SQL

End Sub

Mike

 

[mikej336]

You could also save it as a Query definiton. And rerun it anytime.

Mike

 

[PHV]

Mike, the RunSQL method is for action queries only ...

 

[darinmc]

Hi genomon

I can see the sense of checking if a table exists, but how would i create it.

select empregno, empno from tblBackups

I would like a really simple solution so that it would open a simple un-editable table/query as it does in my original question.

ie: to save a simple sql as a temp query then use the
DoCmd.OpenQuery

Thx Darin

 

[mikej336]

Here is the code to create a basic query definition.
Use genomon's code from above to delete as necessary

Mike


Private Sub Command0_Click()

Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef

strSQL = "Select * From YADPOS2_B1BILLL0"
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("test", strSQL)

DoCmd.OpenQuery "test", , acReadOnly

dbs.Close
Set qdf = Nothing
Set dbs = Nothing

Dim test As New QueryDef

End Sub

 

[JerryKlmns]

If you do this
Set qdf = dbs.CreateQueryDef("test", strSQL)
then you have to delete the query with
dbc.QueryDefs.Delete "test"

but if you do this
Set qdf = dbs.CreateQueryDef("", strSQL)
the query is not saved and you don't have to delete it.

The second is very usefull if there might be two users doing the same thing with that.

 

[darinmc]

Thanks
This seems to work, will try it on the network...

Private Sub Command17_Click()
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef

strSQL = "Select * From tblAbsences"
Set dbs = CurrentDb()

Set qdf = dbs.CreateQueryDef("test100", strSQL)

DoCmd.OpenQuery "test100", , acReadOnly

dbs.QueryDefs.Delete "test100"

dbs.Close
Set qdf = Nothing
Set dbs = Nothing

End Sub

Thx
Darin