DAO 3.6 reference question (Access 2K Win 2K)

Topic

New Reply

Hello again

The company I work for has just upgraded to Access 2000, and I’ve been having ‘fun’ getting the Access 97 databases to work smoothly, still it’s been a learning experience .

The latest ‘lesson’ is that someone has to physically select the DAO 3.6 check box in the reference dialogue box before any VBA with a DAO.Database or DAO.RecordSet will run. My question is this, do I have to walk round every PC that will use my databases and check this box, or is there a way round this? Like making my databases into .MDE files?

This may sound like laziness, but as Access is installed on a ‘needs it’ basis there could be problems in future when a new user hasn’t got the DAO library sorted out and I’m trying to avoid this situation.

Thanks

Ian

Reply | Quote

Replies

Can you set the reference on the copy on your pc and then distribute your copy to everyone (through a batch file, maybe)?

That’s how I do it….

Cecilia

Reply | Quote

How are you distributing the databases and how did you convert them? Normally, if you convert a 97 database, you already have a DAO reference set. If you created a new 2000 database and imported the objects, then the reference will be to ADO, not DAO. In that case, you need to distributed databases with the reference already set. If you are distributing libraries or something like that, then you have a problem if your libraries are full of DAO code but the databases that use them only have the ADO reference set.

Reply | Quote

Charlotte

I don’t exactly ‘distribute’ the database, it’s split into a front end back end set-up on a server. In the past I’ve used this system and just arranged short cuts on the users PC to the front end (there is usually some one in each department that can set-up a short cut). The beauty of it is that when I make any modifications I only have to update the front end on the server.

The lack of ADO / DAO reference is probably due to the trouble I had getting the database to convert from 97 to 2000, the other two systems converted with very little hassle (some spurious code was ‘invented’ some how, but easily tracked down and removed). The database in question would’nt even allow me to open it, security problems, so the only way I could save the work was to import it all into a newly created Access 2000 database, I guess this is why there were no references to DAO set.

If this is ‘bad practice’ (having one copy on the server) let me know, please, as I’ve got three databases running in the company with the same set-up.

As for libraries full of DAO code, sorry, my library only has books in it….. I’m new to VBA (last few weeks) and only know what I’ve learnt from ‘the idiots guide’ and, more usefully, the very nice people on here like HansV and your good self.

Thanks for the repsonse

Ian

Reply | Quote

Ian,

If you have only one copy of each frontend on the server, it shouldn’t be too much work to set the reference to DAO 3.6 in each of these.

It’s often recommended to give each user his/her own copy of the frontend; this purportedly reduces the chance of corruption of the frontend. Multiple users opening the same form sometimes leads to problems.

However, at the company where I work, we have been using single .mdb frontends on the server in Access 97 successfully for more than 3 years now for several databases. After we built in code to prevent users from accidentally saving the design of forms, we have had only one or two cases of database corruption.

Reply | Quote

Hans

I’ve set all the forms to open with pop up and modal as yes, so the user doesn’t get the option to modify the form’s design (short cut menu removed as well). This has worked OK on the maintenance database I’ve had running for the last 2 years, but the database I’m currently working on will have significantly more users, so that’s why I was worried.

As far as the DAO / ADO reference is concerned, Is it set in the database (.mdb file) itself, rather than in the Access (Access.exe) application on each PC? if I understand this correctly (it’s in the .mdb file) then I’m sorted, any new user wont have to worry about the references for DAO 3.6 in their installation of Access. Other wise it sounds like I’m going to have to learn how to write batch files (not done since DOS 6….).

I’ll keep an eye on the new database once it’s fully implemented in case we do have any corruption problems, but as it’s fairly simple (compared to what some people on here are doing) I hope it will be robust.

Thanks for the help

Ian

Reply | Quote

Ian,

References are stored in the database (.mdb or .mde); different databases can have different sets of references.

So you need to set the reference to DAO 3.6 once in each frontend database; there is no need to do anything on the user’s computers (apart from installing Office 2000, of course )

Reply | Quote

Hans / Charlotte

Thanks. Looks like life is going to remain ‘simple’ and relatively lazy for me then (all that potential interaction with people had me worried )

Thanks again

Ian

Reply | Quote

There’s nothing wrong with converting a database by importing the objects, but you have to remember to set the DAO reference when converting from Access 97. If you do that in the database on the server, that’s all that is required. If the other users all have Access installed on their machines, the referenced library should be there for them and everyhting should work.

Reply | Quote

<>

With Access 2000, it gets to be a real pain. When we convert clients to it, we usually put the front-end on the workstation. The problem is with 2000 you cannot make design changes in forms, reports or code unless you have exclusive access to the database, which means you have to kick all the users out. A few times when that need arises may well convince you. In addition, there are a number of other reasons for doing so – take a look at post 175910 for more details.

Reply | Quote