Access Record Updates

Question

Hi!

I am trying to update the record in tables in access. But here is the situtation. I am the PM of a project. I want my colleagues in different departments to update excel for each department. So I don't need to update excels and apparently there are too many updates for the whole project. I have already built the links in access and I don't want to delete the whole table and import it from excel every time and rebuilt the links. I have tried to link tables with excels but it does not allow me to change the date type or build links. I also tried to import excel to the existing table but it will not update the existing records (only append new records). So is there any way that I can keep my relationships and updates records that has changed in the excels?

Answer 1

Trying to translate data from ever-changing spreadsheets to your tables will be a nightmare.

If the spreadsheets are structured like tables with a primary key you can link to them from your database. That will keep the linked "tables" up-to-date.

The best way to do all this is to split your database & give each user a copy of the front end. Then they can enter their data into a linked table via a form. 

---

Bill Mosca
https://wrmosca.wordpress.com/ https://groups.io/g/MSAccessProfessionals

Answer 2

An Excel workbook is unlikely be structured in such a way that it can be imported without further processing into an Access database and be suitable for a relational database made up of a set of correctly normalized tables.  It will almost certainly need decomposing into those tables.

The best, and simplest solution is that recommended by Bill, forgetting about Excel completely, and having all users insert and edit data via their own Access front ends, each of which is linked to a single shared back end.  The front ends do not need to be identical, so, if appropriate, you can design different front ends for different users, each with the functionality appropriate to the user.

If for some reason, this is not possible and you need to stick with Excel (but it would have to be a very strong reason), you do not need to delete the back end tables each time you import the data from Excel; you can simply delete all rows in each table, though this would need to be done in a specific order to avoid violating referential integrity.  The rule of thumb is that rows are deleted from the referencing tables in each relationship before deleting rows from the referenced tables.  This could perhaps be avoided, at least in part, by enforcing cascade deletes where appropriate in the relationships.

Once the tables have been emptied, the Excel data can be imported again en bloc, and decomposed into the Access tables, by executing a set of 'append' queries in code.  Again this needs to be done in a specific order, in this case inserting rows into the referenced tables in each relationship before inserting rows into the referencing tables.  You'll find an illustration of how to do this in DecomposerDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file data from a simple Excel worksheet is first imported into a single table, which is then decomposed into the final tables, with a brief explanation of each stage in the process.  In your case the complete process would be undertaken transparently of course in a single procedure.

---

Ken Sheridan, Stafford, England

Answer 3

Hi Bill,

Thanks for your reply. I would consider your solution but the thing is some of my colleagues are too old to accept "new" stuffs. By the way, besides Access, do you know if there is any other tool/software can achieve the same or similar fuction as I describled?

Answer 4

Hi Ken,

Thanks for your reply. But still I need to "empty" the table first which I think there is no big difference than "import" a new table. If I did not get this wrongly, what you are trying to do is to kepp the relationships untouched? However I looked into your file. It is really inspiring anyway.

Answer 5

But still I need to "empty" the table first which I think there is no big difference than "import" a new table. If I did not get this wrongly, what you are trying to do is to kepp the relationships untouched?

Emptying the tables is merely a case of executing the necessary DELETE statements in the correct order.  In my decomposer demo this is done with the following procedure:

Public Sub ResetTables()


    Dim strSQL As String
    
    ' empty all tables
    strSQL = "DELETE * FROM Contacts"
    CurrentDb.Execute strSQL, dbFailOnError
    
    strSQL = "DELETE * FROM Employers"
    CurrentDb.Execute strSQL, dbFailOnError

    strSQL = "DELETE * FROM Cities"
    CurrentDb.Execute strSQL, dbFailOnError
    
    strSQL = "DELETE * FROM Regions"
    CurrentDb.Execute strSQL, dbFailOnError
    
     strSQL = "DELETE * FROM Countries"
    CurrentDb.Execute strSQL, dbFailOnError
    
    strSQL = "DELETE * FROM MasterTable"
    CurrentDb.Execute strSQL, dbFailOnError

End Sub

The procedure is called before the code which imports the data from Excel.  The predefined table structures and the relationships between them remain intact, so when the data is inserted into them by means of the 'append' queries, referential integrity is enforced, and the end result is that the tables contain the up-to-date data.  This is far easier than trying to update the existing data piecemeal, which, as Bill said, would be a nightmare.

In your case, if each department is updating a separate Excel file, you'd start by importing each of these into the empty 'master' table so that all of the imported data is together in one table, then decompose this as necessary by inserting rows into the empty normalized tables by means of 'append' queries.  Rather than doing it step by step, as in my demo, you'd do it as a single process, so importing and decomposing the data would be at a single button click.

---

Ken Sheridan, Stafford, England