Why does app on user machine reset ADO to higher priority than DAO 2

[jlitondo]

I have an application that I developed using Access 2002 but is distributed to a couple of users who are running Access 2k on their machines. On the references priority list, I set DAO higher than ADO and this works fine through the initial run of the application from the user's machines. However the next time either of the users tries to run the program,an error is generated because the ADO reference is reset to a higher priority than the DAO.I then have to manually reset the priority list.I've also tried setting the references on their local ms access program. Anybody know why this reset is taking place? jlitondo@gatecitysteel.com

 

[ZylaWonder]

JLITONDO:

I have experienced the same situation on a database running only Access2000. I prioritize DAO over ADO. Everything works fine. Close the database and reopen it and now my code has errors because ADO is moved!!

 

[FancyPrairie]

I've read where, to avoid the problem, you should preference all of you declarations with either ADO or DAO. For example, instead of

Dim rst as Recordset

declare rst as

Dim rst as DAO.Recordset.

 

[jlitondo]

Your tip helped FancyPrairie, on my machine but still having same problem on other user machines. jlitondo@gatecitysteel.com

 

[cmmrfrds]

I use Access 2000, but the problem you describe is interesting in that it looks like Microsoft is trying to nudge developers in the direction of using ADO which they have recommended for the last couple of years. For curiousity sake, other than backward compatibility, why are you using DAO if this is new development? Are there things ADO cannot do?

 

[jlitondo]

got used to DAO and haven't really had much time to look into ADO but you're right, I'm planning on crossing over to ADO then upgrading all my users to access 2002. jlitondo@gatecitysteel.com

 

[Imhoteb]

Hi,

I want to make this remark.
There are things ADO has difficulties with.
I experienced difficulties when I tried to compact a database (access97). I managed to compact the catabase in code with ADO, but when I tried afterwards to open the database with access97 I got an error. (unrecognized database format). ADO compacted the database to an access 2000 database so I couldn't open it anymore in access.
I worked around this with DAO code and my problems were solved. So, be aware of what you want to do in ADO.

 

[cmmrfrds]

Thank you for your comments. If you are looking for a reference for ADO, I am really pleased with a book by Rob Macdonald called Serious ADO. I found there are a lot of processing problems that can be solved with a good understanding of ADO. One of the interesting capabilities is being able to build a disconnected or fabricated recordset in VBA code. This can replace problems that were solved with an Array. The fabricated recordset can be sorted, filtered, cloned, etc. I used it in one problem to avoid creating a temporary table in Access.

 

[ZylaWonder]

cmmrfrds: I have experienced things that are more difficult with ADO. In my applications I have created user interfaces that allow system admin to work with security so they are not directly dealing with "exposed" security features that come with ACCESS. I use ACCESS 2000. I have found that ADO does not expose the necessary objects to security that DAO does. After many hours spent and thinking I had to be the MOST STUPID person ever!! I went to the Knowledge Base only to find an article on DAO vs ADO in re: security features. There Microsoft admits that ADO has a way to go before it can do with security what DAO can. As a result I have spent most of my time DAO. I do notice that Microsoft is pushing ADO. I do not necessarily see the advantage.

 

[jlitondo]

ok guys what I determined is that if I compile my app from within access 2k then my users can run the program without a problem but as soon as I compile it from within access 2002 then my users start running into the same problem. Compiling the app from either one of the access versions resets the certain defaults to prioritise dao over ado if compiled in access 2k or vice versa for 2002 regardless of whether you manually set priority for these references. jlitondo@gatecitysteel.com

 

[cmmrfrds]

ZylaWonder, did you use the ADOX reference library to access the security objects? Simple example below.
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Set cg.ActiveConnection = CurrentProject.Connection
'add a table
tb.Name = "Test"
tb.Columns.Append "col1", adInteger
tb.Columns.Append "col2", adVarWChar, 50
Debug.Print "table = "; cg.Tables("Test&quot.Name
cg.Tables.Append tb
'--
'To access security
set cn = activeproject.connection
dim mymdw as string
mymdw = C:\program files\microsoft office\office\system.mdw
cn.provider = "Microsoft.Jet.OLEDB.4.0"
cn.properties("Jet OLEDB:System Database&quot = mymdw
''- mdw now available, example
cg.Users.Append "pete", "pete1"
cg.Users("pete&quot.Groups.Append "Users"
'-- now you should be able to manipulate all the security information in the system.mdw. Check out all the methods etc. in ADOX.

Chapter 10 in the book Serious ADO deals with security.

 

[ZylaWonder]

cmmrfrds:

Looks like a trip to Barnes & Noble this weekend. I have the Access 2000 Bible and my most used book is by Allison Balters, Mastering Microsoft Access Development. She uses alot of ADO in this book. I do use it for most things. But, I guess when we find something familiar, it is human nature not to want to move on. I have put alot of work into by code and especially keeping it clean. Therefore, I would like for EVERYTHING to be ADO. Thanks for the code and I will try oh try but once again! I hope this has helped others as well. I realize how much time it takes to reply to a thread with the level of response you have sent. Thanks again!

 

[gusbrunston]

Microsoft stated some months (years?) ago that there would be no further development of DAO. New applications should use ADO. Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[cmmrfrds]

You can paste the below code into a function and run it to get the users and groups from the system.mdw, it is a basic starting point. It also looks at permissions on 1 table for each user.

Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection
Dim ur As User, gp As Group
For Each ur In cg.Users
Debug.Print "users = "; ur.Name
'- 0 = no permissions, the -number is a bitmap for a set of enumerations
Debug.Print "permissions = "; ur.GetPermissions("IDTable", adPermObjTable)
Next
For Each gp In cg.Groups
Debug.Print "groups = "; gp.Name
Next