limit to number of reports in a ACC2000 database 1

[RaineSpencer]

Does anyone know if there is a limit to the number of reports in an access 2000 .mdb? I have a database with an access front-end and a SQL Server 2000 back-end. The database is about 650 MB on the back end and the front end is about 24MB (tables, queries, reports, forms and code modules), which is by no means a large database. I have a recurring problem with reports where I cannot copy a report to a new report (I get a "name conflicts with existing module, project, or object library" error message, even though nothing else has the same name), cannot open the VBA code for these reports, cannot export the report to a different mdb. The database functions perfectly, no corruption, etc. The reports that have this problem can run just fine. I have decompiled, re-compiled, compacted, repaired, run jetcomp.exe, etc.

Any ideas or known limitations to the number of reports/queries in an mdb??

TIA --

Lorraine Spencer

 

[Clifford77]

Try compacting and reparing the database. The database seems little large for access but if it used extensively then I guess not. Have a databse that is 24 megs that contains no data seems a bit much. There should no limits to the number of reports that access can handle.

 

[Databaseguy]

Whenever you copy anything with code behind it, you have to Compile the DB before closing. One error message you will get is (IIRC) "Network or disk error". The other one is the one you're describing.

Go to the VBE (Alt+F11), and in the left hand pane you should see the duplicate module. Note then when you double click on it, nothing happens.

You're gonna have to restore from a backup. Or (and this doesn't always work) create a new DB and import all of the objects into it.

 

[RaineSpencer]

I have compacted/repaired the database but this does not help. I also compile after copying new code. My problem is that it does not let me copy certain reports at all, and I also cannot import them into a new, empty database. I don't see duplicate modules in VBE; as a matter of fact, the problem reports do not show up in the project viewer at all (even though they have code attached to them). I am truly stumped, since the reports execute just fine.

Lorraine

 

[MichaelRed]

Are they 'hidden' objects? It shouldn't make any difference, but I'm curious.

As an alternative "test" to see if the size is really an issue, try copying OTHER objects out into another db and then deleting them (the copied objects), then compact the db and see if some of the 'mystery' reports are more open to manipulation. Oh - do this on A COPY of the db!

Are the reports in a seperate (referenced or opened as a seperate db) db? That could make them less visible.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RaineSpencer]

Michael,
Thanks for the suggestion. I gave it a try, and it doesn't improve my mystery reports. BTW, the objects aren't hidden. They are also not in a referenced db. Is there any restriction on the length of the object name either in VBA or in ACC2000?

Lorraine

 

[MichaelRed]

AFAIK, the only 'restriction' on naming is in the docs. As I recall, they (the names) shouldn't start with a numeric (0 through 9) and they need to be unique within some number of characters (24?). Otherwise all "LOCAL" reports should be visible.

Please post a sample of the method used to instantiate one (or a few ) of these. Since you cannot 'see' them in the db window, you must be instantiating them in some code. Perhaps that would provide a clue.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RaineSpencer]

Michael,
I can see the reports in the db window. I can open them in design view, I can run them, etc. What I cannot do is save changes made to them, and I cannot get to the vba code for the report (mostly simple code to produce "no data in report" messages to users). If I CTRL-C on a report in the database window, and then ctrl-V to copy the report to a new report name, I get the name conflicts with existing module, project, or object library message (even though the name is unique). I get the same message if I try to import the report to a new .mdb.

Lorraine

 

[MichaelRed]

Are you able (aparently YES) to write some relatively simple code? If so, try to write a module which simply loops through the reports and uses 'transferdatbase' method to copy the reports to a 'new' db. Help has a 'decent' explination and a quite rudimentary example (not for a report though). To simplify the code task, I would suggest generating the "New" .mdb file manually in the same directory (folder for the younger crowd) and setting up an array of the report names to use (from the current db) and only attempt a few - at least at first. I would include a couple of the drama / comedy ones as well as a few of hte mystery ones. If there are error messages in the process, trap and save them, as they may offer additional clues.

Another 'test' issue might be to simply attempt to rename one or more of the mystery reports.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RaineSpencer]

I wrote code that wrote all my report names to a table, then wrote code that pulled the report names from the table and did a transferdatabase acexport for each report name to a new .mdb that I created manually. I had 379 reports to transfer. The new .mdb appeared to have all 379 objects after running the code. I saved the new.mdb. I closed the new .mdb. When I reopened it, I had 17 reports, not 379. All 17 had VBA code I could access. All other reports had "vanished". BTW, my code did an "on error go to" that spat out the report name, and error number and then did a resume next. I only got 6 errors 4 error 2033s and 1 error 0 and 1 error 20... by my reckoning I should have gotten 362 errors, since all but 17 reports seemed to not really transfer.

Thanks again,
Lorraine

 

[MichaelRed]

This is at least an interesting challenge. If it were not such a large db, I would like to "see" it in person. My (el-cheapo) ISP wouldn't permit ANY 24M object through it's portals, so I guess I'm not going to get this opportunity.

Still, there are a couple of further 'question' to ask / explore:

First, have you looked at MSysObjects to see if the missing persons (or reports) show up (in the "NEW" .MDB)?

Second, you previously noted that you CNS "SEE" all of the reports in the db window (in the Original .MDB)

Third, Why is it necessary / useful to generate the list of report names as a table? They should be (programmatically) available through a variety of functions / collections in any version of Ms. A. - which is in accordance with the SQL standard(s).

Fourth, Given that you attempted to climb hte mountain (all 379 of the buggers) and got (at best) confusing results. Could you back track a bit and try just a mole hill or two? An example might be to create an array (of ten or so) report names which APPEARD to transfer (but then got lost) and see if any of these are able to cross the bridge?

Fifth, check to see if there is a PATTERN to the reports which DID sucessfully transfer (Are they the FIRST 17?, Are their name unique within the first N characters? are they bound to object(s) which have specific characteristics? Are they a "Set" which DOES NOT use a custom control? ... )

Sixth - if it isn't out of the question, could you "burn" a CD with the .MDB and snail it to me? I'd really llike to see this one.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RaineSpencer]

Having done further testing, the problem appears not to be a "report" issue, but a VBA issue. I have been modifying an off-the-shelf registration database product. I had been asked to make my modifications fit the existing structures/naming conventions as much as possible. The original programmers named reports with names like rpt05AttendeeLabelsAcross, rpt05AttendeeLabelswithoutSEssions, etc. and then used code to have all rpt05AttendeeLabelss appear together on one report menu, all rpt07s on another, etc. Long story short, my problem appears related to reports with code modules, where the report names are similar for the first 17 characters or so (because in VBA the module name becomes Report_name of report object). I think, and I am still working on verifying this, that VBA becomes "confused" because of these similar lengthy object names. I have found that I can create a new report, copy all existing report structures to the new report, copy code blocks from other modules, name the report something nice, short and non-repetitive and not have this problem re-occur.


Thanks!!
Lorraine Spencer

 

[MichaelRed]

Names, as I recall, may be of a 'generous' length (254 characters?) but must be "unique" within the first 18 characters. Referenece nomenclature not (A I recall) not included in the count. Your predecessor appears to have been flirting (Skating?) along the thin edge of unique nameing requirement.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places