Access and Dynamic SQL

[charli]

(I'm using Access 2000 and ADO)

This is probably a daft question, I'm used to programming where I make everything msyelf- can't quite get sued to Access wanting to do things for me!

I have an unbound form, that creates a dynamic SELECT statement, but then what do I have to do with that sql string to get the results shown in a query datasheet or report?

I've been using ADO, and it connects to the database and appears to run the query, but I have no idea what to tell it to do with the recordset it returns!

would i be better off scrapping it and changing to DAO? Or am I miles off in how I think about Access?

Thanks in advance.
Charli

[DonRayner]

Hi Charli,

Can you let us know what you want to accomplish with the recordset that you have returned. And if you could include the code that you are using that would be helpful as well.

Don

[Delerna]

Hi, you sound like you are used to opening recordsets and displaying the results into a table on a web page.

In an access form you don't use ADO (or DAO for that matter) in VBA code to display data on that form in that way. You need to bind the form to a tabledef but preferably to a querydef and not a tabledef.

You could use vba to change the recordsource property of the form

me.recordsource="Select field1,field2 from Table1"
me.requery
me.refresh
 

That won't work properly though if the number of fields or the field names change because the form needs controls on it that are bound to the fields you are selecting. Having said that you could dynamically create and bind the controls on the form, yuk! Access is not really ideal for that sort of thing.
You generally bind a form and its controls to a query and then that form never changes in the way you seem to be talking.

Another possibility
You could use VBA code to create a new querydef in your database using your dynamic query as the code for the querydef.
Then all you would need to do is open the new querydef from VBA.
I don't know if this idea will work for you but here is an example using DAO

Private Sub Form_Load()
   Dim db As database
   Set db = CurrentDb
   sql = "Select * from table1"
   db.QueryDefs.Delete "qryNew" 'Only do this if the query already exists
   db.CreateQueryDef "qryNew", sql
   DoCmd.OpenQuery "qryNew"
End Sub
 

Good luck.

[charli]

Easy to tell isn't it- I can work recordsets to the web and to Excel, just can't work out Access wanting to do things for me!

I have a completely unbound form, it doesn't display anything from the database- you just make selections as to how you would like the data (it basically builds up the WHERE clause of SQL, example: grouped by week/month/year)

I've managed to get a report to do the SQL-building (based on the open form), and change its own RecordSource, but then I have to repeat all the SQL-building code in every report which seems a horrible thing to have to maintain!

I cannot have a query that just has the form fields in as criteria- the query is a bit more complex than that (what you select is dependant on form fields too)

Essentially I want the recordset to be displayed on a report (or an open query datasheet would do), but what is selected and what is in the where clause (and how it is grouped) can change a lot.

Easiest way may be to make excel talk to the access database? Then i can just throw SQL at it and collect the results! I just can't seem to think like Access does.

Is there an ADO equivalent to the DAO querydef? I'd prefer not to use DAO as this will be migrated to SQL Server (eventually, maybe, one day)

[charli]

Worked the DAO->ADO thing out, here is the code to make a querydef in ADO

Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
 
On Error Resume Next
cat.Views.Delete "qryFilter" 'deletes the query if it already exists, ignores errors if it doesn't
On Error GoTo 0
 
cmd.CommandText = sql 'this should be your sql SELECT string
 
cat.Views.Append "qryFilter", cmd 'makes a new one
cat.Views.Refresh 'this is meant to make it so you can view the new query in the query tab, it doesn't always work. however if you then go to manually create a new query, you can see 'qryFilter' has been created as its in the drop down
 
Set cat = Nothing
Set cmd = Nothing

[Delerna]

If its only the where clases that are going to change then there is another possibility of achieving what you want without going down the dynamic query track.

In the query designer you can link the criteria for a field to a control on a form.

[NeoPa]

When opening a Form or Report from code there is a parameter that allows you to specify a WHERE clause on the fly. If it is simply the criteria that may need to change, then this is a good way to handle that.
EG.

Call DoCmd.OpenForm(..., WhereCondition:="([TxtFld] Like '*New*') AND ([NumFld]<0)", ...)

The WhereCondition parameter is formatted like a SQL WHERE clause, except without the word WHERE. It is just as powerful and flexible.

Welcome to Bytes!