Access 15 and SQL Server

re: Access 15 and SQL Server

Howard Johnson — Tue, 29 Dec 2009 22:29:14 GMT

Thanks for asking for Access needs/features. I've been using Access for many years (at least 10) and consider myself an advanced user. Access 2007 is my third version. Here is what I need from Access: 0) Make it crash less. 1) Make the help work again, especially the vba help. It worked great in Access 2003. Can't hardly get it to work in 2007. I have almost given up on Access for this reason alone. 2) Please make the user interface less clunky. 2003 worked even if it's feature set was less. I think the 2007 user interface stinks. I hate the ribbon. It's just hard to use. Just give me a good old fashioned pull down menu at the top, with optional pallets to choose context sensitive things from. 3) Make using Access as a front end to MySQL much, much better and faster. 4) Add more SQL features to make it create and drop tables etc. 5) Make the help work... please. 9) Make it faster. PLEEASE. Someone go through the code and find places to do the same thing in less time. Make it load faster. Make it load windows faster. Make it faster! While some things in 2007 work better, in my opinion, Access 2007 was the low spot in the Access family. It is slow, clunky, hard to use and believe me there are many days I would rather have Access 2003 back. Thanks for asking. -- Howard

re: Access 15 and SQL Server

AlexDybenko — Mon, 21 Dec 2009 14:03:58 GMT

Hi Andrew,

suggest to look at mdb (accdb) + linked tables to sql server - then you have all fearues of access. You only loose new field types and datamacros, which we already have as TSQL on sql server

re: Access 15 and SQL Server

Andrew — Mon, 21 Dec 2009 13:33:28 GMT

grovelli...I probably phrased that incorrectly whilst rushing this morning. Things like 'Attachment' fields, embeded macros, multi value fields and navigation forms (2010) etc are lost when using ADP's. All these little tweeks were surely put there to make life easier for us. It's just a shame they cannot be used in every situation.

('Wonderful new features' is probably a case of 'creative writing').

re: Access 15 and SQL Server

grovelli — Mon, 21 Dec 2009 09:21:32 GMT

Hi Andrew, what are the wonderful new features that have been added to Access 2007/2010 which would get lost by using an SQL Server backend?

re: Access 15 and SQL Server

Andrew — Mon, 21 Dec 2009 08:48:52 GMT

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?

re: Access 15 and SQL Server

Daniel Isoje — Wed, 16 Dec 2009 15:19:33 GMT

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.

re: Access 15 and SQL Server

John — Tue, 15 Dec 2009 21:49:06 GMT

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.

re: Access 15 and SQL Server

Mark — Mon, 14 Dec 2009 23:30:38 GMT

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

re: Access 15 and SQL Server

Mark — Mon, 14 Dec 2009 23:30:36 GMT

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

re: Access 15 and SQL Server

Mark — Mon, 14 Dec 2009 23:23:38 GMT

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