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.