By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Convert MDB to MDE

P: 40
I'm trying to convert an MS Access 2003 MDB file to an MDE file, to optimize performance for my front end users. I go to Tools, Database Utilities, Make MDE File, and select my file. I get a message "Microsoft Office Access was unable to create an MDE database." Under "Show Help" it says that it could be too large of a database; however my front end database that I'm trying to convert is only 2.5 MB in size.

Any ideas about what I could look for? My final goal is to speed up front end access to the database.


Robin
Sep 20 '07 #1
Share this Question
Share on Google+
9 Replies


Denburt
Expert 100+
P: 1,356
Is that all the error states?
Is there no error number or any other information to help us out a bit?
Is there VBA code in your database? Try to compile the VBA portion of the database (alt + F11) then (alt + D + L).

Other than compiling the VBA portion I can't think of anything else offhand I will keep this one in mind and maybe I can think of some thing else that may help.
Sep 20 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
As Denburt suggested, this message usually means that there's a problem somewhere in your VBA code preventing the MDB to MDE conversion. Compiling your code should point the problem(s) out so that you can correct them, and then the conversion should work. As with any major changes you're making to an app, you should make a backup copy of it first.

Linq ;0)>
Sep 21 '07 #3

Denburt
Expert 100+
P: 1,356
you should make a backup copy of it first.

Linq ;0)>
Yeah, lets NOT forget that I keep about 10 or more backups of most of my DB's at any given time. It may be a bit excessive, however on very large Front End Apps. it can be a good thing at times.
Sep 21 '07 #4

P: 40
Thanks, worked perfect.

I tried it on a development copy. I also keep around 8-10 backups, our server is unstable and it has saved us more than once.

Regarding MDE files, are they faster, or just safer in that users can't change anything? I'm struggling to speed up my front end, and I thought this might help.

Robin


Yeah, lets NOT forget that I keep about 10 or more backups of most of my DB's at any given time. It may be a bit excessive, however on very large Front End Apps. it can be a good thing at times.
Sep 21 '07 #5

Denburt
Expert 100+
P: 1,356
Thanks, worked perfect.

I tried it on a development copy. I also keep around 8-10 backups, our server is unstable and it has saved us more than once.

Regarding MDE files, are they faster, or just safer in that users can't change anything? I'm struggling to speed up my front end, and I thought this might help.

Robin
MDE files are a touch faster and prevents users from making changes. If you are struggling to speed things up there are lots that can be done. but it depends on what you've done and how things are put together structure wise etc. Is this on a LAN or a WAN? Have you tried using BeginTrans CommitTrans for large data manipulations? Hmmm I will think on this one some, glad we could help, let us know if there is anything else.
Sep 21 '07 #6

P: 40
I just now tried BeginTrans and CommitTrans, but it seemed to make it slower, though probably I'm not doing it right.

This is what the on open event calls:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3. Call ProjectList
  4. Call TaskList
  5.  
  6. End Sub
Project List is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ProjectList()
  2. Dim vAllCurrent As Variant
  3. Dim sdef As String
  4.  
  5. On Error GoTo error_handler:
  6.  
  7. If IsNull(Me.ProjectFrame.Value) Then
  8. vAllCurrent = ""
  9.     ElseIf Me.ProjectFrame.Value = "1" Then
  10.         vAllCurrent = "WHERE(((tblTypes.Option) = 'active')) "
  11.         Me.ProjectLabel.Caption = "Active"
  12.     ElseIf Me.ProjectFrame.Value = "2" Then
  13.         vAllCurrent = ""
  14.         Me.ProjectLabel.Caption = "ALL"
  15. End If
  16.  
  17. sdef = _query code_
  18.  
  19. Me.subfrmProjectShortView.Form.RecordSource = sdef
  20.  
  21. Exit Sub
  22.  
  23. error_handler:
  24.  
  25. MsgBox ("Error!")
  26. End Sub
TaskList is pretty much the same thing.

Where would BeginTrans and CommitTrans fit in there? Around the Me.subfrmProjectShortView.Form.RecordSource = sdef?

I've also seen mentions of slowness caused by the linked tables refreshing often. How can I avoid that?

Thanks
R

MDE files are a touch faster and prevents users from making changes. If you are struggling to speed things up there are lots that can be done. but it depends on what you've done and how things are put together structure wise etc. Is this on a LAN or a WAN? Have you tried using BeginTrans CommitTrans for large data manipulations? Hmmm I will think on this one some, glad we could help, let us know if there is anything else.
Sep 21 '07 #7

Denburt
Expert 100+
P: 1,356
Here is a link to a MS Support page with plenty of info this should keep ya busy for a while let me know if you have any questions regarding this item and I will help in every way I can. BTW BeginTrans/CommitTrans (referenced with an example in the document link below) is used when inserting/appending large amounts of data this allows you to rollback if you run into an error it isn't used to help speed up a form opening event etc. What kind of form is this (continuous form, single form, datasheet)? The reason I am asking is that if the table or query behind this form is opening large amounts of data then this could slow things down maybe you should apply a filter or something so the form opens with fewer records?

http://support.microsoft.com/kb/889588
Sep 24 '07 #8

P: 40
Thanks, that link is great. I will work my way through it slowly.

The subforms on my main form are continous, with hundreds/thousands of records each. I'm working on filtering them on the initial form open page, hopefully that will help.

From the main form, double-clicking a record selector opens a single-form view project form, which is unbound until defined by the main form. That form also delays opening, perhaps because there may be quite a bit of code behind it. I'll see what I can work through and bring up any questions I have. What's the best way to ask you, should I place them in this string or a new string?
Sep 24 '07 #9

Denburt
Expert 100+
P: 1,356
I may miss new questions in a new thread but you may get others to respond more quickly that way. If you would like you can do that and post a link in here to the new thread. Happy Coding
Sep 24 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.