A97 FE to SQL2K BE - want to upgrade Access

[Ron Hinds]

I'm using Access 97 as the front-end (yecch!) to a SQL 2000 database. I want
to upgrade Access to a version that (hopefully) works better with SQL
Server. I'm looking at Access 2003. My FE has a lot of local temp tables in
it. What I want to know is, is it a better idea to switch from Jet to ADO?
What are the ramifications for the local temp tables? Can anyone point me to
a book or article on doing this? TIA!

 

[Sylvain Lafontaine]

Switch to A2003 but keep your Jet; ADP doesn't have local tables.

If your application is slow over the internet, add Views to your backend.
Take a look at the following article if you want to know how to have an
updatable view from Access:

http://support.microsoft.com/kb/q209123/

 

[Ron Hinds]

Does A2003/Jet 4.0 do a better job of table locking? That seems to be my
number one issue with A97.I have to set the Default Record Locing to No
Locks in order for it to be useable - but that has a tendency to cause
corruptions.

 

[Sylvain Lafontaine]

You have experiencing data corruption with SQL-Server as the backend?
That's very strange.

You should ask your question to a more appropriate newsgroup than this one
and with all the relevant information: over a LAN or the WAN, type of query
and type of corruption.

Take also note that missing data is not necessarily data corruption when
working over the WAN as the unreliability of the Internet protocol is
legendary.

 

[Joe Butler]

You have experiencing data corruption with SQL-Server as the backend?
That's very strange.

You should ask your question to a more appropriate newsgroup than this one
and with all the relevant information: over a LAN or the WAN, type of query
and type of corruption.

Take also note that missing data is not necessarily data corruption when
working over the WAN as the unreliability of the Internet protocol is
legendary.

Are saying that if packet (n) is dropped that this is not handled in a
sensible way for Access - that packet (n-1) and packet (n+1) are stuck
together as if packet (n) never existed and then passed on to Access for
processing normally?

Would you clarify this? What part of the chain is unable to determine that
packets have been dropped? What are the workarounds in this case?

Thanks.

 

[Sylvain Lafontaine]

First; have you experienced yourself a corruption problem with Access?
Because it would be a far more better idea to describe your situation and
problem then to go around fishing for a possible explanation.

With Access as the backend (using Jet), your only chance of dodging
corruption if to have a rock solid LAN where everything, including routers,
are on power backup (UPS) and with no possibility of network saturation.
(Don't expect to have no problem if your network is frequently running at
over 50% of its capacity.) For the WAN, your only practical chance is
probably to use a properly configured TS/Citrix server; here again with no
possibility of saturation (in this case, this mean a very, very powerful
machine and all timeouts set to their maximum). You can try with a VPN but
in this case, you must use a very good VPN that will run the connection in a
very reliable way (no dropped packet).

With SQL-Server as the backend; Access can usually automatically deal with
simple cases by using the transaction mecanism of SQL-Server but for more
complicated multi-steps transaction, there is no solution other than making
the update yourself outside of a bound form. Most often, the solution used
by programmers is to add command buttons for complex and critical multi-step
tasks, like creating or saving a new order with its associated bill and then
crossing their fingers leaving the rest to be handled automatically by
Access bound forms.

However, for the .NET Framework, MS took the decision to drop all these
configurations and to keep instead a single solution where all (and I mean
all) updates - for bound, unbound or no form, single or multi-steps - can be
directly controlled inside a single or multiple transactions by the
programmer at his leisure. The fact that they have taken this decision
should give you an idea of what MS itself thinks of all these other
solutions.

Finally, if you take a look at other posts; you will find many people that
will say exactly the opposite; so finally, it's up to you to take your own
decision.

 

[Joe Butler]

I haven't experienced corruption of an Access database, probably because
I've never used one in a stressed situation.

However, it's my intention to author a multi-user networked application with
a Jet backend (maybe MySQL, I don't know yet) via a, potentially, busy LAN
or otherwise stressed server. So, it looks like I'll need to perform some
capability tests specifically looking at corruption before settling on Jet.
I'll be using straight VC++ 6 SDK and SQL to interface with the database.

Thanks.

 

[Lynn Trapp]

You might want to read through this website.

http://www.granite.ab.ca/access/corruptmdbs.htm

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Joe Butler]

Thanks.

I was already aware of the oplocks issues, but I hadn't realised that Samba
has been implicated in mdb corruption.

A vendor of an Access-based application has told me that the current version
of Jet/Access means that the oplocks issues is not an issue anymore - but I
think that what this vendor is suspect.

I'll probably get around to knocking up an automated test that I can leave
running for days at a time to test just how flakey Jet is under different
loads. If it's likely to cause too many problems, then I'll look at
workarounds (communicating with program on server rather than opening
datafile directly, or using MySQL instead).

 

[Lynn Trapp]

I'll probably get around to knocking up an automated test that I can leave

running for days at a time to test just how flakey Jet is under different
loads.

Doing a stress test like that is always a good idea before taking any
application to production. Good luck with it.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Guest]

Does A2003/Jet 4.0 do a better job of table locking? That seems to be my

No, worse.

A2003 does a better job of 'autonumber' fields - it seems to handle
the identity_insert seamlessly-, and it handles Unicode, which is the
MS way of letting me put Chinese characters as well as English
characters into the same database.

But we are no longer able to use mix Jet tables with SQL Server
tables in transactions, because as soon as you update from a Jet
table to a SQL Server table, the Jet table is locked, and can't be
read from anymore.

(david)

 

[aaron.kempf]

switch to ADP; ADP _does_ have temp tables; they are prefixed with the
#symbol

MDB is dead and a total joke.. i mean-- it's 2005 guys

 

[Ron Hinds]

switch to ADP; ADP _does_ have temp tables; they are prefixed with the
#symbol

Doesn't that mean that they will be server temp tables? I thought I read
somewhere that MS was touting the performance gains to be had by using local
temp tables. Put all that desktop processing power to work, so to speak.

 

[aaron.kempf]

i strongly disagree.. use an ADP it rocks

lack of temp tables.. shit sylvan

learn SQL.. keep everything in SQL Server. you dont need clientside
temp tables; you just need to correct sprocs for the job