Hi Randy,
Please feel free to let me know if there is anything else I can do for you
:- )
Have a great day!
Sincerely,
Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <
www.microsoft.com/security>
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: "Randy Harris" <
[email protected]>
|
| | > Hi Randy,
| >
| > I am not sure what you mean by "Is it possible to create an "openable"
| > query in A2K", do you mean to create a query that select all fields from
| > one table? Just like:
| >
| > Select * from customers
| >
| > However, below is sample to change the underlying SQL in an existing
saved
| > query via ADOX and ADO.
| >
| > Note: remember to add reference to Microsoft ActiveX Data Object 2.x
| > library, and Microsoft ADO Ext. 2.7 for DLL and Security.
| >
| > Option Compare Database
| > Option Explicit
| >
| > Private Sub Command0_Click()
| >
| > Dim cat As New ADOX.Catalog
| > Dim cmd As ADODB.Command
| > Dim mview As ADOX.View
| >
| > Dim myquery As String
| >
| >
| > Set cat = New ADOX.Catalog
| > Set cmd = New ADODB.Command
| >
| >
| > 'specify the SQL string query
| > myquery = "Select * from customers"
| >
| > 'Return Reference to current database.
| > Set cat.ActiveConnection = CurrentProject.Connection
| >
| > Set mview = cat.Views("myquery")
| > Set cmd = mview.Command
| > cmd.CommandText = myquery
| > Set mview.Command = cmd
| >
| > Set mview = Nothing
| > Set cmd = Nothing
| > Set cat = Nothing
| >
| >
| > End Sub
| >
| > Please feel free to reply to the threads if you have any concerns or
| > questions.
|
| Alick, thanks very much for your reply. I am trying to add an Ad Hoc
query
| capability into an application. The user selects the output fields, the
| criteria, sort order and so on, code then builds the SQL.
|
| I had tried to append a View, with cat.Views.Append, which seemed to work,
| but
| DoCmd.OpenView "MyView" would never open it, it always says could not find
| View "MyView". With the sample code you provided, however, I can save a
| query in advance, then change the underlying SQL as needed.
|
| DoCmd.OpenQuery "MyView" seems to be working. Until now, the closest I've
| come to getting it working, was using SELECT INTO MyTable, then
| DoCmd.OpenTable "MyTable". I'm looking for a better solution.
|
| Again, thanks
| Randy Harris
|
|
|
