I have a 12 yr old Windows app that uses Access97 as a data store. All code is written in visual studio - C#, VB6 and C++. We are using Access simply as a data store and it has worked well for years, but we are adding functionality that will increase the amount of data I need to store, so I was contemplating an upgrade. I have read that Access2010 accdb databases can be twice as large (2Gb) as Access97 mdbs, so that is the primary reason I was going to upgrade. But, much of the data we store is binary data stored in strings. In practice, we have seen issues when mdbs grow larger than 100Mb, and it seemed going to 2010 would be a good thing. But, I need to protect my databases from user access, and I have seen in 2010 that ADODB recordset access takes a lot longer with 2010. It takes 2 seconds on XP but only a little longer on Win7.
So, my questions is this: is it really worth it to upgrade to 2010? I cannot seem to find a way to protect my accdb without using encryption that forces page-level locking, so it appears my app will be slower using 2010. I don't really need to encrypt data, but I need password protection so users cannot get into the databases and mess them up.
I have a requirement to allow "archiving" of data and to also allow reporting upon data that has been "archived". Access97 mdbs can only be 1Gb in size, and eventually we need to create new mdbs for data storage whileetaining" all the older data for reporting. We collect a fair amount of data, but the users of the application are non-technical and are not remotely close to being database admins. I also have to keep cost way down, so I cannot utilize SQL Server in these environments. I have considered SQL Server Express, but the backup/restore is rather complex and I do not see it working for what my app must be able to do in the area of reporting on old data. In order to keep my archiving process as easy as possible, I hoped to retain Access's file-based structure since my unsophisticated users can handle the process. Should I just stay with Access97 and "archive" twice as often?
thanks for any help,
Ken