Convert MDB to MDE

[RobinAG]

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

[Denburt]

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.

[missinglinq]

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)>

[Denburt]

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.

[RobinAG]

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.

[Denburt]

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.

[RobinAG]

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:

Private Sub Form_Open(Cancel As Integer)

Call ProjectList
Call TaskList

End Sub

Project List is:

Private Sub ProjectList()
Dim vAllCurrent As Variant
Dim sdef As String

On Error GoTo error_handler:

If IsNull(Me.ProjectFrame.Value) Then
vAllCurrent = ""
    ElseIf Me.ProjectFrame.Value = "1" Then
        vAllCurrent = "WHERE(((tblTypes.Option) = 'active')) "
        Me.ProjectLabel.Caption = "Active"
    ElseIf Me.ProjectFrame.Value = "2" Then
        vAllCurrent = ""
        Me.ProjectLabel.Caption = "ALL"
End If

sdef = _query code_

Me.subfrmProjectShortView.Form.RecordSource = sdef

Exit Sub

error_handler:

MsgBox ("Error!")
End Sub

TaskList is pretty much the same thing.

Where would BeginTrans and CommitTrans fit in there? Around the Me.subfrmProjec tShortView.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.

[Denburt]

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

[RobinAG]

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?

[Denburt]

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