Microsoft Access VBA SpellCheck Within Tables 1

[SherryBerry]

Hi, all. I've never done this before, but I cannot find the answer in google results as I have so many times before, so this is my last option.

I’m currently interning with a company who has hired me to help automate office processes centered around databases. So far, I’ve done things with access, excel, and VBA that I didn’t even think was possible, but for some reason, I’ve now hit a brick wall.

The task seems deceptively simple: We are importing fields from a project management database into access. Sometimes, fields such as project description or project title will have records that come in with spelling errors. Instead of meticulously reading these, or copying and pasting them into microsoft word one record at a time, what we would like to do is be able to cycle through the records belonging to a particular field and spellcheck them while they’re in the table and then highlight the misspelled word in some way.

Right now, I have a solution that will only check one word in the field, which is not sufficient. Another problem lies in the fact that this database usually has at least 3,000 records in it so the solution that I have right now, in addition to not working properly, is also very slow, which discourages me from trying to tweak it. Any advice whatsoever would be appreciated. Thanks in advance.

 

[SkipVought]

Hi,

Data Cleanup tasks can be quite challenging. In my opinion, one of the first considerations should be to ask the question, "do any of these fields contain controlled data values. I would guess that a Project Title might qualify as one of those fields, in which case there ought to be a MASTER LIST (a standard) to compare to. So the the issue might be not only to correct spelling, but to adhere to a standard.

Depending on the size and complexity, I might do this in Excel and then xfer to Access. If you can standardize your cleanup processes, you can record macro (VBA) that can automate part or all of this task.

Data Cleanup is usually an iterative process. Because of the uncertainty of user errors, not every once can be anticipated. Use a tool that you can manipulate easily and automate as much as possible.

BTW, it is possible to query Access data in an Excel sheet.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SherryBerry]

Skip,

Thank you, and you're right, that is a good idea for project title and I'll definitely consider it.

However, project description has no such standards to adhere to and the task of trying to spellcheck the records in this field is still very daunting to me. At this point, I'd be happy if I could only find a way to select the text in the table, and then run an access spellcheck command on it, in a Do Until recSet.EOF loop or something. I know that would be incredibly slow, but it's the only idea I have right now. Comments or suggestions would be appreciated.

 

[SkipVought]

If you want to do this ALL in MS Access, I'd suggest posting in forum705.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SherryBerry]

That's the thing: I don't necessarily care where it's all done, and it's probably worth mentioning that the tables that make up the database in access originate from spreadsheets in excel anyway.

The only reason I am rather attached to access is because number 1, we're trying to streamline the entire process into microsoft access for simplicity, and two, I think there's a better chance of achieving what I want through access. However, the more I think about it, the more I realize that I've been ignoring excel entirely. So I guess I'm off to try and see where I can get with Excel. =)

I'll also take you up on your suggestion to post in the other forum. Thanks.

 

[SkipVought]

If your approch is Excel, this is a good place to post your issues.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]