CODEWITHSUNDEEP
sql-servervbasql-server-2008excel
user3714330
user3714330

Reputation: 689

Error in VBA Excel-SQL coding as "User Defined Type Not Defined" for "Recordset"

I am relatively experienced in VBA coding, but I am totally new in MS SQL server 2008.

I am trying to export an Excel table like below to a SQL server:

    A    B    C   D    E
1 Name  Year  ID 
2 Jill  2015  17
3 Jack  2012  13
4 Mike  1999  25
5

After activating ADO and DAO in the tools, it fixed the error "User Defined Type Not Defined" for the line Dim rs As New ADODB.Recordset. But now I get an error for the line Set dbclass = New clsDB. I don't understand what is wrong. The same definition structure works in another Workbook.

Private Sub Transtable()

Dim connOk As Boolean
Dim MyWorkBook As Workbook
Dim CurSheet As Worksheet
Dim listObj As ListObject
Dim rs As New ADODB.Recordset
' Dim dbclass As New clsDB  ' both description leads to the same "User Undefined" error
' Dim dbclass As New ADODB.clsDB 
Set dbclass = New clsDB

dbclass.Database = "Tables"
dbclass.ConnectionType = SqlServer
dbclass.DataSource = "E72b1783"
dbclass.UserId = Application.UserName

  connOk = dbclass.OpenConnection(False, True)

  If connOk = False Then
     MsgBox "Connection not successfull"

  Else
     MsgBox "Connection successfull"
  End If

  tableName = "TableName1"
  Set CurSheet = Sheet2
  Set listObj = CurSheet.ListObjects(tableName) 'Table Name

  'get range of Table
  HeaderRange = listObj.HeaderRowRange.Address
  DataRange = listObj.DataBodyRange.Address

  dbclass.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

   strSQL = "SELECT * FROM [" & ws.Name & "$" & Replace(DataRange, "$", "") & "];"
   rs.Open strSQL, dbclass, adOpenStatic, adLockReadOnly

   arrData = rs.GetRows

   rs.Close
   dbclass.Close
   Set rs = Nothing
   Set dbclass = Nothing
   Set listObj = Nothing
   Set CurSheet = Nothing

End Sub

Upvotes: 0

Views: 1240

Answers (1)

Mohit S
Mohit S

Reputation: 14024

Make sure the Data Access Objects library (DAO) is checked in the Tools. Also look at the ActiveX Data Objects library (ADO) in the list. References list in the VBA Editor's menu/ribbon. (The specific name of the library can be different in different versions of Access)

Upvotes: 1

Related Questions