clear rows and insert records from recordset on refresh

[Santa-D]

Hi everyone, I tried posting this a few hours ago but it didn't
appear to load.

What I am trying to achieve is that when I refresh (calculate) the
report, the rows between the headings and totals is deleted and the
new data is inserted. I have seen it done before in other
applications but I am unable to find the answers.

I have created a SQL connection to the access database and it's
filtering when I recalculate the spreadsheet on a value in a cell.
Unfortunately for me, it is not deleting the previous data and
inserting the fresh data.

Can someone provide any further assistance?

The VBA code on doing the SQL connection is below.

Sub import_salaries()
Dim conn As New Connection, rec As New Recordset
Dim ws As Worksheet
Dim sql$, i&

Set ws = ThisWorkbook.Worksheets("Sheet1")

conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
"Data Source=" + ThisWorkbook.Path + "\HR_Occupancy_Table.mdb"

' T3Lookup = ws.Range("CCLOOKUP").Value

sql = "SELECT Employee_No, Employee_Name, Cost_Centre_Description,
Salary_Year, T3_CC_Lookup " & _
"FROM HR_OCCUPANCY WHERE T3_CC_Lookup = " &
ws.Range("CCLOOKUP").Value & " ORDER BY Cost_Centre_Description "


rec.Open sql, conn

While Not rec.EOF
i = i + 1
ws.[A5].Cells(i) = rec!Employee_No
ws.[B5].Cells(i) = rec!Employee_Name
ws.[C5].Cells(i) = rec!Cost_Centre_Description
ws.[D5].Cells(i) = rec!Salary_Year
ws.[E5].Cells(i) = rec!T3_CC_Lookup
rec.MoveNext
Wend
rec.Close: conn.Close


End Sub

 

[Tim Zych]

I saw your earlier post, and here was my reply:


I take that you don't want to perform row-by-row deletions/insertions.

You can use the adOpenStatic CursorType to get a scrollable recordset, then
use RecordCount to figure out how many rows to clear/insert. Then you can
clear out data in one action...much more efficient than deleting data one
row at a time.

rec.Open sql, conn, adOpenStatic

ws.Range("A5").EntireRow.Resize(rec.RecordCount).Insert shift:=xlDown

' Use RecordCount in conjunction with xlDown to know how many rows to
batch delete/insert before adding the recordset.

While Not rec.EOF
... rest of code

I would imagine you would use a combination of xlDown and Insert to know how
many rows to delete beforehand. Hard to give more advice on where to put the
data without more specifics. Hope you can adapt this, if not post back with
more details.