Not Recognizing System Data Types

[bhujanga]

Suddenly code that has been working day in and day out is giving me an error and I haven't changed anything. This is the section of code that crashes:

Dim db As Database 'Current database.
Dim lng As Long 'Loop controller.
Dim rs As DAO.Recordset 'Table to append to.

The error message is "User-defined type not defined."
If I comment out the first line it makes it to the third line before giving the same message. What might cause this to happen? As I said I have done no programming since last time it was working and it still works in my backups. (I would just restore a backup, but I'd have a lot of data to sort out from today and I'd prefer to understand what's going on anyway.)
Thanks.

 

[MajP]

You lost your reference to DAO. Need to reselect your references. Compare your references to your previous version.

Database is in the DAO library. That is why when you code it out it makes it to the next DAO object call.

However, if it was me when something like that happens I get a little nervous. I would create a brand new blank database and then import all of your objects into it. Then double check your references.

 

[bhujanga]

Thank you for your help. I decided to take your advice and copy all of my objects into a blank database before trying to fix the existing one. It went along fine with the tables and queries and then when I got to the forms it started disallowing a lot of them, saying things like "No Current Record", "The search key was not found in any record" and others. It puzzles me why it's even worrying about this sort of thing when I'm just trying to copy objects.

So, it looks like I'm back to trying to fix the current version. I couldn't find where to select references. I'm using 2010. Thanks.

 

[MajP]

I never heard of that problem, do you have some autoexec macros?

When importing into a new database do it in this order.

1) Import all tables
2) Compact and repair
3) Import all queries
4) import all code modules
5) import all macros
6) compact and repair again
7) Import reports

8) Import forms (if there is going to be a problem, it is usually here).
Try importing a few at a time to be safe. If there is a corrupted form it will cause a problem.

9) Compact and repair one more time for good luck.

http://www.ehow.com/how_7210645_add-reference-ms-access-2007.html

If you cannot import a specific form there may be corruption and you can look at decompiling.

http://allenbrowne.com/ser-47.html

The fact that you cannot import, would make me even less inclined to continue with the current datbase. The fact that you lost a reference would also concern me. Normally databases do not get a 'little corrupted'. Once you start seeing symptoms it usually just gets worse.

 

[bhujanga]

I attempted the steps you suggested and it began giving me similar messages when I got to the reports.

I also tried re-attaching the references. I found that one of the references was indeed no longer checked - the problem is that that item was no longer in the list at all so I couldn't re-check it. So, this is getting rather frustrating, in that nothing seems to work. I'm thinking my next step is to start with a blank database and actually re-creating the forms and reports, and copying the controls themselves and then the VBA code for each form in. This seems rather crude and cumbersome, but I seem to be running out of options.

 

[MajP]

I found that one of the references was indeed no longer checked - the problem is that that item was no longer in the list at all so I couldn't re-check it

Which reference was this?

The reference for DAO should be contained here
Microsoft Office XX.X Access Database Engine Object

If this reference is not in the list, then you probably have problems with the Access application and need a reinstall.

Here is a test. Build a blank database. Add a module. Add this code

Public sub Test
Dim rs as dao.recordset
end sub

Now in vb choose Debug from the menu. If this breaks then I think there is an issue with your install.

Normally the only time an object will not import is if the object is corrupt. Please follow the instructions for database corruption in the link. Which means do a database repair before importing into a new database.

Can you import any forms/reports? If specific forms reports do not import then skip those.

I would be nervous about recreating this as you describe. My concern is either the database is corrupt, which is normally 1 or more forms or reports, or possibly even your install of Access.

Again I am not certain because I do not have experience in 2010, but I have never heard of this behavior without some type of corruption.


However, I guess the first step would be simply to add the reference for DAO (assuming you can). If there is not corruption then it should run.

 

[bhujanga]

I'm no longer at my office computer, so I was unable to perform your first suggestion directly. But I took a copy of the database on a flash drive and have tried seeing what happens on my other computer which is of course a completely different install. All of the same problems exist, including the 'Microsoft Office 14.0 Access Database Engine Object' reference not coming up on the list (It comes up for other databases). Am I correct that this would seem to rule out the install as the source of the problem?

I am able to copy some forms and reports but not others, so I could copy a large portion of the objects directly into a blank database. I don't understand corruption very well, so I don't have a clear picture of where it's likely to be hiding. But my thought is that the simpler the pieces that I take over, the chance there is of getting it. So if I take controls and put them on forms or reports and the copy VBA Code textdirectly into the appropriate events, I wouldn't be likely to capture the corruption. Is there any validity to this line of reasoning?

(The database has done a couple other wierd things. On some forms if I make a change in design view, and can't close the form saving the changes, it just keeps asking me if I want to but won't accept an affirmative response. The only way I can close the form is to say I don't want to keep the changes.)

I appreciate your help.

 

[MajP]

Yes it sounds like you application is fine and probably the database is corrupt.

Make sure you read that allen browne article. Try to repair the database first.

You should have not problem importing everything but the forms and reports.

I would try importing the forms and reports one at a time. Get the ones that you can. You can also save the form to a text file and reload from text file. See the link provided.