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.

Alt-F11 and don't forget to add the references.

Tools|References and check the box for 'Microsoft ActiveX Data Objects 2.x
Library'


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Curt" wrote:
|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.

Thanks, Dave.

I'm relatively new to the VBA areana.

However, now I get a run-time error'-2147217904(80040e10)': No value given
for one or more required parameters. I select the debug option and it
highlights this VBA code:

..Open Source:=Src, ActiveConnection:=Connection

"Dave Patrick" wrote:

Alt-F11 and don't forget to add the references.

Tools|References and check the box for 'Microsoft ActiveX Data Objects 2.x
Library'


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Curt" wrote:
|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.

Please paste a link to John's demo so I can see what you're seeing.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Curt" wrote:
| Thanks, Dave.
|
| I'm relatively new to the VBA areana.
|
| However, now I get a run-time error'-2147217904(80040e10)': No value given
| for one or more required parameters. I select the debug option and it
| highlights this VBA code:
|
| .Open Source:=Src, ActiveConnection:=Connection

http://www.dummies.com/WileyCDA/Dumm...24,page-1.html

It's in the Chp. 23 download the ADO_Demo file and Budget.mbd

"Dave Patrick" wrote:

Please paste a link to John's demo so I can see what you're seeing.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Curt" wrote:
| Thanks, Dave.
|
| I'm relatively new to the VBA areana.
|
| However, now I get a run-time error'-2147217904(80040e10)': No value given
| for one or more required parameters. I select the debug option and it
| highlights this VBA code:
|
| .Open Source:=Src, ActiveConnection:=Connection

If you open the Access database and look at the table Budget you'll find
there are no columns [Unit] or [PASCODE] which is why it fails with that
error. For the purpose of completing your task replace;

Src = "SELECT * FROM Budget WHERE Unit = '125 LOGISTICS READINES SQ' and
PASCODE = 'C21CF2BF' "
Src = Src & "and PASCODE = 'C21CF2BF'"

'with this to make it work;
Src = "SELECT * " _
& "FROM Budget " _
& "WHERE (((DEPARTMENT)='Human Resources') AND ((BUDGET)=2859)); "


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"CurtH" wrote:
|
http://www.dummies.com/WileyCDA/Dumm...24,page-1.html
|
| It's in the Chp. 23 download the ADO_Demo file and Budget.mbd