Hello,

I have an Access 2000 application wich uses SQL Server linked ODBC tables
running on Windows 2000 Pro, with the SQL Server running on Widows 2000
Server..
It is a multi-user system with up to 5 users, connected to the server
machine through a Samba network.

The application has worked well with Access as the back-end for almost 8
years, but the amount of data was slowing the response, so I recently
converted to SQL Server. Now the users are having unexpected problems with
several Yes/No fields.
The application has not changed, just the linking to the back-end.

Some times, after entering a record, then trying to edit the record, all the
checkboxes based on Yes/No fields show up as being set, although only one or
two were set originally.

Another peculiar error is when a checkbox is set then cleared before the
record is saved. When editing the record the cleared checkbox shows up as
set, but if it is cleared then saved, it stays cleared.

A third problem has to do with a combo that is normally disabled. It is
enabled when the user sets a checkbox by clicking it. Now, sometimes the
combo is enabled when the form shows an empty record.

The forms are unbased, DAO recordsets are used to copy data from the tabled
to the forms and to update the tables with the data in the forms.

All the SQL Server tables have a primary key.

I had tested the application thoroughly at the office, using the same
platforms as the client is using, before deployment and everything seemed to
work fine.
I can reproduce the error with checking then unchecking the checkbox but not
the other problems.

Any comments or suggestions would be very much appreciated.

Ragnar

The way I read your message, the application uses linked tables
andunbound forms based on DAO recordsets, which are two *different*
methods for fetching data, the first being acceptable, the second not.
Many concurrency issues with forms can be cleared up by adding a
timestamp column to the SQLS table. Other issues can be eliminated by
upgrading to Access 2002, and a whole raft of additional problems
fixed by junking the DAO code and using ADO for data access (even
better, stored procedures or direct SQL).

It is unrealistic to expect that an obsolete Access application is
going to work cleanly by migrating the data to SQL Server. Consider a
complete rewrite if you need it to perform well or be at all scalable.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Tue, 29 Jul 2003 12:30:25 -0400, "Ragnar Midtskogen"
<ragnarm@optonline.net> wrote:

>Hello,
>
>I have an Access 2000 application wich uses SQL Server linked ODBC tables
>running on Windows 2000 Pro, with the SQL Server running on Widows 2000
>Server..
>It is a multi-user system with up to 5 users, connected to the server
>machine through a Samba network.
>
>The application has worked well with Access as the back-end for almost 8
>years, but the amount of data was slowing the response, so I recently
>converted to SQL Server. Now the users are having unexpected problems with
>several Yes/No fields.
>The application has not changed, just the linking to the back-end.
>
>Some times, after entering a record, then trying to edit the record, all the
>checkboxes based on Yes/No fields show up as being set, although only one or
>two were set originally.
>
>Another peculiar error is when a checkbox is set then cleared before the
>record is saved. When editing the record the cleared checkbox shows up as
>set, but if it is cleared then saved, it stays cleared.
>
>A third problem has to do with a combo that is normally disabled. It is
>enabled when the user sets a checkbox by clicking it. Now, sometimes the
>combo is enabled when the form shows an empty record.
>
>The forms are unbased, DAO recordsets are used to copy data from the tabled
>to the forms and to update the tables with the data in the forms.
>
>All the SQL Server tables have a primary key.
>
>I had tested the application thoroughly at the office, using the same
>platforms as the client is using, before deployment and everything seemed to
>work fine.
>I can reproduce the error with checking then unchecking the checkbox but not
>the other problems.
>
>Any comments or suggestions would be very much appreciated.
>
>Ragnar
>

Hello Mary,

Thanks for the cold shower, I was hoping for something more in the line of
a pointer to something obvious that I was unaware of.

I was aware that I could have problems, but the client was not willing to
pay
for a complete rewrite. This is a fairly complex application, so a complete
rewrite
would be a big job, and the client is a state agency with a tight budget and
a record of late payments.

I am planning to rewrite the code for the unbound forms to use ADO, I was
aware that DAO and SQL Server were not a good combination. I had also
read about adding timestamp fields, but I assume I would have to do
something
with those fields for them to have any effect.

I have your book, but unfortunately I had not expected this to come up while
on vacation, a co-worker was given the job to phase them over, so the book
is at home. My strategy now is to just patch things up to allow them to
operate until I get back at the end of August, then see what the client
would
be willing to do.

I think I will have my daughter mail me your book,, by the time I get back I
should have a better understanding of the problems involved in this
phaseover.

Again, thanks for your comments
Ragnar

"Mary Chipman" <mchip@nomail.please> wrote in message
news:6gidiv493ga943c6bo69j37p3qc0s4gl63@4ax.com...
> The way I read your message, the application uses linked tables
> andunbound forms based on DAO recordsets, which are two *different*
> methods for fetching data, the first being acceptable, the second not.
> Many concurrency issues with forms can be cleared up by adding a
> timestamp column to the SQLS table. Other issues can be eliminated by
> upgrading to Access 2002, and a whole raft of additional problems
> fixed by junking the DAO code and using ADO for data access (even
> better, stored procedures or direct SQL).
>
> It is unrealistic to expect that an obsolete Access application is
> going to work cleanly by migrating the data to SQL Server. Consider a
> complete rewrite if you need it to perform well or be at all scalable.
>
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
>
> On Tue, 29 Jul 2003 12:30:25 -0400, "Ragnar Midtskogen"
> <ragnarm@optonline.net> wrote:
>
> >Hello,
> >
> >I have an Access 2000 application wich uses SQL Server linked ODBC tables
> >running on Windows 2000 Pro, with the SQL Server running on Widows 2000
> >Server..
> >It is a multi-user system with up to 5 users, connected to the server
> >machine through a Samba network.
> >
> >The application has worked well with Access as the back-end for almost 8
> >years, but the amount of data was slowing the response, so I recently
> >converted to SQL Server. Now the users are having unexpected problems
with
> >several Yes/No fields.
> >The application has not changed, just the linking to the back-end.
> >
> >Some times, after entering a record, then trying to edit the record, all
the
> >checkboxes based on Yes/No fields show up as being set, although only one
or
> >two were set originally.
> >
> >Another peculiar error is when a checkbox is set then cleared before the
> >record is saved. When editing the record the cleared checkbox shows up as
> >set, but if it is cleared then saved, it stays cleared.
> >
> >A third problem has to do with a combo that is normally disabled. It is
> >enabled when the user sets a checkbox by clicking it. Now, sometimes the
> >combo is enabled when the form shows an empty record.
> >
> >The forms are unbased, DAO recordsets are used to copy data from the
tabled
> >to the forms and to update the tables with the data in the forms.
> >
> >All the SQL Server tables have a primary key.
> >
> >I had tested the application thoroughly at the office, using the same
> >platforms as the client is using, before deployment and everything seemed
to
> >work fine.
> >I can reproduce the error with checking then unchecking the checkbox but
not
> >the other problems.
> >
> >Any comments or suggestions would be very much appreciated.
> >
> >Ragnar
> >
>

The timestamp columns help Access resolve concurrency issues, so you
don't have to do anything special to get the benefit. However, for
unbound forms, you'll need to handle concurrency yourself either using
timestamps or by impelmenting concurrency manually (described in
book).

One suggestion -- since money is tight, consider not doing the app
unbound -- it's going to cost more money and you can achieve nearly
the same result bound with decent querying techniques. Access can work
efficiently as a front-end as long as you restrict the amount of data
retrieved for editing to a single row (IOW, don't link to entire
tables or use "select * from ...". If you use an mdb, take advantage
of pass-through queries and stored procedures for reports, and Jet for
static data for list and combo boxes, etc.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Wed, 30 Jul 2003 05:48:38 -0400, "Ragnar Midtskogen"
<ragnarm@optonline.net> wrote:

>Hello Mary,
>
>Thanks for the cold shower, I was hoping for something more in the line of
>a pointer to something obvious that I was unaware of.
>
>I was aware that I could have problems, but the client was not willing to
>pay
>for a complete rewrite. This is a fairly complex application, so a complete
>rewrite
>would be a big job, and the client is a state agency with a tight budget and
>a record of late payments.
>
>I am planning to rewrite the code for the unbound forms to use ADO, I was
>aware that DAO and SQL Server were not a good combination. I had also
>read about adding timestamp fields, but I assume I would have to do
>something
>with those fields for them to have any effect.
>
>I have your book, but unfortunately I had not expected this to come up while
>on vacation, a co-worker was given the job to phase them over, so the book
>is at home. My strategy now is to just patch things up to allow them to
>operate until I get back at the end of August, then see what the client
>would
>be willing to do.
>
>I think I will have my daughter mail me your book,, by the time I get back I
>should have a better understanding of the problems involved in this
>phaseover.
>
>Again, thanks for your comments
>Ragnar
>
>"Mary Chipman" <mchip@nomail.please> wrote in message
>news:6gidiv493ga943c6bo69j37p3qc0s4gl63@4ax.com.. .
>> The way I read your message, the application uses linked tables
>> andunbound forms based on DAO recordsets, which are two *different*
>> methods for fetching data, the first being acceptable, the second not.
>> Many concurrency issues with forms can be cleared up by adding a
>> timestamp column to the SQLS table. Other issues can be eliminated by
>> upgrading to Access 2002, and a whole raft of additional problems
>> fixed by junking the DAO code and using ADO for data access (even
>> better, stored procedures or direct SQL).
>>
>> It is unrealistic to expect that an obsolete Access application is
>> going to work cleanly by migrating the data to SQL Server. Consider a
>> complete rewrite if you need it to perform well or be at all scalable.
>>
>> -- Mary
>> Microsoft Access Developer's Guide to SQL Server
>> http://www.amazon.com/exec/obidos/ASIN/0672319446
>>
>> On Tue, 29 Jul 2003 12:30:25 -0400, "Ragnar Midtskogen"
>> <ragnarm@optonline.net> wrote:
>>
>> >Hello,
>> >
>> >I have an Access 2000 application wich uses SQL Server linked ODBC tables
>> >running on Windows 2000 Pro, with the SQL Server running on Widows 2000
>> >Server..
>> >It is a multi-user system with up to 5 users, connected to the server
>> >machine through a Samba network.
>> >
>> >The application has worked well with Access as the back-end for almost 8
>> >years, but the amount of data was slowing the response, so I recently
>> >converted to SQL Server. Now the users are having unexpected problems
>with
>> >several Yes/No fields.
>> >The application has not changed, just the linking to the back-end.
>> >
>> >Some times, after entering a record, then trying to edit the record, all
>the
>> >checkboxes based on Yes/No fields show up as being set, although only one
>or
>> >two were set originally.
>> >
>> >Another peculiar error is when a checkbox is set then cleared before the
>> >record is saved. When editing the record the cleared checkbox shows up as
>> >set, but if it is cleared then saved, it stays cleared.
>> >
>> >A third problem has to do with a combo that is normally disabled. It is
>> >enabled when the user sets a checkbox by clicking it. Now, sometimes the
>> >combo is enabled when the form shows an empty record.
>> >
>> >The forms are unbased, DAO recordsets are used to copy data from the
>tabled
>> >to the forms and to update the tables with the data in the forms.
>> >
>> >All the SQL Server tables have a primary key.
>> >
>> >I had tested the application thoroughly at the office, using the same
>> >platforms as the client is using, before deployment and everything seemed
>to
>> >work fine.
>> >I can reproduce the error with checking then unchecking the checkbox but
>not
>> >the other problems.
>> >
>> >Any comments or suggestions would be very much appreciated.
>> >
>> >Ragnar
>> >
>>
>

Thank you Mary,

I appreciate your comments and recommendations.

> The timestamp columns help Access resolve concurrency issues, so you
> don't have to do anything special to get the benefit. However, for
> unbound forms, you'll need to handle concurrency yourself either using
> timestamps or by impelmenting concurrency manually (described in
> book).

I don't think concurrency is a problem, so I will try other solutions first.

> One suggestion -- since money is tight, consider not doing the app
> unbound --

To start with, most forms are bound and displays only one record, the
problem seems to be limited to the two forms that are unbound.

Unortunately, due a unique sequence of operations involving a code
generated key index, which are also sent to a bar code printer, the
problem forms have to be unbound.

So far the problem appears to affect only the checkbox controls
which are based on Access Yes/No fields. Since these fields were
converted to Bit data type in SQL Server by the Access 2002
Upsizing Wizard, I am working on the assumption that this is the
root of the problem, possibly because I had used DAO recordsets.

I am working on converting the DAO recordsets to ADO and at the
same time adding code to explicitly convert the boolean data between
the VB True/False format to to the Bit type data data 1/0 format.

Ragnar

You might try a search on groups.google.com on the bit data type issue
since you aren't the first person to have this problem. First thing to
understand is that Jet yes/no booleans aren't the same as bit in SQLS.
You can't use the same true/false logic in your code. I have no idea
what's involved with the code generated key index, but you might want
to investigate the possibility coding the logic in a stored procedure
instead of client code.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Sun, 3 Aug 2003 06:40:14 -0400, "Ragnar Midtskogen"
<ragnarm@optonline.net> wrote:

>Thank you Mary,
>
>I appreciate your comments and recommendations.
>
>> The timestamp columns help Access resolve concurrency issues, so you
>> don't have to do anything special to get the benefit. However, for
>> unbound forms, you'll need to handle concurrency yourself either using
>> timestamps or by impelmenting concurrency manually (described in
>> book).
>
>I don't think concurrency is a problem, so I will try other solutions first.
>
>> One suggestion -- since money is tight, consider not doing the app
>> unbound --
>
>To start with, most forms are bound and displays only one record, the
>problem seems to be limited to the two forms that are unbound.
>
>Unortunately, due a unique sequence of operations involving a code
>generated key index, which are also sent to a bar code printer, the
>problem forms have to be unbound.
>
>So far the problem appears to affect only the checkbox controls
>which are based on Access Yes/No fields. Since these fields were
>converted to Bit data type in SQL Server by the Access 2002
>Upsizing Wizard, I am working on the assumption that this is the
>root of the problem, possibly because I had used DAO recordsets.
>
>I am working on converting the DAO recordsets to ADO and at the
>same time adding code to explicitly convert the boolean data between
>the VB True/False format to to the Bit type data data 1/0 format.
>
>Ragnar
>
>