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

Yes, you are correct. I used the Demo as a base and tried to duplicate the
effect in my own process. Anyway...I delete the budget table and inserted my
table. I also used your suggested code and it worked. Then I get another
run-time error '3704: Operation is not allowed when object is closed.

I'm really frustrated, because this can resolve my issues and complete the
main part of this work project. Here is the code:

Option Explicit

Sub Update_IMR()
' 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
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear <=== Does this command clear the sheet or can have it clear
the specific range?
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 MedicalStatus " _
& "WHERE (((UNIT)='125 LOGISTICS READINES SQ') AND ((PASCODE)=C21CF2BF)); "


' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range("B8").Offset(0, Col).Value = Recordset.Fields(Col).Name
<=== Run-time error debugs here. How do I know that it will write the
recordset on the correct sheet? I know B8 specifies the range.
Next

' Write the recordset
Range("B8").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

I want to thank you again, I really appreciate your help and it's helping me
plenty. I'm trying to learn on short curve through reverse engineering and
reading.

"Dave Patrick" wrote:

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

You omitted this line that opens the recordset.

.Open Source:=Src, ActiveConnection:=Connection

You can use something like below to clear a specific range.

Sheets("sheet1").Range("B8:H10").ClearContents



--

Regards,

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

"CurtH" wrote:
| Yes, you are correct. I used the Demo as a base and tried to duplicate
the
| effect in my own process. Anyway...I delete the budget table and inserted
my
| table. I also used your suggested code and it worked. Then I get another
| run-time error '3704: Operation is not allowed when object is closed.
|
| I'm really frustrated, because this can resolve my issues and complete the
| main part of this work project. Here is the code:
|
| Option Explicit
|
| Sub Update_IMR()
| ' 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
| Dim Recordset As ADODB.Recordset
| Dim Col As Integer
|
| Cells.Clear <=== Does this command clear the sheet or can have it
clear
| the specific range?
| 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 MedicalStatus " _
| & "WHERE (((UNIT)='125 LOGISTICS READINES SQ') AND ((PASCODE)=C21CF2BF));
"
|
|
| ' Write the field names
| For Col = 0 To Recordset.Fields.Count - 1
| Range("B8").Offset(0, Col).Value = Recordset.Fields(Col).Name
| <=== Run-time error debugs here. How do I know that it will write the
| recordset on the correct sheet? I know B8 specifies the range.
| Next
|
| ' Write the recordset
| Range("B8").Offset(1, 0).CopyFromRecordset Recordset
| End With
| Set Recordset = Nothing
| Connection.Close
| Set Connection = Nothing
| End Sub
|
| I want to thank you again, I really appreciate your help and it's helping
me
| plenty. I'm trying to learn on short curve through reverse engineering
and
| reading.

"How do I know that it will write the recordset on the correct sheet? I know
B8 specifies the range."

Good question and you can (also good practice) specify the sheet as in;

Sheets("sheet1").Range("B8").Offset(0, Col).Value =
Recordset.Fields(Col).Name

--

Regards,

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

Dave,

I insert this code both ways and I still get error on it. If I plugged in
in the with block.

"Dave Patrick" wrote:

You omitted this line that opens the recordset.

.Open Source:=Src, ActiveConnection:=Connection

You can use something like below to clear a specific range.

Sheets("sheet1").Range("B8:H10").ClearContents



--

Regards,

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

"CurtH" wrote:
| Yes, you are correct. I used the Demo as a base and tried to duplicate
the
| effect in my own process. Anyway...I delete the budget table and inserted
my
| table. I also used your suggested code and it worked. Then I get another
| run-time error '3704: Operation is not allowed when object is closed.
|
| I'm really frustrated, because this can resolve my issues and complete the
| main part of this work project. Here is the code:
|
| Option Explicit
|
| Sub Update_IMR()
| ' 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
| Dim Recordset As ADODB.Recordset
| Dim Col As Integer
|
| Cells.Clear <=== Does this command clear the sheet or can have it
clear
| the specific range?
| 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 MedicalStatus " _
| & "WHERE (((UNIT)='125 LOGISTICS READINES SQ') AND ((PASCODE)=C21CF2BF));
"
|
|
| ' Write the field names
| For Col = 0 To Recordset.Fields.Count - 1
| Range("B8").Offset(0, Col).Value = Recordset.Fields(Col).Name
| <=== Run-time error debugs here. How do I know that it will write the
| recordset on the correct sheet? I know B8 specifies the range.
| Next
|
| ' Write the recordset
| Range("B8").Offset(1, 0).CopyFromRecordset Recordset
| End With
| Set Recordset = Nothing
| Connection.Close
| Set Connection = Nothing
| End Sub
|
| I want to thank you again, I really appreciate your help and it's helping
me
| plenty. I'm trying to learn on short curve through reverse engineering
and
| reading.

Thanks, Dave. I believe I got this part down. Still have problems with the
..open code, though.

"Dave Patrick" wrote:

"How do I know that it will write the recordset on the correct sheet? I know
B8 specifies the range."

Good question and you can (also good practice) specify the sheet as in;

Sheets("sheet1").Range("B8").Offset(0, Col).Value =
Recordset.Fields(Col).Name

--

Regards,

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

Dave,

Is it possible (once I get this working) to add additional tables to the
same database and use this process?

"Dave Patrick" wrote:

"How do I know that it will write the recordset on the correct sheet? I know
B8 specifies the range."

Good question and you can (also good practice) specify the sheet as in;

Sheets("sheet1").Range("B8").Offset(0, Col).Value =
Recordset.Fields(Col).Name

--

Regards,

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

Set Recordset = New ADODB.Recordset
With Recordset
' Filter
Src = "SELECT * " _
& "FROM MedicalStatus " _
& "WHERE (((UNIT)='125 LOGISTICS READINES SQ') AND ((PASCODE)=C21CF2BF)); "
' ######## Added the line below #######
.Open Source:=Src, ActiveConnection:=Connection

--

Regards,

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

"CurtH" wrote:
| Dave,
|
| I insert this code both ways and I still get error on it. If I plugged
in
| in the with block.

Yes, no limit.

--

Regards,

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

"CurtH" wrote:
| Dave,
|
| Is it possible (once I get this working) to add additional tables to the
| same database and use this process?

Just noticed a problem with your SQL. C21CF2BF was missing the single
quotes. Should have been;

Set Recordset = New ADODB.Recordset
With Recordset
' Filter
Src = "SELECT * " _
& "FROM MedicalStatus " _
& "WHERE (((UNIT)='125 LOGISTICS READINES SQ') AND ((PASCODE)='C21CF2BF'));
"
' ######## Added the line below #######
.Open Source:=Src, ActiveConnection:=Connection


--

Regards,

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

GReat. Everything is working like a champ:-) Thanks again. If I need to
add more tables. Copy this part of the code:

Set Recordset = New ADODB.Recordset
With Recordset
' Filter
Src = "SELECT * " _
& "FROM MedicalStatus " _ <==== Indicate the new table
& "WHERE (((UNIT)='125 LOGISTICS READINES SQ') AND ((PASCODE)='C21CF2BF'));
<==== Change criteria to match my needs

Thanks again.


"Dave Patrick" wrote:

Yes, no limit.

--

Regards,

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

"CurtH" wrote:
| Dave,
|
| Is it possible (once I get this working) to add additional tables to the
| same database and use this process?

Glad to hear it. You're welcome.

--

Regards,

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

"CurtH" wrote:
| GReat. Everything is working like a champ:-) Thanks again. If I need to
| add more tables. Copy this part of the code:
|
| Set Recordset = New ADODB.Recordset
| With Recordset
| ' Filter
| Src = "SELECT * " _
| & "FROM MedicalStatus " _ <==== Indicate the new table
| & "WHERE (((UNIT)='125 LOGISTICS READINES SQ') AND
((PASCODE)='C21CF2BF'));
| <==== Change criteria to match my needs
|
| Thanks again.