Error message: "Compile error. User-defined type not defined"

Hi,

I am trying to get an Excel sheet to read/write to an Access DB. My code starts as shown below.
When running it, I get the error: "Compile error. User-defined type not defined" it will not let me proceed until i have sorted out the "Global cnPubs As ADODB.Connection" line.
I am also guessing that the line below it "Global rsPubs As ADODB.Recordset" will also throw up the same error as it generally doesn't like it.

i initially had an error come up run time erro '429' active X component can't create object and figured out that the right library wasn't selected. In tools-references i now have Microsoft ActiveX data objects (multi-dimensional) 2.8 library is selected and that run time error has now gone away and the above has now happened.

Although I have been doing VB coding for some years now I an brand spanking new to the linking into access thing, I am looking after any compatibility issues with excel 2003 sheets moving onto 2010 as we are about to upgrade and this is one that has thrown up a problem so i thought I would try and fix it, I am regretting that now! Just to confirm I didn't write this and the person that did is no longer with the business.

Any thoughts on how to solve this would be welcome

thanks

''General Report Variables
Global Login As String


''Email Global Variables
Global Email_to As String
Global Email_CC As String
Global Email_BCC As String
Global Email_Subject As String
Global Mail_Body As String

''ADO Global Variables
Global cnPubs As ADODB.Connection
Global rsPubs As ADODB.Recordset
Global DBase_String As String

''HTML Global Variables
Global HTML_Text As String

''Testing Global Variables


''Specific Report Global Variables

You need a reference set to the Microsoft ActiveX Data Objects 2.n library (the n is a version number, likely 8)

ah ok, I see what I have been missing now, would that be this bit .provider?

Sub Open_Access_Connect_ADO()

' Opens an ADO Connection to an MS Access Database

    Set cnPubs = New ADODB.Connection
    With cnPubs
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source= " & DBase_String & ";"
        .Open
    End With

End Sub

No, the provider is separate. The ADO library I mentioned is where the ADODB.Connection (and ADODB.Recordset) comes from.

ok, sorry for being a bit slow on this, I am regretting even touching this now but I am being stubbourn and I WILL fix it

where and how would i put that in, would it be in the string I have mentioned, in front of the Global cnPubs As ADODB.Connection

''ADO Global Variables
Global cnPubs As ADODB.Connection
Global rsPubs As ADODB.Recordset
Global DBase_String As String

or would i put it somewhere in the body of the VB, shown below

Sub UploadNBActivities()

    Dim acApp As Object
    Dim db As Object
    
    Set acApp = CreateObject("Access.Application")
    acApp.OpenCurrentDatabase ("\\brdrafnp01\shared area\Customer Operations\Customer Contact Centre Resource Planning\Management Reporting\MIS Reports\Proceedures\ReportingProcs\NewBusinessActivity.mdb")
    Set db = acApp
    acApp.Run "CreateTempTables"
    acApp.Quit
    Set acApp = Nothing
    
    
    ' Set sheet variables

    Dim Sheet_Name As String
    Dim Table_Name As String
    Dim Count_1 As Long
    
    Sheet_Name = "NBActivites" '<--- Enter the sheet the Data Table is on here
    Table_Name = "tmpNewBusActivities" '<--- Enter the Database Table Name here

' Define Access Database to be connected to
    DBase_String = "\\brdrafnp01\shared area\Customer Operations\Customer Contact Centre Resource Planning\Management Reporting\MIS Reports\Proceedures\ReportingProcs\NewBusinessActivity.mdb" '<---- Full path to Access Database is required
    
' Open Connection
    Call Open_Access_Connect_ADO

' Create a recordset object.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset

    rsPubs.Open Table_Name, cnPubs, adOpenKeyset, adLockOptimistic, adCmdTable
    
    Count_1 = 0
    
        Do While Sheets(Sheet_Name).Range("B7").Offset(Count_1, 0).Value <> ""
        With rsPubs
            .AddNew
            .Fields("AccountName") = IIf(Sheets(Sheet_Name).Range("B7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("B7").Offset(Count_1, 0).Value)
            .Fields("AccountNos") = IIf(Sheets(Sheet_Name).Range("C7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("C7").Offset(Count_1, 0).Value)
            .Fields("PostCode") = IIf(Sheets(Sheet_Name).Range("D7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("D7").Offset(Count_1, 0).Value)
            .Fields("MasterAccountNos") = IIf(Sheets(Sheet_Name).Range("E7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("E7").Offset(Count_1, 0).Value)
            .Fields("ActivityOwner") = IIf(Sheets(Sheet_Name).Range("G7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("G7").Offset(Count_1, 0).Value)
            .Fields("ActivityOwnerID") = IIf(Sheets(Sheet_Name).Range("F7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("F7").Offset(Count_1, 0).Value)
            .Fields("ActivityStatus") = IIf(Sheets(Sheet_Name).Range("H7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("H7").Offset(Count_1, 0).Value)
            .Fields("CreationDate") = IIf(Sheets(Sheet_Name).Range("I7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("I7").Offset(Count_1, 0).Value)
            .Fields("PlannedStartDate") = IIf(Sheets(Sheet_Name).Range("J7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("J7").Offset(Count_1, 0).Value)
            .Fields("DueDate") = IIf(Sheets(Sheet_Name).Range("K7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("K7").Offset(Count_1, 0).Value)
            .Fields("ActualStartDate") = IIf(Sheets(Sheet_Name).Range("L7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("L7").Offset(Count_1, 0).Value)
            .Fields("ActualEndDate") = IIf(Sheets(Sheet_Name).Range("M7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("M7").Offset(Count_1, 0).Value)
            .Fields("CreatedBy") = IIf(Sheets(Sheet_Name).Range("N7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("N7").Offset(Count_1, 0).Value)
            .Fields("TeamAssignedTo") = IIf(Sheets(Sheet_Name).Range("O7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("O7").Offset(Count_1, 0).Value)
            .Fields("TotalActivities") = IIf(Sheets(Sheet_Name).Range("P7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("P7").Offset(Count_1, 0).Value)
            .Fields("CompletedActivities") = IIf(Sheets(Sheet_Name).Range("Q7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("Q7").Offset(Count_1, 0).Value)
            .Fields("PercentCompleted") = IIf(Sheets(Sheet_Name).Range("R7").Offset(Count_1, 0).Value = "", 0, Sheets(Sheet_Name).Range("R7").Offset(Count_1, 0).Value)
            .Update ' stores the new record
        End With
        Count_1 = Count_1 + 1 'next row
    Loop

    ' Close Connection
    Call Close_Access_Connect_ADO
    
    Set acApp = CreateObject("Access.Application")
    acApp.OpenCurrentDatabase ("\\brdrafnp01\shared area\Customer Operations\Customer Contact Centre Resource Planning\Management Reporting\MIS Reports\Proceedures\ReportingProcs\NewBusinessActivity.mdb")
    Set db = acApp
    acApp.Run "UpdateDataTable"
    acApp.Quit
    Set acApp = Nothing


End Sub

Open VBE. Go to Tool->References and check Microsoft ActiveX Data Objects 2.n Library (the n is a version number, likely 8)
That's what romperstomper is telling you in his first reply.

Buran

And please use CODE tags around your code. Not doing so is against forum rules.

That is not the same reference. You want:
Microsoft ActiveX Data Objects 2.8 Library
and not:
Microsoft ActiveX Data Objects (Multi-dimensional) 2.8 library.

where does the runtime error occur?

Do you actually have Access installed?

If the code errors there, how does it open fine?

Sounds like a configuration problem on your machine - see if this MSKB article helps. I assume it is failing on the first instance of that line?

No, you weren't.