Results 1 to 9 of 9

Thread: ACCDE vs ACCDB

  1. #1
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    67

    ACCDE vs ACCDB

    Looking for advice on my setup

    - Frontend and Backend (backend is behind a folder) setup
    - Files located on a shared drive on server

    I make changes to the frontend on my desktop and overwrite it when it is ready

    Question: Is there a advantage to converting my shared DB files to the ACCDE format from ACCDB?
    - Will it slow down the ballon effect of my file size?
    - Does it speed anything up?
    - Will this keep users from looking inside the DB?
    - Does it make it any more stable?

    Peter
    We tend to look at Linear paths which can lead us to a path of resistance!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Is there a advantage to converting my shared DB files to the ACCDE format from ACCDB?
    Yes. It secures the design of forms, reports and code.
    Will it slow down the ballon effect of my file size?
    Not by much. Look at the cloning file launcher method of starting an Access application in the Code Bank.
    Does it speed anything up?
    I think it does, but only a little.
    Will this keep users from looking inside the DB?
    Forms, Reports and code: yes. Tables, queries: No. Not sure about macros.
    Does it make it any more stable?
    Yes. By the very nature of preventing changes for a start. The fact that MDE/ACCDE's are compiled also makes them more stable imo.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,536
    I believe the only negative, if you consider it as such, is that an MDE/ACCDE file will simply close if it encounters an unhandled error.
    Hope this helps!

    The Devil's in the Details!!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,925
    If you want what I consider the "best" environment is to:

    1. Have all backend tables on a dedicated SQL Sever box.
    2. Use linked SQL Server tables in my frontend.mde (note that I use a compiled mdb which is an mde file.)
    3. Then, it's a give or take on where the front-end resides. I "may" put it on each user's desktop (thus avoiding the network drive completely!) This works great for unstable or slow networks as you have a direct connection between the user's frontend to the backend data. Keep in mind that every connection to a network drive is subject to problems that network may have.

    3. BUT, if I think there's going to be a contsant upkeep project, I might put the frontend on the network drive for ease of updating. OR, I put in a "tbLlocalVersionTable" in the frontend which is basically a table with 2 fields, LoginID and VersionDate. I then also have a SQL Server version table which might be called "tblSQLServerVersionTable" which also has a VersionDate field (and is linked into the frontend). Then in my frontend startup code, I compare the 2 dates and if they are different, I prompt the user to "copy the latest version from the network drive" and have code which does this. This way when I update my code (and VersionDate field), this no longer matches the VersionDate field in the user's frontend local mde copy and prompts them to copy the "source" to their desktop (which is only a few lines of vba code.) If I'm only dealing with 1 or 2 desktop frontends, I may not do this.

    mde (which I believe are called: ACCDE in 2007) is usually the best route to go for frontends since it doesn't send the user into the code should an error occur. It's a very simple 1 step process to make an ACCDE file out of an ACCDB file (not sure where it is in 2007 but in prior versions it's in Tools->Database Utilities-> Make mde file.)

    You again, have to keep in mind that if your backend is on the network drive, you're still bound to problems on that network drive. But you have improved things by having the frontend on the user's desktop. For the frontend, you also have to look at how often you'll be updating it. If often or you have a lot of users, it may pay to simply put the frontend on the network drive and use a script such as here:
    http://www.dbforums.com/6274786-post19.html

    This keeps each user in a "cloned" copy of the source frontend so you never get the "locked by another user" error. Users simply open the vbs script versus the ACCDE or frontend mde file and it'll clone from a "source" mde file. The great benefit of doing this is that you can then make code changes and copy them over the "source" frontend file (on the network drive), without having to run around and have each user close out of the frontend so you can copy new code. I ALWAYS use this script on network front-ends as it really is efficient and prevents a lot of run-around work for me.
    Again though, that would be if I'm constantly making updates to the frontend and also have a good stable network drive.

    Keep in mind that if your backend tables are in an mdb (or ACCDB) file on the network, you still have that "connection" to the network drive and are bound to it's problems and memory load sharing limitations. Most networks are built pretty stable these days (good network administators) but smaller companies may have a different setup.

    The BEST of all BEST worlds would be to have the frontend on each users desktop (possibly using linked tables to the SQL Server), have your forms "UNBOUND" and have functions that access stored procedures directly to SQL Server (and not using linked tables at all) or simply open a recordset on the linked table and write/update/delete the data from the unbound form. This essentially keeps any "connection" to the data source happen only when a user pushes a "Save", "Update", or other events that trigger your code on the form. Thus, users can update the information on the form without the constant connection (on the form) to the data source.

    This also works great for users who have dialup connections as they're not constantly bound to the network connection (if you use VPN).

    If I know a lot of users have slow connections to the network drive (ie. dialup), I'll consider the recordset size (ie. are we dealing with millions of records), to then determine whether I user UNBOUND forms or not as it's a bit more time-consuming designing unbound forms!!

    Usually I try to avoid any "network" connection to the data source should the recordsize exceed 50,000 records or so and the network connection is not very good (again, VPN is very nice).

    If data is important and multiple users are updating it, I ALWAYS put the data on a SQL Server (or MySQL) so it can efficiently do it's thing when it comes to accessing the data (whether using unbound forms or not.) - Again, you're avoiding a connection to a slow or problematic network drive, not to mention the additional benefits such as security, faster update management of the data, etc...etc....
    Last edited by pkstormy; 09-17-09 at 19:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,925
    In specific answer to your questions (like StarTrekker stated):

    Question: Is there a advantage to converting my shared DB files to the ACCDE format from ACCDB?
    = Yes - you should always compile a frontend into a *.E file as this is more secure (keeps users out of the code should it bomb)

    - Will it slow down the ballon effect of my file size?
    = No (look at the vbs script StarTrekker referred to and in the link above.)
    - Does it speed anything up?
    = Yes (and even faster if you use the vbs script.)
    - Will this keep users from looking inside the DB?
    = Yes.
    - Does it make it any more stable?
    = Yes (and using the vbs script keeps the frontend so each user is in their "OWN" copy of the source mde file. *.E files are always more stable than *.B files as they are "compiled" versions of the code which you cannot edit/see the code. Users in their own *.E file (ie. on their desktop) prevent a "connection" to the network drive and is thus faster (but again, slowed down by the "connection" to the backend table *.B file on the network.) Multiple users in the SAME frontend *.E file on a network can be problematic in that if a user opens the *.E frontend and then miminizes it for 4 hours, it'll eventually "lock" the *.ldb (or whatever it's called in 2007) and thus give you the "Locked by another user" error and prevent others from opening the same frontend file. That's another reason I like to use the *.vbs script if the front-end is going to be on the network drive shared by multiple users.

    Backends though should always be in a *.B file (or highly preferable, on a dedicated SQL Server box with an ODBC DSN to make the "linked" table connection in the frontend on the user's desktop). Don't create a *.E file for a backend or you then have to deal with transferring data should you need to make a field change in a table. I do have my backends somewhat protected should they be in a *.B or *.mdb file on the network drive (ie. I have to hold the shift key down when getting into it or I'll get a message box such as the average user would get if they tried opening it without using the shift key - which would show the message box and then quit.)
    Last edited by pkstormy; 09-17-09 at 19:39.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    67

    Database format

    Thanks

    This will enable me to figure out where I am going in the future. I appreciate everyones input.


    Now to get a handle on that pesky Ribbon,

    Peter
    We tend to look at Linear paths which can lead us to a path of resistance!

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I still have much to learn about customising the ribbon too... if you find some resources that are easy to follow and understand please link to them?

    Cheers
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    67

    Here is a step forward on Ribbons

    I liked the cloning method for the frontend and will reasearch some more.

    I found out Microsoft will be placing a Ribbon Editor in 2010, meanwhile they sugguest using an apllication for Ribbon Creation that already exists. see address..

    http://office.microsoft.com/en-us/ma...CE012295101033

    I downloaded the shareware to test it out and it gave me great results on my Ribbon. I only wanted the user to Print, Export to Word, Export to Exce and Send to Email. But you can do so much more with this program.



    Peter
    Attached Thumbnails Attached Thumbnails Capture.JPG  
    We tend to look at Linear paths which can lead us to a path of resistance!

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Oh COOOOL! I'll have to give this a shot and let you know!

    Thank you muchly
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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