Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Cleveland, OH, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All,
    I'm looking for suggestions/options for compressing encrypted Microsoft Access 2007 back-end database files (.accdb).

    Here's the scenario: I have two large BE files (about 1Gb each) that get distributed to a user's local machine during a refresh sequence. Some of the users are in-house connected to the network but many are remote... connected to VPN through WiFi or AirCard or Broadband, etc. In an effort to minimize the refresh sequence (specifically the download time)... I had previously zipped (using 7-zip) the .mdb BEs... downloaded that zip file... unzipped it locally and voila... all was well. But I wanted to take advantage of the better encryption available in Access 2007. However, due to the scrambling (which on the one hand is good)... now the .accdb BEs basically don't compress thus making the refresh time extremely unbearable (which on the other hand is bad).

    Here is what I've thought of, tried and discarded:
    1) Zip & download un-encrypted BEs to the local machine and then have the FE append the un-encrypted BE data into encrypted BE files. - This works but because of the massive amount of data... is still too long for the refresh times.
    2) Zip & download un-encrypted BEs to the local machine and then have the FE programmatically encrypt the BE files. - This works but because of the massive amount of data... is still too long for the refresh times.
    3) Export all the data to txt files. Zip & download the txt files. Import txt files into local encrypted BE files. - This works but because of the massive amount of data... is still too long for the refresh times.
    4) Upload all data into Oracle tables. During local refresh... append data into local encrypted BE files from Oracle ODBC connection. - This works but because of the massive amount of data... is still too long for the refresh times.
    5) Create hidden folder on the local machine. Download un-encrypted BE files to the hidden directory. - This works and is quick but leaves the data unprotected and some smart person would eventually run across the "hidden" folder.

    At this point, I think I'm stuck with reverting back to .mdb files and using the much simpler database password option (which I don't like much for obvious security reasons but... I'm not sure I have any other options).

    Any thoughts or suggestions are greatly appreciated. Thanks in advance.

    Steve

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Why are you copying-down the databases? Do the user's disconnect from the network?

    If they stay connected, I'd consider establishing Remote Desktop Connections from each workstation to the Server. This way they are working on the server, and the BE db is essentially local.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You've run into one of the challenges of working in a WAN environment with limited bandwidth. First of all, let's clear up a misconception. .MDB databases can also be encrypted - but you will have the same issue with them once you do that - they don't compress well at all. Also, I presume the security concern here is that you don't want people to be able to open the back-end and read data without having the front-end database, in case someone manages to snag a copy of the refresh file as it is being transferred, or an unauthorized user ends up at someone's remote workstation.

    I'm surprised that the refresh sequence takes that long if you use option 1 - the encryption of the downloaded BE should go pretty quickly, unless your remote workstations are pretty limited systems. But I would reject that approach because it means you have to have the encryption process on each workstation, which means it would be fairly easy to circumvent it.

    What I really think you might want to look at - but isn't supported as such in Access 2007, is replication. I've not attempted to do replication using .MDB back-ends with either Access 2007 or 2010, but I believe the basic process is still there - though the admin tools have likely been removed. But my approach would be to go to a SQL Server Express back-end (free) or some other platform such as Oracle or mySQL where replication is supported. For one thing, 1 GB MDB files get pretty unweildy, and are approaching the upper limit size wise. Replication will dramatically reduce your synchronization times, and you will also improve at least some aspects of performance by using a true database engine rather than Jet. But I should caution you that replication is a complex process that requires a fair bit of administrative support. Post back if you want to dig into implementing replication and I'm sure we can give you some pointers about setting it up.
    Wendell

  4. The Following User Says Thank You to WendellB For This Useful Post:

    hasse (2011-01-28)

  5. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Cleveland, OH, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    MarkLiquorman,
    Sorry... I forgot to state that yes... one of the needs is that the user be disconnected from the network and thus have stand alone capability.

    WendellB,
    Regarding the .mdb encryption thing... you're right but it's an option for .mdb files which I hadn't implemented. It's not an option for .accdb files (which I just discovered).

    You hit the nail on the head about the security concerns. I don't want anyone having the capability to directly access the backends. Replication is not a viable option for me. Namely because.. to your point... it is complex and requires a lot of administration support which I'm too busy/lazy to do. I thought that option 1 (local programmatic encryption) should have been a pretty good option too but when I tested it... it took 40 minutes for the encryption to complete. And that is on a computer with 2GB of RAM. Most of the user base has half that. A colleague mentioned SQL Server Desktop. I'm going to look into that next (or it's current derivatives).


    Thanks all.
    Steve

  6. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    By using an SQL Server Express you get rid of the need to distribute the database and SQL Server can deal with the all the workstations traffic without any issues. It may even be as easy as just linking the front-ends tables to the SQL Server tables and like that you can likely get your existing client app working immediately. Performance may be a problem in some situations, though, especially situations where you may have forms dealing with big recordsets.
    A move to a client / server scenario usually will require some changes to the way an Access app works, in order to avoid the network being a serious performance bottleneck, but that depends on what your app accesses the data.
    Rui
    -------
    R4

  7. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Stephen Kohler View Post
    ... A colleague mentioned SQL Server Desktop. I'm going to look into that next (or it's current derivatives).
    In fact, SQL Express (the follow on to SQL Desktop) would be the solution I would choose, and I would definitely implement replication. In your situation, you would still have to move the back-end database using your current approach, and 1GB in Access will be 1GB or more in SQL Server. Replication is complicated to setup, but once it is up and running, it is pretty stable. I've been running a replicated database used by some 800 web users, and we've had to rebuild the replica 4 times in a bit over 3 years. The beauty of it is synchronization only takes a few seconds once the replica is created, and as long as the user is connected they are seeing the current data.

    It would help to understand how many remote users are out there, and how frequently you need to make design changes to the back-end. The former factor determines to a large extent how many subscriptions are required, and how much pain there is in installing SQL Server Express on each of the workstations. The latter factor determines how often you need to rebuild replicas - some design changes are managed with replication. An example is adding a new field at the end of an existing table - that seems to work well. However adding a new table, deleting a table, and similar changes will typically require changing the publication, and then creating a new subscription. I should also note that using SQL Server replication would require installing at least one instance of SQL Server Standard as the publisher, which does have a server and user licensing fee.
    Wendell

  8. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by WendellB View Post
    In fact, SQL Express (the follow on to SQL Desktop) would be the solution I would choose, and I would definitely implement replication. In your situation, you would still have to move the back-end database using your current approach, and 1GB in Access will be 1GB or more in SQL Server. Replication is complicated to setup, but once it is up and running, it is pretty stable. I've been running a replicated database used by some 800 web users, and we've had to rebuild the replica 4 times in a bit over 3 years. The beauty of it is synchronization only takes a few seconds once the replica is created, and as long as the user is connected they are seeing the current data.

    It would help to understand how many remote users are out there, and how frequently you need to make design changes to the back-end. The former factor determines to a large extent how many subscriptions are required, and how much pain there is in installing SQL Server Express on each of the workstations. The latter factor determines how often you need to rebuild replicas - some design changes are managed with replication. An example is adding a new field at the end of an existing table - that seems to work well. However adding a new table, deleting a table, and similar changes will typically require changing the publication, and then creating a new subscription. I should also note that using SQL Server replication would require installing at least one instance of SQL Server Standard as the publisher, which does have a server and user licensing fee.
    You think this would be preferable to use just one SQL Server database and link all the clients to it? SQL Server will run ok on 2 GB but will have a bit more trouble on 1 GB machines. Sorry, I am just curious why you would recommend a replication scenario when the typical client / server - Access / SQL Server could work well here. Of course, there would be no performance issues related to database traffic over the network, that would be an advantage...
    Rui
    -------
    R4

  9. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,186
    Thanks
    47
    Thanked 983 Times in 913 Posts
    Rui, you missed the bit about requiring off-line access.

    cheers, Paul

  10. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Yep, seems I did.

    Thanks, Paul .
    Rui
    -------
    R4

  11. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quite frankly, I don't think you will ever have a satisfactory solution that includes downloading that amount of data. I think you need to look at what it will take to give Remote Desktop access to the server for those workstations that aren't hardwired to the network. I think it would be cheaper in the long run.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •