clear rows and insert records from recordset on refresh

[Forgone]

Hi Everyone,

I need some assistance with trying to achieve what I'm wanting to do.

I have a worksheet that has a series of headings and a row that sums
up the totals. I am guessing that there will be further calculations
but for now I'm trying to achieve the first step which is to delete
the existing data and insert the new data.

For example,

If I want to look at a certain branch's data, I insert their account
number and refresh the calculation. This will bring up all the
records associated with that branch and provide subtotals.

I have managed to succesfully create an ADO connection to an MS Access
database with the code below, but unfortunatley for me it's not
clearing the data and I can't seem to find how I go about doing it.

I did think of using .XLDOWN to delete the existing data and .INSERT
to insert the data but I have a feeling that there is something more
practicable to do this.

Any assistance would be appreciated.

<CODE>

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

</CODE>

 

[Tim Zych]

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.


--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility

Hi Everyone,

I need some assistance with trying to achieve what I'm wanting to do.

I have a worksheet that has a series of headings and a row that sums
up the totals. I am guessing that there will be further calculations
but for now I'm trying to achieve the first step which is to delete
the existing data and insert the new data.

For example,

If I want to look at a certain branch's data, I insert their account
number and refresh the calculation. This will bring up all the
records associated with that branch and provide subtotals.

I have managed to succesfully create an ADO connection to an MS Access
database with the code below, but unfortunatley for me it's not
clearing the data and I can't seem to find how I go about doing it.

I did think of using .XLDOWN to delete the existing data and .INSERT
to insert the data but I have a feeling that there is something more
practicable to do this.

Any assistance would be appreciated.

<CODE>

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

</CODE>