Hello,

I am getting an error message from Access when I try to add VBA code to a form. The error states "Out of Memory". Having done some research, I am thinking that I have bumped up against a character limit for a single module. The problem is that, having gotten this error message, I can't add a module to separate the code and avoid this limit. Even removing some of the code and then compacting and repairing does not help. I still cannot add any modules.

I should also state that I have already put a lot of VBA code behind this form. I am running a large number of SQL statements to import and normalize data from multiple sources, and elected to run the queries as sql statements in the code rather than saving the queries and using "docmd.openquery".

Is anyone aware of a way to recover from this, or do I need to re-create the database? (would not be too difficult, just a headache).

Also, and perhaps more importantly, if I do recreate the database, I will be splitting the code into multiple modules. Am I correct in assuming that this should prevent this problem going forward? Thanks!

Chris

Try going into the VB Editor, highlighting the existing module then selecting Export File from the File menu.

If that works, open the file you exported (it's just a text file...) and break it into a couple of smaller files.

Create a new database, go into the VB Editor and import your smaller files through the Import File option on the File menu.

Good luck!

Thanks Doug - it appears that re-creating the code in a new database is the way to go. Not difficult - but an unexpected detour. At least it will result in a cleaner structure (and a little more knowledge on my part).

By the way, are you aware of any issues running multiple back-ends, other than loss of some relational ability?

If your backend is so large that you need to split it across multiple databases, you may want to move to SQL Server. The inability to enforce referential integrity is a major issue to me: I'd have to be very desperate to split!

That being said, I recognize that sometimes you're forced to integrate between databases maintained by different groups, so sometimes it is a necessary evil. I'd assume it would be a little less efficient running queries that cross multiple back ends, but other than that, there's probably no other issue.

Incidentally, I should have mentioned that you should be able to export the module from your database, delete the module, compact your database then import the multiple modules into the same database. However, I'd recommend very strongly doing this on a copy, just in case...

If your backend is so large that you need to split it across multiple databases, you may want to move to SQL Server. The inability to enforce referential integrity is a major issue to me: I'd have to be very desperate to split!

That being said, I recognize that sometimes you're forced to integrate between databases maintained by different groups, so sometimes it is a necessary evil. I'd assume it would be a little less efficient running queries that cross multiple back ends, but other than that, there's probably no other issue.

Incidentally, I should have mentioned that you should be able to export the module from your database, delete the module, compact your database then import the multiple modules into the same database. However, I'd recommend very strongly doing this on a copy, just in case...

For the long term I agree SQL Server is the way to go. Given time constraints and hurdles needed to get through IT, it's not an option at this time. In some ways, this is a proof of concept to show how data has additional value when combined with other datasets. Assuming that's apparent when the dust settles, the best option would be to simply link to the original tables (read-only) and skip all of this importing and re-normalizing....

Onward and upward-

Chris