Talking to other files (1 Viewer)

[mbc]

Hi everyone.

I have looked over the forum but I cannot find an answer for this... I have imported and redesigned a program from access 97 to access 2007. But because I have added so much to the new program it has exceeded the limits of Access. Yes I did not even know there was a limit. It runs fine but I cannot make an mde file (the 2007 equivalent) as it exceeds the limits...

Access reports that it exceeds the table limits. We are not talking about tables themselves but it uses tables or something to this effect and allocates one to each form etc... I am going through and culling forms and making sure that the modal is not set to yes (I have many of these set to yes, if forms that dont require this are set to yes it allocates 2 table links to that form).. So far I got to a point where the mde creates but does not run properly... I did a test and indiscriminately deleted a whole bunch of forms to see if this was still a problem and it worked once I did this.. So it tells me that I have more forms to cull and fix..

This is all good as I am having to tidy up.. But I can only go so far with this, I will get it to work but it wont leave much more room for further enhancement. It means that I may have to eventually recreate in dataflex or similar, a whole new program and something new to learn. Which kind of like sux as I have put another 300 hours into this project and It would take at least another year to create it from scratch again. .

I am just a hack so I know I have a lot of screens that could be doing more but the program is very big and you still cant put a V8 into a 4 Cylinder Car, so I it needs a lot of what I have done. In fact I have kept everything to a minimum the whole way as this allows the user simplicity, and most of the work is done out of sight, which is where most of the forms are used.

Can I link to forms outside of the main access file.. Linking to tables is obvious, but linking to forms in another file is not something I know if I can do.. I doubt this is possible but I don't know everything so I am hoping it is possible and one of you smart guys can help me out

thanks

 

[gemma-the-husky]

i would suspect your app is not correctly normalised.

what is it doing, broadly? and how many tables do you have?

 

[Jeanette Cunningham]

In addition to Gemma's fine answer -
Tell us which limits are causing a problem. What is the text in the error messages?
How many forms do you have? How many saved queries? How many reports? How many ActiveX controls? How many relationships?

Does your app compile?
What references are set?
Is the app split into a back end and a front end?

 

[mbc]

The tables are all linked so as far a I understand they are not effecting anything. But there are only maybe 30 tables so this is nothing.

I am not sure what you mean by broading. Can you explain what this is.

thanks

 

[mbc]

Hi,

This is the message I get when I got to create the mde:

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.


Yes it compiles fine, there is nothing wrong with running the app in the standard format, but attempting to secure the code by creating the mde is where access limitations come in..

All the tables are linked from another database file, this database file is strictly for the running of the app.. Some ideas I have used are my own to get away from standard thinking, which has caused a lot more forms for things like help screens, as users of many programs hate the standard method of getting help, so I may have to rethink some of this and follow like sheep and join the pak..

There could be 300 odd forms (possibly more), a whole pile of reports and maybe at least another 100 queries, macros are limited to may 30, Active x I think one maybe two as it has no real need for this as its all about storing transactions.

Relationships are not heavy either, there are only 4 tables which strictly relate to another 4 tables, and all the rest is just information gathering and storing.

Reports is a let down, I one area where I have started to redesign to reduce the reports. In the beginning it was easy to create a new report to do exactly what another one did except add a few extra things, so I can reduce this.

Maybe there is something else I have to do which I am not aware of, I am a hack, self taught.. My limitations are understanding how to create really intricate blocks of code to do things that I know a query and a form and do anyway, so this may be partly where I have let myself down.

In the end I had now expectation that access had a mde limitation...

I am grateful for any help I am getting,,, thanks.


yours

mbc

 

[Jeanette Cunningham]

The number of tables, queries, forms reports ect looks normal for a large database.

You can get that error if you open objects but don't close them properly. Here is an example using a recordset. We call it 'cleaning up'.

'start of example
I always clean up in the Exit area with an On Error Resume Next at the top of it. That way if I attempt to clean up an object that was never initialized because the error occurred before that initialization had occurred, the exit area still runs through without an error. That would look like...

Dim db As DAO.Database
Dim rs As DAO.Recordset

'Set Statements here

'Code here

ExitHandle:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub

ErrHandle:
'error handling here
Resume ExitHandle
End Sub
'end of example

I believe you can also run into this error if you have massive numbers of combo boxes in an app - it hasn't happened to me, but I have read of it happening.

Hope the above helps you.

 

[ChrisO]

Since you specifically mentioned Reports here is something you can try to see if they are the problem.

Make a backup or two or three or…
Delete all Reports.
Try to make the MDE.

A compile from MDB to MDE simply compiles the code; it does not try to run the code during the compile.
This means that the code is syntactically correct for compile even though, obviously, you could not open a Report at runtime if it doesn’t exist.

However, this type of binary division could produce some worthwhile result.

If you get a compile to MDE, without the Reports, then you know there is nothing wrong with the rest of the application.

If you do not get a compile, to MDE, then the problem is not the Reports or not just the Reports. In this case then reinstate the Reports and drop the Forms. Again test by attempting to make an MDE. Now most databases have auto start at some Form so we do not want to run the beast because that would be a runtime error and not a compile time error.

Depending on the results obtained you have a more confined place to look for an answer.

This type of binary division can fairly quickly isolate the problem to one section of the application.

Chris.

 

[mbc]

Hi,,

thanks for your help...

I have actually managed to get it to compile into the mde, not once but several times so maybe I have seen the the back of this problem for now..

I ended up going over all the forms and making sure I deleted any form that was left behind and no long in use, there wasn't that many, but there were some just the same... I also ended up changing the module setting on any from that was of little significance except as help tips or just forms that had one set task (I still have more to look over).. After doing this and a couple more things I was able to create the mde without a problem and it runs fine.

I am going to continue with the clean up and tidy up as maybe I created some of this myself.. But it is funny that Jeanette mentions about the combo boxes.. I did a compile and created the mde file about 2 weeks ago to send to a friend to see the latest version, and it went fine. But sense then I have spent a lot of time on several forms each containing a bunch of combo boxes to make life easier. So I am wondering if this has also added to the experience I have been enjoying...

At any rate I am happy it is compiling to the mde and I can breath again..

thanks everyone for your help

mbc