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

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.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Jim Franklin" <(E-Mail Removed)> wrote in message
newsib_p.75153$(E-Mail Removed)2...
> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> Larry Linson, Microsoft Office Access MVP
> Co-author: "Microsoft Access Small Business Solutions", published by Wiley
> Access newsgroup support is alive and well in USENET
> comp.databases.ms-access
>
> "Jim Franklin" <(E-Mail Removed)> wrote in message
> newsib_p.75153$(E-Mail Removed)2...
>> 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
>>
>>
>>
>
>

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.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"Jim Franklin" <(E-Mail Removed)> wrote in message
news:GZx_p.102044$_(E-Mail Removed)2...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>
>> --
>> Larry Linson, Microsoft Office Access MVP
>> Co-author: "Microsoft Access Small Business Solutions", published by
>> Wiley
>> Access newsgroup support is alive and well in USENET
>> comp.databases.ms-access
>>
>> "Jim Franklin" <(E-Mail Removed)> wrote in message
>> newsib_p.75153$(E-Mail Removed)2...
>>> 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
>>>
>>>
>>>
>>
>>
>
>

On Wed, 3 Aug 2011 13:37:55 +0100, "Jim Franklin"
<(E-Mail Removed)> wrote:

>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/