COMPLETE NOOB STARTER QUESTION - COMPILE ERROR - USER DEFINED TYPE NOT DEFINED

it115it

New Member
Joined
Mar 15, 2021
Messages
6
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
I have been given an excel program containing a VBA code. Its password protected and when i open the VBA mode and run the program i get the error Compile error : User-defined type not defined on the line con As ADODB.Connection
The Excel program basically looks at an Access database ( located on a shared folder in accdb format) and compares some tables in it and produces an output.
The program used to work previously and some reason it stopped i am trying to troubleshoot it. I have moved the database locally on my C drive and changed the code to point at my C drive instead of the shared folder. Instead i get the following error as shown in the attachment. I will appreciate any help in resolving.
 

Attachments

  • 1.PNG
    1.PNG
    29.2 KB · Views: 4

it115it

New Member
Joined
Mar 15, 2021
Messages
6
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
It's not being set anywhere in that code, and you can't open a recordset using a blank SQL string.
Sorry found this in sheet 1

VBA Code:
Private Sub cmdGetData_Click()

Dim MySheetCount As Integer
    
    MySheetCount = Worksheets.Count
    
    If MySheetCount > 1 Then
        ActiveWorkbook.Unprotect Password:="XXXXXX"
        Application.DisplayAlerts = False
        For i = 2 To MySheetCount
            Worksheets(2).Select
            ActiveSheet.Delete
        Next i
        Application.DisplayAlerts = True
        'ActiveWorkbook.Protect Password:="XXXXXX", Structure:=True, Windows:=False
    End If
    
   MySQL = "SELECT [ Number], ABC, [Name], [HAN], MAN, CMAN, [ Category]," _
            & "[B ], [A ], [B ], [A ] " _
            & "FROM Data " _
            & "WHERE ( Is Not Null) And ([Number] < '200') " _
            & "ORDER BY [Number]"
    
    Application.ScreenUpdating = False
    
    Call GetData
    
    MySQL = ""
    
    Call FormatSheet
    
    Call DoCompare
    
    Application.ScreenUpdating = True

End Sub[CODE=vba][CODE=vba]
[/CODE][/CODE]
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Your SQL string doesn't look right to me. In your WHERE clause, what are you testing with the Is Not Null? There should be a field name there. You also appear to have random spaces added to some field names.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,219
Messages
5,640,957
Members
417,183
Latest member
CuteLeo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top