Access & SQL Server (1 Viewer)

[JohnPapa]

I am considering using SQL Server for some of my applications that are at present in Access 03. I have downloaded SQL Server Express 8 and have linked an empty Access db via ODBC to a couple of tables that I created locally on SQL Server.

I just wanted to ask whether it is worth investing time in using Access + SQL Server, or whether I should move to a different FE environment? If the answer is yes can you please suggest some guidelines. If the answer is no, can you please suggest an alternative FE. Thanks.

BTW, in linking the Access db to the SQL Server via ODBC I was able to create a form based on one of the tables. I am able to view the table records but I cannot add records, even though the form settings are set to enable additions.

 

[tehNellie]

This is one of those unhelpful answers in that it largely Depends (on what you want to do).

Access is (or can be at least) a very good front end to SQL Server. It's relatively easy to set up, especially if you're already used to Access. Is very flexible in terms of how you manipulate data and done well you get the best of both worlds.

The biggest question is probably "Can you do what you need to do using Access as a front end?"

The second question might be "If we can migrate the existing Access app to SQL server for near zero development cost, should we stick with it for a few months and see if it does the job while we gather requirements to justify spending lots of money on developers?"

While you can do it this way, just linking Access to the tables in SQL server and carrying on as it worked before arguably gives you the worst of both worlds as all your processing is still being done by Access rather than taking advantage of SQL Server and Access is "locked" into your table structure restricting what you can do in SQL Server. As part of an ongoing migration of an existing, working application it might be acceptable in the short term, but you really should give some consideration to what you want from SQL server and stop viewing Access as "the database" in this context, it's now "the Application" and on that basis I always try to keep the application(s) out of my SQL Server Tables.

You don't really give any context to the organisation (size) that you're in or what the purpose of the database is. If it's a hack and slash project for your own enjoyment and learning then nothing to stop you getting a copy of Visual Studio Express which will let you explore the world of .NET and there are plenty of Java, .PHP and other IDEs around that you can experiment with.

Stepping up from VBA into a more modern development language/environment can be a pretty steep learning curve, even if you pick what you might consider to be a related language (VB.NET) so if you are considering dropping Access in favour of developing your own bespoke front end I'd give serious consideration to who is actually going to do that development.

 

[JohnPapa]

Many thanks for your reply which was very helpful.

Ideally I would like to continue using Access with SQL, due to the big investment I have made with Access.

A typical project would be to have the dental software www.VisualDentist.com be rewritten so that it is accessible over the Internet by about 30-40 concurrent users. The current Access/Jet setup will face multiuser and multi site if it tries to handle more than say 10 concurrent users in a single geographical location.

There are also security (HIPAA) issues.

Do you think it is advisable to stick to Access/SQL for a similar software package which will serve 3-4 sites and 30-40 concurrent users with connection over the Internet?

Could Citrix help in any way for this setup?

Thanks,
John

 

[tehNellie]

The short answer is No, I wouldn't use a distributed Client at all for an app like this even if those 3-4 sites were all the same company, I'd want the business logic on my servers where I control what's going on especially on a system holding and manipulating patient data (that's my responsibility). i.e. a browser based interface. Not just for security but to remove the headaches inherent in making sure that people are using the latest versions and so on.

This isn't really my area of expertise anymore so call it personal opinion but once you put a client application out "there" it's no longer under your control but I used to work in IT security (not in this direct area) so call me paranoid

 

[JohnPapa]

Point taken. Many thanks.

 

[bparkinson]

Since you have HIPAA rules to contend with, using access as your BE DB engine is a poor choice, IMO.

for your 3-4 sites and 30 users, a hosted SQL Server database in the cloud with an Access front end will work fine. I'm doing that with my app with users on several continents and it's all great.

The big thing is to exploit SQL Server's advantages - security, obviously, and server-side processing by using stored procedures, views, triggers (if you are philosophically inclined that way) scheduled jobs, SQL Server Integrations Services (SSIS) SQL Server Reporting Services (SSRS), and so on. It's an enormously richer development environment.

 

[spikepl]

@bparkinson

Would you happen to have any good non-expert-human-readable links about how to get into a setup like you are describing? I.e. Cloud BE and Access FE?

 

[bparkinson]

Maybe I can start by just describing the steps I took?

First, download SQL Server express.

Find a company to host your SQL Server database. Price will vary depending on things like whether you are on a shared server or a dedicated server, number of databases you need, etc. I use DatabaseMart LLC. If you are on a shared server and want your tables you create there to be in the dbo schema, adjust your user account to use dbo instead of the user-named schema. I pay US$7.00/month for a shared server. Eventually we will move to full bore dedicated servers as clients come in.

Create a new empty SQL Server database at your new host. Either create your database design in that DB using SQL Server Management Studio (your new best friend forever), or use the upsizing wizard to migrate your existing Access DB to your new SQL Server DB.

You want your SQL Server tables LINKED in your Access DB. They will then be available in your Access app as if they were local tables. When you create new tables in SQL Server, you will need to link them to your Access FE. It is not automatic as it was in (deprecated) ADPs.

Buy Ken Henderson's book "Guru's Guide to Transact SQL". Commence re-architecting your app, moving all data intensive code from Access to SQL Server. This will require learning ADO if you don't know it.

I came to Access a year ago after years as a SQL Server DBA/VB6 guy. Learning Access was infinitely easier than learning SQL Server. If you aren't a SQL Server DBA, expect a steep learning curve, but it is well worth it. If you go this way, feel free to ask me anything. It took me about three months to be safe and productive in SQL Server. There's a lot to learn.

 

[JohnPapa]

bparkinson, many thanks for your input. I have been using Access since Access 2 (early 90's) and would hate to give it up. I will follow your advice and will get the book you mention.

Can you recemmend a book that deals with the Access + SQL Server combo?

I will be in touch if you do not mind.

John

 

[pbaldy]

I like the Microsoft Access Developers Guide to SQL Server by Chipman and Baron. It's a couple of versions out of date, but most of the concepts and techniques are still relevant.

 

[JohnPapa]

Many thanks Paul,

Will have a look a the book you mention.

The book has very good reviews, you are right it is a couple of editions back. Big questionmark?

 

[pbaldy]

There could certainly be other books as good or better and more up to date. Hopefully somebody will post about them so we can all learn. I can only say that I keep that book on my credenza at work, and probably still open it up once a week or so (and I got it when it was current). Of course I'm old and set in my ways, so old books fit right in.

 

[pbaldy]

By the way, in answer to an earlier question, Citrix is certainly an option. I basically work in two cities (both using Access/SQL Server). In one, I have remote users that connect via VPN and run a couple of applications. The FE is on their desktop. The performance is very good (I wrote it with that environment in mind). In the other we use Citrix server, and of course the performance is good since the actual running of the applications are within the LAN, regardless of the location of the user. It makes design less a factor.

 

[bparkinson]

John,

You can contact me directly at bparkinson@gmail.com if you wish. I'm happy to help however I can.

Any of the great big Access books will have chapter on using Access as a FE to SQL Server. I'm also a big fan of William Vaughn, from whom I learned how to use VB6 against SQL Server in his popular Hitchikers Guide to..."

 

[JohnPapa]

There is also the question of ADP Vs MDB as a FE. I understand that with ADP you cannot have local tables. Is there any clear advantage in using one instead of the other?
Thanks

I guess one disadvantage of ADP is that it only connects to SQL Server (in case you want to simultaneously connect to other databases)

 

[bparkinson]

There is also the question of ADP Vs MDB as a FE. I understand that with ADP you cannot have local tables. Is there any clear advantage in using one instead of the other?
Thanks

I guess one disadvantage of ADP is that it only connects to SQL Server (in case you want to simultaneously connect to other databases)

I believe ADP's are deprecated.

 

[JohnPapa]

In other words avoid ADPs? There seem some staunch supporters of ADPs on the Web.
Thanks, John

 

[pbaldy]

ADP's are deprecated, MS is not pushing them like it did, and they don't appear to be furthering their development of the platform. That said, there are people I respect a great deal that use them. I don't personally use them, but I wouldn't call it a mistake if you decided they were the best fit for your situation.

 

[Angelflower]

I like the Microsoft Access Developers Guide to SQL Server by Chipman and Baron. It's a couple of versions out of date, but most of the concepts and techniques are still relevant.

I bought this book over the weekend and already finding it very useful... even if it is outdated...

 

[VilaRestal]

I've been working on an adp FE to a SQL Server BE for a while now. Here's my take on it:
The one slight advantage to ADPs that I can see is that they don't use an external ODBC connection, which could be used by other 'applications' without you wanting them to: Anyone can create an access database and connect to that DSN and link the tables and bypass your FE.
Certainly, the connection details can be read out of an ADP but it's a little less obvious and with some clever code it could be made harder (having no connection at startup, setting it after startup and disconnecting again before close - although if it crashes or it's killed then the connection info will remain in it).

Either way, that is the main problem with an Access FE - the security: the connection string to the Server will always be accessible one way or another and thus the FE can be bypassed and the tables accessed directly (unless there's some clever code on the FE and the Server that will disconnect a connection if it doesn't do something very specific - i.e. execute an SP with a particular password - very soon after connecting). On top of that it's some work to make sure a user can never get into the Access Options and change settings.

The second biggest problem is it's always a little bit unprofessional and hiding Access quirks from end users to keep up a professional appearance is very tricky.

For best results, especially with regards to security and professional appearance then a better FE is needed. The downside is it's a lot more work. Even all the work it takes to lock down an Access FE as best you can and make it look as professional as possible (capture and replace all Access error messages for instance) is less work than building the same thing in VB.NET.

If it's going to be a web interface and therefore accessible to all the hackers in the world then Access is not nearly secure enough imo.

Still, ADPs seem a little bit more secure than odbc connected DBs to me because of the internalized connection settings, which is also useful for deployment - you just have to deploy the adp, not a file DSN too or setting up a Machine DSN on each client. (Having said that, having an external DSN that can be edited easily has its own advantages when deploying to environments where the connection string needs to change.)

But as Nellie said, whether to use access depends on a lot of factors, but I think security is the biggest and most deciding factor. If that's not so much a concern (and a slightly less than perfect finish is ok) then Access is a good FE and because ADPs are deprecated you're better off choosing the ODBC method.