Are there any similarities to a Microsoft Access database and an SQL database when it comes to making design changes?
17 years ago I designed a Access database for a client. 5 years later they had the backend converted to SQL. They have asked me to make some changes for them. I have almost no SQL knowledge.
The FE is still Access and I have been making changes to it. My question is if I need to add a field or create a new table in the BE is it difficult to do for a beginner? Or is it similar to an Access BE?
The FE is still Access and I have been making changes to it. My question is if I need to add a field or create a new table in the BE is it difficult to do for a beginner? Or is it similar to an Access BE?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<I get the impression from your post, it might not be that difficult.>>
It's not. Use SSMS (or SQL statements DML from Access) to add what you need, then refresh the links in Access.
And are you aware already of the issues with bit fields and floats?
Jim.
It's not. Use SSMS (or SQL statements DML from Access) to add what you need, then refresh the links in Access.
And are you aware already of the issues with bit fields and floats?
Jim.
ASKER
<<And are you aware already of the issues with bit fields and floats?>> I am not
So when dealing with a ODBC source, Access uses something called a keyset cursor.
It records a "key" to build a recordset. Then when you edit a record, it has a copy of what the fields looked like before you did anything. When you do an update, it re-fetches the record and compares what it started with vs what's in the record. If it see's any differences, it believes someone else changed the record.
The problem with bits is nulls, and with floats (double's/single's) is floating point math, which gives different decimal values because of that. So Access will think the record has changed even when it has not.
The way around that is to add a TimeStamp (Row version in later versions of SQL), which is a single field with something akin to a book mark. If Access sees this field, then it only needs to look at that one field to know if a record was really changed or not.
So if you have bit fields or floats in the record, make sure you add the timestamp/rowversion field to the table. There's nothing you need to do with it other than add it. Also avoid bits and floats as part of a key as you will have issues with the keyset cursor as well.
Also, for some other tips on working with SQL right out of the gate (including the autonumber problem I mentioned), have a look at "Best of both worlds" here:
https://www.jstreettech.com/downloads.aspx
Jim.
It records a "key" to build a recordset. Then when you edit a record, it has a copy of what the fields looked like before you did anything. When you do an update, it re-fetches the record and compares what it started with vs what's in the record. If it see's any differences, it believes someone else changed the record.
The problem with bits is nulls, and with floats (double's/single's) is floating point math, which gives different decimal values because of that. So Access will think the record has changed even when it has not.
The way around that is to add a TimeStamp (Row version in later versions of SQL), which is a single field with something akin to a book mark. If Access sees this field, then it only needs to look at that one field to know if a record was really changed or not.
So if you have bit fields or floats in the record, make sure you add the timestamp/rowversion field to the table. There's nothing you need to do with it other than add it. Also avoid bits and floats as part of a key as you will have issues with the keyset cursor as well.
Also, for some other tips on working with SQL right out of the gate (including the autonumber problem I mentioned), have a look at "Best of both worlds" here:
https://www.jstreettech.com/downloads.aspx
Jim.
ASKER
Thanks
"TimeStamp (Row version in later versions of SQL),"
It's interesting that supposedly TimeStamp is deprecated, yet there is no RowVersion data type at least in SQL Server 2014 ... still only TimeStamp ... when working in SSMS (v17.8.1)
It's interesting that supposedly TimeStamp is deprecated, yet there is no RowVersion data type at least in SQL Server 2014 ... still only TimeStamp ... when working in SSMS (v17.8.1)
ASKER
I get the impression from your post, it might not be that difficult.