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:
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
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.
Code:
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:
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.