DAO vs ADO? 5

[dabruins]

I've been asked to extend an existing Access database to include many more tables containing many more records (some tables contain over 1 million records). In order to handle these table sizes I've decided to move the tables into a MySQL database and utilize access as a frontend database. As I have been working on building the inteface to the data I have been reading many articles and postings on using access as a frontend to a database server and I feel myself becoming more confused than knowledgable. The application will be used by about 100 users in two offices in different cities. I'm looking for a good resource that will help me decide how to develop this application. If you can point me in the direction of a good text or site that would be great. The following are a few points I want to clarify.

1. My current application uses many bound forms. Is the use of bound forms advisable? Why or why not?

2. If forced to use unbound forms must I automatically move to using ADO or is DAO still an option? There seems to be much more coding required with unbound forms and I'm still struggling to determine exactly what functionality ADO and DAO do/do not provide.

3. Is DAO synonymous with Jet Database Engine and Access? I've read that relying on Jet for larger dataserver environments is not advisable, hence my concern about continuing to use DAO recordsets.

4. I'm also trying to find an example of how an unbound form might synchronize with a subform(s). I've gotten used to the bound form world and am trying to understand how to provide simliar functionality within an unbound form.

I apologize for the lengthy post. Again I'm not expecting anybody to solve all my problems. I'm just looking for a little direction on these points. Thanks.

 

[ddiamond]

sozzer,

Very clever. I'll also give you a star. Here is an enhancement to your idea:

In A2, put =A1+10. Then copy that formula into A3 thru A?. This will save time if you have a lot of lines. For a small routine, it probably doesn't make a difference.

- Dan

 

[dabruins]

Absolutely Sozzer and ddiamond. Great tips!

Just a note though, I've noticed that line continuations are seem to cause problems for the parser when you add the line numbers. You may have to adjust or remove some line numbers or adjust the code.

 

[sozzer]

Thanks for the stars!

On the subject of ADO, does anyone know whether it works slower than SQL?

This may sound mad, but I've got a bit of ADO that runs to update records and then the table is queried with some SQL. Dont really want to convert the ADO to SQL but it's almost as if I need to slow down the code as the SQL doesnt always pick the amended records. However, if I just run the query again a second later, I get the expected result...
Cheers!

 

[ddiamond]

sozzer,

When you say SQL, what object model are you refering to? DAO, JET, stored query, etc...

- Dan

 

[Golom]

sozzer

"ADO" (ActiveX Data Objects) is a data access technology as is "DAO" (Data Access Object).

"SQL" (Structured Query Language) is a language that is understood by relational database management systems for creating, maintaining and displaying relational databases and their contents.

Both "ADO" and "DAO" send SQL statements to a DBMS and receive information back from the DBMS.

In short "SQL" is not something that you could "convert" to from ADO. It is rather one of the underlying constructs that is used within an ADO environment to talk to a relational database.

The problem that you are experiencing has to do with timing issues within ADO (and to some extent, within DAO as well.) That issue is that changes to a database are buffered in memory and are not instantaneously applied to the database. It's therefore possible to issue a query after an update has been made but not see the updated information because it hasn't yet been written to the database.

Create a reference to Microsoft Jet and Replication Objects 2.6 Library and then try the

Dim je As New JRO.JetEngine
je.RefreshCache cnn  [COLOR=green]' <-- cnn is an ADODB.Connection Object[/color]

RefreshCache ensures that the latest dara are available.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[sozzer]

Golom

Thank you - Self taught so dont always know the correct terminology! Looking through my code, I dont kow why that never occured to me as of course i use SQL with ADO and DAO. Indeed, the problem I have only occurs when using the syntax

Set cnn = New ADODB.Connection
With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = Application.CurrentDb.Name
    .Open
End With

rather than

Set cnn = currentproject.Connection

and then only if there is only one record matching the query.

je.RefreshCache cnn

doesnt solve my problem though, but i have a separate post on this issue so wont repeat here

However, have a star - i learn more and more about access everyday!

One more question if I may: When using , eg. DoCmd.RunSQL strSQL, does this send straight to JET?

many thanks
ad

 

[jsteph]

dabruins,
For your error question, the err.Description may just say
"ODBC Call failed".

However, Errors(1)="ODBC Call failed".
However, Errors(0)="Cannot insert duplicate key in index blah, blah..." <or something like that>

As to your original questions, my opinions:

1. My current application uses many bound forms. Is the use of bound forms advisable? Why or why not?

Bound forms can significantly hurt db performance by adding much unecessary traffic. For a simple app with a few users, yes, that's a quick & dirty way that works well. For 100+ users, it'll kill you.

2. If forced to use unbound forms must I automatically move to using ADO or is DAO still an option? There seems to be much more coding required with unbound forms and I'm still struggling to determine exactly what functionality ADO and DAO do/do not provide.

For updates, never use a bound form. For a list/browse form, you could use a bound form, even to a linked table. However, I recommend a model where a user browses records via a read-only continuous (or datasheet) form that has it's recordsource a dynamically created pass-thru query that is filtered by user input. For example

A user is editing customer info. You give him an initial empty form with criteria textboxes. If he doesn't know the cust#, he enters a name. Your passthrue sql has a where clause with the following appended to it:
sql = sql & " CustName Like '" & me.txtCustNameCrit & "'"

Then the contiuous form or datasheet's recordsource is set to this passthru. When he selects the correct record (or if his initial criteria was the cust#) you do a new passthru, open up an Unbound form, load the textboxes based on the passthru fields.

Then upon making changes, you update the record (I'm ignoring locking issues because that's a seperate discussion, but here you'd want to deal with that by checking a timestamp for changes since the initial form load, or lock the record at time of form load, etc etc) by looping through fields that are dirty and creating an update sql and issuing it via passthru or ADO.

3. Is DAO synonymous with Jet Database Engine and Access? I've read that relying on Jet for larger dataserver environments is not advisable, hence my concern about continuing to use DAO recordsets.

For environments with just a few users, DAO is very quick, often faster than ADO, for recordset processing on ODBC-linked tables (as well as JET local tables). Again, the trouble comes if you use DAO to update ODBC-linked table records when there are a large number of users on the db.

4. I'm also trying to find an example of how an unbound form might synchronize with a subform(s). I've gotten used to the bound form world and am trying to understand how to provide simliar functionality within an unbound form.

For syncing, this is all manual. Use my example above for the single record update, but if that form had child records (say, customer orders) then you just do a read-only datasheet or continuous form of orders where cust# = <blah, blah>.
Bottom line--you don't want to use linked tables/bound forms for updating, especially in a continuous form, which would open a dao dynaset type recordset on the odbc linked table which is resource intensive--and not good to have 100 people with this type of dao recordset open on your backend db.
--Jim

 

[ddiamond]

Jim,

Those are some good points. I'll give you a star.

- Dan

 

[dabruins]

Hi Jim.

Thanks for your reply to my originally posted questions. Through some of the earlier replys from ddiamond and others, and now yours I've come to better understand the differences better the database engine and the data access schemas. I've decided on moving forward with ADO and after some initial form design tests I have come to the same conclusion that you alluded to here. Having used bound forms I had been attempting to restrict the number of records returned to an unbound form upon opening it and then provide filter capabilities to the user to work with the records from there. I then dawned on me that I shouldn't even return any records when opening the form leaving it completely unbound and then let the user utilize the searching cababilities to determine which records they wish to view. I also automatically LIMIT the number of records returned (100) from a search to help improve performance and created buttons that allow the user to move to the next or previous 100 records for any search they have initiated. They only get 100 records at a time and I only retrieve the next 100 when the user requires them.

I'm still figuring out how the locking features work between Access and Mysql. I have timestamp fields created on all my tables that I need to edit and at the very least thusfar it seems the conflict detector within access does alert the user to changes on the mysql tables by another user in an editable recordset.

I'm still working on the subforms option and am hoping to add a button on a subform that will allow the user to retrieve the child records at their disgression rather than as it is done in bound forms where scrolling to a new parent record automatically calls the child records forward. I think I can programatically set the LinkChildFields and LinkmasterFields values and pass the appropriate query via ADO to MySQL ODBC.

On a more general note I can't thank you all enough for the help you have provided to me here. I've used other sites to pose other questions and have never gotten anywhere close to the response that I have received here. You should all be commended for your willingness to share your knowledge and experiences with others as well as for the valuable personal and professional time you have taken to do so. This site is absolutely fantastic!! A VERY BIG STAR TO YOU ALL!

 

[ddiamond]

They only get 100 records at a time and I only retrieve the next 100 when the user requires them.

How have you accomplished the get next 100 / get previous 100? I've often attempted something like that, but never found an adequate solution.

 

[dabruins]

Hi ddiamond.

Yes but I'm utilizing the "LIMIT" keyword in the MySQL select statement that I'm passing directly to MySQL bypassing jet. I don't think there is an equivalent in VBA? This option allows me to restrict the number of records returned as well as which allotment of the records for a given select statement.

Sorry but I'm not at my desk today or tomorrow. I'll paste what I've done here in a couple of days.

Basically, I've declared two private integer variables on the form, one to hold a "start at record number" value and a second to hold the number of record to return, in this case set to 100. (for larger tables you might nedd to use longs?) I also declare a string variable to hold the value of the current select statement for the unbound form.

Dim intstartat, intnumrecs as INTEGER
'Set the values at 0 and 100 respectively in the form.
intstartat = 0
intnumrecs = 100

Once the user opens the form and provides query parameters, applying the search criteria to the form, develop the query string in such a way that it is a combination of the main select information derived from the form inputs as well as the limit statement with the default values.

'Set the values at 0 and 100 respectively for the form.
intstartat = 0
intnumrecs = 100

strSql & " LIMIT " & intstartat & "," & intnumrecs & ";"

On the "Next 100" command button and within the click event code make sure that the LIMIT variable values are updated (increment intstartat by 100) prior to passing the basic sql statement along with the updated limit values in a new sql query for the form.

In the case of the "Previous 100" just decrement the intstartat value instead of increasing it. Check to see if the current intstartat value is <=100 before doing so though.

In the case of a new search being initiated reset the values back to 0 and 100 respectively, as well as the basic query string to inlcude the new parameters provided by the user.

In the case of jet database I'm not too sure of how this might be done? If I think of something I'll certainly let you know.