Comments

# Ken said on May 22, 2006 1:36 PM:

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:

http://www.databaseadvisors.com/gazette/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  

# AL said on May 24, 2006 12:56 AM:

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.)

# Nelson said on May 24, 2006 2:29 PM:

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

# CyrusB said on May 24, 2006 8:13 PM:

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

# Ken said on May 25, 2006 11:53 AM:

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. )

# clintc said on May 26, 2006 7:15 AM:

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.

# Ken said on May 30, 2006 10:29 AM:

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...)

# Jake said on May 30, 2006 2:37 PM:

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?  

# StepUP said on May 30, 2006 3:34 PM:

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??

# clintc said on May 30, 2006 8:22 PM:

Ken,
You should re-try downloading the beta. From what I understand everyone should be getting into the program http://www.microsoft.com/office/preview/beta/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.

# AL said on May 30, 2006 11:50 PM:

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!!!!  

# Ken said on May 31, 2006 10:55 AM:

clintc-

THANKS! THANKS!

The link you sent me was not the link I used originally!  Will get it today!

Thanks again!

Ken

# clintc said on May 31, 2006 2:25 PM:

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).

# AL said on May 31, 2006 11:11 PM:

"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 :-))

# Clint Covington said on June 1, 2006 8:39 PM:

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.

<quote> 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.</quote>

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.

<quote> If an ACCDB allows unrestricted access to the native table objects, etc, it's basically stillborn for security purposes.  </quote>

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.

<quote> very often winds up pulling data locally to do things that can't be translated into T-SQL.  </quote>

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.

# Brakerm19 said on June 3, 2006 12:24 PM:

<quote> 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. <quote>

Access Beta 2 12.0.4017.1003: Where is this builder button? Create New Blank Database just gives option of where to save this new accdb. Only other items on Getting Started Screen is Microsoft Office Online (templates mostly)

In Access Options there are only a three default format options 2000, 2002-2003, 2007 Beta. Online help gives no information on creating an ADP database project.

# Clint Covington said on June 3, 2006 12:57 PM:

<quote>Where is this builder button? </quote>

To the right of the File Path field there is a folder icon. Click on that and then change the file type dropdown.

# AL said on June 4, 2006 11:47 AM:

Clint,

I understand everthing that you are saying, yet I still have to disagree with you.  

Views are not the answer to security.  My applications take great care to restrict and validate the users changes to prevent accumulation of garbage.  If I create a view, then my user can still easily arbitrarily change or delete or vandalize all records in the underlying tables, using Excel or whatever to connect to the server.  If I embed a password for ODBC or use a DSN, I will have to use SQL Server Security, which is specifically NOT recommended by MS, because of its weak security - according to BOL.  And besides, I would then have to roll my own security inside of an Access ACCDB anyway.

Many security-conscious developers move to web apps specifically to avoid this situation, so that users will have absolutely no access to tables (or views) outside of the web app, while still allowing custom user-specific permissions.  The same level of security should be possible easily with Access, i.e. providing a simple method for user-specific security, with no access to tables/views outside of the app.  

Instead of hardening the Access client-based security in this release, the Access team has instead decided to delete it entirely from ACCDBs, forcing users to roll their own.  And even in this case, there is no ability to use application roles, because the old ODBC technology does not support them.  So therefore, ACCDBs make it harder, not easier, to enforce basic security.  

None of this really affects me personally anymore, because I did roll my own security system.  It was very difficult and very time consuming, and should NOT have been necessary if the Access team would pay more attention to security and security documentation.

Regarding the issue of SQL authentication (as opposed to trusted connections), BOL advises us not to use it, yet provides little reason as to why.  Various SQL Server websites provide ways in which this system can be hacked.  Perhaps you have more info about this, particularly regarding SQL Server 2005?  Is there a good reason that Access users should trust SQL authentication when BOL recommends otherwise?  Do you have a reason for your opinion that embedding passwords with SQL authentication really is acceptable?  

I realise that most Access users essentially ignore security and performance issues, but Access is also very widely used in sensitive fields such as health care.  It is extremely important that the Acess team provides us with the best possible tools and extensive security documentation to ensure the safety of our data.  So far, I have not seen anything from the Access team that makes me feel comfortable using ACCDBs or MDBs in a secure environment.   IMO, the only secure route with Access and SQL Server is an ADE that uses application roles with an encrypted password mechanism.  My reading of the MS BOL and other documentation leads me to believe that the ACCDB options you suggest are all suboptimal.  Of course, the use of an MDB/MDE in a secure environment is not even worth considering.

My bottom line is: there is no secure substitute for the ADP, so please keep it alive and growing.   Furthermore, there is nothing else on the market that even comes close to it for SQL Server RAD.  IMO, you should ENcourage its use, not DIScourage it.  

# Erik Rucker said on June 5, 2006 4:41 PM:

Hi Al, I'm Erik stepping in for Clint who's out this week.

Glad to hear you are so passionate about Access and that you use it so extensively.  We are as well, and agree that it is a great tool, both in MDB / ACCDB and ADP guises.  We're excited to see you and so many others excited about Access 2007.

Your points on security are well taken and give us a bunch to think about as we move forward on the next version of Access (after 12).  We're balancing several issues here including the availability Jet in the system, the inherent nature of a file-based client-side database application, and the relationship between Access & SQL.  The trick is to chart a path through this terain that is both secure and useable.  We're working hard on that in Access 12, and expect to be working on it going forward as well.

# Stevbe said on June 15, 2006 7:41 AM:

Back to the Navigation Pain for a moment ...

I must say I don't like it at all. If I expand the Tables group I can no longer see the other groups. I either have to scroll my life away or collapse tables and then expand the other group I want to work with. The application does not even need to be moderate in size before the extra clicking / scrolling becomes annoying. There is no way I am going to remember the name of every object in my apps so the Search functionality does not buy me anything and would be more keystrokes anyway.

Maybe if you left all the group labels at the top and displayed the contents below the groups instead of in between ... yea .. I think I could work with that :-)

# clintc said on June 17, 2006 12:14 AM:

>> I must say I don't like it at all. If I expand the Tables group I can no longer see the other groups.

Have you discovered the dropdown filtering mechanism? It should be really easy to filter down to just tables. Also, right click on the group headers allow you to expand and collapse all sections.

# Stevbe said on June 19, 2006 9:57 AM:

While I understand how to get the new UI to work it is more work that the previous version. I like to add Description properties to my objects which now show up as second line (square character at end of line?) which effectivly reduces by half the number of objects I can see at a time which increases the number / amount of scrolling I need to do in step 5.

New:
1. Click on tables group
2. Scroll to table
3. Right click and collapse all
4. Click on Forms group
5. Scroll 2 to form

Old:
1. Click on tables tab
2. Scroll to table
3. Click on Forms
4. Scroll to Form

New Comments to this post are disabled