Rich Text Box only holds 64,000 Characters

[RMittelman]

I'm trying to create a small database to store code snippets.

The CodeSamples table has a field formatted as Long Text with Rich Text specified.
I loaded the code from a code module into a record. The code module, when exported has a file size of 216KB, so it's pretty big.

On the form bound to that table, a TextBox is bound to that field and configured as Rich Text. Everything should work fine, but the code is cut off short.

I found this on a Microsoft article:

Long Text
    
In .accdb files, the Long Text field works the same as the Memo field of old. That is, it can store 
up to about a gigabyte of text, even though controls on forms and reports can only display the first 64,000 
characters. You can set Long Text fields to display Rich Text, which includes formatting like bold and 
underline.

So the question is, is there any way around this limitation?

Thanks...

Update:
The articles say you should be able to store much more data in the Long Text field in the table than is being allowed in my database.
The text that is in that table field is 160,356 bytes. I get that by querying the table (Select Len(SampleCode) AS len From Samples).
At this point, I cannot add any more text to that field, even directly in the table and not using the form at all.
For some reason, if I edit the field data by tapping Shift-F2 and bringing up the edit window, I can remove text from the end of the field, but can't add any more text. Even if I remove several characters, save the field then go in to edit it again, I can't add any more text to it. What's going on here???

[moke123]

Up to about 1 gigabyte (GB), but controls to display a long text are limited to the first 64,000 characters.

Might not be the field. Could be exceeding the controls limit.

[Minty]

I think once the field data approaches the 64k control limit you can only manipulate it via VBA.

TBH you could easily split the display of the data over two or more controls by simply checking the length of it and dividing it onto suitable portions.
On a more practical note, how many pages of code is 160000 bytes? Surely if that is one sub or function it could be split down a little to make it more manageable?

[RMittelman]

I think once the field data approaches the 64k control limit you can only manipulate it via VBA.

TBH you could easily split the display of the data over two or more controls by simply checking the length of it and dividing it onto suitable portions.
On a more practical note, how many pages of code is 160000 bytes? Surely if that is one sub or function it could be split down a little to make it more manageable?

Thanks for the answer Minty. The code I'm trying to store is an entire module. I have a utility module with all my much-used subroutines and functions. Even though it is large, it seems to work fine in my projects, with the various methods easy enough to locate in alphabetical order by function or subroutine name.

Still, it seems like this project is not suited for Access, since only 64,000 bytes will "fit" in a TextBox. I would need 2 or 3 just to hold that module code.

Maybe this needs to be a c# solution instead...

[RMittelman]

Might not be the field. Could be exceeding the controls limit.

Thanks moke123. I know it's too much text for the control. But for some reason I can't even add more text to the end of the field directly in the table. I believe you need to do that in VBA rather than editing the field directly if the text is so long.

[Micron]

Did you give any thought to outputting the modules to documents such as Word, and storing their paths in a db table? You could also use db documenter for that. You might get a bit of fluff before option statements, but it's possible to output only code for one or every module into one or more documents if that helps. Not sure I'd want to try and read a whole lot of code in a textbox even if it did all fit.

[moke123]

I have a helper form that I import when working on a db. It contains several different helper procedures, one being a quick view of the modules and the code within them.
I stripped this form down to just the module part which is attached. Just import the form to a project, select a module from the list of modules, then a procedure from the list of procedures in that module.

***** You need to set references to Microsoft scripting runtime and Microsoft visual basic for application extensibility 5.3

Perhaps something like this would solve your issue.

[RMittelman]

Did you give any thought to outputting the modules to documents such as Word, and storing their paths in a db table? You could also use db documenter for that. You might get a bit of fluff before option statements, but it's possible to output only code for one or every module into one or more documents if that helps. Not sure I'd want to try and read a whole lot of code in a textbox even if it did all fit.

Thanks, Micron.
Actually, that's kind of what I am doing. Instead of trying to store code in a Long Text field in the database, I'm storing it in a text file, one for each code snippet. This could be the same exact file that the VBE exports (*.bas, *.cls, etc.). In the data table is a field containing the path-name of the text file for each code snippet.

[isladogs]

You can add up to 1GB of data in a long text field if done programmatically.
This is from Access specifications:

Number of characters in a Long Text field Note: Beginning in Access 2013, Memo fields are now called Long Text fields.

65,535 when entering data through the user interface; 1 gigabyte of character storage when entering data programmatically

However, I would suggest a different approach.
Create a module code library as a separate database app to store all the modules you want to have available for re-use
This is a screenshot of my version:



Double clicking any module in the list, opens the module so I can view/edit/copy the code for a procedure or entire module
Sometimes I just add my module code library as a reference file so all the objects are available to that file

Or save it as a template so you can re-use items as 'application parts'

[RMittelman]

I have a helper form that I import when working on a db. It contains several different helper procedures, one being a quick view of the modules and the code within them.
I stripped this form down to just the module part which is attached. Just import the form to a project, select a module from the list of modules, then a procedure from the list of procedures in that module.

***** You need to set references to Microsoft scripting runtime and Microsoft visual basic for application extensibility 5.3

Perhaps something like this would solve your issue.

Wow, what a nice sample. Love this!

Playing with your sample brings to mind a new trick. By doing this:

Dim ap As New Access.Application
ap.OpenCurrentDatabase "[other database path].accdb"

I can use

?ap.VBE.ActiveVBProject.VBComponents.Count

in the immediate window, and it returns data for that database instead of the one I'm working in.

So it looks like I can modify this to compare a module in the current database to the same module in a different database.

[isladogs]

Hi Moke
Your utility is excellent but it doesn't show any of the code in the declarations area of each module. For example, API and Type statements as well as any module level variable declarations.
It was for that reason that I chose to only display each module in its entirety when I created my module code library
I haven't checked but think it should be possible to show the declarations section on its own using VBE Extensibility

[moke123]

Thanks. That example was just a small piece. Still a work in progress.



My app is just 1 form with all the code within the one form. I keep in my Application Parts so i can import it easily into any project.
It also connects to the backend of my code repository database. So any code I write or come across I can save with the click of a button.

Also makes it easier to search for procedures, rather than clicking and scrolling through the VBE.

[RMittelman]

I have a helper form that I import when working on a db. It contains several different helper procedures, one being a quick view of the modules and the code within them.
I stripped this form down to just the module part which is attached. Just import the form to a project, select a module from the list of modules, then a procedure from the list of procedures in that module.

***** You need to set references to Microsoft scripting runtime and Microsoft visual basic for application extensibility 5.3

Perhaps something like this would solve your issue.

This is helping out quite a bit Moke123. Thanks for posting that. I was able to use your code (attributed of course) in my project. Also figured out how to get the declarations code and property procedures. I will post my sample app when a bit more complete.

I was also able to make it a stand-alone utility with 2 panes, so I can get lists of code from 2 different databases and compare them. So far everything works except for the comparing part. I thought I'd use an SHA1 hash of the procedure code and keep that handy to compare against the other database's procedure code of the same procedure. This seems a simple way to compare "sameness" without having to store the procedure code in its entirety for comparison. Unfortunately I can't figure out how to get the SHA1 hash in Access VBA. There are 3rd party utilities that will do that, but I'd rather put the entire logic in the 1 form for simplicity. I found various examples of hashing a text string, but they all require calling .Net functionality in System.Text and Cryptography. I can't figure out how to do that, because I can't see how to reference the .Net framework itself in my Access project. It's not in the list of references. Any ideas how to do that?

Thanks...

[RMittelman]

Using the suggestions supplied, I have created a database which will compare code from 2 different databases. Since developers often reuse code modules between databases, it would be valuable to compare these and identify differences.

There are 2 TextBoxes on the form, each one used to enter the name of an Access database. Each TextBox also has a browse button to the left of it, which can locate a database file. Instead of the path-name of a database, you can also enter ".." (without the quotes) to indicate this current database.
To the right of each TextBox is a button to load the database objects into the TreeView.

Once both databases are loaded, there is a "Check Procedures" button to identify which objects and procedures differ between the 2 databases.
TreeView nodes will be shown with images indicating whether code agrees or disagrees.

When an appropriate TreeView node is clicked, code from that procedure will be displayed. If the other database has the same procedure name, it will also select that object in the other TreeView and display it's code. Code differences can then be seen. If the ShowCodeHeaders setting is turned on, any comments above the procedure will also be shown.

If differences are not obvious, the "Compare Code" button can be clicked, which will export both procedures to text files and run a file-comparison utility to get an accurate comparison. Currently, Beyond Compare 4 is supported. Other utilities (such as WinDiff) may also be supported if they provide a command-line option for running the utility with the 2 file names to be compared. This can be changed in the Settings form.

If the ExpandTreeViews setting is turned on, all TreeView nodes are expanded by default. If not, only those modules with code differences are expanded by default.

Please feel free to test this utility out, and supply corrections or enhancements in this thread.

Thanks for all of the suggestions so far!

AccessCodeCompare.zip

[RMittelman]

I've refactored the code a bit to remove all tables and forms except the main form, so it can be copied into other databases and work as-is.
AccessCodeCompare2.zip