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.

 

[fneily]

DAO was strictly used inside Access with the Jet Engine from Office 97 back. Microsoft decided to created a generic language to interface with other databases and starting in 2000 distributing access with ADO. However, you can still use DAO, but Microsoft has stopped any development on it. Some DAO statements work in ADO, some don't. And ADO has it's own statements. Plus they reference different libraries.
The more important question to me is that you state you have 100 users in two remote offices. Do you want them to Access the data through the internet?
For simplicity, you may want to consider a Citrix server. (

http://WWW.CITRIX.COM).

Your users can then remotely login and access the database directly. This would avoid creating an application in Data Access Page, ASP, SQL or whatever.
Others, hopefully, will offer their opinions.

 

[Melagan]

On the subject of Citrix - I've had a much easier time with Remote Desktop Connection (assuming the server with your db frontend has terminal services enabled).


~Melagan
______
"It's never too late to become what you might have been.

 

[ddiamond]

I've written many applications similar to what you describe, only our backend database is oracle, not mySQL. If you link your backend tables to MSAccess, you will be able to develop as if everything were local Access tables. I would not recommend this approach, however, because performance suffers. I have had the best results with Pass-Thru queries. Pass-Thru queries work with both DAO and ADO. I have had the best perfomance results by using DAO to create an ODBC-Direct connection. This allows you to use DAO objects, but completely by-passes the jet engine, sending all sql directly to your beckend database server. Bound forms are not a problem as long as you are smart about how you use them. I have also found the Citrix is a good solution for remote locations. The only problem we occasionally run into with citrix and MS Access, is it occasionally picks the wrong default printer.

 

[dabruins]

I guess I'm trying to determine if I'm using an access frontend do I have to use DAO and I gues the answer is no. You can go with either one but as you have pointed out DAO is on its way out.

Maybe I should have asked how do I ensure that I am not utilizing the Jet database engine when performing any data manipulations (Selects/Updates/Deletes)? Looking at some texts involving ADO it appears that I have to send all requests to a Command object which calls a MySQL stored procedure on my database server. I now think it doesn't matter which command object (ADO or DAO though but most folks seem to be moving to ADO). This way I will bypass the JET DBE and hopefully improve performance.

Thanks for the input on enabling clients to access the application. We currently have a WAN environment that permits clients from both offices to see certain areas of a server that would host the dataserver. This was another point I had been looking into as well. The connection is not a very fast one. That would have to be addressed as well. The server will have terminal services installed so Remote Desktop which we do use might be the way to go. I've never had to set up clients with this but I'll look into it. Maybe I could call on you guys again when I get into setting this up. Eventually we would like to set this up as an intranet application but right now I'm going with what little I know about VBA in Access. I'll look in PHP at some point. The first task is to get a functioning database up and running.

Thanks for you help.

 

[dabruins]

Thanks for your input ddiamond. As for the pass-through queries in access, the only way I have found to pass parameters to the query is via the command object. I'm don't know of any other way to get the variables I've set up inside the VBA environment to my database server for processing. Did you create a generic stored procedure for running all database queries which accept a single parameter, that being the actual SQL string? I can't see any other way of doing this other than setting up a vast number of pass-through queries.

How do you go about working in DAO ODBC direct mode? Is this something you explicitly state when setting up the connection to the database?

Thanks again!

 

[ddiamond]

If you need to call a stored procedure, then you will need to use ADO's command object. If you instead dynamically build your queries in VBA, then you can use the ODBC-Direct connection. If you go to the queries tab, and create a pass-thru query, I'm not sure which technology is used, but the net result is the same - your sql will be passed directly to the backend database. Here is the function I use to connect to an odbc direct database.

Private Sub ConnectToODBC(db As DAO.Database, ws As DAO.Workspace, ByVal Connect As String, ByVal DBName As String)
  On Error GoTo ErrHandler
  Set ws = CreateWorkspace("ws" & DBName, "", "", dbUseODBC)
  Set db = ws.OpenDatabase(DBName, dbDriverComplete, False, Connect)
  db.QueryTimeout = 0
  Exit Sub
  
ErrHandler:
  MsgBox DBEngine.Errors(0)
  End
End Sub

 

[dabruins]

Thanks for the example of a connection via ODBCdirect ddiamond. I'm still not sure how I can filter at runtime via a pass-through query in this manner though. The pass-through queries seem to restrict the developer to statically defining the query text prior to calling it. I don't know what filtering is required until the parameters are provided by the user and handled in the VBA code at runtime. The only way I've found to get around it is to create a stored procedure that accepts input parameters. This is why I was looking more favourably upon ADO. I didn't know if DAO provided the same option (and I thought it meant I was using the JET DBE anyway). It seems that your saying that DAO (and DAO ODBCdirect) does not?

On an earlier point, in your use of bound forms was it simply a matter of restricting the number of records returned that allowed you to continue to use them. I can see where opening a table with 20,000 records in a bound form would kill performance and bog down the network. I believe you would have to maintain a DSN connection where you use bound forms as well? I was hoping to avoid the overhead of opening multiple connections to the database upon opening the frontend, connecting only when required when the user opens a specific form. As an aside were you able to utilize subforms efficiently in your database frontend environments?

Thanks agin for your help ddiamond. The haze is slowly lifting and I think I'm seeing the road forward.

 

[ddiamond]

Although I said that using linked tables may cause a performance problem, it has one distinct advantage over pass-thru queries. You can directly reference input fields from a form in your query that references linked tables. This way you avoid the whole parameter issue. And the performance may be acceptable. It all depends on how much the Jet decides to do itself and how much it decides to pass-thru to your backend database. But if you want to reference a stored procedure using parameters, I believe your only options are ADO and RDO. Of the 2, I recall that RDO was a little bit faster, but it's been so long since I've used RDO, I couldn't tell you how. And just like DAO, MS has stopped supporting RDO. Bill Gates really wants you to use ADO. Your bigest performance problem will be sending data over the wan, if you choice do go that route. That is why we had to start using citrix. If you decide to go web based using ASP, all of this is mute.

 

[Icecream7]

I was reading this post to find out more about the ADO and DAO and it made me wonder about a database I'm doing for someone. So far I haven't done any programming because it's a new database and I've just finished up entering in the information and creating the tables,queries, & forms. I won't be having as many people accessing and using the form as dabruins but they want to have access at 2 different locations, one location having only one computer accessing it and the other location having 2 computers. They also want to be able to make changes and save records. This is for a business that creates customer bids for the product and on completing the bid form wants it to generate a printed copy with the information added to it. I was going to create the program in VB but someone told me that Access would work better.

What I thought I would do is just put the database on my ftp and give them access to it so they can work with the database, save the completed forms and retrieve information from both locations. My question is do I need to set up the ADO and will an ftp work? I'm really new to all of this so I'm learning as I'm creating.

 

[ddiamond]

Icecream,
I don't think you have an issue with ADO vrs DAO. Either will work in your scenario. I'm little bit confused about how you plan to use ftp to distribute the databases and still keep them in sync. I've only use MS Access as a central database/front end. It sounds like you may be getting into the realm of database replication. I've never used database replication so I cannot give you advice on it. If anyone else has any thoughts, please chime in.

- Dan

 

[dabruins]

Sorry for the delay in response guys. Another urgent matter called me away. Thanks for your input.

I've been thinking about the performance issues over the WAN and ddiamond's suggestion to use Citrix. One of the access databases currently utilize has been split into back and front ends. The frontend has been copied onto the desktop of the various users. This was neccessary so that they could have change prvileges on some reports that required reordering data in it based upon the query fed to it at runtime. With citrix does the citrix server use a single version of the frontend that is shared with the users? If so this could be a problem for me. I'm not sure what rights the citrix user would have with the application it is sharing out.

I'm not sure how ftp works with access database either Icecream7? Maybe you are just referring to the location of the access database on your ftp site? My two main concerns were trying to determine what performance problems would arise from using bounds forms and maintaining the persisitent connections via DSN to the backend database. Secondly, if this proved troublesome then I had to figure out what is the best way to communicate with the backend, either ADO or DAO. As stated earlier it looks like they are trying to get everyone onto the ADO wagon but ddiamond does provide the DAO ODBCdirect option as an alternative. It appears either should work. DAO is just more attuned to the JET database engine which I'm trying to avoid using as it is not reliable in large user environments. This might not be of any concern to you though. I think your main concern would be limiting the amopunt of data that is passed in a query so that the application doesn't get bogged down reading from or writing to many records to large tables in a single opration. I would pay close attention to the queries you allow the user to perform and hopefully eliminating user frustration with long waits on executing queries to return records. Again I haven't been at this for very long either so I can't provide too much certainty on this.

Thanks again guys for the help.

 

[ddiamond]

With citrix does the citrix server use a single version of the frontend that is shared with the users?

I believe so. I am not a citrix expert, though. In what way are your users modifying the report at run-time? If the end user simply needs to customize certain aspects of the report based on criteria they enter, there are ways to do that without modifying the report at run-time.

 

[ddiamond]

Just a word of caution about citrix, it is a fairly expensive solution to the wan performance problem.

On the subject of Citrix - I've had a much easier time with Remote Desktop Connection (assuming the server with your db frontend has terminal services enabled).

Melagan, I've never used Remote Desktop. How does it compare to Citrix?

 

[evalesthy]

Several of our clients use a single front end on Citrix and it has proven reliable, reasonably fast, and easy to maintain. However, as ddiamond mentions it is fairly expensive (licenses, etc). Our clients use Citrix for other solutions so they did not implement it solely to accomodate MS Access.

 

[dabruins]

A couple of the reports I am generating contain information that requires the information contained within be ordered based upon the query that is presented to it at runtime. In this case it is invoicing information and every month the type of information contained varies and I was asked to make it develop it so that the person creating the invoice would have the option of ordering the invoice items to their liking. With my very limited knowledge of VBA and access reports I developed a form that allowed the user to manipulate the order of cost items for a customer and send them to the report where they would populate a number of text boxes in the required order. The recordsource for the text boxes would change for each report generated. Maybe there is a better way to go about this and avoid the problem?

I haven't yet looked into the cost but I'll certainly look into it now. As I said earleir we are using Remote Desktop already so maybe that might be an option.

Thanks again for your help!

 

[dabruins]

Getting back to an earlier point ddiamond. In the code you sent earlier that illustrated how to connect to ODBCdirect you used and error handler. I am working in Access 2003, using an Access 2002 frontend database to connect to a MySQL 5.0.18 backend database via MySql ODBC 3.51 Driver. I have some error handling in place that I have written to handle VBA errors. Usually I turn this off while I am debugging the code so I can jump to the line of code containing the error. In the case that the error originates with an ODBC call I don't get any indication of where the error occurred. If I turn on the error handling the handler has been coded to give me the ODBC error and error number but again I have no idea where the error is coming from.

Could you tell me how you set up a debugging environment to handle the ODBC errors and permit more effective debugging?

Thanks for your help.

 

[ddiamond]

dabruins,

In my error handler I put an end statement after diplaying the error message. I could have instead used a resume statement. That would bring you back to the line where the error originated after the error handler displays the message. Is that what you are looking for?

Private Sub ConnectToODBC(db As DAO.Database, ws As DAO.Workspace, ByVal Connect As String, ByVal DBName As String)
  On Error GoTo ErrHandler
  Set ws = CreateWorkspace("ws" & DBName, "", "", dbUseODBC)
  Set db = ws.OpenDatabase(DBName, dbDriverComplete, False, Connect)
  db.QueryTimeout = 0
  Exit Sub
  
ErrHandler:
  MsgBox DBEngine.Errors(0)
  [blue][b]resume[/b][/blue]
End Sub

If you do want to turn off error handling, you can enter commands in the immediate window to determine more details about the error that occured.

 

[dabruins]

Hi ddiamond.
Thanks for the code example but I still can't get the debugger to work the way I need it to. In the following sub I have nominated an error handler and it outputs the proper information in the message box ("91 Object variable or With block variable not set") and exits the sub. However if I comment out the error handler line "On Error GoTo Err_cmdGotoNext_Click" no message will be displayed and I am not alerted to any error. I do not get the offending line highlighted and the execution terminated. I don't want to continue execution if an error occurs. I want to be alerted to the error and then shown which line is causing the error. This code would be fine if the error occurred in the VBA environment but it seems to me that the ODBC call errors do not get thrown to the same error collection??


Private Sub cmdGotoNext_Click()
On Error GoTo Err_cmdGotoNext_Click

Dim rst As ADODB.Recordset
Dim conn As ADODB.Connection

Set rst = Me.Recordset
Set conn = rst.ActiveConnection
conn.Open
Debug.Print rst.RecordCount

rst.Requery
rst.MoveFirst

Do Until Me.id = rst.Fields("id")
rst.MoveNext
Loop

Dim intFlds As Integer
With rst
intFlds = rst.Fields.Count
Debug.Print intFlds
For i = 0 To intFlds - 1
Me.Controls(rst.Fields(i).Name) = rst.Fields(i).Value
Next i
.Close
End With

conn.Close

Set rst = Nothing
Set conn = Nothing

Exit_cmdGotoNext_Click:
Exit Sub

Err_cmdGotoNext_Click:
MsgBox Err.Number & " " & Err.description
Resume Exit_cmdGotoNext_Click
End Sub

 

[PHV]

When in VBE have a look at the General tab of the menu tools -> Options ...
Tick the Stop on unhandled error checkbox.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886