Compile Error User-defined Type Not Defined Adodb.connection
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Compiler Error: User-defined types not defined up vote 11 down vote favorite 1 I get the compile-time error "User-defined types not defined" on this line: Dim cn As ADODB.Connection` What could be wrong? Code: Sub test() Dim cn As ADODB.Connection 'Not the best way to get the name, just convenient for notes strFile = Workbooks(1).FullName strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" Set cn = CreateObject("ADODB.Connection") 'For this to work, you must create a DSN and use the name in place of 'DSNName 'strSQL = "INSERT INTO [ODBC;DSN=DSNName;].NameOfMySQLTable " & "Select AnyField As NameOfMySQLField FROM [Sheet1$];" strSQL = "SELECT F1 FROM [Sheet1$];" cn.Execute strSQL End Sub excel vba excel-vba share|improve this question edited Jan 30 '15 at 14:27 pnuts 33.7k63569 asked Mar 18 '11 at 8:27 Parth Bhatt 13.1k24105200 add a comment| 3 Answers 3 active oldest votes up vote 19 down vote accepted I had forgotten to add a reference to "Microsoft ActiveX Data Objects 2.5 Library": Tools > References > Check the checkbox in front of "Microsoft ActiveX Data Objects 2.5 Library" share|improve this answer edited Apr 23 '13 at 11:09 Jean-Francois Corbett 23.9k1678113 answered Mar 18 '11 at 8:43 Parth Bhatt 13.1k24105200 add a comment| up vote 7 down vote You can use late binding: Dim cn As Object will make the problem go away. VBA will make the reference automatically when the CreateObject("ADODB.Connection") statement is executed. share|improve this answer edited Apr 23 '14 at 6:49 answered Mar 19 '11 at 11:45 Jean-Francois Corbett 23.9k1678113 @Jean-FrancoisCorbett S
360 games PC games Windows games Windows phone games Entertainment All Entertainment Movies & TV Music Business & Education Business Students & educators Developers Sale Sale Find a store Gift cards Products Software & services Windows Office Free downloads & security Internet http://stackoverflow.com/questions/5349580/compiler-error-user-defined-types-not-defined Explorer Microsoft Edge Skype OneNote OneDrive Microsoft Health MSN Bing Microsoft Groove Microsoft Movies & TV Devices & Xbox All Microsoft devices Microsoft Surface All Windows PCs & tablets PC accessories Xbox & games Microsoft Lumia All https://support.microsoft.com/en-us/kb/184609 Windows phones Microsoft HoloLens For business Cloud Platform Microsoft Azure Microsoft Dynamics Windows for business Office for business Skype for business Surface for business Enterprise solutions Small business solutions Find a solutions provider Volume Licensing For developers & IT pros Develop Windows apps Microsoft Azure MSDN TechNet Visual Studio For students & educators Office for students OneNote in classroom Shop PCs & tablets perfect for students Microsoft in Education Support Sign in Cart Cart Javascript is disabled Please enable javascript and refresh the page Cookies are disabled Please enable cookies and refresh the page CV: {{ getCv() }} English (United States) Terms of use Privacy & cookies Trademarks © 2016 Microsoft
be down. Please try the request again. Your cache administrator is webmaster. Generated Thu, 06 Oct 2016 00:10:36 GMT by s_hv720 (squid/3.5.20)
Posts Search Community Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts Go to Page... Thread Tools Rating: Display Modes 07-16-2013, 08:15 AM #1 lowanam1 Newly Registered User Join Date: Jan 2012 Posts: 106 Thanks: 3 Thanked 0 Times in 0 Posts MS Error Compile Error User-defined type not defined" Good morning, I am trying to convert from Access 2007 to 2010 and I am getting this error "Compile Error User-defined type not defined. Please help Thanks! Private Sub cmdAppend_Click() Dim cnn1 As ADODB.Connection Set cnn1 = CurrentProject.Connection 'set up the recordset Dim myRSQuotes As New ADODB.Recordset myRSQuotes.ActiveConnection = cnn1 'variables for the work required Dim ctl1 As Control Dim lngloop1 As Long 'variables for the subcontractors Dim ctl2 As Control Dim lngloop2 As Long 'variable to hold the contractors for form filtering Dim strIDs As String 'open the recordset based on the tblSubcontractorQuotes table myRSQuotes.Open "tblSubcontractorQuotes", , adOpenDynamic, adLockOptimistic 'set the controls to the work required list or subcontractor list Set ctl1 = Me.lstWorkReq 'work required control Set ctl2 = Me.lstSubs 'subcontractor control 'check to make sure at least 1 required work item has been selected otherwise return message If ctl1.ItemsSelected.Count = 0 Then MsgBox "You must select at least one required work item from the list" ctl1.SetFocus Exit Sub End If 'check to make sure at least 1 required subcontractor has been selected otherwise return message If ctl2.ItemsSelected.Count = 0 Then MsgBox "You must select at least one subcontractor from the list" ctl2.SetFocus Exit Sub End If 'for each selected work, add the subcontractors to the tblSubcontractorQuotes table via the recordset For lngloop1 = 0 To ctl1.ItemsSelected.Count - 1 For lngloop2 = 0 To ctl2.ItemsSelected.Count - 1 'store the IDs of the subcontractors If ctl2.ItemsSelected.Count = 1 Then strIDs = ctl2.ItemData(ctl2.ItemsSelected(lngloop2)) Else If lngloop2 = 0 Then strIDs = strIDs & ctl2.ItemData(ctl2.ItemsSelected(lngl