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

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You need to set a reference to one of the Microsoft ActiveX Data Objects libraries.
 

it115it

New Member
Joined
Mar 15, 2021
Messages
6
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Thank you. I think i have selectecd it. I am really sorry if its going to be a long thread.
 

Attachments

  • 11.PNG
    11.PNG
    50.4 KB · Views: 7

it115it

New Member
Joined
Mar 15, 2021
Messages
6
Office Version
  1. 365
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Also i have this while trying to debug. Am i missing any ODBC connection
 

Attachments

  • x.PNG
    x.PNG
    26.4 KB · Views: 3
  • debugger.PNG
    debugger.PNG
    4.9 KB · Views: 3

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I suggest you post the full code rather than pictures.
 

it115it

New Member
Joined
Mar 15, 2021
Messages
6
Office Version
  1. 365
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
Dim con As ADODB.Connection
Dim rst As Recordset
Public MySQL As String
Dim fld As Fields
Dim MyRecordCount As Long
Dim MyFieldsCount As Integer

Public MyDataCheck As Boolean

Public MinJobNo As Double
Public MaxJobNo As Double

Public MyToOpenFilePath As String
Public MyLastRow As Integer

Dim MyBudgetValue As Currency
Dim MyActualValue As Currency
Dim MySpendPercentage As Double

Dim MySheet As String
Dim MyRange As String

Dim MyOBArray As Variant
Dim MyBaseJobNumber As String
Dim MyCheckJobNumber As String

Public Sub GetData()
    
    MyDataCheck = False
    
    Application.ScreenUpdating = False
    
    ActiveWorkbook.Unprotect Password:="XXXXXX"

    Sheets.Add Count:=1, after:=Sheets(Sheets.Count)
    
    ActiveSheet.Name = "Data" '& Sheets.Count
    
    Application.ScreenUpdating = False
    
    MySheet = "Data"
    MyRange = "A2"
    
    Set con = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    If Application.Version >= "12.0" Then
        With con
           .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SHAREDFOLDER\DATA.accdb"
           .Open
        End With
    Else
        With con
           .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SHAREDFOLDER\DATA.accdb.accdb"
           .Open
        End With
    End If
    
        With rst
            .ActiveConnection = con
            .CursorType = adOpenStatic
            .CursorLocation = adUseServer
            .LockType = adLockOptimistic
            .Source = MySQL
            .Open
        End With
        MyRecordCount = rst.RecordCount
        
        If MyRecordCount = 0 Then
            With rst
                .Close
            End With
            
            With con
                .Close
            End With
            Application.ScreenUpdating = True
            MyDataCheck = False
            MsgBox "No records found for the criteria set." & vbCrLf & vbCrLf & "Data reteval request will be aborted", vbOKOnly + vbExclamation, "Invalid Data Request"
            
            Unload frmWelcome
            ThisWorkbook.Close SaveChanges:=False
            Exit Sub
        ElseIf MyRecordCount >= 3000 Then
            With rst
                .Close
            End With
            
            With conn
                .Close
            End With
            Application.ScreenUpdating = True
            MyDataCheck = False
            MsgBox "To many records found for the criteria set." & vbCrLf & vbCrLf & "Data reteval request will be aborted", vbOKOnly + vbExclamation, "Invalid Data Request"
            
            Unload frmWelcome
            ThisWorkbook.Close SaveChanges:=False
            Exit Sub
        Else
            MyDataCheck = True
            MyFieldsCount = rst.Fields.Count
    
            Worksheets(MySheet).Range("A1").Select
            
            For i = 0 To MyFieldsCount - 1
                ActiveCell.Value = rst.Fields(i).Name
                ActiveCell.Offset(0, 1).Select
            Next i
            
            Worksheets(MySheet).Range(MyRange).CopyFromRecordset rst
        End If
    
    With rst
        .Close
    End With
    
    With con
        .Close
    End With
    
    Set rst = Nothing
    Set dbs = Nothing

End Sub

Public Sub FormatSheet()

    Cells.Select
    With Selection.Font
        .Name = "ARIAL"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Columns("A:A").ColumnWidth = 10
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "XXXXXX" & Chr(10) & "Number"
    
    Columns("B:B").ColumnWidth = 8
    Columns("C:C").ColumnWidth = 30
    Columns("C:C").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Columns("D:F").ColumnWidth = 7.5
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "X" & Chr(10) & "XX"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "X" & Chr(10) & "XX"
    
    Columns("G:G").ColumnWidth = 13.29
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
  
    
    
    Rows("1:1").EntireRow.AutoFit
    Range("A1").Select
    
    Do While ActiveCell.Value <> ""
        ActiveCell.Offset(1, 0).Activate
    Loop
    
    MyLastRow = ActiveCell.Row - 1
    
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
    Range("J2").Select
    Selection.Copy
    Range("J3:J" & MyLastRow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
    Range("M2").Select
    Selection.Copy
    Range("M3:M" & MyLastRow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    MyLastRow = 0

    Range("C2").Select
    ActiveWindow.FreezePanes = True
    Range("A1:R1").Select
    Selection.AutoFilter
    Range("A1").Select
    
End Sub

Public Sub DoCompare()
    
    Dim i As Integer
    
    ThisWorkbook.Worksheets("Data").Activate
    ActiveSheet.Unprotect Password:="XXXXXX"
    
    Range("A1").Select
    
    Do While ActiveCell.Value <> ""
        ActiveCell.Offset(1, 0).Activate
    Loop
    
    MyLastRow = ActiveCell.Row - 1
    
    Range("A2").Select
    
    For i = 2 To MyLastRow
        'Compare Columns H and I
        MyBudgetValue = Range("H" & i).Value
        MyActualValue = Range("I" & i).Value

        If MyActualValue > Value Then
            Cells(i, 8).Interior.ColorIndex = 38
        End If
    
        MyBudgetValue = 0
        MyActualValue = 0
        
        'Compare Columns K and L
        MyBudgetValue = Range("K" & i).Value
        MyActualValue = Range("L" & i).Value
        
        If MyActualValue > Value Then
            Cells(i, 11).Interior.ColorIndex = 38
        End If
    Next i
    
    MyBudgetValue = 0
    MyActualValue = 0
    
    'Add Summary Values
    Range("H" & MyLastRow + 2).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-" & MyLastRow & "]C:R[-2]C)"
    
    Range("H" & MyLastRow + 2).Select
    Selection.Copy
    Range("I" & MyLastRow + 2).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("K" & MyLastRow + 2 & ":L" & MyLastRow + 2).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("J" & MyLastRow + 2).Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
    
    Range("M" & MyLastRow + 2).Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
    
    Call FormatPrintSetUp
    
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1
    
    ActiveSheet.Protect Password:="XXXXXX", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
    Range("A1").Select

End Sub

Public Sub FormatPrintSetUp()

    Columns("A:M").Select
    ActiveSheet.PageSetup.PrintArea = "$A:$M"
    'Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    
    'Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$A:$M"
    'Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&D: Current Production Values"
        .CenterFooter = "&""-,Bold"" Confidential"
        .RightFooter = "Page &P"
        .LeftMargin = Application.InchesToPoints(0.31496062992126)
        .RightMargin = Application.InchesToPoints(0.31496062992126)
        .TopMargin = Application.InchesToPoints(0.748031496062992)
        .BottomMargin = Application.InchesToPoints(0.551181102362205)
        .HeaderMargin = Application.InchesToPoints(0.31496062992126)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    
    'Application.PrintCommunication = True
    Range("A1").Select

End Sub
VBA Code:
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Where do you assign a SQL string to the mySQL variable?
 

it115it

New Member
Joined
Mar 15, 2021
Messages
6
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Where do you assign a SQL string to the mySQL variable?
Sorry i am not sure. This code has been working for years and something happend and i cant figure it out.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It's not being set anywhere in that code, and you can't open a recordset using a blank SQL string.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,386
Messages
5,636,015
Members
416,892
Latest member
Bensch

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