Clear table contents before importing new data

Hello,

I 've found some fine piece of code to import Exceldata into Access, but I still have one problem: Excel places the new data under the already present data. However, I do not want to append the Exceldata, but to overwrite (part of) the existing data.

How can I make Excel clear the contents of one or more Access columns (before subsequently importing new data into these cells)?

Thanks in advance,

Mede

Re: Clear table contents before importing new data

Thanks for your reaction, but I'm not sure how to perform this operation. In my current code (see below), I use an ADO connection to import the Exceldata into a Access table. The number of rows in Excel and Access is identical.

How can I initiate this update-query within this ADO-transfer? I have indicated in the code where about I think this clearance should happen. And is compaction also relevant for update-queries?

Thanks in advance!

Sub ExportToAccess()


' exports data from the active worksheet to a table in an Access database


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long


    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=D:\nlrfr\My Documents\Oefenmodel.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "Leeg", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    'Now I want to clear (some columns of) the existing Access-data...
    ' all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
    
   ' If Not AlreadyExists(rs, "Spare_Naam", Range("A" & r).Text) Then
   '        rs.AddNew ' create a new record
   ' End If
        
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("Spare_Naam") = Range("A" & r).Value
            .Fields("Spare_Life") = Range("B" & r).Value
            .Fields("Spare_MC") = Range("D" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Re: Clear table contents before importing new data

Well, I do not know much about this table linking and found many suggestions about using ADO to import/export data between Access and Excel.

In short, I want to perform the following functionality:
The user clicks on a button in Acces
Data is being exported to Excel
Excel performs some calculations on a few columns
Excel exports these updated columns back to Access. overwriting the existing ones.

At this point, I can perform all functions, except for the overwriting. The adjusted data is simply entered below the existing ones. So if I only could erase the existing records just before importing the newly calculated ones, I'd be done. I'd rather not switch to a completely different method like this table linking now I've come so far, unless there are some good grounds to do so.

But if I'm not mistaken, ADO connections are faster/more efficient/more flexible than table linking, which would advocate sticking to ADO.

If I'd be able to execute the clear contents step as well... Could you help me with this very part, or give more details about the updating procedure?

Many thanks,

Mede

Re: Clear table contents before importing new data

I guess not...I have to run a certain algorithm on the data, which involves many iterations. So far as I know, this is not possible in Access.

But I'm open for any suggestions...

Re: Clear table contents before importing new data

Hello,

The excelsheet is far too large to post on the site, but I am running the macro below in Excel after having imported the "raw" data from Access:

Private Sub CommandButton1_Click()


Dim WorkRange As Range
    Dim rFound As Range
    Dim MaxVal As Double
     
    Do
        Range("J2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Set WorkRange = Selection
        totalrows = WorkRange.Rows.Count
 
        MaxVal = Application.Max(WorkRange)
        'Range("J270").Value = MaxVal
     
        With WorkRange
            Application.Goto .Range("A" & WorksheetFunction.Match(MaxVal, .Columns(1), 0))
        End With
     
        ActiveCell.Offset(0, -2).Select
        ActiveCell = ActiveCell + 1
     
     Loop Until Range("I65536").End(xlUp) > Worksheets("Assumptions").Cells(5, 2).Value
     
     Cells(1, 1).Select
End Sub

This generates a column with updated figures, which I'd like to re-import into Access, whilst getting rid of the previous column. But I'm struggling with this very last step...

Thanks in advance for your ideas,

Mede

Re: Clear table contents before importing new data

Hello,

I've managed to comprise my file to an acceptable format, although this required deleting lots of formulae.

In short, the sheet does the following: launching the "Run"macro starts calculating the desired stock levels for the spare parts listed, based on price and availability measures. The algorithm calculates the added availability if one extra spare would be added on stock and then selects the part where the price per availability relation is the best. (It's a so-called greedy algorithm).

Subsequently, the total stock value is compared to some norm and continues until the norm is reached.

For the next run session, first the "Reset" macro should be launched.

The formulae how the availability is calculated had to be replaced by their respective values to save kb space, but it involves "e"-powers, which I suspect not to be possible in Access.

Also, the macro where the raw data is imported from Access is left out completely. However, I am interested in exporting it again into Access, replacing the old data.

Re: Clear table contents before importing new data

Something like e^x, where is is the natural logarithm.

I wonder whether this functionality is available in Access...

Re: Clear table contents before importing new data

Hello,

Finally, I've found a solution for my problem: I call the Excel-codes/macros from Access and before re-importing the updated data into Excel, I erase the contents of my "old" table by means of a delete query.

But of course, a new issue has emerged;

I can surpress the message in Access about"Delete confirmation, but when closing Excel automatically, I'm asked whether or not to save this Excelfile.

How can I automatically safe and close my Excel workbook (from Access), without any disturbing message?

Thanks in advance,

Mede

Re: Clear table contents before importing new data

Thanks!!