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