Microsoft Access “User-defined type not defined”

When trying to run the following code, I’m getting an error when it hits Dim qdf As QueryDef saying

User-defined type not defined

I think it’s really weird because I have a backup of this same project in which not many changes were made after it which is working completely fine for this. I’ve tried to retrace my steps and am struggling with that.

Any clue on what I might be missing? I’ve already tried the common suggestion of Tools > References and adding from there, and I had it working without that before anyway.

Private Sub Complete_Click()

Set objAD = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objAD.UserName)
strDisplayName = objUser.DisplayName

Dim dtTrained As String
Dim sprTrained As String
Dim compCheck As String

dtTrained = InputBox("Enter date trained as 'mm/dd/yyyy':", "", Format(Date, "mm/dd/yyyy"))
Debug.Print dtTrained

sprTrained = InputBox("Trained By:", "", strDisplayName)
Debug.Print sprTrained

compCheck = InputBox("How was competency verified?", "", "Enter here")
Debug.Print compCheck

Dim ConfirmMsg, ConfirmStyle, ConfirmTitle, ConfirmResponse
ConfirmMsg = "Continue?"
ConfirmStyle = vbYesNo
ConfirmTitle = " "
ConfirmResponse = MsgBox(ConfirmMsg, ConfirmStyle, ConfirmTitle)

If ConfirmResponse = vbYes Then

Const dbConnStr = "CONNECTION STRING IS HERE"

Dim qdf As QueryDef

Set qdf = CurrentDb.CreateQueryDef("")

Dim sqlString As String
sqlString = "INSERT INTO dbo.TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, TRAINED_BY, STATUS, COMPETENCY) " & _
"SELECT '" & EMPLOYEE_ID.Value & "', '" & DOCUMENT_ID.Value & "', '" & LATEST_REV.Value & "', '" & dtTrained & "', '" & sprTrained & "', 'C', '" & compCheck & "'"

qdf.sql = sqlString

qdf.ReturnsRecords = False

qdf.Connect = dbConnStr

Debug.Print sqlString
qdf.Execute

'CurrentDb.Execute "INSERT INTO TRAINING_RECORDS (EMPLOYEE_ID, EMPLOYEE_NAME, DOCUMENT_ID, FILENAME, REVISION, DATE_TRAINED, TRAINED_BY, STATUS) " & _
'"SELECT EMPLOYEE_ID, EMPLOYEE_NAME, DOCUMENT_ID, FILENAME, REVISION, DATE_TRAINED, TRAINED_BY, STATUS FROM uSysTRAINING_RECORDS " & _
'"WHERE DOCUMENT_ID = '" & DOCUMENT_ID.Value & "'"

CurrentDb.Execute "INSERT INTO TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, TRAINED_BY, STATUS) " & _
"SELECT * FROM uSysTRAINING_RECORDS " & _
"WHERE EMPLOYEE_ID = '" & EMPLOYEE_ID.Value & "'"

CurrentDb.Execute "DELETE FROM TRAINING_NEEDED " & _
"WHERE EMPLOYEE_ID LIKE '" & EMPLOYEE_ID.Value & "' AND DOCUMENT_ID LIKE '" & DOCUMENT_ID.Value & "'"

Forms!TRAINING_MATRIX.TRAINING_NEEDED_SUBFORM.Form.Requery
Forms!TRAINING_MATRIX.TRAINING_RECORDS_SUBFORM.Requery
Forms!TRAINING_MATRIX.ALL_TRAINING_RECORDS.Requery

Else

End If

End Sub

Answer

Because QueryDefs like TableDefs, Recordsets, and even Database are objects under Access’ Database Object (DAO) library, you need to have your MS Access project reference the library. Otherwise, VBA cannot interact with such objects, properties, and methods. DAO is the default database API of MS Access and very important to have selected if developing VBA modules among other defaults as shown below:

• Visual Basic for Applications
• Microsoft Access #.0 Object Library
• OLE Automation
• Microsoft Office #.# Access database engine Object Library

By contrast, ADO is a different database API requiring a different, non-default library: Microsoft ActiveX Data Objects #.# Library. In fact, as shown above, VBA itself is a reference as first checked item!

Actually, the full named reference of DAO objects would be prefixed with DAO to underscore its source. It is shorthand as you do to leave it out.

Dim db  As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set rst = db.OpenRcordset("SELECT * FROM myTable")
Set tdf = db.TableDefs("myTable")
Set qdf = db.QueryDefs("myQuery")