My previous posts about the SQL Migration Assistant for Access reported generally favorable results with this product which is used to migrate Access data into SQL Server. It does a remarkably better job than the old Access Upsizing Wizard which very often failed, especially when dealing with date fields. When SSMA has completed its work, the system is mostly operational.
However, there are two common Access design flaws that can impact the application - the first is relying on the behavior of Boolean fields to evaluate as -1 = True and 0 = False. Many Access developers will test for "-1" meaning True, but SQL Server returns "1" for bit fields so the comparison logic fails. The fix is simply to substitute "-1" with "True" (without the quotes) and the operation will succeed.
A second design flaw recently noted is that in Access you can subtract one date from another date, and then perform math on the result - for example
24 * ([date_end] - [date_start])
in Access will give you the number of hours between the two date/time values. However, once your data is moved into SQL Server, this expression will result in
Implicit conversion from data type smalldatetime to int is not allowed. Use the CONVERT function to run this query.
The solution is to use the DateDiff function which exists in both Access and SQL Server. In Access you would write
DateDiff("h", [date_start], [date_end])
and when converting this query to T-SQL for SQL Server you might use
DATEDIFF(hh, [date_start], [date_end])
Saturday, June 21, 2008
Sunday, June 08, 2008
Access Replication
Replication in Access provides a method of allowing remote users to work with a local copy of the database while disconnected from the network, and then synchronizing their changes when they do connect. This feature has been part of Access since the 90's but sadly, although Access 2007 supports it if you stay with the .mdb file format, if you choose to convert to the new .accdb format this feature goes away (along with WorkGroup Security).
The idea of replication is that you create a Design Master (DM) that adds additional hidden system fields which Access uses to track record changes that need to be replicated. You also create a Replica which has all the programming and data of the DM but without any design options, thus protecting the design from unauthorized changes. During the sync process, data is exchanged between the DM and replica, and design changes made to the DM are also propagated to the replica.
The replication model is sensitive to the drive, path, and file name; moving the DM will cause it to become a replica; copying the DM will result in creating another replica.
In addition to the fields added to each table, there are also a number of hidden system tables created when you replicate a database; the net result is that the DM and the replicas are considerably larger than the original non-replicated file. For example, a 27 MB file can grow to 44 MB when replicated. Once the users begin working with the file and syncing, that file can grow to 48-59 MB (these numbers are taken from an actual production system). For that reason, it is strongly recommended that you enable the Compact on Close option to keep the file as small as possible under the circumstances.
It is important to note that creating a replica will change AutoNumber fields from Increment to Random. This is necessary to prevent conflicts - for example, if user A added a new record, and user B added a new record, Increment would otherwise result in a key violation when attempting to sync. Therefore, if the app relies on a sequential series of new numbers, e.g. invoice numbers, you will need to develop a different strategy for that numbering system.
During the sync process, any conflicts will result in a hidden table being created, e.g. if a table tblOrders has sync conflicts then Access will create a tblOrders_Conflict table that holds the GUIDS for the "winning" and "losing" replica, table, and field.
It should be noted that simply deleting old records will not necessarily reduce the file size; Access maintains a system table, MSysTombstone, which retains a pointer to every deleted record. Unless you utilize Replication Manager (from the Office 2000 developer kit) or a third-party utility, the default retention period is 1000 days, so all those deleted records will be adding to the size of the file with little real benefit.
A common technique of dealing with the bloat of replication is to rebuild the master file periodically. To accomplish this, you first have all users sync & then delete their local copy, then you would need to create a new empty file, export all the Access objects into the new file, and also create new tables in the new file that do not have the replication fields & export the data into those new tables. (It is not possible to delete the replication fields from the tables because they are system fields.) The final steps would be to convert that new file to a design master & create a replica, and then distribute that replica to the database users.
The idea of replication is that you create a Design Master (DM) that adds additional hidden system fields which Access uses to track record changes that need to be replicated. You also create a Replica which has all the programming and data of the DM but without any design options, thus protecting the design from unauthorized changes. During the sync process, data is exchanged between the DM and replica, and design changes made to the DM are also propagated to the replica.
The replication model is sensitive to the drive, path, and file name; moving the DM will cause it to become a replica; copying the DM will result in creating another replica.
In addition to the fields added to each table, there are also a number of hidden system tables created when you replicate a database; the net result is that the DM and the replicas are considerably larger than the original non-replicated file. For example, a 27 MB file can grow to 44 MB when replicated. Once the users begin working with the file and syncing, that file can grow to 48-59 MB (these numbers are taken from an actual production system). For that reason, it is strongly recommended that you enable the Compact on Close option to keep the file as small as possible under the circumstances.
It is important to note that creating a replica will change AutoNumber fields from Increment to Random. This is necessary to prevent conflicts - for example, if user A added a new record, and user B added a new record, Increment would otherwise result in a key violation when attempting to sync. Therefore, if the app relies on a sequential series of new numbers, e.g. invoice numbers, you will need to develop a different strategy for that numbering system.
During the sync process, any conflicts will result in a hidden table being created, e.g. if a table tblOrders has sync conflicts then Access will create a tblOrders_Conflict table that holds the GUIDS for the "winning" and "losing" replica, table, and field.
It should be noted that simply deleting old records will not necessarily reduce the file size; Access maintains a system table, MSysTombstone, which retains a pointer to every deleted record. Unless you utilize Replication Manager (from the Office 2000 developer kit) or a third-party utility, the default retention period is 1000 days, so all those deleted records will be adding to the size of the file with little real benefit.
A common technique of dealing with the bloat of replication is to rebuild the master file periodically. To accomplish this, you first have all users sync & then delete their local copy, then you would need to create a new empty file, export all the Access objects into the new file, and also create new tables in the new file that do not have the replication fields & export the data into those new tables. (It is not possible to delete the replication fields from the tables because they are system fields.) The final steps would be to convert that new file to a design master & create a replica, and then distribute that replica to the database users.
Subscribe to:
Posts (Atom)