User defined type not defined

[Mac R]

Howdy,

I went from Acc97 to Access 2013 and when I did I had to add a reference to Microsoft DAO 2.5/3.5 Compatibility Library in order for variable declarations like

Dim MySet As Recordset
Dim MyDB As Database
Dim TempQuery as QueryDef

to work... Once I added this reference my code compiled and worked fine. I think I know this, because when I look back at my code I saw this:

Option Compare Database
Option Explicit
'REQUIRES Reference to Microsoft DAO 2.5/3.5 Compatability Library
Dim MyFile As Variant
Dim Resp As Variant
Dim SQL, Msg, varID As String
Dim MySet As Recordset

I have now migrated to Access 365 under Office 365, and the code no longer compiles. I get a Compile Error: User defined type not defined. Not knowing what I'm doing, I attempted to reference the Microsoft DAO 3.6 object library and got this... Error loading DLL. There is no reference to Microsoft DAO 2.5/3.5 Compatibility Library in the list.

I use the above declarations all the time in my code, everywhere. For example, I create temporary query defs using this 'generic' function.

Function CreateTempQueryFromSQL(varSQL As String)
On Error Resume Next
DoCmd.DeleteObject acQuery, "AdHocExcelQuery"
On Error GoTo 0
Dim MyDB As Database
Dim TempQry As QueryDef
Set MyDB = CurrentDb
    Set TempQry = MyDB.CreateQueryDef("AdHocExcelQuery", varSQL)
Set MyDB = Nothing


'Old:
'With MyDB
'    Set TempQry = .CreateQueryDef("AdHocExcelQuery", vSQL)
'End With


End Function

Yeah, I guess I hafta do full disclosure too... I migrated from Win7pro to Win11 Home... not that that would do anything, just talking variables in the equation.

Interesting Note: I copied the above code exactly, including the declarations section, opened a NEW database in 365, created a test table called t_test and populated it with some silly data, then pasted and ran this function in a new module then ran it in the immediate window and it worked. No errors, no issues, created AdHocExcelQuery without a stumble. Ran it again, it deleted the old AdHocExcelQuery query and created a new one. I checked the references in the test database against the one producing the error(s). Here are the two reference lists side by each. Accdb 365 is the TEST db, and COOP1 is the db with the compile issue.



This is the pasted code:

Option Compare Database
Option Explicit
'REQUIRES Reference to Microsoft DAO 2.5/3.5 Compatability Library
Dim MyFile As Variant
Dim Resp As Variant
Dim SQL, Msg, varID As String
Dim MySet As Recordset


Function CreateTempQueryFromSQL(varSQL As String)
On Error Resume Next
DoCmd.DeleteObject acQuery, "AdHocExcelQuery"
On Error GoTo 0
Dim MyDB As Database
Dim TempQry As QueryDef
Set MyDB = CurrentDb
    Set TempQry = MyDB.CreateQueryDef("AdHocExcelQuery", varSQL)
Set MyDB = Nothing


'Old:
'With MyDB
'    Set TempQry = .CreateQueryDef("AdHocExcelQuery", vSQL)
'End With


End Function

WHAT is going on? Can anyone shed any light on this, please and thank you?



I did attempt to search the forums for something, anything like this and gave up after about 30 minutes of trying. Maybe I don't know how to search here. Sorry if I'm duplicating problems.

[Welshgasman]

Qualify them with either DAO or ADO.

[Mac R]

Thanks,

Tried what you said, edited the first compile failure the compiler found from

Dim MySet As Recordset

to

Dim MySet As DAO.Recordset

and compiled, same message on same line, Compile Error: User defined type not defined


Also tried

Dim MySet As ADO.Recordset

which also fails on compile.

[Mac R]

Is it possible I have something turned off? InteliSense (?) didn't even begin to recognize ADO or DAO when I typed it, like it does with so many other things? Also, it doesn't explain why no compile error on the test accdb...

[Welshgasman]

Well for 2007 I have DAO 3.6 checked?,
So look for that or a later library. Microsoft DAO 3.6 Object Library

Edit: This site appears to be playing up today? Takes a while to load, had to press Save several times before being able to post the first part of this message?

Anyone else having issues? Other sites appear to be OK.

[Mac R]

Yeah, it's this site specifically I think; had exact same issues today.

As stated in my original post...

I attempted to reference the Microsoft DAO 3.6 object library and got this... Error loading DLL

[Welshgasman]

Try a repair of Office

[davegri]

I've used this routine for a few years. Arguments are sql string and the query name.

'---------------------------------------------------------------------------------------
' Procedure : fcnMakeNamedQuery
' DateTime  : 9/26/2006 20:57
' Author    : davegri
' Purpose   : Attach new SQL attribute to an existing querydef. If the Query doesn't exist,
'           : create it with the passed SQL and name.
'---------------------------------------------------------------------------------------
'
Function fcnMakeNamedQuery(qName As String, strPassedSQL As String)
    Dim qthisQuery As DAO.QueryDef
        If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
            Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
        Else
            Set qthisQuery = CurrentDb.QueryDefs(qName)
            qthisQuery.SQL = strPassedSQL
        End If
    Application.RefreshDatabaseWindow
    Set qthisQuery = Nothing
End Function

What's up with the 4 DIM lines just after Option Explicit? They have nothing to do with the function shown in your post.

[Mac R]

I've used this routine for a few years. Arguments are sql string and the query name.

'---------------------------------------------------------------------------------------
' Procedure : fcnMakeNamedQuery
' DateTime  : 9/26/2006 20:57
' Author    : davegri
' Purpose   : Attach new SQL attribute to an existing querydef. If the Query doesn't exist,
'           : create it with the passed SQL and name.
'---------------------------------------------------------------------------------------
'
Function fcnMakeNamedQuery(qName As String, strPassedSQL As String)
    Dim qthisQuery As DAO.QueryDef
        If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
            Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
        Else
            Set qthisQuery = CurrentDb.QueryDefs(qName)
            qthisQuery.SQL = strPassedSQL
        End If
    Application.RefreshDatabaseWindow
    Set qthisQuery = Nothing
End Function

What's up with the 4 DIM lines just after Option Explicit? They have nothing to do with the function shown in your post.

davegri: I included those lines as they all produced the error in other code snippets, so when I tested the code in the new 365 dB I wanted to have them in there to see if they created the error. Thanks for the function sample, looks cool! I am working now; it was a reference thing-see overall response.

Update: Hey, I C&P'ed your function to my dB and it works perfectly; slick 'n smooth as silk. I like it, and will most likely start using it in place of mine which is a little rough around the edges. Assuming that's okay with you? Thanks!

[Mac R]

Try a repair of Office

Welshgasman: Will try, but I've found the solution. It's a reference thing. Thanks for hanging in with me.

[Mac R]

Fixed!

Turns out it's a reference thing after all. Good friend Chris is like a dog on a bone when it comes to figuring out a problem and he gave me a solution last night.



Added these two references this ayem and *POOF*, I compile, and all functions (so far) work. I'd like to thank everyone who jumped in and attempted a fix, on a Sunday night no less. I am back in my happy place! Y'all ROCK!

[davegri]

Hey, I C&P'ed your function to my dB and it works perfectly; slick 'n smooth as silk. I like it, and will most likely start using it in place of mine which is a little rough around the edges. Assuming that's okay with you? Thanks!

OK with me. I think any code responses here are posted with the same assumption. Wouldn't be much 'help' otherwise.

[Vita]

I've used this routine for a few years. Arguments are sql string and the query name.

'---------------------------------------------------------------------------------------
' Procedure : fcnMakeNamedQuery
' DateTime  : 9/26/2006 20:57
' Author    : davegri
' Purpose   : Attach new SQL attribute to an existing querydef. If the Query doesn't exist,
'           : create it with the passed SQL and name.
'---------------------------------------------------------------------------------------
'
Function fcnMakeNamedQuery(qName As String, strPassedSQL As String)
    Dim qthisQuery As DAO.QueryDef
        If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
            Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
        Else
            Set qthisQuery = CurrentDb.QueryDefs(qName)
            qthisQuery.SQL = strPassedSQL
        End If
    Application.RefreshDatabaseWindow
    Set qthisQuery = Nothing
End Function

What's up with the 4 DIM lines just after Option Explicit? They have nothing to do with the function shown in your post.

This makes me think I could write a function for all the passthrough queries I have been using. I have a chain of them that I just reassign the SQL and re-execute.
They all use the same connection string and most of them return records. I just change the "WHERE" criteria and the table/field.

Dim SOqdf As DAO.QueryDef, SOrs As DAO.Recordset
    

    TableFound = "dbo.SOTable"
    SearchSQLStr = "SELECT " & TableFound & ".Status "
    SearchSQLStr = SearchSQLStr & "FROM " & TableFound & " "
    SearchSQLStr = SearchSQLStr & "WHERE " & StatusCriteria & ";" 'Status criteria defined elsewhere based on user input
    
    Set SOqdf = CurrentDb.CreateQueryDef("")
    SOqdf.Connect = MainConnStr 'defined elsewhere
    SOqdf.SQL = SearchSQLStr
    SOqdf.ODBCTimeout = 1
    SOqdf.ReturnsRecords = True
    Set SOrs = SOqdf.OpenRecordset

[Mac R]

Vita: I use what I call AdHocQuerys all the time. I write a lot of data screens where search and sort criteria are on the main page, and data results appear in a sub-form below, all interactive immediately. However, users want what users want and the demand to output resulting recordsets in Excel, paper or PDF is high.

Every time the user interacts with the search/sort criteria a new SQL is generated as the recordsource for the underlying sub-form. When the user asks to output the result to a report, I use the SQL to set the record source for that report. The SQL is built using three vars, vSQLFrom, vSQLWhere, and vSQLSort, then they get concatenated together for the final recordsource. If the user selects Excel, the logic creates the AdHocQuery def and then a transferspreadsheet to a file name and location of the user's choosing.

The only drawback to this process is the inability to pass sort criteria to a report. I'm working on that as there are ways to do it by altering the def of the report in VBA. Not easy, but doable.

I guess what I'm saying in a long winded, roundabout way is, I use this utility ALL THE TIME, and davegri's function is more compact and efficient than mine, and it's great.

[Vita]

Vita: I use what I call AdHocQuerys all the time. I write a lot of data screens where search and sort criteria are on the main page, and data results appear in a sub-form below, all interactive immediately. However, users want what users want and the demand to output resulting recordsets in Excel, paper or PDF is high.

Every time the user interacts with the search/sort criteria a new SQL is generated as the recordsource for the underlying sub-form. When the user asks to output the result to a report, I use the SQL to set the record source for that report. The SQL is built using three vars, vSQLFrom, vSQLWhere, and vSQLSort, then they get concatenated together for the final recordsource. If the user selects Excel, the logic creates the AdHocQuery def and then a transferspreadsheet to a file name and location of the user's choosing.

The only drawback to this process is the inability to pass sort criteria to a report. I'm working on that as there are ways to do it by altering the def of the report in VBA. Not easy, but doable.

I guess what I'm saying in a long winded, roundabout way is, I use this utility ALL THE TIME, and davegri's function is more compact and efficient than mine, and it's great.

I am a little confused on what you mean and interested in what you are having your users be able to type. Would you be able to attach a sample database of what you are describing or clarify in a bit more detail?
Maybe someone here can help you integrate criteria.