How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask JamesI Your Own Question
JamesI, Other
Category: Microsoft
Satisfied Customers: 2235
Experience:  James has experience providing 3rd lines support for a range of office apps, from 2000 to 2013, including project and visio.
Type Your Microsoft Question Here...
JamesI is online now

Need help to make a script to update a file in access

This answer was rated:

Need help to make a script to update a file in access
Customer: replied 1 year ago.
I have a large "master" file that i have in excel, each day I get an incremental update to this file. Containing new records and the requirment to delete old ones. Row A is unique, and Row G contains the report time. Eg 01 is a new report so needs to be added, I need a way to import this data and run a command that says if row G contains 91 to look up the value in row A and delete all other instances of that value from the workbook
Customer: replied 1 year ago.
M243FUA 91That is a sample from the update - as you can see it has 91 which means all traces of "M243FUA" need to be deleted from the workbook - obviously the value in row A is different each time

Just to clarify your input (incremental) document is in excel, but your master database is in access?

I would first start by just getting the entire input sheet imported into Access, we can then add constraints to add \ remove rows once we have got the data import working.

To do this have a look at the video here, and let me know how you get on.

JamesI and 2 other Microsoft Specialists are ready to help you
Customer: replied 1 year ago.
At the moment it is just in excel, I don't know how to go about managing the update process
Customer: replied 1 year ago.
How much would it cost for you to build me a database that I can just add the incremental file each day and access will do the rest?

Please find completed access database, which will take an input xlsx file in the users Downloads folder, import it when you click the button on the navigation form, and then upload records into the master, and remove any where Column G contains "91", as a bonus I also have designed it to export the Master database after the alternations to an XLS file in the same Downloads folder.

So its just a case of placing the file in the correct folder with the correct name, opening the database, clicking the button, accepting the warnings\notifications at each stage, and thats it. I have put the instructions for use on the navigation page.