Try moving the DAO reference up in the list one space at a time and see if this works. If the data type mismatch is the only error you are getting, then one of the fields is trying to capture the wrong type of data.
This part of the code does the following:
stSQL = "SELECT * FROM [Table]" 'when you open the recordset, you need to set the rs with some sort of recordset. SELECT * FROM Table selects all records from the table Table. This data is then held in memory so that you can manipulate it with the code.
stLongest = "I" ' this sets the longest word as "I", or one character long. The next word parsed that is longer than I, such as ME, will replace this, and the length of the longest word will go to 2 characters, or the word me.
Set db = CurrentDb() ' sets the database as the current database. In other words, this is the database you want to connect to, not some other database. If the data is stored in another database, then we need to connect to that database.
Set rs = db.OpenRecordset(stSQL) 'this uses the db object to open the recordset that we are going to parse and populates the recordset with all the records from the table Table.
This works in an Access 2000 mdb file, that has the table Table within it, with a DAO reference listed as the second lowest.
The query returns the longest word (not it's character count), the shortest word, and the average characters per word.