Solved

Parse Excel file and update data in access using VBA from access

Posted on 2014-01-30
2
2,806 Views
Last Modified: 2014-01-31
Hi,
I was wondering if some one can help me modify my access file.

I have access database and form which transfers data from excel file to access. The simple transfer is working but I have the following problem:
The transfer works vin a very simple way it takes content of excel file and transfers it to access table. Now in many instances excel file has the same datasets as already in the database plus new datasets. I need to do the following

1. For new data I just need to add it to DB, this seems to be working
2. For datasets that are not new and already exist in db, i need to compare and see if there are any changes in fields value_4 and value_6. If it is different then i need to replace the values in those fields. If it is the same then I do not touch that dat row.


I have atatched sample files excel and db file.
The Value 9 field in both excel and access are calculated fields(value1 +value2+value3). So records have to be compared by those fileds since that represents uniquness .

The module basFileImport is where the code for updating data happens.

In excel file row 26 and 20 is different than in db. rows 33 and 39 are not in db at all.

Any help will be apreciated.

Thank you!
testing-auto-update.xlsx
data-transfer-sample.accdb
0
Comment
Question by:maximyshka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39822702
That's a fairly complex routine. What have you tried so far that didn't work?

If Value1 + Value2 + Value3 define a unique record, and if you have THOSE values in your incoming file, you could first check to see if that exists in the destination table (you'd do this in the ProcessFileImport, just after the PostMessage "Processing Record" line). If you determine that a record already exists, you wouldn't use AddNew:

intRec = intRec + 1
               PostMessage "Processing record " & intRec & ".  {StoreID=" & wks.Cells(intRow, 1) & "}"

Dim rstCheck As DAO.Recordset
Set rstCheck = Currentdb.OpenRecordset("SELECT Count(*) FROM YourDestinationTable WHERE Value1=" & rstRead("Value1") & " AND Value2=" & rstRead("Value2") & " AND Value3=" & rstRead(Value3"))

If (rstCheck.EOF And rstCheck.BOF) Then
  '/ did not find a record, so continue on with the current code you have
               rstWrite.AddNew
               Do Until rstRead.EOF
               etc etc

Else
  '/ found a record, so update it:
  Currentdb.Execute "UPDATE YourDestinationTable SET Value4=" & rstRead("Value4") & " AND Value5=" & rstRead("Value5") & " AND so on .... WHERE Value1=" rst("VAlue1") & " AND Value2=" & rstRead("Value2") & " AND Value3=" & rstRead("Value3")

End If

Open in new window

So essentially you have two distinct paths the process can go - either it adds a new record, or if finds an existing record and updates that record.
0
 

Author Comment

by:maximyshka
ID: 39824070
I did it alittle bit differently. I created temp table where i first copy all records from excel using my procedure and then i run 2 queries. 1- append query with outter join sub query, 2- update query based on join. It works but i gues not very eficient. Your solution is better .

thanks!
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question