Filtering the Navigation Pane

Kind of off topic (and originally posted in the wrong month, so I am sorry for the repost) But...: I ran into a problem in Access 2003 with a very complex inline IIF statement, (Access could would not allow more than 1024 characters in the design mode) Has this been changed? What about the QBE interface in general? Any changes? I read that the SQL text editor is pretty much still the same, and was wondering if the same holds true for the QBE interface. Oh, btw, what do you think about this: www.databaseadvisors.com/.../sqlexpress.htm "Microsoft Moving Away from ADPs in Access Kent Tegels quotes Mary Chipman (who wrote the book) in the SSXE newsgroup as saying that Microsoft are now recommending moving away from ADP based solutions. It looks like this may be an experiment that has not been completely successful which leaves those of us who have implemented these solutions with an interesting support problem going forward. Here is the quote: However, for new application development, ADPs aren't looking so promising, especially if you are thinking in the Yukon timeframe. A couple of problematical issues are complex data types and CLR assemblies. Tackling these head-on in the ADP UI graphical tools in the next version of Access is a daunting challenge, to say the least. ...and... FWIW, the Access team has moved away from recommending ADPs as a front-end to SQLS apps over the last year or so, based on several public talks given by team members at industry conferences. If you are contemplating new development with Access as a FE to a SQLS BE, you'll likely be ahead of the game with an efficiently-designed MDB/linked table solution rather than an ADP. " Thanks

This unsubstantiated rumor has been going around for a few years now. She has never revealed her sources, and the whole notion sounds dubious to me. ADPs are the premier development environment for Microsoft's flagship database, SQL Server. Sure, you can use an MDB, C++ or .NET, etc. but ADP beats them all, especially in RAD development. If you study how ODBC linked tables actually work in Access with SQL Server, you would want to stay far away from that suboptimal approach, if at all possible. (However, sometimes ODBC can be a lifesaver.)

Hi, I was wondering if there is an official newsgroup for Access 2007 beta questions - I was not sure if the existing Access newsgroups (Microsoft hosted) are appropriate for these types of questions. I am trying to figure out how to create an Access Database Project (ADP) in Access 2007, but not sure where to go for help. Sorry for this being off-topic, but I was not sure where to begin. Thanks

Nelson

Al, I have read this blog from the outset (including replies from MS staff - Erik/Clint) and have certainly formed the impression that ADPs should be avoided in the future. I don't have a specific reference for you as I don't have time to reread everything from the start, but you may wish to. -Cyrus

AL- RE:ADPs-- I am using SQS 2K5 and really miss using Access as a front end. Going to have to wait for Access 2K7 to come out before I can use ADPs again (assuming that the article I cited is somewhat off base. )

Ken—we haven’t changed how many characters can be stored in recordsource properties. I would look at moving your complex IIF statement into a VBA module. Seems to me it would be far easier to debug that way. Also, there haven’t been any substantial changes to the QBE designer. This is something we want to look at in vnext. We did make it possible for you to build complicated queries using the new filtering functionality and then flatten the filter into the query. This does make it easier to build queries. Nelson—you can create a new ADP or MDB database in the getting started screen by clicking on the builder button to the path property and selecting the appropriate file format. ADPs are interesting and important to many of our customers. We are committed to making sure they continue to work. You can download beta 2 today and use the ADP visual designers (Tables, Views, Stored Procedures, and Functions) with SQL Server 2005 objects just as you would have used Access 2003 and SQL Server 2000. The two weaknesses of ADPs that likely won’t be fixed because of the underlying architecture include the inability to link to external data sources (without setting up linked servers) and local queries. Combining adhoc heterogeneous data sources is a big part of Access functionality that is used heavily by most customers. The Access team is making a big investment in using the Access Dara Engine as the way to get to external data sources. I expect that to continue in the future. For new SQL Server projects where you are evaluating which platform to start from, we recommend MDBs or ACCDBs with linked tables because they offer more flexibility and that is likely where the Access team will invest the most when building new features. We have also found that applications that are upsized from mdb back-end tables to SQL Server link tables require far less application changes than upsizing to ADPs. The upsizing wizard defaults have been changed from upsizing to ADPs to move the data to SQL Server and replace local tables with link tables. In other words, the upsizing wizard will not default to ADPs.

clintc- Any chance of getting some posts using Access 12 as a FE for sqs 2k5? (linked tables or native sqs tables accessed via adp.) I registered for the beta program but never heard back from MS so I rely on this, and other blogs for all of my infomation. (I did eventually have to port my IFF statement to a VB function btw...)

The reason I switched to ADP is that it is trival to hack into any MDB file with a free password cracker, and get complete admin control over every single object (including tables) in the database. No thank you. I sure hope that is fixed in the new ACCDBs. It sounds from Clint that ADPs are going the way of the DAP. From his words, I would guess this is the last version with any new ADP features, and then ... roll over dead. I always hear that MDB and ODBC have terrible performance compared to ADP and OLEDB. Is that still true for the new version?

To quote: "The reason I switched to ADP is that it is trival to hack into any MDB file with a free password cracker, and get complete admin control over every single object (including tables) in the database. No thank you. " I agree with the sentiment expressed in this statement. I have brought this issue up previously and it was not answered directly. Will this be addressed in the new version? It would seem that this would NOT be a huge task. It boils down to having strong encryption in the "system.mdw" file. Without that, as pointed out, security in Access MDB's is practically useless. Doesn't it make sense to fix this??

Ken,

You should re-try downloading the beta. From what I understand everyone should be getting into the program www.microsoft.com/.../getthebeta.mspx. I will defer to Erik on when he plans on getting an ADP post together--I know there are some other cool topics in the que like data collection and SharePoint. Jake,

As mentioned in one of the first posts on security ACCDB files can be encrypted using one the many Office supported algorithms, bringing encryption in Access on par with the new encryption models for other Office applications. The default is RC4 cipher with 128 bit encryption. I will ask around about ODBC verses OLE-DB perf—our perf and engine guy is OOF for a couple weeks so it might be a while before I have more information on this topic.

Clint, Please correct any mistakes, but this is the info I have gleaned from the available sources and my own experiences: Encryption does not help if your user has admin priv. on the tables. He can just delete all the records for fun. Been there, done that. You have to roll your own security using a variety of tricks, and an MDB gives the extremely dangerous impression that it is secure. From your previous posts, it seems that ACCDBs have no built in security at all. So I fail to see the any advantage over ADPs, unless you need a quick fix for upsizing. Granted, that is useful at times, but it means you have to suffer through ODBC performance, at least as I understand it. Of course, you are completely correct about heterogeneous data sources, ad hoc querying, and quick & dirty apps. Nothing (I really mean that) can beat an MDB for those things, in my experience. And those types of apps probably represent 95% of the total apps out there, after all. But for a mission critical, secure, long-term, heavily-used, enterprise app - I have grave doubts about MDBs or ACCDBs - you have to prove to me (that means with DATA) that they are safe and efficient and do not bog down the server! There is a reason that .NET has native SQL Server and Oracle providers and does not use ODBC! Seems to me that ADP is the only game in town for secure SQL Server RAD development with enterprise level performance. ODBC is great for overloading your server with useless connections. Please correct me if I'm wrong on any of the facts...

I really think we need to nail down these ADP and security issues, so we don't wind up making huge development mistakes going forward. So I will also put in a plea: It's OK to focus on MDBs, just please do not let ADPs go the way of the DAP!!!!

clintc- THANKS! THANKS! The link you sent me was not the link I used originally! Will get it today! Thanks again! Ken

Initially, we don't think ODBC is much slower than OLE-DB but we need to get some numbers for you. It certianly is possible using ODBC to design a database that pulls down more records than are required. The Access engine attempts to remote as much of the query and where clause as it can but there are cases where that can't happen. If you are looking to port a Access application to SQL Server--SQL Profile is invaluable to understand what data is crossing the wire and where you need to move your local queries to SQL Server views. We will post more later on this topic and the general perf questions with ODBC verses OLEDB--like I said earlier our perf/engine guy is on vacation. Regarding the security question. "Encryption does not help if your user has admin priv. on the tables. He can just delete all the records for fun. " If you have a need for per item level security on tables we recommend you use SharePoint, SQL Server, or SQL Server Express. Servers are best at handling these scenarios in a safe and secure way. I don't see how ADPs buy you any better security than link tables to SQL Server. In both cases SQL Server is managing the security on the tables. ACCDB files are the only way to have a secure password on the database (forms and reports).

"I don't see how ADPs buy you any better security than link tables to SQL Server." Answer: You can manage security in MDBs using a trusted ODBC connection and user-specific permissions, but there are well-known fatal security flaws: Since the MDB password is so easily cracked, anyone that has delete/update priveleges on a table or view, now has carte blanche to do anything at all in the database table, even in an mde file. Just because I give a user delete or update rights on a table does not mean I want him to have complete control over that table! Also, I do not want him to have any control over database tables outside of my application. And, of course, it is not possible to apply application roles over ODBC connections. And of course, you should not use "SQL server security" to apply app-level security, as Microsoft repeatedly warns against it - the BOL and other docs repeatedly indicate that it provides suboptimal security, and often can be easily be cracked if you know the tricks. ADPs (actually ADEs), AFAIK, have no password to crack, so hackers have no access to the native tables, forms, or anything else, unless the programmer permits it. And you can apply application roles using trusted connections. In this case, users do not need any passwords, have access ONLY inside my app, and cannot access any part of the database outside of my app. If a user needs a different set of permissions, you can create a new application role and apply it to the specific user. This is a much more secure solution that the MDB/ACCDB approach. Perhaps the new ACCDB format solves these fatal security flaw, but I'll have to have a lot more info on the security features before I believe it. If an ACCDB allows unrestricted access to the native table objects, etc, it's basically stillborn for security purposes. Also, if you can connect to an ACCDB via VBA code in any way, or connect to it as a library database, and then get to the unsecured tables, it's also no good. If the new ACCDB format solves all these problems, and also has decent performance, and also allows me to apply application roles to connections, then I would take a second look. Otherwise, I'll just use them for quick and dirty disaposable projects that don't require much security. (Regarding performance and ODBC, my understanding is that JET/ODBC opens a new connection for each query, and very often winds up pulling data locally to do things that can't be translated into T-SQL. You need to use pass-throughs in many cases, and that eliminate the RAD benefits of Access. I suppose I could test this connection business myself, but I'm too busy at the moment. I'll wait for Mr. ODBC to return :-))

AL,

Hopefully we can clear up some misconceptions about how Access connects to SQL Server using ODBC. Maybe there were some limitations on the server in the past but using Access 2003 and SQL Server 2000 your scenario is possible. Since the MDB password is so easily cracked, anyone that has delete/update privileges on a table or view, now has carte blanche to do anything at all in the database table. There are three points here:

1. As mentioned before ACCDB file formats now support a much 128 bit encryption. I wouldn’t say they are easily cracked.

2. Whether you connect using ODBC or OLEDB the connection string is can be stored in the database. In mdb/accdb it could be stored in in a file DSN or as a property on the table (DSN Link Table). In an ACCDB the connection information is more secure if it is protected with a database password.

3. It isn’t a requirement to expose tables to your users when using ODBC. ODBC supports linking directly to views. Simply create a view and set permissions on that just that view. When you setup your ODBC connection in your front-end database link to the views you want available to your users. If an ACCDB allows unrestricted access to the native table objects, etc, it's basically stillborn for security purposes. This is strictly a design decision made by the SQL Server developer. There is nothing about MDBs or ACCDBs that require developers to expose tables. very often winds up pulling data locally to do things that can't be translated into T-SQL. The Access data engine makes its best attempt at pushing query and filter predicates to the server. You are correct in that there are cases where the query can’t be translated into t-sql. The RAD architecture of Access does make it easier for developers to pull lots of data from the server. It is important when you build Access applications to use SQL Profiler and review exactly what your application is doing. When you find more data traveling the wire than needed—reconsider the design and optimize for the scenario. This might mean changing the design for the form, expressions, or creating a view on the server that does the processing.