| Groups | Blog | Home |
sql server odbc : Problem with Yes/No data types - Access 2000 Linked ODBC tables in SQL Server
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" [quoted text, click to view] <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 [quoted text, click to view] "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" [quoted text, click to view] <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. [quoted text, click to view] > 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. [quoted text, click to view] > 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" [quoted text, click to view] <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 > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |