bmcnulty
asked on
Converting Bits to INT
Hey everyone,
I have a SQL table that is full of Bit type fields, typically i avoid using these because our front-end Access data base hates them if there is a null value..I've tried updating the nulls to 0. I've made sure on the trouble fields that allow null is not checked...i've ensured we have a timestamp. Up until now i got away with re-linking the table after the changes and it started working. One table just wont work no matter what i do. SO the question is - Can i convert these bit fields to INT or TinyINT? In access i created a local copy of the trouble table with structure and changed the data type there, and ran an append query to grab all the info from the original tabl and, then renamed the new table to so all queries and forms now look at the newly created table..i have alot of testing to do but i wanted to see if this would not be a problem if i was to recreate the table in sql with INT instead of BIT and append the data over.
I have a SQL table that is full of Bit type fields, typically i avoid using these because our front-end Access data base hates them if there is a null value..I've tried updating the nulls to 0. I've made sure on the trouble fields that allow null is not checked...i've ensured we have a timestamp. Up until now i got away with re-linking the table after the changes and it started working. One table just wont work no matter what i do. SO the question is - Can i convert these bit fields to INT or TinyINT? In access i created a local copy of the trouble table with structure and changed the data type there, and ran an append query to grab all the info from the original tabl and, then renamed the new table to so all queries and forms now look at the newly created table..i have alot of testing to do but i wanted to see if this would not be a problem if i was to recreate the table in sql with INT instead of BIT and append the data over.
@PatHartman and I have disagreements about bit fields.
I have learned to disallow nulls on the SQL Server, and run on the server an update query that changes any nulls to zero. You can't do that on the Access end because Access doesn't have anyway to display a null in a bit field, and so the update query in Access doesn't always work
You have discovered, as have I, that Access is ...a bit squirrelly... about what constitutes TRUE when it comes to a SQL Sever linked table.
You can use TRUE as a criteria -- and get no records--sometimes
You can use 1 as a criteria -- and get no records--sometimes
You can use -1 as a criteria--and get no records--sometimes
It can make you crazy...right up until you do the needful thing that @PatHartman doesn't like.
If ...IF...IF there are no nulls, then you can use 0 for False and <> 0 for True.
That's absolutely bulletproof.
I've tried int and tinyint.
It's a pain, because Access knows those aren't Boolean fields and gets angry.
Once you disallow null and know to use <>0 for True, then things smooth out pretty quick.
As always, YMMV
Nick67
I have learned to disallow nulls on the SQL Server, and run on the server an update query that changes any nulls to zero. You can't do that on the Access end because Access doesn't have anyway to display a null in a bit field, and so the update query in Access doesn't always work
You have discovered, as have I, that Access is ...a bit squirrelly... about what constitutes TRUE when it comes to a SQL Sever linked table.
You can use TRUE as a criteria -- and get no records--sometimes
You can use 1 as a criteria -- and get no records--sometimes
You can use -1 as a criteria--and get no records--sometimes
It can make you crazy...right up until you do the needful thing that @PatHartman doesn't like.
If ...IF...IF there are no nulls, then you can use 0 for False and <> 0 for True.
That's absolutely bulletproof.
I've tried int and tinyint.
It's a pain, because Access knows those aren't Boolean fields and gets angry.
Once you disallow null and know to use <>0 for True, then things smooth out pretty quick.
As always, YMMV
Nick67
ASKER
Thanks for the reply's. The issue we have with the bits isn't displaying their value in Access, because i do use Nz(your_checkbox, 0) = -1 method for those. The problem we keep bumping into with older tables that have bits is updating a record in the table..constant write conlifcts and chasing my tail finding the problem or working around it...I've have good luck with using int/tiny int as a source for check boxes ( our senior dev also avoids bits and uses int).
because I do use Nz(your_checkbox, 0) = -1
You'll hit grief with that.
Only Access sees -1 as true
The minute you go to SQL Server, you should look for all that -1 logic and change it to TRUE
Access can generally do a good job with a bit field that has no nulls
Use -1 instead of True and it all starts going south.
The problem we keep bumping into with older tables that have bits is updating a record in the table..constant write conflicts
If you try this
Dim rs as recordset
Set rs = Currentdb("Select SomeBitField from SomeTable where id = someid;",dbopendynaset, dbseechanges)
with rs
.Edit
!SomeBitField = -1
.Update
End with
You can expect it to fail.
If the -1 gets through to SQL Server, it's an invalid value and not going to happen.
Access will not mind the -1, and You'll get weird results.
!SomeBitField = True
or
!SomeBitField = Not False
will both work
Use True when you mean true, and not -1.
If, and only if, you have problems, then use <>0 or Not False
our senior dev also avoids bits
When I first switched to SQL Server, Access was really squirrelly with True.
Subsequent updates have made Access much better at always successfully handling the -1 to 1 transition, but successful old habits die hard.
Many devs who worked extensively in A97 still don't allow controlnames to be the same as fieldnames -- despite the fact that it is now the default for bound controls and has never caused me a problem in A2003+
You'll hit grief with that.
Only Access sees -1 as true
The minute you go to SQL Server, you should look for all that -1 logic and change it to TRUE
Access can generally do a good job with a bit field that has no nulls
Use -1 instead of True and it all starts going south.
The problem we keep bumping into with older tables that have bits is updating a record in the table..constant write conflicts
If you try this
Dim rs as recordset
Set rs = Currentdb("Select SomeBitField from SomeTable where id = someid;",dbopendynaset, dbseechanges)
with rs
.Edit
!SomeBitField = -1
.Update
End with
You can expect it to fail.
If the -1 gets through to SQL Server, it's an invalid value and not going to happen.
Access will not mind the -1, and You'll get weird results.
!SomeBitField = True
or
!SomeBitField = Not False
will both work
Use True when you mean true, and not -1.
If, and only if, you have problems, then use <>0 or Not False
our senior dev also avoids bits
When I first switched to SQL Server, Access was really squirrelly with True.
Subsequent updates have made Access much better at always successfully handling the -1 to 1 transition, but successful old habits die hard.
Many devs who worked extensively in A97 still don't allow controlnames to be the same as fieldnames -- despite the fact that it is now the default for bound controls and has never caused me a problem in A2003+
ASKER
Good points. I typically don't have an issue with -1 and have also seen the same write conflict errors using -1 or true. I believe the main issue is the fact that alot of the older tables allow nulls in bit fields...I can update that to not allow null and update the data, but in alot of cases the old code is looking for a null and converting it to a 0...I'm really thinking the best route to go at this point is to convert those to Int. I've never had an issue using -1, true, false, 0 or 1 as a value from a checkbox while updating an Int field.
So, if you think my plan of Making a new Table structured the same but with int instead of bit and appending the data over will not blow up in my face i think that's the way to go here.
So, if you think my plan of Making a new Table structured the same but with int instead of bit and appending the data over will not blow up in my face i think that's the way to go here.
So, if you think my plan of Making a new Table structured the same but with int instead of bit and appending the data over will not blow up in my face i think that's the way to go here.
Oh no, I am not saying that at ALL.
There'll be plenty of Boolean logic go bang if you do that because a tinyint field won't take a negative and an int field will NOT be seen by Access as a Boolean. So then, you have to run all over the place throwing in
CBool(TheFieldThatShouldbe
and
CInt(TheResultThatIsABoole
That's no fun to do, and no fun to maintain.
in alot of cases the old code is looking for a null and converting it to a 0
Is that worded poorly or did someone really bass-ackward that?
This is one thing, and good
If Nz(SomeField, False) = False then... 'coerce any remnant nulls to false
This, not so much
Dim MyBoolean 'False if null, true if any value exists, but we've only ever bothered entering true, so great!
MyBoolean = Not IsNull(MyField)
I've never had an issue using -1, true, false, 0 or 1 as a value from a checkbox while updating an Int field.
That's not surprising.
A bit field can be a monster that way, though, and was why I had done some bit-to-int conversions long ago.
There's two way of fixing that wagon but good when you encounter it.
One is to replace a checkbox with an option group. You have complete control over what value each control in the option group represents.
The other is to unbind the checkbox (if it isn't on a continuous form)
Then you throw an invisible textbox bound to the field on the form
In the current event of the form, you toggle the checkbox based on the invisible control
In the AfterUpdate Event of the checkbox, you write code to change the field value that the checkbox used to be bound to.
For me, both beat the hell out of dealing with the problems posed by storing bits-as-int
Oh no, I am not saying that at ALL.
There'll be plenty of Boolean logic go bang if you do that because a tinyint field won't take a negative and an int field will NOT be seen by Access as a Boolean. So then, you have to run all over the place throwing in
CBool(TheFieldThatShouldbe
and
CInt(TheResultThatIsABoole
That's no fun to do, and no fun to maintain.
in alot of cases the old code is looking for a null and converting it to a 0
Is that worded poorly or did someone really bass-ackward that?
This is one thing, and good
If Nz(SomeField, False) = False then... 'coerce any remnant nulls to false
This, not so much
Dim MyBoolean 'False if null, true if any value exists, but we've only ever bothered entering true, so great!
MyBoolean = Not IsNull(MyField)
I've never had an issue using -1, true, false, 0 or 1 as a value from a checkbox while updating an Int field.
That's not surprising.
A bit field can be a monster that way, though, and was why I had done some bit-to-int conversions long ago.
There's two way of fixing that wagon but good when you encounter it.
One is to replace a checkbox with an option group. You have complete control over what value each control in the option group represents.
The other is to unbind the checkbox (if it isn't on a continuous form)
Then you throw an invisible textbox bound to the field on the form
In the current event of the form, you toggle the checkbox based on the invisible control
In the AfterUpdate Event of the checkbox, you write code to change the field value that the checkbox used to be bound to.
For me, both beat the hell out of dealing with the problems posed by storing bits-as-int
ASKER
Oh no, I am not saying that at ALL.
There'll be plenty of Boolean logic go bang if you do that because a tinyint field won't take a negative and an int field will NOT be seen by Access as a Boolean. So then, you have to run all over the place throwing in
I just created a test table by copying the structure and changed all the yes/no to numbers. Worked fine after i appended the data over - but i guess it's a different story for doing the same on the SQL server?
in alot of cases the old code is looking for a null and converting it to a 0
Is that worded poorly or did some really bass-ackward that?
No, alot of the older code looks for a null and triggers a value based on the null, the null simply means the record has not been used yet...something like:
If IsNull(field1) then
do something
end if
And in alot of cases i stumble on they are Bit fields.
I have tried the hidden text box, i've tried setting a combo bow with a text field behind, i just tried using True instead of -1 or 1. It will not save. On top of that i can not enter a value into the table directly unless i run an update query..Driving me crazy!
There'll be plenty of Boolean logic go bang if you do that because a tinyint field won't take a negative and an int field will NOT be seen by Access as a Boolean. So then, you have to run all over the place throwing in
I just created a test table by copying the structure and changed all the yes/no to numbers. Worked fine after i appended the data over - but i guess it's a different story for doing the same on the SQL server?
in alot of cases the old code is looking for a null and converting it to a 0
Is that worded poorly or did some really bass-ackward that?
No, alot of the older code looks for a null and triggers a value based on the null, the null simply means the record has not been used yet...something like:
If IsNull(field1) then
do something
end if
And in alot of cases i stumble on they are Bit fields.
I have tried the hidden text box, i've tried setting a combo bow with a text field behind, i just tried using True instead of -1 or 1. It will not save. On top of that i can not enter a value into the table directly unless i run an update query..Driving me crazy!
It will not save. On top of that i can not enter a value into the table directly unless i run an update query..Driving me crazy!
I feel your pain.
Now it's time to be systematic.
1.
On the SQL Server through MS SQL Server Management Studio, open the table with Edit (in my case I made it All, default was like Top 200) Rows.
What values are in your bit field? 1, 0, and NULL?
True, False, and NULL?
Or checkboxes?
No Nulls? Good!
No Nulls permitted? Good!
A default value for the field assigned so no nulls can ever get in? Good!
You have a timestamp field I suspect
Can you change a value here and make it stick?
By rights, here only 0,1, and NULL will be valid or True, False and NULL depending on your settings
If not, then the problem is here in SQL Server, and not in Access!
But if the server is the problem I have no help for you.
2.
Delete the linked table and recreate it.
If there was cruft, this is simple to do and may fix the problem.
Now, open the table in Access.
There's possibilities.
Change a record. Zero should always work.
If -1 is displayed for True, then anything that isn't zero will go in but be changed to -1
Try to make a change and see if it sticks.
If 1 is displayed for true then things are tricky.
Look here;
All three are bit fields from the same table
The middle one is actually all nulls
The middle and right will let me enter everything. Anything not zero gets flipped to -1
The left one will ONLY take 0 and 1
Odd, yes? Why Access behaves differently, I'll never know.
I feel your pain.
Now it's time to be systematic.
1.
On the SQL Server through MS SQL Server Management Studio, open the table with Edit (in my case I made it All, default was like Top 200) Rows.
What values are in your bit field? 1, 0, and NULL?
True, False, and NULL?
Or checkboxes?
No Nulls? Good!
No Nulls permitted? Good!
A default value for the field assigned so no nulls can ever get in? Good!
You have a timestamp field I suspect
Can you change a value here and make it stick?
By rights, here only 0,1, and NULL will be valid or True, False and NULL depending on your settings
If not, then the problem is here in SQL Server, and not in Access!
But if the server is the problem I have no help for you.
2.
Delete the linked table and recreate it.
If there was cruft, this is simple to do and may fix the problem.
Now, open the table in Access.
There's possibilities.
Change a record. Zero should always work.
If -1 is displayed for True, then anything that isn't zero will go in but be changed to -1
Try to make a change and see if it sticks.
If 1 is displayed for true then things are tricky.
Look here;
All three are bit fields from the same tableThe middle one is actually all nulls
The middle and right will let me enter everything. Anything not zero gets flipped to -1
The left one will ONLY take 0 and 1
Odd, yes? Why Access behaves differently, I'll never know.
One table just wont work no matter what i do.
When you create a linked table, Access asks you to identify column(s) to uniquely identify each record.
Access, on its side, uses these responses as a Primary Key, and makes the table read-write.
If that information gets lost (say in using the Linked Table Manager) the table suddenly becomes read-only for a lot of operations.
Is the whole table, or only the bit fields, squirrelly?
When you create a linked table, Access asks you to identify column(s) to uniquely identify each record.
Access, on its side, uses these responses as a Primary Key, and makes the table read-write.
If that information gets lost (say in using the Linked Table Manager) the table suddenly becomes read-only for a lot of operations.
Is the whole table, or only the bit fields, squirrelly?
ASKER
Is the whole table, or only the bit fields, squirrelly?
The whole table, i cant change any data directly unless i do an update query. However i've just seen that a few of the bit fields allow null..but also get a default value? Changing data is fine on the SQL server though.
The whole table, i cant change any data directly unless i do an update query. However i've just seen that a few of the bit fields allow null..but also get a default value? Changing data is fine on the SQL server though.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Same result, however i noticed a null in the table..one of the bits was allowing nulls and had no default...seems ok now. Thanks.
Hi,
the thing with True/False is that VBA will automatically handle these internal constants which stands for -1/0 as datatype Boolean. If you use -1/0 as a value, it will by default be handled as Integer (the smallest datatype which VBA can use with negative values).
You can see that if you try "?VarType(True)" and "?VarType(-1)" in the VBA immediate window.
But VBA also does a lot of implicit datatype changing. If you open a recordset (either DAO or ADO) the field will get it's datatype from the database driver, so if it was defined in an Access database as "Yes/No" (where Access databases cannot use NULL in Yes/No fields) the datatype is "dbBoolean". It doesn't matter in any case if you assign True or -1 or False or 0 to this field, as the target datatype only accepts boolean values it will automatically converted to boolean. You can also use -1& as a long value or, if you like 123.4 as a decimal. Any number value which is not 0 will be converted to True, and 0 to False. That also works with strings, try to assign "1" or "0" as string to such field, works also. So no matter what you do in VBA, the implicit conversion works very good and you can't do anything wrong here.
If you use ACCDB/MDB you have the curse of the ACE/JET engine in between your form and the SQL Server. That means, any datatype will be converted between a database which has so many more datatypes and possibilities and the really poor selection of datatypes in Access. The need to use a timestamp column is the proof for that problem: Without that column each field in the form will be compared with each field in the database table to see if anyone else has changed something. And that means: Each datatype of ACE/JET will be converted to SQL Server datatypes for this comparison, and not very seldom the result is that the conversion also changes the data. Most often it are decimal fields with rounding problems, but the bit field is also an example: ACE/JET simply has no NULL for Yes/No fields, no "Required" setting in the table definition. That means, it cannot convert the NULL correct as there is no pendant in Access. It converts it, as there is no other possibility, to 0 (False). If you change anything else in the record and you try to save that it can happen that the comparison of the fields says "NULL is not the same as 0" and so "someone else" must have changed the record. By using a timestamp column this should not happen as the comparison doesn't happen anymore, but the problem still exists: If you would change another value in the record and try to save that, the Yes/No bit field would try to save 0 although you do not want to change this field.
Makes no sense to work with ACE/JET with bit fields.
You could guess that a Pass-Through-Query could break that problem (if you do not need an updatable query because PTs are read-only always) - it's not the case. Unfortunately the PT query executes the query as T-SQL, but when the data comes back it will also be converted to ACE/JET like any other query. So forget that.
But there is a solution for the problem: Simply use ADO to directly open an ADO recordset and then assign the filled recordset to the form's recordset property. Forms can use ADO and DAO recordsets, so that's no problem. You only need to make sure that the recordset is updatable, that means, you are forced to use the SQLOLEDB driver and you must set the cursor location to adUseServer (which in fact means the recordset will be handled by the driver, not the frontend, it does not mean that the SQL Server uses a remote cursor to handle that, so it's no problem).
And - surprise - the NULL will now be handled as NULL by the checkbox (of course TripleState must be turned on). You can work with bits without problem now because ADO works with the correct datatypes of SQL Server directly. That's why I prefer to use ADO.
You can of course change the datatype to smallint (tinyint is Byte which is not allowed to use negative values which would return an error if you try to set the checkbox to True - normally a Byte can also use negative values in the same way like bigger integer datatypes but it's a strange convention in modern languages that Byte can only be 0 to 255 and not -127 to 128 like i.e. in Assembler, so you are forced to use at least smallint). The only thing is that you must make sure, maybe with check constraints, that the value will be nothing else than 0,1 (or -1) and NULL.
Advantage of an integer datatype is that you can sum their values which is not possible with a bit datatype.
I know why I develop further on with ADPs instead of ACCDB where I never have such datatype conversion types. There are other issues also, not only the problems mentioned here.
Cheers,
Christian
the thing with True/False is that VBA will automatically handle these internal constants which stands for -1/0 as datatype Boolean. If you use -1/0 as a value, it will by default be handled as Integer (the smallest datatype which VBA can use with negative values).
You can see that if you try "?VarType(True)" and "?VarType(-1)" in the VBA immediate window.
But VBA also does a lot of implicit datatype changing. If you open a recordset (either DAO or ADO) the field will get it's datatype from the database driver, so if it was defined in an Access database as "Yes/No" (where Access databases cannot use NULL in Yes/No fields) the datatype is "dbBoolean". It doesn't matter in any case if you assign True or -1 or False or 0 to this field, as the target datatype only accepts boolean values it will automatically converted to boolean. You can also use -1& as a long value or, if you like 123.4 as a decimal. Any number value which is not 0 will be converted to True, and 0 to False. That also works with strings, try to assign "1" or "0" as string to such field, works also. So no matter what you do in VBA, the implicit conversion works very good and you can't do anything wrong here.
If you use ACCDB/MDB you have the curse of the ACE/JET engine in between your form and the SQL Server. That means, any datatype will be converted between a database which has so many more datatypes and possibilities and the really poor selection of datatypes in Access. The need to use a timestamp column is the proof for that problem: Without that column each field in the form will be compared with each field in the database table to see if anyone else has changed something. And that means: Each datatype of ACE/JET will be converted to SQL Server datatypes for this comparison, and not very seldom the result is that the conversion also changes the data. Most often it are decimal fields with rounding problems, but the bit field is also an example: ACE/JET simply has no NULL for Yes/No fields, no "Required" setting in the table definition. That means, it cannot convert the NULL correct as there is no pendant in Access. It converts it, as there is no other possibility, to 0 (False). If you change anything else in the record and you try to save that it can happen that the comparison of the fields says "NULL is not the same as 0" and so "someone else" must have changed the record. By using a timestamp column this should not happen as the comparison doesn't happen anymore, but the problem still exists: If you would change another value in the record and try to save that, the Yes/No bit field would try to save 0 although you do not want to change this field.
Makes no sense to work with ACE/JET with bit fields.
You could guess that a Pass-Through-Query could break that problem (if you do not need an updatable query because PTs are read-only always) - it's not the case. Unfortunately the PT query executes the query as T-SQL, but when the data comes back it will also be converted to ACE/JET like any other query. So forget that.
But there is a solution for the problem: Simply use ADO to directly open an ADO recordset and then assign the filled recordset to the form's recordset property. Forms can use ADO and DAO recordsets, so that's no problem. You only need to make sure that the recordset is updatable, that means, you are forced to use the SQLOLEDB driver and you must set the cursor location to adUseServer (which in fact means the recordset will be handled by the driver, not the frontend, it does not mean that the SQL Server uses a remote cursor to handle that, so it's no problem).
And - surprise - the NULL will now be handled as NULL by the checkbox (of course TripleState must be turned on). You can work with bits without problem now because ADO works with the correct datatypes of SQL Server directly. That's why I prefer to use ADO.
You can of course change the datatype to smallint (tinyint is Byte which is not allowed to use negative values which would return an error if you try to set the checkbox to True - normally a Byte can also use negative values in the same way like bigger integer datatypes but it's a strange convention in modern languages that Byte can only be 0 to 255 and not -127 to 128 like i.e. in Assembler, so you are forced to use at least smallint). The only thing is that you must make sure, maybe with check constraints, that the value will be nothing else than 0,1 (or -1) and NULL.
Advantage of an integer datatype is that you can sum their values which is not possible with a bit datatype.
I know why I develop further on with ADPs instead of ACCDB where I never have such datatype conversion types. There are other issues also, not only the problems mentioned here.
Cheers,
Christian
@Christian
The problem is that ADP is now deprecated.
So regardless of one's opinions, that is trying to row up a rapidly flowing stream.
I am still on Access 2003 and SQL Server 2008 R2 using .mdb
If MS ever offers something more productive in Access in terms of the help files, the ability to manipulate form properties on the live objects like you can in Access 2003, and the return of the Database Window, I may move on. With ADP, Access 2010 and Server 2008 R2 are the end of the road.
If you use ACCDB/MDB you have the curse of the ACE/JET engine in between your form and the SQL Server.
Like it or not, curse it or not, it is the only way forward.
Or abandon Access, and perhaps MS altogether.
ADO has its place. So does DAO. With a SQL Server backend, .accdb offers nothing of use. ADP's had their place -- and for this we must all roundly curse MS, the Cloud and Azure -- they've lost it. Until MS WAKES UP!!!! and realizes that the 'surge' in Cloud usage is just the dead-cat bounce, we're all dans la merde, as the French put it.
Nick67
The problem is that ADP is now deprecated.
So regardless of one's opinions, that is trying to row up a rapidly flowing stream.
I am still on Access 2003 and SQL Server 2008 R2 using .mdb
If MS ever offers something more productive in Access in terms of the help files, the ability to manipulate form properties on the live objects like you can in Access 2003, and the return of the Database Window, I may move on. With ADP, Access 2010 and Server 2008 R2 are the end of the road.
If you use ACCDB/MDB you have the curse of the ACE/JET engine in between your form and the SQL Server.
Like it or not, curse it or not, it is the only way forward.
Or abandon Access, and perhaps MS altogether.
ADO has its place. So does DAO. With a SQL Server backend, .accdb offers nothing of use. ADP's had their place -- and for this we must all roundly curse MS, the Cloud and Azure -- they've lost it. Until MS WAKES UP!!!! and realizes that the 'surge' in Cloud usage is just the dead-cat bounce, we're all dans la merde, as the French put it.
Nick67
Hi Nick,
That is self-contradictory...:-)
On one hand you (and many other often) say that ADP is deprecated so "don't use it anymore" - on the other hand you are going on working with the really outdated A2003 and MDB. Doesn't make sense. I would understand (not share) your opinion if you are only working with the newest available Access version.
For the same reason why you do not want to use >=A2007 I do not want any other than ADP anymore, I'm currently developing a very big ERP system with A2010, SQL Server 2008R2 and ADP for a worldwide application for an automotive industry company. Same as your A2003 database: It will go on working the next 30 years without problem. Although I'm sure that at least my project will not get that old..:-)
With SQL Server as backend, ADO is the really best method to use as you don't need the JET/ACE stuff in between and can work with SQL Server data without headache.
I fully agree in your opinion about Cloud, Azure and (what you didn't mention) A2013 which is in my opinion the wrong way. It's good only for Microsoft, they get their hands on data on thousands of companies, the data itself and the data about the usage of the products, they can sell more services and computing time on servers in data centers - a really very big business. But I also see no real advantage for a company which want to use an in-house database. I don't think that MS is not aware of what developers would prefer to have like ongoing support for ADP, a better VBA or even .NET technology in Access or other great stuff. The point is, it will not raise the amount of money to earn. If one developer (like the example of my project) develops an application where thousands of people around the world can use that, it means, MS earns the money for one full version licence of my Access, one licence for a fat database server and Windows server licence - and that's all. Any user runs a Runtime version of Access for free and as many users as the hardware can handle can connect to the database and work with the data - nothing to earn here. I can go on developing and make sure that all is working the next 20 years if I want to. Nothing to earn at all the time for MS. Cost would only be created in buying bigger harddisks and maybe some time in some single licences for SQL Server and Windows for a distributed server system to make the system more performant - little to earn.
And the opposite? If we all use Office 365 and Azure, MS earns every year for Office, every year for the service to host SQL Server, for backup of the data, and so on - the company don't need to invest in bigger hardware or admins and they have a regular amount of money to pay which is calculatable, so good for both sides. So really - nobody is interested in what we developers would like to have, we are also simply not enough people to change anything here.
If half of the world screams about the return of the start menu that is something which can slightly move MS to do something here, but why should they invest money in a technology which in the end means that they earn less money? Not very likely. So really, I cannot hear the old sentence "ADP is deprecated, don't use that" anymore, it's nonsense. If I like to dig my garden with a rusty spade, why not? It works as good as to buy a new one, the new one only looks better and maybe is rustproof, but the result is the same and my garden will get digged with both in the same time. The new one breaks because the handle is made with plastic, my old one still goes on digging because it was made with a wooden handle from an oak tree.
So why don't you go on to work with a newer Access? It's the only way forward. Or abandon Access if you don't want to use a modern Access with ribbons and navigation window...;-)
Cheers,
Christian
The problem is that ADP is now deprecated.
So regardless of one's opinions, that is trying to row up a rapidly flowing stream.
I am still on Access 2003 and SQL Server 2008 R2 using .mdb
That is self-contradictory...:-)
On one hand you (and many other often) say that ADP is deprecated so "don't use it anymore" - on the other hand you are going on working with the really outdated A2003 and MDB. Doesn't make sense. I would understand (not share) your opinion if you are only working with the newest available Access version.
For the same reason why you do not want to use >=A2007 I do not want any other than ADP anymore, I'm currently developing a very big ERP system with A2010, SQL Server 2008R2 and ADP for a worldwide application for an automotive industry company. Same as your A2003 database: It will go on working the next 30 years without problem. Although I'm sure that at least my project will not get that old..:-)
With SQL Server as backend, ADO is the really best method to use as you don't need the JET/ACE stuff in between and can work with SQL Server data without headache.
I fully agree in your opinion about Cloud, Azure and (what you didn't mention) A2013 which is in my opinion the wrong way. It's good only for Microsoft, they get their hands on data on thousands of companies, the data itself and the data about the usage of the products, they can sell more services and computing time on servers in data centers - a really very big business. But I also see no real advantage for a company which want to use an in-house database. I don't think that MS is not aware of what developers would prefer to have like ongoing support for ADP, a better VBA or even .NET technology in Access or other great stuff. The point is, it will not raise the amount of money to earn. If one developer (like the example of my project) develops an application where thousands of people around the world can use that, it means, MS earns the money for one full version licence of my Access, one licence for a fat database server and Windows server licence - and that's all. Any user runs a Runtime version of Access for free and as many users as the hardware can handle can connect to the database and work with the data - nothing to earn here. I can go on developing and make sure that all is working the next 20 years if I want to. Nothing to earn at all the time for MS. Cost would only be created in buying bigger harddisks and maybe some time in some single licences for SQL Server and Windows for a distributed server system to make the system more performant - little to earn.
And the opposite? If we all use Office 365 and Azure, MS earns every year for Office, every year for the service to host SQL Server, for backup of the data, and so on - the company don't need to invest in bigger hardware or admins and they have a regular amount of money to pay which is calculatable, so good for both sides. So really - nobody is interested in what we developers would like to have, we are also simply not enough people to change anything here.
If half of the world screams about the return of the start menu that is something which can slightly move MS to do something here, but why should they invest money in a technology which in the end means that they earn less money? Not very likely. So really, I cannot hear the old sentence "ADP is deprecated, don't use that" anymore, it's nonsense. If I like to dig my garden with a rusty spade, why not? It works as good as to buy a new one, the new one only looks better and maybe is rustproof, but the result is the same and my garden will get digged with both in the same time. The new one breaks because the handle is made with plastic, my old one still goes on digging because it was made with a wooden handle from an oak tree.
Like it or not, curse it or not, it is the only way forward.
Or abandon Access, and perhaps MS altogether.
So why don't you go on to work with a newer Access? It's the only way forward. Or abandon Access if you don't want to use a modern Access with ribbons and navigation window...;-)
Cheers,
Christian
Doesn't make sense.
You didn't or couldn't read between the lines.
I am presently on SQL Server 2008 R2 -- but there are no impediments to a version upgrade if and when circumstances dictate.
I am presently on Access 2003 -- because of the versions presently available, it represents the best version to develop on, for me. My users are on a mix of Access 2003, 2010 and 2013. And there is no impediment to all of us moving to new versions going forward, if and when that makes sense.
ADP has no path forward. You have been FoxPro'd.
With SQL Server as backend, ADO is the really best method to use as you don't need the JET/ACE stuff in between and can work with SQL Server data without headache. I don't have headaches. I have DAO linked tables, DAO and ADO recordsets as the situation dictates, Views, Indexed Views, stored procedures and functions as needed. It all works rather well.
I can go on developing and make sure that all is working the next 20 years if I want to. I doubt it. You won't have Server 2008 R2 and SQL Server 2008 R2 in production 20 years from now. You won't have Access 2010 in production 20 years from now. 5 years, yes. 10 years, doubtful. 20 years, not a chance.
If we all use Office 365 and Azure, MS earns every year for Office, every year for the service to host SQL Server, for backup of the data, and so on - the company don't need to invest in bigger hardware or admins and they have a regular amount of money to pay which is calculatable, so good for both sides. This is where MS has lost its mind. My ISP wants $5500 to bring fiber to the door and $1350/ month for symmetric 10 Mbps DSL. 'You can put a terabyte online for free!' Hmmm, let do some math. 1 terabyte --> 8 terabits. Without the expensive DSL, my ADSL gets maybe 400kbps upload. So, saturating my network connection, it takes ~ 20 million seconds to upload a terabit. 60 seconds per minute, 60 minutes per hour, 24 hours per day. 231 days to push up a terabyte. Let me get right on that. There's vast swaths of the market that the cloud will never work for. MS needs to see it for what it is -- a niche, not the mainstream.
If I like to dig my garden with a rusty spade, why not?
Wrong analogy. If you want to continue to bale your hay with a Massey Harris 44 tractor, you could. Right up until something breaks and you can no longer get parts. And then what?
So why don't you go on to work with a newer Access? It's the only way forward.
If and when something comparably efficient to Access 2003 comes along, I will. I have a way to move forward.
ADP users, like FoxPro users before them, do not.
And when the inevitable day comes that your application can no longer run, will people curse that the effort put into it was not directed toward a non-deprecated platform?
You didn't or couldn't read between the lines.
I am presently on SQL Server 2008 R2 -- but there are no impediments to a version upgrade if and when circumstances dictate.
I am presently on Access 2003 -- because of the versions presently available, it represents the best version to develop on, for me. My users are on a mix of Access 2003, 2010 and 2013. And there is no impediment to all of us moving to new versions going forward, if and when that makes sense.
ADP has no path forward. You have been FoxPro'd.
With SQL Server as backend, ADO is the really best method to use as you don't need the JET/ACE stuff in between and can work with SQL Server data without headache. I don't have headaches. I have DAO linked tables, DAO and ADO recordsets as the situation dictates, Views, Indexed Views, stored procedures and functions as needed. It all works rather well.
I can go on developing and make sure that all is working the next 20 years if I want to. I doubt it. You won't have Server 2008 R2 and SQL Server 2008 R2 in production 20 years from now. You won't have Access 2010 in production 20 years from now. 5 years, yes. 10 years, doubtful. 20 years, not a chance.
If we all use Office 365 and Azure, MS earns every year for Office, every year for the service to host SQL Server, for backup of the data, and so on - the company don't need to invest in bigger hardware or admins and they have a regular amount of money to pay which is calculatable, so good for both sides. This is where MS has lost its mind. My ISP wants $5500 to bring fiber to the door and $1350/ month for symmetric 10 Mbps DSL. 'You can put a terabyte online for free!' Hmmm, let do some math. 1 terabyte --> 8 terabits. Without the expensive DSL, my ADSL gets maybe 400kbps upload. So, saturating my network connection, it takes ~ 20 million seconds to upload a terabit. 60 seconds per minute, 60 minutes per hour, 24 hours per day. 231 days to push up a terabyte. Let me get right on that. There's vast swaths of the market that the cloud will never work for. MS needs to see it for what it is -- a niche, not the mainstream.
If I like to dig my garden with a rusty spade, why not?
Wrong analogy. If you want to continue to bale your hay with a Massey Harris 44 tractor, you could. Right up until something breaks and you can no longer get parts. And then what?
So why don't you go on to work with a newer Access? It's the only way forward.
If and when something comparably efficient to Access 2003 comes along, I will. I have a way to move forward.
ADP users, like FoxPro users before them, do not.
And when the inevitable day comes that your application can no longer run, will people curse that the effort put into it was not directed toward a non-deprecated platform?
ASKER
I have DAO linked tables, DAO and ADO recordsets as the situation dictates, Views, Indexed Views, stored procedures and functions as needed. It all works rather well.
Is this something you do in code? instead of:
set rs= currentdb.openrecordset("b
Is this something you do in code? instead of:
set rs= currentdb.openrecordset("b
Is this something you do in code?
Some of it is, anyway
If you only require read-only (i.e. reports) data, to create a pass-through query in Access that sends a T-SQL request for the results of a stored procedure works very well.
To base a form of a stored procedure is much, much easier in an ADP, but you can do that in an mdb/accdb as well, but you then have to use code to push the data changes to the server. I have one form that takes about 60 seconds to load from a query based on link tables and 3 seconds from a stored procedure. For the three most-likely to be edited controls, I put a transparent control over them to capture any text input, and push changes in the AfterUpdate events of those controls. There's a button to permit full editing, but then the user has to wait for the load. saves them a boat load of time.
I have other forms that would take a long time to load from a query that instead I've created an Indexed View on the server and base the form on that. The data is assembled on the server, and that is more efficient in that case.
In certain cases, ADO recordset can be wicked faster than their DAO counter parts.
This code was good, took about 3 seconds to run a stored procedure and return a result
The stored procedure replaced a query with a lot of left joins that took 15 seconds to run
But this takes 3/10 of a second to run
It's always a work in progress.
Looking at what is slow, and investigating causes, and trying alternative strategies.
Sometimes ADO is blindingly fast. Sometimes not so much.
Sometimes there's a big difference between getting a certain dataset from linked tables, queries of linked tables, from a linked View or from a stored procedure.
And sometimes the performance differences are negligible.
There are no hard-and-fast rules.
Some of it is, anyway
If you only require read-only (i.e. reports) data, to create a pass-through query in Access that sends a T-SQL request for the results of a stored procedure works very well.
To base a form of a stored procedure is much, much easier in an ADP, but you can do that in an mdb/accdb as well, but you then have to use code to push the data changes to the server. I have one form that takes about 60 seconds to load from a query based on link tables and 3 seconds from a stored procedure. For the three most-likely to be edited controls, I put a transparent control over them to capture any text input, and push changes in the AfterUpdate events of those controls. There's a button to permit full editing, but then the user has to wait for the load. saves them a boat load of time.
I have other forms that would take a long time to load from a query that instead I've created an Indexed View on the server and base the form on that. The data is assembled on the server, and that is more efficient in that case.
In certain cases, ADO recordset can be wicked faster than their DAO counter parts.
This code was good, took about 3 seconds to run a stored procedure and return a result
The stored procedure replaced a query with a lot of left joins that took 15 seconds to run
'DAO code
Dim SQL as string
SQL = "exec qryjobTypesTest2 " & myJobID
qdf.SQL = SQL
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)'If rs.RecordCount = 0 Then
GenericJobType = ""
Else
GenericJobType = Nz(rs!MyJobType, "")
End IfBut this takes 3/10 of a second to run
Dim dbCon As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
dbCon.ConnectionString = Forms!tblDummy!ADOConnString
dbCon.Open
With cmd
.CommandType = adCmdStoredProc
.CommandText = "qryjobTypesTest2"
.Parameters.Append .CreateParameter("@JobID", adInteger, adParamInput, , myJobID)
.ActiveConnection = dbCon
.NamedParameters = True
Set rst = .Execute
End With
If rst.RecordCount = 0 Then
GenericJobType = ""
Else
rst.MoveFirst
GenericJobType = Nz(rst!MyJobType, "")
'MsgBox rst!JobID
End If
rst.Close
Set rst = Nothing
Set cmd = Nothing
dbCon.CloseIt's always a work in progress.
Looking at what is slow, and investigating causes, and trying alternative strategies.
Sometimes ADO is blindingly fast. Sometimes not so much.
Sometimes there's a big difference between getting a certain dataset from linked tables, queries of linked tables, from a linked View or from a stored procedure.
And sometimes the performance differences are negligible.
There are no hard-and-fast rules.
Hi Nick,
That's the strange opinion I read often and I really do not understand. Software doesn't disappear magically. I can start a DOS program on my current hardware and it works the same as in 1987. So if I really want to use Word for DOS I can use it today the same as before, using an emulator. My server in my cellar is running since more than 10 years with the same software - I don't need to upgrade it, it does everything it needs to do for me. So if I want to run an ADP in 20 years with SQL Server 2008 I can run it in 20 years the same way I do it now, be sure.
It's only not very likely that a software in a company will be used that long period, but that's in the decision of the company, nothing else.
That is really strange. If you would have used an ADP that would of course easier as you can use a SP directly as recordsource in ADP - but you really don't need to use an ADP to achieve the same with ACCDB/MDB.
Simply execute the SP using ADO und set the recordset property of the form to the returned recordset and you are ready. The SP will only execute once, and the returned recordset handles all the UPDATE/INSERT/DELETE commands without the SP. So there is no need to do such strange things which are annoying for the users.
There is only one issue with this method: If you ever press F5 to requery the form Access tries to execute the SP again and that doesn't work here, ends in an error message. But that is no big problem, simply use the AUTOKEYS macro to catch F5 generally, let it execute a VBA function which forward the requery attempt to the right form and execute the SP with VBA the same like in Form_Load and F5 works again. So in result you get an updatable form which directly works with SQL Server in the same way an ADP handles that and you also can bind any SP to any form you want - I use exactly that method in my ADP so if I really would need to switch off ADPs later on there is not much code to change, I guess nearly nothing.
Simple code to proof that:
Three things are important: The connection string must use exactly the shown drivers (can also be earlier or later than 10.0), the cursor location must be adUseServer (so that the driver can handle the cursor) and adOpenKeyset so that Access can move through the records.
Try it, you'll never need to use two different methods of updating single fields with hidden controls and control switching...
If MS ever decides to end ADO development and don't allow to set ADO recordsets to forms anymore than that's the point where I'll leave the Access route and built frontends with .NET instead - then Access is dead.
Cheers,
Christian
You won't have Server 2008 R2 and SQL Server 2008 R2 in production 20 years from now. You won't have Access 2010 in production 20 years from now. 5 years, yes. 10 years, doubtful. 20 years, not a chance.
That's the strange opinion I read often and I really do not understand. Software doesn't disappear magically. I can start a DOS program on my current hardware and it works the same as in 1987. So if I really want to use Word for DOS I can use it today the same as before, using an emulator. My server in my cellar is running since more than 10 years with the same software - I don't need to upgrade it, it does everything it needs to do for me. So if I want to run an ADP in 20 years with SQL Server 2008 I can run it in 20 years the same way I do it now, be sure.
It's only not very likely that a software in a company will be used that long period, but that's in the decision of the company, nothing else.
For the three most-likely to be edited controls, I put a transparent control over them to capture any text input, and push changes in the AfterUpdate events of those controls.
That is really strange. If you would have used an ADP that would of course easier as you can use a SP directly as recordsource in ADP - but you really don't need to use an ADP to achieve the same with ACCDB/MDB.
Simply execute the SP using ADO und set the recordset property of the form to the returned recordset and you are ready. The SP will only execute once, and the returned recordset handles all the UPDATE/INSERT/DELETE commands without the SP. So there is no need to do such strange things which are annoying for the users.
There is only one issue with this method: If you ever press F5 to requery the form Access tries to execute the SP again and that doesn't work here, ends in an error message. But that is no big problem, simply use the AUTOKEYS macro to catch F5 generally, let it execute a VBA function which forward the requery attempt to the right form and execute the SP with VBA the same like in Form_Load and F5 works again. So in result you get an updatable form which directly works with SQL Server in the same way an ADP handles that and you also can bind any SP to any form you want - I use exactly that method in my ADP so if I really would need to switch off ADPs later on there is not much code to change, I guess nearly nothing.
Simple code to proof that:
Dim strConn As String
strConn = "Provider=Microsoft.Access.OLEDB.10.0;" & _
"Data Provider=SQLOLEDB.1;" & _
"Data Source=TheDatabaseServer;" & _
"Initial Catalog=TheDatabaseName;" & _
"Integrated Security=SSPI;"
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.CursorLocation = adUseServer
cn.Open strConn
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.Open "EXECUTE dbo.procTestSP @intID = 2", cn, adOpenKeyset, adLockOptimistic
Set Me.Recordset = rsThree things are important: The connection string must use exactly the shown drivers (can also be earlier or later than 10.0), the cursor location must be adUseServer (so that the driver can handle the cursor) and adOpenKeyset so that Access can move through the records.
Try it, you'll never need to use two different methods of updating single fields with hidden controls and control switching...
If MS ever decides to end ADO development and don't allow to set ADO recordsets to forms anymore than that's the point where I'll leave the Access route and built frontends with .NET instead - then Access is dead.
Cheers,
Christian
Simply execute the SP using ADO und set the recordset property of the form to the returned recordset and you are ready.
I willing to be wrong, but the recordset returned by a SP is generally read-only unless you are running an ADP.
The plumbing is not there in mdb/accdb
So there is no need to do such strange things which are annoying for the users.
The recordset returned from an SP is read-only. Workarounds are required.
and the returned recordset handles all the UPDATE/INSERT/DELETE commands without the SP.
I am willing to be wrong, but no, in an mdb you can use the recordset you created in code to update the data, but the controls themselves are read-only -- and hence the need for my workarounds. ADP existed for a reason. The forms LOOK the same, but the underlying way that MDB and ADP handle changes in controls is fundamentally different.
I guess nearly nothing.
Nearly everything I am afraid.
Every time you used an SP in a read-write situation, you have a problem, because only in ADP are those actually read-write.
If MS ever decides to end ADO development
It has ended. Long ago.
It ended at ADO 2.8
It got renumbered to 6.0, and that's it
ADO is never getting any new functionality.
ADO.NET, yes. ADO, no. DAO is getting what little new stuff MS is creating, and it is mostly concerned with abominations like multi-valued fields.
don't allow to set ADO recordsets to forms anymore
Bind all you like with ADO -- but unless it is a Select statement, you are read-only.
that's the point where I'll leave the Access route and built frontends with .NET instead
That's where you are heading, then.
That's the strange opinion I read often and I really do not understand. Software doesn't disappear magically
20 years. Windows 95. You aren't going to find it for sale, and you wouldn't run it if you could. Nor will there be drivers for it.
I can start a DOS program on my current hardware and it works the same as in 1987
No, you try getting DOS 6.2.2 running on a new box in real-time mode today.
That is highly unlikely to happen
I've gotten some old 16 bit stuff to run on DosBox, but that's not native.
You can keep a lot of stuff limping along for a very long time.
But the end comes.
I've got Office 97 on diskette -- but I don't think I could get it going natively.
And really, who wants to fire up a VM to run something like that?
So if I want to run an ADP in 20 years with SQL Server 2008 I can run it in 20 years the same way I do it now, be sure.
Dollars to doughnuts you'll hit a hardware impossibility before then.
You gotta do what you gotta do - but I don't think advising people to run with a deprecated technology is good advice.
But you are free to proffer it, and the advisee is free to evaluate it, and I am free to add my two cents.
That's life.
Nick67
I willing to be wrong, but the recordset returned by a SP is generally read-only unless you are running an ADP.
The plumbing is not there in mdb/accdb
So there is no need to do such strange things which are annoying for the users.
The recordset returned from an SP is read-only. Workarounds are required.
and the returned recordset handles all the UPDATE/INSERT/DELETE commands without the SP.
I am willing to be wrong, but no, in an mdb you can use the recordset you created in code to update the data, but the controls themselves are read-only -- and hence the need for my workarounds. ADP existed for a reason. The forms LOOK the same, but the underlying way that MDB and ADP handle changes in controls is fundamentally different.
I guess nearly nothing.
Nearly everything I am afraid.
Every time you used an SP in a read-write situation, you have a problem, because only in ADP are those actually read-write.
If MS ever decides to end ADO development
It has ended. Long ago.
It ended at ADO 2.8
It got renumbered to 6.0, and that's it
ADO is never getting any new functionality.
ADO.NET, yes. ADO, no. DAO is getting what little new stuff MS is creating, and it is mostly concerned with abominations like multi-valued fields.
don't allow to set ADO recordsets to forms anymore
Bind all you like with ADO -- but unless it is a Select statement, you are read-only.
that's the point where I'll leave the Access route and built frontends with .NET instead
That's where you are heading, then.
That's the strange opinion I read often and I really do not understand. Software doesn't disappear magically
20 years. Windows 95. You aren't going to find it for sale, and you wouldn't run it if you could. Nor will there be drivers for it.
I can start a DOS program on my current hardware and it works the same as in 1987
No, you try getting DOS 6.2.2 running on a new box in real-time mode today.
That is highly unlikely to happen
I've gotten some old 16 bit stuff to run on DosBox, but that's not native.
You can keep a lot of stuff limping along for a very long time.
But the end comes.
I've got Office 97 on diskette -- but I don't think I could get it going natively.
And really, who wants to fire up a VM to run something like that?
So if I want to run an ADP in 20 years with SQL Server 2008 I can run it in 20 years the same way I do it now, be sure.
Dollars to doughnuts you'll hit a hardware impossibility before then.
You gotta do what you gotta do - but I don't think advising people to run with a deprecated technology is good advice.
But you are free to proffer it, and the advisee is free to evaluate it, and I am free to add my two cents.
That's life.
Nick67
Hi Nick,
That's a rumour I read often and it's totally wrong. I've tried it five minutes ago with my old SQL Server 2005 Express (without ADP of course, with file format MDB) and it works without problems.
Same as above. Wrong, as nobody would invest the time to create two ways of working with the same object (controls here). The controls have NOTHING to do with the underlying mechanisms to update data. ONLY the recordset handles updating and there is a property which tells Access (and any other frontend using DAO/ADO recordsets) if the field is updatable so the controls can be set into the right mode. The recordset can be changed using code with VBA, that's the most often way developers work with it - but do you really think MS has made an extra method to update data of a form? Would be very senseless. Of course Access does nothing else than you can do in code: Reading all the changed data in the controls (where all controls changes their data WITHOUT changing anything in the underlying recordset!) and then forwarding the data into the recordset so that the recordset can do the job for which it was made for: Creating the needed SQL commands to change the data in the underlying datasource, although that are not always INSERT/UPDATE/DELETE but also execution of MS internal SPs in case of SQL Server for example.
The form doesn't "look the same", they are really totally identical. You can copy a form from an MDB into an ADP and vice-versa, there will be no kind of conversion. That's the reason why the method with ADP and SPs I described above works the same in ADP and MDB, THERE IS NO DIFFERENCE as the form object has a wrapper around the Recordset property and this wrapper method analyzes the type of the real recordset object you use if you set the recordset object to the form recordset property, and it switches internally to use ADO or DAO methods to handle the recordset object it got. If you always use RecordSource property only then you are forced to use the kind of recordset ADP (ADO) or MDB (DAO) wants to use, but not if you assign a recordset directly. Simply try it.
Repeating yourself all the time does not make it more true...:-)
As I use the direct assignment of Recordsets and not the ADP method of using the RecordSource and InputParameters properties I can switch the whole code to an ACCDB and it works. 100% sure. Maybe some little tweaks, nothing else.
The users don't need to install anything locally except a remote connection and so it will run when all of them has Office 2036 on their PCs...:-)
That's nonsense in the same way. You try to create strange workarounds with switching fields just to avoid using a normalized way which works for sure, that are really bad methods if you are forced to do that just to don't leave the path of Access DAO JET/ACE stuff, which makes everything twice as problematic as needed. My ADP project is made in a way that everything happens on the server regarding the data so that the frontend doesn't need to do anything else than displaying controls and entering data. If Access and ADO will die at any time, I can create a new frontend based on .NET or PHP and it will work without any big effort to do as all the SPs do the hard job. I'm safe for future with this approach and as long as ADP makes my life easier because it doesn't use any special frontend stuff like datatype conversion or local tables, all the things which are not available with any other frontend technology, I can switch really fast to anything I want. Anyone who still goes on with linked tables and all the stuff will wake up when they must switch to a new frontend technology. And I think that's not very far in the future if Access goes on getting more worse in each version.
Cheers,
Christian
but the recordset returned by a SP is generally read-only unless you are running an ADP.
That's a rumour I read often and it's totally wrong. I've tried it five minutes ago with my old SQL Server 2005 Express (without ADP of course, with file format MDB) and it works without problems.
in an mdb you can use the recordset you created in code to update the data, but the controls themselves are read-only.. The forms LOOK the same, but the underlying way that MDB and ADP handle changes in controls is fundamentally different.
Same as above. Wrong, as nobody would invest the time to create two ways of working with the same object (controls here). The controls have NOTHING to do with the underlying mechanisms to update data. ONLY the recordset handles updating and there is a property which tells Access (and any other frontend using DAO/ADO recordsets) if the field is updatable so the controls can be set into the right mode. The recordset can be changed using code with VBA, that's the most often way developers work with it - but do you really think MS has made an extra method to update data of a form? Would be very senseless. Of course Access does nothing else than you can do in code: Reading all the changed data in the controls (where all controls changes their data WITHOUT changing anything in the underlying recordset!) and then forwarding the data into the recordset so that the recordset can do the job for which it was made for: Creating the needed SQL commands to change the data in the underlying datasource, although that are not always INSERT/UPDATE/DELETE but also execution of MS internal SPs in case of SQL Server for example.
The form doesn't "look the same", they are really totally identical. You can copy a form from an MDB into an ADP and vice-versa, there will be no kind of conversion. That's the reason why the method with ADP and SPs I described above works the same in ADP and MDB, THERE IS NO DIFFERENCE as the form object has a wrapper around the Recordset property and this wrapper method analyzes the type of the real recordset object you use if you set the recordset object to the form recordset property, and it switches internally to use ADO or DAO methods to handle the recordset object it got. If you always use RecordSource property only then you are forced to use the kind of recordset ADP (ADO) or MDB (DAO) wants to use, but not if you assign a recordset directly. Simply try it.
Nearly everything I am afraid.
Every time you used an SP in a read-write situation, you have a problem, because only in ADP are those actually read-write.
Repeating yourself all the time does not make it more true...:-)
As I use the direct assignment of Recordsets and not the ADP method of using the RecordSource and InputParameters properties I can switch the whole code to an ACCDB and it works. 100% sure. Maybe some little tweaks, nothing else.
And really, who wants to fire up a VM to run something like that?If I would have an application which I've developed over x years so it was cost intensive (and not an old 500$ Office which is peanuts against 250,000$ for the current project) and it works for all processes and can also work in future as it keeps to be updatable with little further developing - yes, I would definitively do if I would be the investor... the DOS examples should only show that you can work with them nowadays so you can also go on using ADP/SQL Server 2008. As my current project is made to use a terminal server which itself is a VM Windows 2008 R2 server I'm definitive sure that it can run in 20 years without any change. It will only not get security updates of MS anymore but that's really no point for an in-house application.
The users don't need to install anything locally except a remote connection and so it will run when all of them has Office 2036 on their PCs...:-)
Dollars to doughnuts you'll hit a hardware impossibility before thenSorry, but that's nonsense. When I can run a DOS programm today I'm sure I can run it in future also. Nobody really wants to work with a DOS program today but there are emulators to work with DOS, so someone wants it, you see?
You gotta do what you gotta do - but I don't think advising people to run with a deprecated technology is good advice.
That's nonsense in the same way. You try to create strange workarounds with switching fields just to avoid using a normalized way which works for sure, that are really bad methods if you are forced to do that just to don't leave the path of Access DAO JET/ACE stuff, which makes everything twice as problematic as needed. My ADP project is made in a way that everything happens on the server regarding the data so that the frontend doesn't need to do anything else than displaying controls and entering data. If Access and ADO will die at any time, I can create a new frontend based on .NET or PHP and it will work without any big effort to do as all the SPs do the hard job. I'm safe for future with this approach and as long as ADP makes my life easier because it doesn't use any special frontend stuff like datatype conversion or local tables, all the things which are not available with any other frontend technology, I can switch really fast to anything I want. Anyone who still goes on with linked tables and all the stuff will wake up when they must switch to a new frontend technology. And I think that's not very far in the future if Access goes on getting more worse in each version.
Cheers,
Christian
Well old son, here's the scene
This is the sproc, nice simple select sproc
and here's the MS Access passthrough
exec qryFRClientID
Any here is the query open in Access, nice and uneditable and uninsertable.
So, you clearly know something I don't because all of my passthroughs are READ-ONLY
How are you creating read-write passthroughs?
This is the sproc, nice simple select sproc
USE [MyDb]
GO
/****** Object: StoredProcedure [dbo].[qryFRClientID] Script Date: 05/01/2015 16:44:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Nick
-- Create date: 27-Mar-08
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[qryFRClientID]
-- Add the parameters for the stored procedure here
-- no parameters
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT tblClients.[Client ID], tblClients.[Client Name]
FROM tblClients
ORDER BY tblClients.[Client Name];
END
GOand here's the MS Access passthrough
exec qryFRClientID
Any here is the query open in Access, nice and uneditable and uninsertable.
So, you clearly know something I don't because all of my passthroughs are READ-ONLY
How are you creating read-write passthroughs?
Hi Nick,
When did I ever talked about an Access Pass-Through query? That's the same stuff as a linked table/view: It will execute as T-SQL but converted to ACE/JET when the data comes back because a PT is a DAO QueryDef object like any other QueryDef object. A PT is by design always read-only, that's not new (although not understandable, MS decision).
I showed you above a code for ADO which returns a recordset executing an SP (quick and dirty of course without using the parameter collection as I would normally do) and assigning the result to a form's recordset property. I wrote more than once about assigning, so what was unclear to you to now compare that with a PT query? Really, you should read the whole thing and not pick some words and argue about something completely different.
And again, I also wrote that I've tested that before writing it, in MDB, just to be really sure that it is no difference between MDB and ADP in that case. That's the main reason why I wrote that I do not need to change much code as I do that in ADP at the moment in my project and this one has proven that it also works in MDB (and so of course also in ACCDB).
Cheers,
Christian
MS Access passthrough
When did I ever talked about an Access Pass-Through query? That's the same stuff as a linked table/view: It will execute as T-SQL but converted to ACE/JET when the data comes back because a PT is a DAO QueryDef object like any other QueryDef object. A PT is by design always read-only, that's not new (although not understandable, MS decision).
I showed you above a code for ADO which returns a recordset executing an SP (quick and dirty of course without using the parameter collection as I would normally do) and assigning the result to a form's recordset property. I wrote more than once about assigning, so what was unclear to you to now compare that with a PT query? Really, you should read the whole thing and not pick some words and argue about something completely different.
And again, I also wrote that I've tested that before writing it, in MDB, just to be really sure that it is no difference between MDB and ADP in that case. That's the main reason why I wrote that I do not need to change much code as I do that in ADP at the moment in my project and this one has proven that it also works in MDB (and so of course also in ACCDB).
Cheers,
Christian
Last I remember there was a TriState property for check boxes: Yes, No, and NULL.
Set it to False and it only allows Yes and No.
You can also set the column that stores these values to Not NULL.
>So the question is - Can i convert these bit fields to INT or TinyINT?
Sure.
Another possibility is to use Nz to filter them out...
Open in new window