error message: "Method ‘Width’ of object ‘llmexGridColumn’ failed"

[roderator]

I've been building up an Access database for several years now. It has 6,800 entries, in the form: Day|Month|Year|Date|Events|Main Source where | is the separator. All of the entries are small except for Events. In my infinite wisdom I have developed the habit of using the database itself to enter new entries, that is, I prepare the text in Word, and copy it into the database sections. Sometimes the Events texts are really quite long, so I periodically experience the error message that says the text is to long, and the program won't accept it. I trim it back (to roughly sixteen pages in Word) and then the program accepts the entry. Periodically I export the database in .txt file format, do some extensive checks for words with accents and many other things, and then re-import it into Access. Once in a while I get the error message: "Method ‘Width’ of object ‘llmexGridColumn’failed" and I have gone into the text file and messed around until it is once again able to be imported into the database. The number of entries in the database have gotten more numerous, and I am desperately hoping there is some methodology I can use to make it simple. I gather the message refers to there being more than 65,000 (or whatever the limit is) characters, but as I say the program itself originally accepts the entries, so what the hey? This time I took the text file and tried to cut it back and cut it back to see when Access finally accepts the import file. It is ludicrously bizarre, because I kept cutting back until I had entries 10-20 all by themselves in a text file, and it still wouldn't accept it. I took out the two longest events, and then it worked ... but the events had been working since basically I created the database, and they were only about two or three pages long, no where near the purported upped limit. It seems that whatever the problem is has somehow, magically, infected basically ALL of the database entries. It is making me crazy(er). Please, if you can help me out you would be saving my sanity. And get my eternal gratitude. The ideal solution would be to be able to determine easily which entry(ies) are causing the problem, and I will trim them (although, as I've said, Access itself initially accepted the entries). THANKS!!

[CJ_London]

not sure I can help, but if something was working and now it isn't, quite simply, something has changed. Perhaps you've recently moved to win 10 or access 2013, perhaps you are now using unicode text, perhaps your db has reached its 2gb limit, perhaps the db has become corrupted. Not aware of lmexGridColumn, perhaps this is an active x component which doesn't work in a new environment. The field names Day|Month|Year|Date are all reserved words, using them can cause issues.

[roderator]

Thanks for the message. I've been using Win 10 for ages, and also access 2013 since the beginning of the database. I've run into this problem a few times before, and by going in and messing with the files, it would start working again. The database is 265,780 kb. Re day month, etc., again I've been using that since the beginning, four or five years ago. Thanks again; need all the help I can get.

[isladogs]

You might find this link interesting for info about size limits for different parts of access databases.
https://support.office.com/en-us/art...rs=en-US&ad=US

There are two different size limits for memo fields depending on how data is inputted. Either 65535 characters or 1GB of character storage.
This may explain your situation.

You could consider splitting the longest entries into two or more fields

Even if you've not had problems to date, I would still recommend changing field names.
I would also recommend making a backup and then compacting as 265MB seems far too large for that number of records.

It's likely that all that pasting from Word causes database bloat as well as the risk of corruption
If you can possibly do data entry in Access it would be far better.
Failing that, I would use a text editor like Notepad as you can import text files rather than copy and paste from Word

[roderator]

Thank you very much for your thoughtful comments. I've tried the compact and repair and it did not fix the problem. As may be evident I threw myself into Access without really understanding things. Typical, I fear. I will look into your other points progressively. In particular I will look into how you do data entry; and your comment about using Notepad seems excellent. Given the continuing occasional problems with Access however, I have been increasingly thinking about taking the data into a sql database, about which I also know essentially nothing. If I may ask, in your opinion is this a reasonable concept, given that in due course (perhaps quite soon) I want to put this and several other Access databases on a website. Any comments and suggestions would be greatly appreciate. Thanks very much.

[CJ_London]

Given the continuing occasional problems with Access however

Re day month, etc., again I've been using that since the beginning

this could be the reason why. The errors they generate are often inexplicable and can appear to be unrelated. The problems you are having is likely to have cause by your acknowledged lack of experience, so not sure why you think moving the back end to sql server will resolve that, given that you know 'essentially nothing'.

Re sql server, if you are planning to migrate to the web, then it makes sense to change now. But you will need to rewrite the front end in html if it is intended the public has general access (with or without login details) from your website or you want to be able to access it from multiple locations. You can use online data storage which an access front end can use as a backend (such as sharepoint, sql azure) but due to security requirements, the backend will not be accessible by anyone. Typically you provide a list of allowed IP addresses - so you will need fixed IP's for all the locations you intend to access from. If you are a company with 10, 100, 1000 employees this is not normally a problem because they have the appropriate IT infrastructure.

If for your personal use, also consider using an online terminal server provider. Both front and back end are on the server and the only thing passing back to your monitor is screen updates. Consequently performance is usually nearly as good as if the backend was on your local drive.

[isladogs]

Compacting will shrink the database but it can't fix your issue with excessively long text.

For info, several of my databases use data imported from JSON (text) files downloaded from the web.
These files can be very large. For example the attached image shows an imported JSON file of over 47MB with almost 49 million characters!
This is imported in around 1 second, then parsed and saved as a normalised table - I don't experience the issue you describe
Just for comparison I opened the file in Word - it was 7527 pages long



Using SQL server as a backend database has many advantages e.g. scalability & stability but whether you can convert without much work will depend on your database structure.
For example, neither multivalue fields nor attachment datatypes can be used in SQL Server.

As for web databases, Access has removed support for these & new web databases can no longer be created.
If that is a priority, you will need to find other ways of achieving this e.g. using Azure .... about which I know nothing

[Micron]

Don't know where you're getting your data from, but my suggestion would be to dump it into excel so that it's in columns. Then copy 1 column at a time to a 2nd worksheet and try to import from there. Keep adding 1 column to the 2nd sheet until you encounter the error, then you will know which field is the culprit. I suspect you are exceeding the max character limit for that field.

Not that my answer suggests I disagree with any of the comments re: the use of reserved words, or any other sage advice that was already given.

[roderator]

Thanks so much for the comments. Re my going to sql, as you have realized I basically don't know what I'm talking about, other than I gather that is the standard format for web pages. I do have the free time to learn such things, which I guess is always the good news. I have gathered that by having my web page on Wix that they incorporate all of the necessary stuff, including (again, so I gather, or at least devoutly wish) being able to set up database programming easily using their programming. I will change the day, date, etc. labels, and see what, if anything, develops. Again, thanks enormously. If you have any further comments, like "Are you really this stupid?", please feel free to put them up. As I've said, clearly I need all the help I can get.

[roderator]

Thanks again. I will keep in mind your comments about JSON (wow, by the way), but hopefully my data won't be getting to that level! As mentioned elsewhere in this forum thread, I hope to start learning about sql databases ... and if that strikes you as an incredibly naïve thing to say, I fully expect you're right.

[roderator]

Thanks for the comments. I did try converting to excel, and found that my text data was clipped off after a page or two. Perhaps saying this again reflects the fact that I don't know anything about excel either.

[isladogs]

Agree with ajax that there is no point transferring to SQL Server if you are struggling with Access.
Its certainly not easier to understand though, as previously stated, it has many advantages.

The main point I was making stating that similar issues do not occur with JSON files was that its a text file so no formatting - formatting causes truncation of memo fields
It wouldn't surprise me if issues had arisen if I'd tried to import the same data from that Word doc over 7527 pages.
I'm not going to attempt it even as an experiment.... Access will almost certainly crash!

Suggest you read this article explaining various reasons why memo fields can get truncated: http://allenbrowne.com/ser-63.html

[roderator]

Well, I changed the labels to utterly inoffensive ones, exported the text file, opened a new database in access, tried to import the text file, and had the usual error message. Disappointing, but certainly in keeping with my general philosophy regarding the way the world works: it works badly, if it works at all. Regardless, thank you all for your help.

[isladogs]

Well, I changed the labels to utterly inoffensive ones, exported the text file, opened a new database in access, tried to import the text file, and had the usual error message. Disappointing, but certainly in keeping with my general philosophy regarding the way the world works: it works badly, if it works at all. Regardless, thank you all for your help.

Were the labels 'offensive' originally?
Access censors may have stepped in .... !

Suggest you post your new database together with an 'inoffensive' text file for someone to look at & hopefully resolve this for you