Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 695
  • Last Modified:

MDE Conversion Error - Other solution...

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
j_coreil
Asked:
j_coreil
  • 8
  • 4
1 Solution
 
nico5038Commented:
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
 
j_coreilAuthor Commented:
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
 
nico5038Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
nico5038Commented:
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
 
nico5038Commented:
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
 
nico5038Commented:
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
 
j_coreilAuthor Commented:
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
 
j_coreilAuthor Commented:
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
 
nico5038Commented:
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
 
j_coreilAuthor Commented:
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
 
nico5038Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now