Re: Converting Application from ADP to ACCDB

Tech-Archive recommends: Fix windows errors by optimizing your registry

True. I guess I'm looking for the best of all worlds here. Since I
specialize in the design and fabrication of data analysis and reporting
applications in Access for executives, it is very important to me to meet the
following criteria as best I can.

- Use server-side processing when pulling data from SQL Server as a back-end.
- Have a multi-user front-end file on the server that several executives can
use simultaneously, which is what they want (maintaining a copy on their hard
drive is "too hard and too confusing"...).
- Handle and process user-created queries on the fly (different SELECT fields,
tables, joins, etc.).
- Have local tables available for special, personal, uses.

In order to do this, I have to use ADO as much as possible to get the server-
side processing and avoid using querydefs since you can't change the SQL
string in them without every user that is using the same querydef being
affected by the change. (If one user modifies the SQL string in a querydef,
then EVERY USER gets the modification, and their recordset changes in front
of their eyes - like magic!)

However, I've "discovered" that you can set .recordsource and .rowsource
properties to SQL strings without affecting other users of the file, so a
user can load forms and controls with SQL strings on the fly WITHOUT
affecting any other user in the application - and without having to use
querydefs.

Discovering that you could load a form's .recordset property with an open ADO
recordset and get an updatable form was a tremendous help! Now I want to be
able to do this for subforms and controls with server-side ADO processing too!
I've tried setting the .rowsource property of listboxes and comboboxes to ADO
recordsets, but it doesn't work... (do these controls have .recordset
properties too?)

Any ideas? (I anyone out there knows of a set of tools that someone has
developed to do this, you have a customer!)


Sylvain Lafontaine wrote:
Hum, this doesn't work with an ADO recordset? Then try to set the recordset
of the control to a DAO recordset instead or build a string to be used as
the RowSource of the control; see:

http://www.tek-tips.com/viewthread.cfm?qid=1280174&page=8

Also, I don't see why you want to use a recordset instead of a querydef with
a passthrough query for your controls. Passthrough queries are read-only
but listboxes and comboxes are themselves not updatable, so this changes
nothing.

It works for attaching an ADODB recordset to a form, but can you do the
same
[quoted text clipped - 37 lines]

What's the problem with this solution? It looks fine to me.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200906/1

.

Relevant Pages

  • Re: More ASP.Net Newbie Questions
    ... The Command is then what you're doing with this connection, ... In regards to your final point, making grids and controls in general do ... > Connection and Recordset objects into, like, 37 different things. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: "." versus "!"
    ... if rstMyTable is a recordset object and MyField ... focused on the question of whether to use bang or dot when ... member of the form object's default collection, the Controls collection. ... "proper" and the dot notation improper. ...
    (microsoft.public.access.formscoding)
  • Re: "." versus "!"
    ... if rstMyTable is a recordset object and MyField ... member of the Fields collection of a recordset, you would use the bang: ... member of the form object's default collection, the Controls collection. ... "proper" and the dot notation improper. ...
    (microsoft.public.access.formscoding)
  • Re: Comparing Record Change Unbound Form
    ... VB controls do not expose the same events as Access controls, ... using ADO to access the same Jet tables you're ... having to purchase a SQL Server license to run your product! ... >> associating it with a recordset. ...
    (microsoft.public.access.formscoding)
  • Re: How can I create a dynamically FORM?
    ... Same thing with the attached labels. ... I have used this method in forms and reports to display the results of Xtab ... I can bind the recordset to a form without a problem. ... Change programmatically the name of the controls (acTextControl) ...
    (microsoft.public.access.formscoding)