Solved

MDE Conversion Error - Other solution...

Posted on 2004-04-17
12
667 Views
Last Modified: 2013-12-05
I would like to create an MDE for a 97 database that I semi-successfully converted to 2003. When I try to create the MDE from it, it informs me with only the following explaination:
***
This error is usually associated with compiling a large database into an MDE file.  Due to the method used to compile the database, a considerable number of TableID references are created for each table.  The Microsoft Jet database engine version 4.0 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 Jet 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.
More information about this error message online.
 ***
I don't think this is the real problem but how can I tell? I only have a total of 30+- objects so I know it's not I've exceded the limit. Could my problem be that I am dealing with linked tables?

Let me get to my question, if I cannot create an MDE from this, is there a method or tool to create a form in VB .NET from a form in Access automatically?
0
Comment
Question by:j_coreil
  • 8
  • 4
12 Comments
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I would start with compiling the A97 database first as in most cases a compile error causes trouble.
Do you still have A97 available ?

Nic;o)
0
 
LVL 2

Author Comment

by:j_coreil
Comment Utility
Yes I do still have a copy of the A97 database. I don't have a copy of A97 and A2003 makes me convert it first, can you explain a little more.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
It would be preferable to do a Debug/Compile all modules first with A97 before the migration to A2003.
When zipped less then 2Mb you can drop it in my mail and I can do it for you.

Nic;o)
(Click membername for email)
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Hi j_coreil,

The database compiles with no errors in A97 !
There's however a relink tables module that's probably causing all the trouble.

Personally I use a simple relink routine like:

Function fncRelink()
'function to relink tables to a "_be.mdb" database in the same folder as the frontend

Dim td As TableDef

For Each td In CurrentDb.TableDefs
  If Len(td.Connect) > 0 Then
     td.Connect = ";DATABASE=" & Left(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "_be.mdb"
     td.RefreshLink
  End If
Next

End Function

You just have to be able to change the line:
td.Connect = ";DATABASE=" & Left(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "_be.mdb"

into the path/filename you need.
As you use a .mde I guess the backend will be located elsewhere and perhaps even a flexible approach will be needed when you install the application on multiple PC's in different folders.

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Oops forgot to mention that there's a reference in the .mdb to DAO, this reference will have to be created in your A2003 version before generating the .mde.

Just press CTRL+G to get into VBA and goto Tools/References. There scroll to the "Microsoft DAO version #.##" library and check it. (The version number isn't important)

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Same problem here:
http://www.experts-exchange.com/Databases/MS_Access/Q_20957996.html#10853020

Just check alan's proposal for "light" forms.

Nic;o)
0
 
LVL 2

Author Comment

by:j_coreil
Comment Utility
So you are thinking that if I get past this error then I could create a MDE?

AND

To get past this error, I should A)redo the linking B)make lightweight forms?
0
 
LVL 2

Author Comment

by:j_coreil
Comment Utility
Would it be easier if I just redid this entire database in 2003 using importing but not have the linking? You've seen the database, all there is to it is like 2-3 real tables they use and 3 forms with one being a splashscreen.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Not sure or that will help, basically the converted database shouldn't be different from the A97 one.
I did try the conversion and .mde conversion from A97 to A2000 and from A97 to A2002 and both versions created a working .mde (Have no A2003 yet)
So it looks like 2003 is the trouble maker.
Wouldn't a 2002 .mde work in your situation ?
You should be able to create an A2002 copy using the database "convert to" utility.

Nic;o)
0
 
LVL 2

Author Comment

by:j_coreil
Comment Utility
Nic;o)

I've taken out everything I don't need and successfully converted it to a 1 database MDE file. I thought making a MDE file would allow me to run this as a standalone application but I was wrong. Besides creating a VB app, can I use sometype of runtime engine so I don't have to install Access on the target machine?
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
Comment Utility
Yes, but for that you need the .Net developer as far as I know.
I've had the A2000 developer edition, but the Access Runtime isn't for me "the way to go" as it's forcing an extra test in the runtime environment.

Personally I would go for a VB frontend solution in this case and then you can use the .mdb data with the free MDAC for Jet engine 4.0 or another RDBMS.

Nic;o)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now