ADP and workgroup files

[mmitchell]

Are workgroup files (.MDW) still used in an Access 2002 project (.ADP ) ?

I am converting an Access 97 .MDB version to to an Access 2002 project (.ADP) and the workgroup administrator is grayed out in the Tools-->Security section when I open my .ADP file.

If it is not, then how do I protect my code (and code from forms) from being seen when using a compiled .ADE?

 

[Pat Hartman]

You can't. I guess you have pointed out yet another reason to not use .adp's.

Are you converting for a reason? Do you understand what you are giving up? Are you aware that you can use SQL Server (or any other RDBMS for that matter) just fine with an .mdb/.mde front end? Are you aware that the .adp FORCES you to use SQL server? That's why Microsoft wants you to switch. Once you do, they have you!

 

[mmitchell]

One of the main reason is for data access and manipulation speed. From what I have read, it is the faster way to work with the data on SQL Server.

Yes, I don't like giving up local tables!

One thing I don't understand yet, is why, when I create someting or even look at something in code, like a button, I get the login screen?

If there is no workgroup file, how does the new ADP know about my logins?

Might it be because I dragged and dropped some of my forms from the old MDB into the ADP?

 

[Pat Hartman]

From what I have read, it is the faster way to work with the data on SQL Server.

Yes it is but that doesn't mean that you must convert ot an .adp. You can acheive the gains by using an .mdb if you understand a little of what happens behind the scenes. Just using an .adp will not give you any performance improvement and in fact you app may actually be slower. You really need to change the way you specify RecordSources for forms and reports in order to realize any performance improvement.

 

[SQL_Hell]

You can't. I guess you have pointed out yet another reason to not use .adp's.

There is an option to create an ADE file which is completely secure. ADPs should not be distributed to users.


{Quote] One thing I don't understand yet, is why, when I create someting or even look at something in code, like a button, I get the login screen?

I had this problem, I think it is better to upsize a completely unsecured version of your database, this way Access doesn't get confused.


Pat I have asked this before, but I simply cannot understand your problem with Access project? why is it you are so against this? what problems have you had?

 

[Pat Hartman]

The .adp is a not so subtle ploy by Microsoft to force Access users to switch to SQL Server. With an .mdb you have a choice of back end databases. You can use Jet, Oracle, DB2, Sybase, MySQL, and SQL server as well as any other RDBMS with an available ODBC driver. With an .adp you do not have a choice. You are restricted to SQL server and there are situations where you can't even link Jet tables to your .adp!!!!@@ This is not a huge problem in a corporate environment where they are likely to already have SQL server installed and also have DBA's to tune the database and make sure it is properly backed up. But, it will limit the use of Access in any corporate environment where they do not use SQL server. Most of my clients use Oracle so they will no longer be able to develop Access applications when Microsoft drops support for Jet. That is a big loss for everyone.

Also, the vast majority of Access databases are used by small companies who do not have professional IT people to support their databases. They should not be forced into an environment that they don't understand and can't support.

I belive that unless there is a groundswell of grass roots complaints, Microsoft will just continue on their merry way killing off their premier desktop database. Just as Access replaced dBase almost overnight, Access will be replaced by whoever has the forsight to see what is happening and take advantage of it.

 

[SQL_Hell]

I really don't agree with this Pat, I think saying Microsoft are forcing people to use SQL Server through Access Project is wrong, Access Project is just designed for use with Sql server, for once Microsoft have built in cross compatability.

The reason Access project only works with SQL server is because it incorperates functionality like the Stored procedure designer, you also mention that that most companies that use Access databases do not have professioanl IT people but if their Database has grown beyond the scope of Access then they need to employ a DBA regardless. If they don't employ a DBA and they are only aware of the SQL server route, then surely thats their fault not Microsofts.

I have built and overseen a few Access project front ends and I have only really seen a couple of bugs that are easily sorted out once you know how, but to get a .MDB to work as a front end for SQL server must be lots more hassle to set up, for example how do pass parameters to stored procedures in a mdb?

 

[Pat Hartman]

Almost all of my databases have non-Jet back ends. Mostly Oracle but some DB2, SQL Server, and Sybase. They are 0 problem to set up with linked tables.

As long as you don't do anything to prevent it, Jet sends each query in its entirety to the be server for processing and returns only the selected rows. So as long as your forms and reports are bound to queries with selection criteria, there is no excess data movement. There is slightly more overhead when using Access queries than stored procedures for the obvious reason that the stored procedure is bound when it is saved and the Access query passed through by Jet is dynamic. This is similar to the situation of stored querydefs (which are bound when they are saved) and SQL strings in code or as RecordSources to access Jet tables. The SQL strings are dynamic and therefore require extra processing at runtime that has already been done for the querydefs.

Using stored querydefs rather than SQL strings even saves some processing time when the linked tables are ODBC. This is because Jet determines what it needs to do with the query at the time it is saved rather than each time it runs. So, Jet knows ahead of time if it can pass off the whole query or if some part of the processing needs to be done locally.

You use a pass-through query to pass parameters to a stored procedure.