- Local time
- Today, 10:45
- Joined
- Feb 19, 2002
- Messages
- 43,457
I disagree. The book is woefully out of date and it barely considered the linked tables solution. It assums tha the ADP is the "only" way to go which is definately not correct. At this point Microsoft has deprecated the ADP because no one used it. It was different enough from standard Access so that a mindset change was required and it limited you to SQL server. Think about it! An Access application that couldn't even use Access (Jet) tables!!!
I skimmed through the thread and disn't notice that anyone answered the original question which was why couldn't you update the table. The answer is most likely that the SQL Server table did not have a primary key defined. Access requires a primary key for ODBC tables to be updated. Think about it - Access is not directly in contact with the data as it is with native Jet/ACE tables. All its communication is done via messages. How could it request an update to a row if it couldn't uniquely identify the row?
As to whether or not you should ditch Access in favor of some other platform - not unless you really want to. I have an application that is sold to the public (well not exactly) and the client has a choice of an ACE version or a SQL Server version for data storage. The FE is actually a single FE that works with either ACE or SQL Server linked tables. The app includes a database linking form that simply deletes the ACE links and replaces them with SQL Server or vice versa. That is the power of Access!!!! Try that with a VB.Net winforms app or a web app. So no, my heart belongs to Access. I fell in love with Access in the early 90's when I discovered that I could get to my company's DB2 data on the mainframe and I could rid myself of my COBOL/CICS shackles. I was hooked and nothing has changed my mind. I have dabbled with other environments and as far as I am concerned, they are way too much work. One thing that I like these days is the ability to link to Azure (limited SQL Server) in the cloud. This gives you lots of flexibility with apps that need to be shared among sites that do not share an intranet. I do not recommend the Access "web" database. It is way too expensive because of the environment it runs it (the most expensive version of Sharepoint only) and way too limited. You cannot convert an existing application because Microsoft elected to go with macros rather than code so none of your VBA can be used. You would need to rewrite it all as macros and macros are limited so you may not even be able to implement all of it. Also the event model is limited, form layout is more restrictive, and then there's the problem of being stuck with sharepoint lists instead of a real relational database. I predict this "feature" will go the way of the ADP so unless the next version of Access supports code and a more flexible environment and supports conversion of existing apps, it will die sooner rather than later.
The only application that Access is not suitable for is one that requires outsite, stranger access. For this you need a web app because you can't install Access on these visitor computers and Access is still required to run the app.
With A2007 and A2010, the Access runtime engine is free which makes distributing Access applications much more feasible. Many people balked at the requirement to purchase Access or for developers to purchase the development environment. Now the development environment is "free" and so you can distribute Access apps packaged for the public even if they don't own Access.
As to distributing Access with an SQL Server back end - our installation does not install SQL Server or the actual database (if does install the ACE BE if that is the option the client chooses). We require the client to do that himself. We send a .bak file that he loads and then configures security to meet his corporate needs. When the application first opens, it asks to be connected to it's database. The client chooses ACE or SQL Server, specifies the location and name and off it goes.
Last edited:
