Conversion to SQL Server backend

[Emily Jones]

Dear All

I've got a MS Access application. Split FE/BE. 9 user LAN.

I'm considering moving to a server backend. Probably SQL Server, with an
Access FE still. Because:

1. We've been getting many 'disk or network error' messages. Emphasising
what people have said - that Access is sensitive to network instability. The
BE hasn't corrupted yet, but I'd rather not wait for that to happen.

2. There are new data requirements, so there will be a bit of re-structuring
anyway.

3. I like the idea of referential integrity being enforced by the db engine.
And not all business rules can be enforced by Jet.

4. I want recoverability. The db is backed up to tape daily, but I still
don't want the users to lose a days work.

5. It would be useful for some of the data to be accessible from a web
interface, so an ASP application is a strong future possibility.

I've got Chipman and Baron's book, the ADH, and have googled this newsgroup
a fair bit. I 'played' with an ADP, and liked it, but understand the
limitations.

I have seen this sort of thing presented as the 2 options:

A) MDB -> DAO -> Jet -> ODBC -> SQL Server

or

B) ADP -> ADO -> SQL Server

And am a tad confused. It looks to me as though things which aren't the same
are sometimes being confused. I'll talk myself through it. Tell me when I'm
wrong or right.

In the examples above MDB and ADP are comparable. They are both user
interfaces, yes?

ADO and DAO are comparable. Object libraries for manipulating data, yes? So
the 1st example could actually read

MDB -> ADO -> Jet -> ODBC -> SQL Server ?

Except that most people think that DAO is a better object library for Jet
databases. And getting at SQL Server via ODBC means that it is, effectively,
a Jet database (as far as the data objects appear to the programmer)?

In the 2nd example the thing that takes the place of ODBC is OLEDB? And we
haven't got any choice about that.

The 2 examples, A and B I give above, are the only 2 ways that you can get
bound forms. Anything else will be unbound, yes? For example MDB -> ADO ->
SQL Server?

Is example MDB -> DAO -> Jet -> ODBC -> SQL Server still going to give me
the same problems with dropped network connections? I assume that a SQL
Server db can't be 'corrupted' like an MDB.

And what's the future? It _appears_ as though ADPs were a good idea that
haven't taken off. If they aren't going to be supported in the future then I
won't go that way. But Lyle Fairfield also quotes plenty about ODBC being
deprecated.

I also am willing to consider a non MS backend. PostgreSQL maybe, or Ingres.

Yours, Emily

[david epsom dot com dot au]

MDB-DAO-ODBC-SQLSERVER Transactions don't work any more.

If you want transactions, you will need to use ADO-SQLSERVER
or JET-MDB or Transact-SQL stored procedures

DAO Transactions are still there, which may confuse you into
thinking that they still work correctly, but that is not true.

(david)
"Emily Jones" <em********@hot mail.com> wrote in message
news:43******** *************** @news.aaisp.net .uk...

Dear All

I've got a MS Access application. Split FE/BE. 9 user LAN.

I'm considering moving to a server backend. Probably SQL Server, with an
Access FE still. Because:

1. We've been getting many 'disk or network error' messages. Emphasising
what people have said - that Access is sensitive to network instability.
The BE hasn't corrupted yet, but I'd rather not wait for that to happen.

2. There are new data requirements, so there will be a bit of
re-structuring anyway.

3. I like the idea of referential integrity being enforced by the db
engine. And not all business rules can be enforced by Jet.

4. I want recoverability. The db is backed up to tape daily, but I still
don't want the users to lose a days work.

5. It would be useful for some of the data to be accessible from a web
interface, so an ASP application is a strong future possibility.

I've got Chipman and Baron's book, the ADH, and have googled this
newsgroup a fair bit. I 'played' with an ADP, and liked it, but understand
the limitations.

I have seen this sort of thing presented as the 2 options:

A) MDB -> DAO -> Jet -> ODBC -> SQL Server

or

B) ADP -> ADO -> SQL Server

And am a tad confused. It looks to me as though things which aren't the
same are sometimes being confused. I'll talk myself through it. Tell me
when I'm wrong or right.

In the examples above MDB and ADP are comparable. They are both user
interfaces, yes?

ADO and DAO are comparable. Object libraries for manipulating data, yes?
So the 1st example could actually read

MDB -> ADO -> Jet -> ODBC -> SQL Server ?

Except that most people think that DAO is a better object library for Jet
databases. And getting at SQL Server via ODBC means that it is,
effectively, a Jet database (as far as the data objects appear to the
programmer)?

In the 2nd example the thing that takes the place of ODBC is OLEDB? And we
haven't got any choice about that.

The 2 examples, A and B I give above, are the only 2 ways that you can get
bound forms. Anything else will be unbound, yes? For example MDB -> ADO ->
SQL Server?

Is example MDB -> DAO -> Jet -> ODBC -> SQL Server still going to give me
the same problems with dropped network connections? I assume that a SQL
Server db can't be 'corrupted' like an MDB.

And what's the future? It _appears_ as though ADPs were a good idea that
haven't taken off. If they aren't going to be supported in the future then
I won't go that way. But Lyle Fairfield also quotes plenty about ODBC
being deprecated.

I also am willing to consider a non MS backend. PostgreSQL maybe, or
Ingres.

Yours, Emily

[lylefair]

Perhaps you should consider a non MS-Access FrontEnd.

IMO ADPs (I have tested only to Access 2002) are fundamentally flawed.
To use ADPs we must give users MS-SQL permissions. Whatever those
permissions are, they are not confined to nor dependent upon the ADP
application, they are part of the MS-SQL server configuration. This
means that if the user can log on to the MS-SQL server independently of
your ADP, the user can do whatever those permissions allow him/her to
do, without the contraints of the ADP application. Access makes it
extremely easy to do such log ons, just create a new ADP and all the
servers and dbs the user has logons for appear in a connection dialog.
I've seen this in a large system and I was startled to see 20 servers
and for each server, many dbs. I could have destroyed much of the
institution's data structure in a matter of minutes. Of course, this
could be restored (oh for sure!). But I could have made subtle changes
that might never have been discovered, another employee to pay, or
whatever.
Of course, MS thought about this problem and invented Application
Roles. With application roles users have only log on permissions, the
permissions to actually do something, rest with the application. The
dbatabase window (if shown) is blank). This is a great idea and there
is only one problem with it. Application Roles don't work properly.
ADPs create many concurrent connections to MS-SQL server and each
connection must be empowered with and Application role permission.
Oops, MS forgot to expose alll these connections and their
idiosyncracies to us; there goes a few hundred (yes, Really Hundred on
a larger app) hours of programming, tinkering, swearing etc. One finds
that the connection that the ADP used for the combo-box School Names
on form Schools yesterday, is not necessarily the connection the ADP
will use today.
Well, some experts suggest using a User and Password and
hiding/encrypting these in code. This is another great idea. And I
think it works great in something like ASP. That's because we keep the
User and Password strings in our _Private directory which reputably,
allows only script to run, and can't be accessed except by those with
special credentials. But we're not doing ASP. We're doing ADPs. Yes we
can put a password on our code, etc, etc. But can we prevent everyone
from discovering it? Maybe; but now we're amplifying our work, because
maintaining those users and passwords and permissions can be a LOT of
work. And if the user/password is hacked/broken, who is going to take
the flak?
I think I cannot justify the use of ADPs for any major applications.
I can't comment on the ODBC route. I don't know whether or not it has
security flaws. I can think of only two reasons I would not go that
way. One is that it's pretty old technology, and MS keeps threatening
to make it go away. (But who knows?), Two is that it must have to have
user names, passwords, permissions etc, for the server. And if it does,
then it may suffer from the same security deficiencies that ADP-OLEDB
does. And three is that MS-SQL is very powerful; can you use all the
power with OBDC? I don't know that either, but I doubt it. Maybe people
who use it will say, "Yes", but if they've never used anything else,
how will they know?
Of course, we will have the opinions of knowledgeable MS insiders. I'm
sure they're very responsible and forthright people. Can they afford to
say, "ADPs are flawed"? "ODBC may be archaic and soon deprecated.".
"MS-Access has no good technology for using MS-SQL"? I don't know. But
I'm not a knowledgeable MS insider. And these are the things I say.

[Emily Jones]

Thanks Lyle,

In my searching of the archives I saw that you had used ADPs. Forgive me if
I've got this wrong but you appeared initially to like them, and then your
opinion gradually changed. Yes? They do appear to me like a good idea. Thin
dedicated client to SQL Server.

As you say, if ODBC requires the same sort of permissions to the server then
that method too will suffer from the same problem, in which case I'm looking
at a completely different client anyway. Or probably not. The security needs
of this organisation are not so great, at least at the moment, that I am
going to spend a lot of my time (=their money) on that aspect. If I were
thinking like that I would actually be more inclined to save their money by
using a free server db.

I've also read some of the "knowledgea ble MS insider he say...." stuff.
Which may or may not be true/useful/worth paying attention to. To be honest,
if people can't use real names then I'm inclined to take a lot of that with
a pinch of salt. No wonder what personal agendas are driving it.

Yours, Emily
"lylefair" <ly******@yahoo .ca> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

Perhaps you should consider a non MS-Access FrontEnd.

IMO ADPs (I have tested only to Access 2002) are fundamentally flawed.
To use ADPs we must give users MS-SQL permissions. Whatever those
permissions are, they are not confined to nor dependent upon the ADP
application, they are part of the MS-SQL server configuration. This
means that if the user can log on to the MS-SQL server independently of
your ADP, the user can do whatever those permissions allow him/her to
do, without the contraints of the ADP application. Access makes it
extremely easy to do such log ons, just create a new ADP and all the
servers and dbs the user has logons for appear in a connection dialog.
I've seen this in a large system and I was startled to see 20 servers
and for each server, many dbs. I could have destroyed much of the
institution's data structure in a matter of minutes. Of course, this
could be restored (oh for sure!). But I could have made subtle changes
that might never have been discovered, another employee to pay, or
whatever.
Of course, MS thought about this problem and invented Application
Roles. With application roles users have only log on permissions, the
permissions to actually do something, rest with the application. The
dbatabase window (if shown) is blank). This is a great idea and there
is only one problem with it. Application Roles don't work properly.
ADPs create many concurrent connections to MS-SQL server and each
connection must be empowered with and Application role permission.
Oops, MS forgot to expose alll these connections and their
idiosyncracies to us; there goes a few hundred (yes, Really Hundred on
a larger app) hours of programming, tinkering, swearing etc. One finds
that the connection that the ADP used for the combo-box School Names
on form Schools yesterday, is not necessarily the connection the ADP
will use today.
Well, some experts suggest using a User and Password and
hiding/encrypting these in code. This is another great idea. And I
think it works great in something like ASP. That's because we keep the
User and Password strings in our _Private directory which reputably,
allows only script to run, and can't be accessed except by those with
special credentials. But we're not doing ASP. We're doing ADPs. Yes we
can put a password on our code, etc, etc. But can we prevent everyone
from discovering it? Maybe; but now we're amplifying our work, because
maintaining those users and passwords and permissions can be a LOT of
work. And if the user/password is hacked/broken, who is going to take
the flak?
I think I cannot justify the use of ADPs for any major applications.
I can't comment on the ODBC route. I don't know whether or not it has
security flaws. I can think of only two reasons I would not go that
way. One is that it's pretty old technology, and MS keeps threatening
to make it go away. (But who knows?), Two is that it must have to have
user names, passwords, permissions etc, for the server. And if it does,
then it may suffer from the same security deficiencies that ADP-OLEDB
does. And three is that MS-SQL is very powerful; can you use all the
power with OBDC? I don't know that either, but I doubt it. Maybe people
who use it will say, "Yes", but if they've never used anything else,
how will they know?
Of course, we will have the opinions of knowledgeable MS insiders. I'm
sure they're very responsible and forthright people. Can they afford to
say, "ADPs are flawed"? "ODBC may be archaic and soon deprecated.".
"MS-Access has no good technology for using MS-SQL"? I don't know. But
I'm not a knowledgeable MS insider. And these are the things I say.

[lylefair]

Emily

You are right. I was very enthusiastic about ADPs for about two years.
I did a massive application for a large school board. It worked well
and they were tres happy. About 65 school principals were to use this
application; a few could not log on. In a meeting with the Board's SA,
Network Admin, IT Head etc, and principal user (Personnel Manager) we
decided to NT log in as these people who could not connect, on some
pristine new machines to see if we could discover the problem. We found
nothing. Then I suggested we create a new ADP and try. (We were using a
projector and so all looking at the same screen.) I was appalled at my
own stupidity and gullibility when all the objects of the Database for
which the user we were impersonating had permissions appeared before
us, naked and vulnerable, without the benefit of the application. None
of the others sensed the problems this new ADP had exposed. I was
devastated, I spent two months (unpaid) of 14 hour days changing this
application to accept and use Application Roles. I have coded,
(sometimes creatively even) for more than 20 years. ADPs with
Application Roles required every bit of experience and ingenuity I
could muster; even then, some of the application is held-together with
Hack Tape. I read everything I could find about Application Roles;
there wasn't much (summer of 2004). Mary Chapman just said, "Don't use
them".

When we tried out the revised app (with application roles) with a
sampling of 15 or so principals, SA went a bit berserk when he counted
some users having 5 or more connections to the db open.

If there's no problem with security then ADPs are great. One must learn
how to avoid non-updateable form recordsets, and how to deal with
dynamic report input parameters, and forms loading only 50 records etc
initially, but all these are doable and satisfying. Application Roles
are hell.
Now I use ADPs for my own personal and company quick stuff only. I
don't think I can sell an ADP in good conscience.
And disappointed so much with ADPs I've honed my ASP, J-Script and HTA
skills; I have a bunch of templates and can plug just about anything
into these. I'm happier with them, especially if I am on a strange
computer or in a strange land. If the Server is internet enabled then
it's just like being at home.

Shut Up, Lyle, you've run on way too long already!

Lyle

[Ace Calhoon]

I can't really tell you which is better, as I'm still learning a lot of
this stuff myself. However, I recently converted an Access FE/BE
application to Access FE/SQL Server BE (as that's the standard
determined by the IT department I'm working with).

Overall the transition was pretty smooth. I linked to the SQL Server
tables using ODBC. Because the names had to change when they were
moved to SQL Server (company policy), I created queries with the
original table names that converted the field names from the SQL Server
name to the old Access name. I also had to perform a few other
modifications in the code (such as using dbSeeChanges when opening
recordsets), but most of these were pointed out by the debugger. All
my bound forms continued to work correctly, as they were now bound to
queries (which caused Access to automatically handle the connection to
the ODBC database).

So far as transactions go, my DAO transactions appear to work (can
anyone be more specific in what they mean by "these don't work?"), but
they are a little twitchy... Editing the links in the linked table
editor caused them to fail (crash), for example.

You can find more information on this (including a kind of neat way to
implement the transactions server-side) at the following thread:

http://groups.google.com/group/comp....0525e18be825f1

[david epsom dot com dot au]

> So far as transactions go, my DAO transactions appear to work

DAO ODBC Transactions are asynchronous: you can't
tell if the transaction has committed or is blocking.

DAO creates multiple connections, which block each
other, but you can't tell because the transactions
are asynchronous. Then the transaction fails when
you leave the application.

DAO uses different transaction locking strategies
than SQL Server, and tries to reduce them to a
common denominator for odbc connections. This
leads to excessive locking, which leads to blocked
transactions, which leads to unreported transaction
failures.

The multiple connections are by definition asynchronous:
they hold until the transaction is committed. But since
they are separate connections, they will block each other.

The only solutions are:
Don't use DAO, or
Use One Stored Procedure or
Never read or write any table more than once.

If you use one stored procedure, you might as well
put the transaction methods inside that: no point
to using a DAO transaction at all.

If you can't touch any table more than once (and that
includes cascading updates), then all of the actions
in the transaction are logically unrelated. If all
of the actions are logically unrelated, why use a
transaction at all?

In practice, you can avoid these problem by only ever
using append queries, and only from a single table,
and only from and too unbound tables, and only when
the source table is not used by another user, but
it is still a flaky and opaque solution.

(david)
"Ace Calhoon" <ac********@gma il.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...

I can't really tell you which is better, as I'm still learning a lot of
this stuff myself. However, I recently converted an Access FE/BE
application to Access FE/SQL Server BE (as that's the standard
determined by the IT department I'm working with).

Overall the transition was pretty smooth. I linked to the SQL Server
tables using ODBC. Because the names had to change when they were
moved to SQL Server (company policy), I created queries with the
original table names that converted the field names from the SQL Server
name to the old Access name. I also had to perform a few other
modifications in the code (such as using dbSeeChanges when opening
recordsets), but most of these were pointed out by the debugger. All
my bound forms continued to work correctly, as they were now bound to
queries (which caused Access to automatically handle the connection to
the ODBC database).

So far as transactions go, my DAO transactions appear to work (can
anyone be more specific in what they mean by "these don't work?"), but
they are a little twitchy... Editing the links in the linked table
editor caused them to fail (crash), for example.

You can find more information on this (including a kind of neat way to
implement the transactions server-side) at the following thread:

http://groups.google.com/group/comp....0525e18be825f1