Access 15 and SQL Server

Tweeted this. Would love to see some "dashboard" reports for SQL Server status.

Since Access 2.0 and SQL Server 6.5, you can call me a veteran, using and combining both and since Access 2000 using adp's as front-end and SQL Server as back-end, pure due to performance and security and presently working for a large book-publising company in the Netherlands, where we are using several adp's with SQL for either production up to financial/sales reporting using the power of Access Reporting.

Further Access gives us many opportunities using VBA in forms and reports. Before responding to Access 15 I am dissapointed with Access 14 (presently beta) as this still using Access 2002-2003 adp, although we can program now directly on SQL Server 2008, we still don't see any improvements, neither does Access 14 gives us the extra's of using SQL Native Client and still have to use MDAC. When using a mdb or accb, SQL Native Client can be used, so why not in an adp, maybe Access 15 ? Now your points/questions; 1. Access is limited in database sizes and we have some with coming close to 1Tb, where SQL does toe job and Access.adp's used for in-/output. This, besides the point you mentioned are together main reason, while we further having other systems, such as AS400 from which we transfer data, using SSIS. 2. We are working for 99% using adp's (ade's) and stopped using mdb's (mde's), simly because of performance reasons and we have to install manually ODCB connection at the client's( which are around 500 now). 3. What I am missing in adp's is the scale-out of forms and reports (theme's) and web-based-forms, such as introduced in Access 14, but again, only in mdb's/acccb's. Further using SQL Native Client in the references instead of MDAC. 4. Difficult question, as up to so far SQL Server, specially 2008, does a great job. 5. What Features - simply using themes, web-based forms and get rid of the ugly grey forms, as long there is no rendering, such as Access 2007 did before the first servicepacks. In case you have any further questions or need information please feel free to contact me at; cemos@xs4all.nl (private) or cmonden@ndcvbk.nl (business). Best regards,

Cees Monden

Access/SQL Server Developer

Hi Clint, For Access.mde/accde/ade FrontEnd Distribution: Make StandAlone EXE - Compiler Like Thinstall! For Access FE-SQL Server: Make Link table technology without ODBC same as access FE-Access BE, also supports postgreSQL,MySQL,Oracle,Sybase,Firebird; passive-link table based like AlphaFive is also a good feature. For Access Native Controls: Make it like DevExpress For Access Report: Can we embed report on forms in Access 2010? not sure but a good feature. Support for SDI/MDI forms like VFP Forms Template With Attached Customizable Menu or Ribbon At tools Startup: options to hide Main Access Window Standalone Access Developer's Version/Release Thanks ERwin Leyes

Access DieHard

Hi Greg, 1)Definitely for performance and to avoid record locking issues that a file-server MDB encounters that are less common on SQL Server. Having a routine backup plan (that is more frequent than a file server in some companies) is another. 2)I stopped using ADP projects with Access 2003. It just didn't have enough value, and a few statements on Microsoft help sites that indicate ADP projects are no longer the recommended direction sealed the deal. Linked ODBC tables work perfectly fine, and you can still run stored procs from ADO if desired. 3) The Upsizing tool doesn't seem to work in 2007. I encountered errors trying to upsize to both SQL Server 2005 and 2008. I've been using the SSMA tool (v4.0) and it is terrific. I'd recommend just pointing people to use it instead of investing more duplicate work in the Access upsizing wizard. SSMA is easy to install and understand. 4) None come to mind. I think it will be natural, and desirable, to have some feature separation. You want information works to be able to create an Access database that uses SQL Server, but you don't want that information worker to have too much access to SQL Server functionality. 5) It would be great to be able to create a table or query in Access, and "promote" it individually to SQL Server as a table or view, respectively. Sort of a "Publish to SQL Server" akin to publish to sharepoint. A facility for local caching (with controllable refresh intervals) would be ideal. We have many customers that use our Dashboard Builder product with SQL Server, but they want fast access to a local snapshot copy of the data. Being able to incrementally bring down updated data to a table on a configurable interval would help make Access a better ad hoc reporting tool for SQL Server. Would love to collaborate with the Access team on this.

Q3: What existing features in Access, that are targeted at SQL Server, are falling short of your needs? A3:

- Implementing the Tablix feature in Access

- More powerful report designer based on SQL Server Reporting Services

- Zoom in / Zoom out functionality in form / report design view!

1. I can't think of a good reason to use mdb files in an application any more, except for standalone database work where the size or stability of the database isn't important. SQL server is now a tested and proven solution which scales easily when necessary. The only time I use the mdb solution is when using Access as a tool to eg convert data from one format to another.

2. I have invariably used the adp front-end, SQL Server back-end scenario in a number of bespoke applications. I never use an mdb linked to SQL Server. I can't see the advantage of having a local mdb to maintain as well as the SQL server database, and just using the adp makes for a cleaner solution.

3. I find the adp file format very workable in Access with few problems. It would be nice if the views could be updated so that features like in-line functions can be represented in diagrams, rather than having to work solely in the SQL pane. In an ideal world it would be good to have some of the functions of SQL Management Studio integrated, such as management of backups and security. I would also like a SQL Query Analyser area, where I can fire off SQL commands to the SQL back-end directly when required.

4.See 3.

5. I would echo a lot of the comments here, I would like the option to be able to produce an application from Access that is easily updateable and hides the main Access window. Having the application check for updates itself would be wonderful.

I will be watching this thread for ideas/feedback. Thanks.

Hello, for all types of Access applications it would be great if there was a better integration with .net. E.g. Integrate usercontrols written in DotNet. Not the COM-Way but onother one. Without to have to registrate the Controls as COM-Objects. Only with cut and paste. Better Collections in VBA compatible with e.g. a generic list in dotnet for to comunicate with those controls. I know you can enhance Access with dotnet and ActiveX-Controls. But you allways have to deploy those components and therefore you need administrive rights on the clients. I write applications for a very big company and there the normal user have no admin rights on there computers. Its very difficult to deploy ActiveX components.

All that came with Access out of the Box we don't have to deploy. E.g. GUI-elements Treeview, Lists and Grids.

Perhaps there is a way to implement dotnet components without to have them to register. That were great. Sorry for my bad english.

Hello, for all types of Access applications it would be great if there was a better integration with .net. E.g. Integrate usercontrols written in DotNet. Not the COM-Way but onother one. Without to have to registrate the Controls as COM-Objects. Only with cut and paste. Better Collections in VBA compatible with e.g. a generic list in dotnet for to comunicate with those controls. I know you can enhance Access with dotnet and ActiveX-Controls. But you allways have to deploy those components and therefore you need administrive rights on the clients. I write applications for a very big company and there the normal user have no admin rights on there computers. Its very difficult to deploy ActiveX components.

All that came with Access out of the Box we don't have to deploy. E.g. GUI-elements Treeview, Lists and Grids.

Perhaps there is a way to implement dotnet components without to have them to register. That were great. Sorry for my bad english.

I think Access can be an BI tool for SQL Server. I do not think Excel is the best BI tool for SQL Server. It is natural for Access to act as a data mart for the data warehouse in SQL Server Analysis Service.

We have a successful Access application (currently in Acc2007) that we distribute as runtime application to about 100 companies. Most of the users are novices at a computer and have little IT support, and so we have been very reluctant to move to SQL because:

1) Difficulty of deployment: setup of SQL server (even Express) is convoluted, fails frequently, and requires more expertise than our users have, particularly if needing to expose SQL to several computers in a workplace.

2) Difficulty of retrieving and viewing their data: when dealing with support issues, we can easily retrieve a user's accdb file and work with what their data to reproduce problems or help users to use the product. We have custom code to upload the BE database, but this would be more problematic with an SQL database. Features that would simplify and streamline such deployments (e.g. tools to install runtime app + setup and configure SQL Server Express for the most novice of users) would be most useful. We use ACCDB FE vs. ADP due to the limitations and major dev work invested in the application over the last 12 years. Access developer since 1.0 and still going strong...

The main reason for change is that we are told that Access is not suitable for a large number of simultaneous users and (less often) that the JET or ACE database will not hold data reliably without corruption. I would be more than happy with a tailored subset of SQL Server that works almost seamlessly with Access but without the above (perceived or otherwise) limitations. The point (raised above) is a good one, that it would be nice to be able to transfer individual tables to SQL Server rather than all tables at once. The specific difficulties I have are as follows: 1. The Latest Access database is always designed to be compatible with the previous version of SQL Server, not the current one 2. Upsizing by linking tables we are told is inefficient and using an ADP requires a high level of skill (eg using Passthrough queries) 3. Stored Procedures mean learning a new and unfamiliar language. 4. Datatypes are different and not always compatible (eg Dates & Money). 5. Query syntax is different 6. Having upsized a Database, the Access user has a hybrid which needs a large skill set to manipulate. 7. An SQL Server / Access Database is more difficult to deploy on a users system than a Linked Access Database.

Hi Greg, I'm delighted to read that you are already planning the next version of Access and soliciting suggestions at this early stage. 1. One of the reasons I upsize to SQL Server is because of the Access file size limitation. It would be great if this could be increased in Access 15. 2. I use linked SQL tables so that I can also use linked Access tables. 3. Pass-Through queries do not provide a way to get the return value from a stored procedure. 4. Table valued parameters: it is possible to use TVPs from Access with a Pass-Through query as Mary Chipman has demonstrated but I would like to be able accomplish this by passing a recordset. 5. I would like to be able to bind any kind of form to a disconnected updatable SQL Server recordset and pass changes back to the server. 5. I would like to be able to design a query in Access and publish it from Access into a SQL Server stored procedure and have that process understand how to convert any built in Access functions (IIF, Nz, Format, InStr, Mid, etc). Thanks for the opportunity to share these ideas. Patrick.

Few expectations from me too. 1) I understand Access 2003 ADP is a dedicated single user client. I have to distribute the changed file to client computers. It will be very useful if a single Access ADP file can be placed in a shared folder and the same is opened by multiple users. 2) In Access 2003, I can not open more than one connection to SQL Server simultaneously. My application requires accessing data from more than one SQL Server database simultaneously. 3) Creation of .ade has limitations and only small applications can be compiled. I have not been able to compile any of my current applications. 4) I can not override the connection string at application start-up. I can change it once the start-up is successful. I would like to start an adp application without any initial connection. 5) To have provision to separate code into a different file without the hassle of dlls. 6) I'll love to have MS Projects Gantt chart type control available for Access form or at least treeview and listview combined into one control. Ashok

Sorry to have two bites at the Cherry but a very good solution to the ACE / SQL Server incompatibility problems would be to develop LINQ (from .NET) to use in Access. The Query panes would produce LINQ statements (possibly as well as SQL Statements). Forms could be based on LINQ Statements and VBA would not need to surround SQL with quotes. A LINQ to ACE and LINQ to SQL Server and LINQ to any other database type would be automatically invoked when the command string was initiated and so when a form or report requests data the vLINQ statement would then be converted 'behind the scenes' to the appropriate version of SQL. This would allow a seamless transition for Access Front Ends which will be able to work with any backend database. It would also make queries less complex for the novice user and simplify transfer of the forms and reports to .NET.