I need to do this from several areas, the fields will differ for each query. Is there a way to make the form adapt to the fields of the query so I can have one form stored to use anytime I need to display query results? I would prefer not to have a form for this purpose, that is what a query is for.
I have opened a code built query using DAO code and I like the result, I just wanted to change this over to ADO.
This is what I am looking for, but I want an ADO version, I can't find the ADO equivalent of a querydef
Dim db As Database
Dim qdf As QueryDef
Dim SQL As String
Set db = CurrentDb
SQL = "SELECT * FROM balances"
Set qdf = db.CreateQueryDef("temp1", SQL)
No way that I know of that automatically adapts a form to the actual columns in a recordset. I think you're in for some fairly detailed nuts & bolts programming using an unbound form to do this.
Also no easy way to bind a form to a recordset that you have created dynamically via ADO (or DAO for that matter). I have in the past done something similar but you need to create the Tabledef and then poke all the rows into the new table. In this way Access then 'knows' this table and can bind forms to it.
Why not abandon the ADO approach altogether and as Bbender27 suggests use a bound data sheet view. Try playing with the Filter and OrderBy propeties to limit the information shown and the sequence in which it is shown. Still can't do anything about the columns.
By the way you need ADOX to create tables using the ADO model and I have trouble getting ADOX to work properly when creating table fields/columns. All seems fine until I try to poke a null into the field; Access then reports that the field is not nullable. If I try to set the nullable property for that field I get another error. Needless to say my attempt to create tables dynamically using ADOX was abandoned.
So you are telling me that opening an actual query window is something that is simple to do in DAO but impossible in ADO? That doesn't seem right somehow, I figured it would be just as easy in ADO code.
No, I'm not saying that. In fact I think the premise that a query window depends upon your data access object model is wrong.
ADO has a very condensed object model consisting of: Connection; Errors, Error; Command, Parameters, Parameter; Recordset, Fields, Field. No mention there of any query object. To match the functionality of DAO (or at least provide some of the essentials) Microsoft provides the ADO extension, ADOX. This extension provides objects such as Table, Index, Column, View, User - all those good things you need for the Jet RDBMS. (Incidentally it also provides the Procedure object which is useful for dynamically altering your VBA code - but we never do those sort of things, do we?) Again there is no mention of a query object.
Queries are definitely members of Access. Query itself is a string property of the Form object and I am suggesting that you may be able to use this property to alter the underlying recordset of a form. You may also wish to use the Filter property as 'fine tuning' (remember to set FilterOn to True). However this does not overcome your requirement to adapt the form design to accommodate only those fields that are in your query result set. It can be done but is tedious, messy and almost unmaintainable. I would suggest you experiment to see if you can design a limited number of form variation that fulfil the requirements. When you know the query you are going to use then by inference you know the form variant.