If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > ACCDE vs ACCDB

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
L33t Helpa Munky
 
Join Date: Nov 2007
Location: Adelaide, South Australia
Posts: 4,049
Quote:
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.
Quote:
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.
Quote:
Does it speed anything up?
I think it does, but only a little.
Quote:
Will this keep users from looking inside the DB?
Forms, Reports and code: yes. Tables, queries: No. Not sure about macros.
Quote:
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
Reply With Quote
  #3 (permalink)  
Old
Moderator
 
Join Date: Jun 2005
Location: Richmond, Virginia USA
Posts: 2,507
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
Reply With Quote
  #4 (permalink)  
Old
Moderator
 
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:
DBForums Code Bank

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....
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Last edited by pkstormy; 09-17-09 at 19:38.
Reply With Quote
  #5 (permalink)  
Old
Moderator
 
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.)
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Last edited by pkstormy; 09-17-09 at 19:39.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #7 (permalink)  
Old
L33t Helpa Munky
 
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
ACCDE vs ACCDB-capture.jpg  
__________________
We tend to look at Linear paths which can lead us to a path of resistance!
Reply With Quote
  #9 (permalink)  
Old
L33t Helpa Munky
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On