Access was unable to create mde database Results

Hello!

I followed boblarson - Access Front-end Auto-Updating Utility procedure. I tested the procedure to a smaller database following the procedure, it stop after it deleted the old copy but tried different ways and made it to work. Applying the same on a much larger database with lots of links to the BE, might cause me the problem. I tried different approach, still gave me “MSAccess was unable to create an MDE database”. One thing I did to avoid deleting the master-FE was to change the name of the mde and the location, the same as in the tbl-version_master_location. It works in my test database. Any idea what causing unable to create a MDE database?

BobLarson - great utility!

When trying to create an MDE using Access 2002 I get the message

"Microsoft Access was unable to create an MDE database"

The database is split and in Access 2002 format.

Could this be anything to do with References? I say this because I recently downloaded a dll for Redemption to make use of the SafeOutlook Library

Thanks

Oliver

Does anyone know the solution to this problem. I converted an Access97 db to Access 2002 and when I tried to create a .mde I get a message that Microsoft is unable to create a .mde. When looked this message up in the help files it says that I can not create a .mde from a replica database. I did not use replication when I create this file. All I did was change the default from 2000 to 2002 and imported my Access97 files to a Access 2002 file and then tried to create a .mde.

Can anyone tell me if by doing it this way Access still considers it a replication. If this is so how can I convert my Access97 to Access 2002 successfully.

Thanks

Well,

I've never before desired the option to compile to an MDE since previously I'd never done a professional level Access Application. Usually just little ditties for myself that are quick and simple. Now I've rather gone all out, designing a corporate database for a small business in town, and was planning to release it tomorrow for initial user testing, which usually finds a few glitches I couldn't. So i'm sitting on a split Database. 1 MDB is all tables and the data. The second MDB is forms, reports, queries, and two context (popup) menus.

I wanted to protect (hide) the code and figured life would be simpler compiling this into the MDE application and we'd be good to go. However, I'm getting this error: Microsoft Office Access was unable to create an MDE database. It goes on in the added help information about this error being the result of compiling large databases into MDE files, and only 2048 open TableIDs can be handled at a time.
Forms: 18
with modules: 18
Module: 1
Reports: 4
Queries: 14
Tables: 0
Linked Tables: 11

Total Objects: 66
So how is in unable to create an MDE file? what can I do to fix this process?
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

Good afternoon

When I click the Create MDE button I get the error message You cannot add or change a record because a related record is required in table 'MySysAccessStorage'.

I am a user who has been making some improvements to our database. I have been pretty successful with the help of this forum. Now that I want to test the improvements I need to create an mde, but can't.

The database was created in Access 2003 and I am now using Access 2007 on windows 2010. The original developer advised that we continue to use mde format - something to do with the runtime version everyone else who uses the database have.

Can anyone suggest what I need to do to convert my mdb to an mde?

With thanks

Greg

I am trying to create an MDE file. I have done this in the past. But for some reason I can not create one today. I keep getting an error stating "Microsoft Access was unable to create an MDE database." Does anyone know why I am getting this error.

Please Help

James

For some reason, Access is unable to create an mde file out of my mdb database. This has never been a problem before - I've converted this exact same database to an mde file several times before. I've been working on fixing a bunch of bugs recently, and now that I have those fixed, I want to recompile to an mde to distribute to my users. But everytime I choose Tools > Database Utilities > Make MDE File... it gives me an error that says "Microsoft Access was unable to create an MDE database." Anyone know what might be going on? How would I fix it?

Hi,

I've got a really simple Access 2010 database app [not Web] that I want to distribute and I have a few questions about that.

I am still working on a few features of the app and expect that I will be for a couple of months while it is being used.

Right now - my users all have MS Access 2010 and are using that to open the database. I don't expect any of them to know how to open in design mode - or have the time to play around as they are always really busy with other work.

But . . .
1. What is the easiest way for me to deploy so that users cannot get into design mode?
2. Is there a preferred method - other than the easiest way?

I just tried using the 'Make ACCDE' option and got this error message:
Microsoft Access was unable to create the .accde, .mde, or .ade file.
This error is usually associated with compiling a large database into an MDE file. Because of the method used to compile the database, a considerable number of TableID references are created for each table. The Access database engine can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Access database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used. It's a small database - and there are only 6 Forms and 5 tables [with very little data in them right now . . .

I have tried following some other methods [splitting the database is one] and am not sure which one I should focus on.

I'd prefer they don't have the ability to get into design mode. And I like the idea of the data being in a separate location so I can just distribute the changes as and when I make them.

I'd appreciate any suggestions.

Thanks!

Robin

I receive the following error message when I try to create an .accde file.
"Microsoft Office Access was unable to create the .accde, .mde, or .ade file." When I click on Show Help this is the message contained in it:
"This error is usually associated with compiling a large database into an MDE file. Because of the method used to compile the database, a considerable number of TableID references are created for each table. The Access database engine can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Access database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used."

From reading this, could the issue be that we have too many tables, forms, and queries existing in the database to be able to create the .accde file? Any help you could provide would be much appreciated.

Thanks,

Hello,
I am about to allow users to access a database I redesigned for others to use.
I am trying to save it as an ACCDE so they can't mess it up. The database is very very simple.

6 tables each with only 4 fields or less... and none are related to one another
7 forms.
1 report.
0 queries... it's just a SKU generator.

When I attempt to save as ACCDE I get an error that reads.

"Microsoft Access was unable to create the .accde, .mde, or .ade file.
This error is usually associated with compiling a large database into an MDE file.* Because of the method used to compile the database, a considerable number of TableID references are created for each table.* The Access database engine can only create a maximum of 2048 open TableIDs at one time.* Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Access database engine uses during the process of compiling a database as an MDE.* However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used."

Is there anything anybody can think of that might cause this? It seems like a weird error considering the database is so simple/small.

Thanks!!!

Hello,

This is my first posting, so at the outset, let me apologise for any inadvertent transgressions or violations of any etiquette of this forum.
---------------------
I have been attempting to create an MDE for an application which I want to commercially sell. When I attempted to create it, I got a message saying "Microsoft Access was unable to create an MDE Database".

I posted this to another webboard where I was told that it could be a compilation error and that I could check that out by trying the "Compile" option in Run sub-menu in VBA editor. I did it and I got the following error:

"Cannot define a public user defined type within an object module."

This error occurs for a statement which reads:

"Public Type DisplaySettings"

in a Class Module "Screeninfo"

My help does not give any help on this:

Can someone help me on this please?

Thanks in advance.

Uttam

I am trying to publish a new front end from my database. I have done this many times without a problem, but now all of a sudden I am getting a "Microsoft Access was unable to create an MDE file" error. Any ideas?

This may have been answered before but I can not find it anywhere.
I have created a database with lots of SQL code underlying and I am trying to establish some security.

I thought I would be able to create a front and backend database and set then change them over to MDE, but no go.

When I try to change the frontend I get the error:
"Microsoft Office Access was unable to create the .accde, .mde, or .ade file"

Can someone help me with this?

I need some advice on the best direction to go.

I finished a large project, 15 tables, 150 queries, 150 forms, 10 reports, and could not make an ACCDE file in Access 2007.

The error message reads:
Microsoft Office Access was unable to create the .accde, .mde, or .ade file. This error is usually associated with compiling a large database into an MDE file. Because of the method used to compile the database, a considerable number of TableID references are created for each table. The Access database engine can only create a maximum of 2048 open Table IDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc.).
There is no accurate method to estimate the number of Table IDs the Access database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one Table ID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.

So I did a lot of normalizing and increased the tables to 20, with a great deal of remaking the queries to remove about 50 foreign keys, as in Car1, Car2, Car3, etc., by making linking tables.

Tried to make an ACCDE file and got the same error message.

My question, do I really need to prune back the number of forms, which will limit the scope of the project (80% are dialog or subforms), or could this be gremlins from my normalizing the database where I need to build a virgin database?

Also, how important is this obstacle? I was just getting ready to split the database for my office server, I haven't done before. Is there a way to work around this.

Another problem I'm having is that Windows 7 Pro is not letting me switch the file from an accdb to an accdr form. The new explorer is not letting me do it the way XP did. Headaches, since I have a great accdb that I cannot evaluate in runtime.

Appreciate your help,

Dan

I have made a fe/be database in Access 2007 for use in Access 2003

Prior to beginning the design I saved both ends in as *.mdb for Access 2000-2003

After completion, I saved the FE as *.mde - however, upon distribution the end-user is unable to open the FE with an error that it was created in a newer version of Access.

The user was able to open draft versions of the *.mdb all along - why not the *.mde???? I just want this project to end!!!

Any suggestions?? Thanks in advance.

Hello all,
I have a little bit of a dilema. I have an Access database which I just finished converting to Access 2000 (it was 97 before). Part of the conversion was done by the wizard and part manually. I finished my conversion and ran my tests, all works great. So I created my MDE and away we go....so I thought. It seems as if no one else but myself can be logged into the computer and be able to open the application. The database is secured and I run a number of functions at logon in order to check for certain criteria before opening the app. (You must enter a username and password to open the app.) None of these functions were altered during the conversion and seem to work fine for me and any test accounts that I create, even if I give them the group permissions as the rest of the users (as opposed to Admin). All of this works fine if I am logged on the computer. If I go to someone else's computer and try opening the app (using my own username & password), the app bugs and doesn't open. I have narrowed down the problem to the following function and have even determined the line of code where it bugs out.

Code:

Function LogOn()
On Error GoTo ErrHandler

Dim usr As String
Dim db As Database
Dim rst As Recordset
Dim i As Integer
Dim flg As Integer


Set db = CurrentDb()

***bugs out here at the next line****
Set rst = db.OpenRecordset("tblUsers")

usr = CurrentUser

flg = 0

rst.MoveFirst

While Not rst.EOF
If usr = rst!strUsername Then
If rst!numFlag = 0 Then
flg = 0
Else
flg = 1
rst.Edit
rst!numTimesLogged.Value = rst!numTimesLogged.Value + 1
rst.Update
End If
rst.MoveLast
End If
rst.MoveNext
Wend

rst.Close
Set rst = Nothing
Set db = Nothing

If flg = 0 Then
DoCmd.OpenForm "frmPasswordSetup"
Else
DoCmd.OpenForm "frmMainMenu"
End If


ExitHere:
Exit Function
ErrHandler:
Debug.Print Err, Err.Description
Resume ExitHere

End Function


Could anyone see a reason that I would have to be logged on the computer that is opening the application in order for this line to work? The thing that I find so odd is that it has nothing to do with who is logging into the application but rather who is logged on the computer. Any ideas? I am desperate!!!

Having had little luck in getting any concrete information about effective ways to manage ODBC data, and after days of long trials and errors, I thought at least I could write up a summary of what I've found to work well for ODBC sources and hopefully others may be able to contribute to this.

Disclaimer: This is a far cry from being an authoritative and is woefully very subjective, being written by me and myself, using a MySQL server and Access 2003. I do hope that others who are able, can contribute more information to make this somehow more useful for those who would like to use Access as a front-end client.

There are three principal issues that must be considered when you are using a ODBC data sources:

1. Numbers of connections and different flavor of connections.
2. Size of recordsets and network traffic.
3. Binding forms to ODBC data sources.

As a starting point, one should read the whitepaper on Jet and ODBC Connectivity. This was for Jet 3.0, but should be valid for 4.0 as well.

ODBC Driver and Connections

The next thing is to understand what your ODBC driver is capable of. In a connection string, there is a parameter for "Options", which is usually a long integer. You should look at the ODBC driver's manual to ensure you have all options you need turned on. A good example would be to ensure that a certain data type is correctly mapped to Access's data type. In my case, I ensured that Big Integer (64-bit) were turned off because Access does not support this (and thankfully, I don't need it anyway).

By far most important thing you want to look for is to ensure that the driver supports two things- Multiple statements and Active statements. Jet does try to pool all queries to the server into one connection whenever possible, but if the driver cannot support multiple statements over single connection, Jet will open another connection to submit a statement. Therefore, if you have a form that has a combobox, Jet will send two queries, one for form's recordsource and another for combobox's rowsource. In case of lack of support for multiple statement, Jet will need two connections.

Even if your driver supports multiple statements, Jet may find it necessary to open a second connection if the driver cannot have more than one active statement. Active statement is when you fetch a big recordset and need to wait a bit for the full set to come over the wire. So back to that form with a combobox, if Jet find that the form's recordsource will take a while to be fetched, it will go ahead and open second connection to populate the combobox in order to allow for 'instantaneous' loading of a form from a user's POV.

Wherever possible, keep numbers of connection to a minimum. As mentioned before, Jet will try to pool all statements into one connection wherever possible, but Jet cannot help you out if you issue a query using a new ADO connection, DAO ODBCDirect workspace, or create a recordset within VBA. If you want to avoid the additional overhead of another connection, you should use a stored query instead, and make sure it has a Connection string in its query property window set, so Jet knows that it uses ODBC data and pools it with its open connection to the ODBC server. The stored query can then be called within VBA without incurring another connection.

DSN and DSN-less/ODBC and OLE DB connections

There are two ways to create connections, by using Data Source Name (DSN) or direct connection. To find right connection string, look at Carl Prothman's excellent website listing all possible connection strings.

Some people say connecting to DSN is better than without. Others say it's faster without DSN. One thing for sure is if you use DSN, you will need to distribute the DSN file to your users along with the finished database. I would recommend experimenting with both connection flavors before deciding which is better suited for your needs.

To make a DSN connection DSN-less, look at Doug J Steele's example or perhaps this Add-in by Paul Litwin.

Also, make sure you know whether you have different drivers available. I know for an example, some people prefer third-party drivers for Oracle over one supplied by Oracle themselves. Furthermore, some drivers are OLE DB which is supposedly better than ODBC (I say supposedly only because I am skeptical of Microsoft's promises of new and latest technology superseding a older technology, then dropping their claims and going back to older technology).

Binding forms

To Jet's credit, it is quite intelligent in retrieving just enough rows to populate the bound form, and will continue to fill up the recordset periodically while a user is working on a row. However, Jet has some quirks where it can do something very stupid, such as asking for multiple full table scans.

If you bind a form to ODBC table, Jet will do a full table scan. The simplest solution would be to add a WHERE clause to make the recordset smaller. But by far the better solution would be to set the recordsource's connection string to use ODBC instead of "(current database)". Jet will then query for keys, which it must have locally in order to manage a recordset, then afterward query a few rows at a time. If you can manage to keep keys smaller, all the better.

Furthermore, if you want to use subform, do not use Master/Child linking fields. This makes Jet go ape-shit, issuing several queries to show tables and index which is quite unnecessary. Rather, leave the link blank, and set subform's recordsource with a WHERE clause to match the parent form's key, so Jet will only ask for rows that match the key only without asking for indexes and table status every time you move around.

An additional benefit of making all queries for forms' recordsource an ODBC query is that you now have more control over how you handle those forms *with* Jet's intelligence. For example, you now can start transaction across multiple forms with subforms and commiting/rollbacking as you see fit, which would have not been possible using Access itself. I have been able to rollback the changes in two parent records and their related child records by issuing a SQL Pass-through query which simply says "Rollback;" and nothing was changed for any one of records, just as expected.

If you have a combobox or listbox on a form, this will mean another query in order to fill in the rowsource. Ideally, you want to keep some tables local to front-end clients, especially for tables that will never change (e.g. a list of states or provinces for example). For tables that may be updated peridocially but is otherwise select-only, you need to decide whether you want to make it a dynaset or a snapshot. For a small set, snapshot is faster, but for a larger set, dynaset is faster. You will need to experiment with the rowsoource to ensure that the network traffic and time to load the rowsource is satisfactory.

One problem is that Access won't accept an variable (at least I have been unable to do so) for a stored query's connection string, so if you need to change a connection string (e.g. you want to use different set of options, perhaps?), you would need to do this by hand, or at least write a function to loop through queries and updating the connection strings. Haven't tried that yet, but would imagine this is very possible.

Keep an eye on SQL log when developing

You definitely will want to have the server write a log of what queries it has received from Jet to give you feedback in ensuring that Jet doesn't do anything stupid. This has told me far much more about Jet than working within Access environment.

Unresolved issues

There is only one thing I haven't yet worked on- Sharing a recordset for different controls. Suppose we have a form with a combobox and a subform, both which use same table as a rowsource and recordsource, respectively. In this case, Jet will issue two separate query to the server, even if they may use same recordset. If anyone has been able to show how to get such controls to share recordset, that would be cool.

Also, I'm a bit worried about scattering connection strings all over the place, especially that it will contain a password. As I see no point in asking users to authenicate themselves every time they use a query with a ODBC connection string, given that variables can't be used in query's connection string. Would MDE make this less of a problem (I do not know if password still can be plucked out from a hex editor?). A possible solution is to synchronize Access's security with the server's security, because Jet will try to use Access's user & password for initial connection, and if a call to retrieve password was made along with a module at startup to 'fix connections' along with updating the connection string with the entered password, this may help somehow with keeping the password secret? Does anyone have more information on that?

External Link

A useful FAQ for linking Access with Postgresql which may be useful in giving you some ideas of how you can work with Access.
If anyone wants to contribute to this, please do feel free to do so. If anyone finds anything that is dead-on wrong, please give me a good can of whoop-ass- I hate to lead blind into ditch, so to speak.

I have created a database with forms containing sub-forms, based on tables held therein.
This will be used as a reporting tool which colleagues will send me via email for me to import their records into my central database (networking is not an option).

I have shyed away from embedding buttons and have not delved in to macros, believing that the functionality present in the window edges will suffice.
(My experience of these more technical aspects is very limited).

I have 'hidden' the tables in the Navigation pane and made the file into a MDE file for security reasons.

Basically, I want to be able to access the reported data in its Datasheet view AND also be able to export it out of others' versions and into mine.

Is this possible as I can't see how to retrieve my hidden tables! I want to have administrator rights - locking access to others but able to obtain full access to all views myself.
I have heard reference to Switchboards and the respective wizard also, but been unable to identify this.

Also, when I attempted to export the data I was only able to retrieve the main form's records and not that data held in the sub-forms...
What am I missing?

Any ideas to help a 'damsel in distress'!

Thanks.


Not finding an answer? Try a Google search.