class module- to use or not to use

[scottmech]

i have a tab control that accesses ~8 fields per page/each from a separate table. i am using subforms on each page. each subform displays data in continuos form mode and the data is based off a customerID. i am attempting to build solution without using any bound fields/etc as i will eventually be upsizing it to sql server. would it be best to call for ADODB recordsets from the main form's code or from class module(s). i attempted to call from class module and got error: Command Text was not set for command object. dunno why tho.

what do you experts think the best route to take is? this program will be used by 30 users and datastore will definitely swell quickly. i set fsub***.SourceObject = "" for all pages except one being displayed and then called sub to create recordset when that particular page is selected.

whats the BEST real world way to handle these 30 users located at 4 sites.
Please help........ im starting to hate computer.

Scott Mech

 

[grnfvr]

I am by no means an expert. But i have a little experience in dealing with something similar.

If the fields on each tab are going to be unbound, you shouldn't need to use subforms. you can just call a procedure when you open the form to pull data from a recordset through an ADO connection.

I have never done this with an external ADO source, But I have done this with access tables. If you don't anticipate ever needing any more forms in the future i would just store all the procedures in the form module.
I don't have any other insight as to why it wasn't working from the module, or advantages of doing it one way or the other. I have not ever opened an ADO connection in Access VBA, so i don't have any experience there. But as i understand it, you should have a ADO connection object that you must open. Then you can open recordsets through the connection object. With a recordset you can set the appropriate properties of the different controls you may have.

 

[Pat Hartman]

The biggest advantage Acces has over VB is its built in ability to work with bound data for forms and reports. You are making a sufficient amount of work for yourself by insisting on unbound forms that I would suggest building the app in VB to begin with.

I have developed many applications that use non-Access backends including SQL server. In fact most of my apps have used Oracle or DB2 back end databases. They all use bound forms and linked tables without difficulty. What I have found in general is that your best bet is to use stored querydefs for all data access. If you have particular performance problems with an Access querydef when run against SQL server, you can create a stored procedure or use a pass-through query instead.

As long as you don't use user defined or internal Access (rather than general SQL) functions in the WHERE clause or HAVING clause of a query, Jet will normally just parse the query and send it directly to the back end database for processing.