C
Curt
I got this VBA from a John Walkenbeth website Demo file. This VBA is just
what I need to resolve a problem at. However , I get a Compile Error:
User-defined type not defined. Below is the code and underlined is where the
error occurs:
Sub ADO_Demo()
' This demo requires a reference to
' the Microsoft ActiveX Data Objects 2.x Library
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection <===== Compile Error
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Cells.Clear
MsgBox "This retrieves the updated data for the records in which UNIT =
125 LOGISTICS READINESS SQ and PASCODE = C21CF2BF"
' Database information
DBFullName = ThisWorkbook.Path & "\budget.mdb"
' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
Src = "SELECT * FROM Budget WHERE Unit = '125 LOGISTICS READINES SQ' "
Src = Src & "and PASCODE = 'C21CF2BF'"
.Open Source:=Src, ActiveConnection:=Connection
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range("B8").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
' Write the recordset
Range("B8").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub
Any help would be appreciated.
what I need to resolve a problem at. However , I get a Compile Error:
User-defined type not defined. Below is the code and underlined is where the
error occurs:
Sub ADO_Demo()
' This demo requires a reference to
' the Microsoft ActiveX Data Objects 2.x Library
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection <===== Compile Error
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Cells.Clear
MsgBox "This retrieves the updated data for the records in which UNIT =
125 LOGISTICS READINESS SQ and PASCODE = C21CF2BF"
' Database information
DBFullName = ThisWorkbook.Path & "\budget.mdb"
' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
Src = "SELECT * FROM Budget WHERE Unit = '125 LOGISTICS READINES SQ' "
Src = Src & "and PASCODE = 'C21CF2BF'"
.Open Source:=Src, ActiveConnection:=Connection
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range("B8").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
' Write the recordset
Range("B8").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub
Any help would be appreciated.