SQL Server as Default BE to Access FE (1 Viewer)

[WSplawn]

I am beginning to wonder if I should not use SQL Server as the back-end to my database applications by default. I am a Access developer in the Sacramento region. If I can truly use an Access FE with a SQL Server BE in much the same way I would an Access BE then I think this is a good idea. As I remember it, there are a few catches, a few things where things are different with a SQL Server BE, but not many. Is there a white paper on this somewhere?

I am not interested in using all the bells and whistles SQL Server has to offer except in really unusual circumstances. For example I see no real need for stored procedures most of the time. I think for most of what I do queries written and devised in the way I am used to will normally suffice. I seem to remember that DAO does not work as well with SQL Server as the back-end, though I am not clear on the catches and how significant they may or may not be. If the work around is to use ADO, well that's not too big a problem. I realize you can start an application using Access as the BE and then switch or up-size to SQL Server but if there are catches, it seems more logical to find them out early by using SQL Server from the git go rather than to try to second guess yourself later.

In short, I can think of no good reason not to start all future applications with SQL Server as the back-end, except that it is not as portable as Access. However, it is very seldom that I send a single Access .accdb to someone else any way. My apps are almost always split. As I remember it, backing up an SQL Server db is not easy like it is in Access, though I seem to remember something being said about easier backups are available or coming in 2014.

I've said a lot, so I will stop. Don't mean to put anyone to sleep.<grin> But I would appreciate feedback from any who are or have been in a similar position. If you've made the switch to SS as your standard back-end I would like to know how that's going for you, and if you like it, why?

Thank you.

 

[pbaldy]

I couldn't make it down to the meeting Tuesday, hope it was informative. Leigh has a lot of info here:

http://www.utteraccess.com/forum/Beginning-Sql-Server-Deve-t1732935.html

Bill has a few thoughts here, about half way down:

http://www.thatlldoit.com/Pages/howtosarticles.aspx

I use SQL Server most of the time. Backing it up isn't difficult, but certainly more so than just copying a file. The latest version I've worked with is 2008, and in the Management Studio you can simply right-click on a db to back it up. More commonly you set up a job/maintenance plan that does it automatically on a schedule (including the transaction log). One nice thing is that SQL Server will back up the db while users are working. The files created are xxx.bak and xxx.trn for the database and transaction log respectively, which I then back up with normal backup software.

DAO works fine against linked tables. ADO is supposedly better optimized, but I never saw a difference.

Because I have it available, I've only used the full versions of SQL Server, never the free express version. Not sure of the differences and how they might affect this discussion. I'm nowhere near a qualified SQL Server dba, I just figure things out as I need them.

 

[WSplawn]

Hi Paul,

Hi Paul,

Your input is very much appreciated and I will check into the references soon. I have a book I purchased a long time back called From Access to SQL Server by Russell Sinclair. The copyright date is 2000. I wonder if there is anything else like this that is out there that may be newer? On the other hand, much of what he says in the book I imagine still applies.

Best Regards

Woody

 

[pbaldy]

Could be. I've got the Microsoft Access Developer's Guide to SQL Server by Chipman & Baron, but it is from a similar era (based on Access 2000 & SQL Server 2000). There are certainly new features of both products not covered by either of our books, but I haven't looked around for more current books. As you say, many of the concepts are still relevant.

 

[pbaldy]

I couldn't make it down to the meeting Tuesday, hope it was informative.

You must think I'm dippy (you wouldn't necessarily be wrong). I was thinking the Sacto meeting was last week, now I see on my calendar it's actually tomorrow night. Still doesn't look good for driving down though.

 

[Khalid_Afridi]

Why i can not create new thread???????

 

[Rx_]

RE: DAO works fine against linked tables. ADO is supposedly better optimized, but I never saw a difference.

Agreed! I used linked tables and DAO.
Not apposed to ADO, just can't see any net gain. And, since I have much more DAO experience, use it.
Of course, some of the old DAO for replication and security were dropped and wouldn't need to be used if SQL Server became the BE.

My preference is to create new local table in Access, then to move them to SQL Server after testing them. The free tool SQL Server Migration Assistant for Access is great for that. My personal preference is to use DSN-Less linked tables with code using the free SQL Server Native Client (that uses ODBC). It is also free.

One other small thing that won't work on linked tables.
For testing, I use DAO to clear a table and re-set the Autocounter field.
That won't work on Linked tables.
Example:
Public Sub R_EvaluationDeleteAndResetAutoCounter()
10 DoCmd.SetWarnings False
20 DoCmd.RunSQL ("Delete * From R_Evaluation")
30 Call ChangeSeedAutocounter("R_Evaluation", "ID_R_Evaluation", 1, 1) ' warning this will not work on linked tables
40 DoEvents
50 DoCmd.SetWarnings True
End Sub

So, there are a few things to consider. But, for the most part, the new ODBC translates SQL Statements into very efficient TSQL. So long as MS Access or custom functions are not involved.