Access 15 and SQL Server

Khuzema Kanore,

"Make the error reporting between and SQL Server and MS Access more robust. The dreaded 3146-ODBC Call failed is very limited and not useful." When you get such an error, do you check the Errors collection? You should find more information in there. Alan

Ashok stated "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." See the Auto FE Updater as it nicely handles the ADP/ADE files. And it's very convenient to distribute new FEs and related files.

Suggestion: add SQL support to Excel sheets:

- filtering

- use sql as function

- create new sheet from the result of a query

Thanks.

1- Auto FE Updater is absolutely essential for anyone using MS Access in more than one PC... Its features should have been "included" in MS Access! Thanks once again to Tony Towes for his free utility. 2- I see that this post of MS Access- SQL receives more comments (almost 50) than similar posts of MS Sharepoint. MS Access Webforms with link tables to SQL Express would look easier and less expensive than Sharepoint. Joao Santos

Yes, I do Dao.Errors(0)... etc. what I mean it should be more straight forward. It took me some time to get to this... Thanks

How about some native interface with Infopath- ie, export form as an infopath template to publish or email, so users don't have to connect directly to the Access Db to submit information.

A feature that feel much is the colorful intelisense and syntax of terms. And if possible, the same resource available in Zoom box of the expression builder.

PLEASE! Portuguese: Coloquem um editor de código decente no access por favor!

I am developing Access applications starting 15 Years ago with Access 2.0 1) I switched to a SQL Server backend for: - prevent MDB database corruption - better security - better performance ( over WAN/VPN connections )

2) I have never create my application as a ADP solution. I am still using linked SQL tables ( with the ability to relink them to the production, test, qa database )

3) Differences in SQL query syntax and data types

4) Working with stored procedures in an mdb is a bit clumsy. ( you have to create ADO connections ) 5) What new features would I like to see in Access: Seamless integration of tables: let me flag a table as local so that the data will be kept in the mdb/accdb near the user ( eg for small static lookup tables ) let me flag a table as cached, so that the data is on the SQL server but cached locally ( eg for seldom changing data ) let me flag a table as serverside, so that the data is stored on the SQL server Seamless integration of queries: let me flag a query if it is defined locally or as a view on the sql server Switching those connection between different servers ( production, test, qa ... ) More modern controls (Treeview, Graph, Grid, Trackbar .. ) maybe support for .NET controls (MessageQueue, BackgroundWorker ) Support for accessing Web-Services Upgrade the VBA IDE. Use VB.NET as programming language, but keep the speed of Access ( even VS2010 is sluggish to use )

In my organisation Access really does provide a vital role for individual work colleagues to do small high value repetitive tasks. One user uses it with the lad to coordinate a clothing grant applications. 2 people will never need to be bigger. I use one version for our sports people to give them a list of sites again between two lads. 2 people unlikely to get any bigger. I have a further application that records planning applications, has a CRM section and can record sites and link costs to each. This is a small very high value system again only two people really access this. Although potentially might wish to see it. I have a further CRM system that I am about to network potentially for 5 users. I have developed a system that can time athletics races. This uses access run on a laptop out of the back of a van quite often in the pouring rain in a field in the middle of nowhere. May be up to 2000 athletes takes a hexidecimal key from an electronics box and esstentially performs a pivot table on it. Maximum user at any one time - one till now off chance that might need to be 2. So what I'm saying is I think don't forget the small users. Everything seems to be about upscaling the thing. Fine but please no loss of the individual standalone nature that makes it superb. Saying that if you could just link to sql tables like you can link to access tables it would definitely increase the chance of migration to SQL or make it almost inevitable!!!

Realised I went a bit off topic there and forgot the original question Not sure but time formats in access seem a bit wonky ie subtraction over midnight. Not really experimented with SQL but imagine its probably got proper support. No 1 is probably.

Full easy linking to SQL Tables no ODBC required or anything!!!

Realised I went a bit off topic there and forgot the original question Not sure but time formats in access seem a bit wonky ie subtraction over midnight. Not really experimented with SQL but imagine its probably got proper support. No 1 is probably.

Full easy linking to SQL Tables no ODBC required or anything!!!

1. Our monthly databases have about 25 million records. We use SQL server because of size and for speed for our queries. 2. We use ADP's for the IT team and linked Access databases for the general users. We have a few users who are Access literate. We avoid having them us ADP's, because then we would need to teach them SQL, instead of using the Access query builders/wizards. 3. Pass through queries. They need to use SQL statements instead of Access functions. The most used one is the IIF statement. It needs to be converted to a case statement. So most end users avoid using them. If the query builder could convert IIF to Case before running, it would be great. 4. Query analyzer tools. Especially the indexing tool. 5. A query builder, like access has, where you can access SQL functions with help and autofill.

I support that access should be made the primary front-end development tool for SQL server as this will promote a tighter integration between the two. However, if this is unachievable then access queries should translate seemlessly into SQL server stored procesures and views when fired. This will boost performance to a great extent when using access as the front-end and SQL server as the backend. AND AGAIN THE ACCESS TEAM SHOULD TREAT ACCESS WITH SOME LEVEL OF RESPECT. ACCESS IS I DEVELOPMENT PLATFORM. SO IT IS SAD FOR END USERS OF APPLICATIONS DEVELOPED WITH ACCESS TO START SEEING RECONFIGURATION SCREENS ALL THE TIME WHEN TWO VERSIONS OF ACCESS ARE RUNNING ON THESAME MACHINE. ACCESS 2010 AND 2007 STILL CONFLICTS. MY CLIENTS ALWAYS EMBARASS ME WITH QUESTIONS ON HOW TO STOP THE CONFLICT.

Access is a brilliant tool for rapid development of database systems. The major issues (speed and stability) arise when you start adding many users and large amounts of data. So you find yourself using SQL as a more reliable backend. You then end up loosing the wonderful new features that have been added to Access 2007/2010.

So would it not be better to have an 'Access Server' product that still have these feature but without the size limitations and the corruption issues (not always the fault of Access may I add!). I realise this would be a little ambitious for Access 15 but maybe one for the future?