Access mdb or SQL Server Back End?

[Jim Franklin]

Hi,

I have a Access 2003 car wash app where the main screen shows up to date
ticket information on the cars being processed. The form is unbound and
opens a recordset to pull 10 pieces of information on each current ticket
(one record per ticket, 10 fields). This data then populates clusters of
unbound controls on the screen (1 cluster per ticket) - each cluster is
mainly text boxes but also includes a rectangle whose border and fill
colours represent 2 bits of info.

There are 25 clusters on the screen (so a lot of controls!) and a maximum of
about 50 open tickets at any time (if more than 25 records are returned in
the recordset, up and down buttons appear which select 1-25, 26-50, 51-75
etc. The code which populates the controls uses the corresponding records
from the recordset. This data is constantly changing and the form is
re-populated roughly every 15 seconds, all day long. There are 4 terminals
all querying the same BE, which is on a fileserver.

At present, the table of current tickets is kept deliberately small (100
tickets). Once a ticket is completed, the record is appended to a sales
table, and the ticket record is re-set and re-cycled. Over 100 sales are
added every day. However, it would be advantageous to keep all the data in
one table, with an indexed field tktCompletedTime to distinguish current
from completed tickets.

Primary question: At the moment, the BE is a .mdb file. To speed up
performance, I am considering migrating the Back End to SQL Server and
opening an ADO connection each time to establish the recordset, but am
unsure what the performance advantage would be. Can anyone give me any ideas
about what the performance implications would be? This is the main source of
data traffic across the LAN - all other FE functions call up and edit 1
record at a time and sometimes a small (<10) number of related detail
records.

One other secondary question: because of the number of controls (approx 250)
on the form, I was considering trying to write (Using VB6 installed) a
custom ActiveX control to replace each cluster and display all the relevant
info on each ticket. I have never done this so is this a bad idea and is
there any advantage in doing so?

Many many thanks for reading all this (!) and for any help anyone can
provide,

Cheers,
Jim

 

[Access Developer]

You seem to have gone to a great deal of extra work to accomplish having 25
items (in identical format on the screen)... perhaps you have sufficiently
compelling reasons for doing so.

It seems to me that it would be significantly simpler to use a (bound)
continuous-forms view to show all the information on each ticket (or if
there's not enough room, enough to identify a particular ticket, then allow
click or double-click to display the details in a bound form for a single
ticket). You can keep all your tickets, current and archived, in the same
table, with a field to indicate that the ticket is completed/archived -- use
that field in the Where clause of Queries to limit the Records returned to
active tickets. You can scroll through 25 records rapidly, or, if you wish,
you can include Combo Boxes to allow location / selection of specific
tickets (e.g., by number, or car model, or car color, etc.).

You cannot create an ActiveX control with Access VBA. You could write a
class module, or, for what you describe you want, I see no reason you could
not do it with a function or sub in a standard module, but before I invested
that sort of time and effort, I'd give serious consideration to what I
suggested in the first paragraph. If you feel compelled to create an
ActiveX, then you'll have to do so outside Access with a different
languages, such as C++, and unless you are an accomplished C++ programmer,
that is not a task "for the faint of heart".

The Access team suggests that the approach-of-choice is to use linked tables
via ODBC to MS SQL Server. Caveat: You should not, repeat NOT, expect
automatic performance increases by moving to SQL Server; you will have some
advantages in reduced probabilty of corruption in case of communication
interruption if the back-end MDB or SQL Server is on a different machine*,
and in recoverability, but, often, you will have performance decreases --
which you may be able to overcome with some design 'tweaks'.

* a frequent happening if you are using a wireless network,
even under 'good conditions'.

You should, for a number of reasons, discussed in depth at MVP Tony Toews'
site, http://www.granite.ab.ca/accsmstr, split your data into a 'back end'
(tables and relationships) from your user interface, 'front end' (queries,
forms, reports, macros, and modules) if you decide to.

 

[Jim Franklin]

Hi Larry,

Thanks for your reply. I do have reasons for the screen layout, primarily
that the interface is touchscreen point-of-sale and the normal bound
continuous form setup just does not work. The user touches a 'ticket'
rectangle to bring up a screen showing all data on the chosen ticket &
various further functions, and this just doesn't work with rows of data in a
continuous form in a hectic environment.

I know you cannot create an Active X control in Access VBA. I have Visual
Basic 6 installed on one pc, so I would use this. Is it a bad idea? Its not
a problem having 250 controls on the screen - I was just wondering about a
more elegant solution.

As for the SQL server issue. the LAN is a wired 100MB one - so no Wifi.
Instead of using an ODBC linked table, I was going to open an ADO connection
to a SQL Server view in vba and create a recordset to collect the data
needed to populate the unbound screen. The screen only needs to be
read-only. Is this less efficient than having an ODBC linked table and
creating a recordset based on that?

I am still wondering if I am better off sticking with a back-end .mdb with
linked tables. I am just worried about the frequency of requests & network
traffic - 4 machines each hitting the Back End every 15 seconds all day
long.

Thanks once again - I do appreciate the effort you guys put in to help.

Jim

 

[Access Developer]

Yes, that would be a compelling reason.

Whether ADO or DAO via linked tables to an SQL Server back end would be
faster is just something you'd have to test. My guess, and only a guess, is
that ADO to SQL Server might be faster, but that it is not likely to be
enough different that people at the terminals will notice (and, though it's
not 'impossibly' complicated, I found it more so than DAO, and not
appreciably different in response).

I'm not a fan of ActiveX Controls with Access... anything that requires an
external .DLL adds to the complexity. If you want to simplify filling and
emptying indentically-formatted display segments, I'd recommend a subroutine
or function written in VBA, or if your thinking is 'object oriented', you
can do that with a class.

 

[Tony Toews]

Primary question: At the moment, the BE is a .mdb file. To speed up
performance, I am considering migrating the Back End to SQL Server and
opening an ADO connection each time to establish the recordset, but am
unsure what the performance advantage would be. Can anyone give me any ideas
about what the performance implications would be? This is the main source of
data traffic across the LAN - all other FE functions call up and edit 1
record at a time and sometimes a small (<10) number of related detail
records.

I wouldn't worry about DAO vs ADO. To me there wouldn't be a lot of
performance difference especially in a LAN. On a WAN you might want
to muck about with comparing things.

SQL Server can be slower than Access until it's debottle necked and
optimized. Which doesn't necessarily take a lot of effort and you'd
only do that on high usage screens such as the one you mention.

I would be more tempted to consider the reliability issues. What
happens if they have a computer crash of some sort, and the database
gets corrupted. And now you have to restore from last nights backup?
In such a busy environment this could be a nightmare with screaming
customers and such. This alone might want you to switch to SQL
Server.

BTW SQL Server Express, which is free, should be way more than
sufficient to handle the workload.

One other secondary question: because of the number of controls (approx 250)
on the form, I was considering trying to write (Using VB6 installed) a
custom ActiveX control to replace each cluster and display all the relevant
info on each ticket. I have never done this so is this a bad idea and is
there any advantage in doing so?

Actually you might just consider a VB6 program completely for this
functionality only. If you are using completely unbound forms for
this functionality then what's the difference between Access and VB6?
You'd just recreate the controls in VB6 and copy and paste the code
across.

But then why bother switching? If it's working in Access then don't
muck with it.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/