Is there a way to update my form with a new source file automatically?

[vha7]

Hi all,

I have a search form right now that allows me to search any publication/paper of interest.

Lets say I get a new source file (.csv)(EDIT: or .xlsm) on a monthly basis. Is there a way to somehow automatically update the form I created with the new excel sheet(s) i.e importing the new csv file, overwriting my old data, and updating the entire form?


And if not, is there a way to optimize the way I can update the form with a new source file on a monthly basis?

Thanks!

[ranman256]

You update tables,not forms.
you can run a macro to :
empty the table
import the CSV file
display the new data on a form.

[JoeM]

You should only have one Table for this data (a well-designed database will not have multiple tables with the same structure). So all monthly data should be imported to the same Table.

If you want to keep the old data, one option would be to add a field to your table where you enter in the month that the data is for (you could always create an Update Query to update this field after import).
You could then create a parameter query which prompts the user to enter the month they wish to see.
Then, if you base your form on this parameter query, it will prompt the user to select which month they want to see data for, and only return that.

[vha7]

Ah exactly - I meant Table. not form.

Sorry, kind of a newbie here, but I would create a macro aka VBA code to do the protocol you mentioned?

[vha7]

Ah this is what I actually meant :P to update the same table with the monthly data.

And in regards to the your recommendation, this is exactly what the new data has but instead of it displaying the month, there is a field/column in the new data sheets that says "New Since Update" and the cell will be filled with "NEW" if the publication is newly added on.

In regards to the data, on a monthly basis, new publications are added onto the previous and current existing excel sheet (i.e June has 100 papers, July has 120 papers (20 new ones with 100 old ones)). So how would I go about creating an Update Query or Parameter Query as you mentioned? Sorry - kind of a newbie here - so how exactly would I go about importing the new data into the table?

[kd2017]

Yes you can do this! What's your comfort level with vba?

It will be helpful to us if you posted your table structure (maybe a screenshot of the relationship window of your database) and an example CSV file.

[vha7]

In terms of VBA, by no means am I an expert, defs on the newbie side, but I am comfortable and have been working with some code to create my little search engine (i.e enter keyword to search the entire table to acquire publications of choice) and am now in the midst of finishing it up with one of the final steps being finding a way to update the single table to which my form is based off of (automatically if possible).

As for table structure/relationship/queries, there are non that exist because I wanted it to be relatively straight forward and I simply am using one table only.
Attached below is an example csv file that I would be getting.
example_monthly_sheet.zip

Would deeply appreciate the steps to move forward

[kd2017]

The example you provided is an excel file, the OP stated CSV. What file type are you going to be importing?

You mention that you are only going to use 1 table, 1 form, and will be overwriting the the data completely rather than appending it.. why not just use excel?

[vha7]

Does it matter the file type? Ok lets say it is an excel file.

I want to update the table/form and will be overwriting the data completely because I want to distribute my MS Access file (which is a search engine) that includes my form for the multiple multiple users that will be searching this database of publications/papers, and on a monthly basis, the search form is the most up to date with new publications/papers.

[kd2017]

It does matter...

Let me backup. How did you originally import the data?

If you use the "External Data" tab -> New Data Source -> From File -> Excel (or Text File for CSV) it will take you to an import wizard. On the first page of this wizard it'll ask for the filename to import and what method you want to use to import it. If you select the last import method option to "Link to the data..." then access will reload your excel file when ever you open the Access file, the data will always be current. But you won't be able to edit the file from access in this case.

Then when you get your monthly updated spreadsheet you can rename last months spreadsheet to DataFile_18_08.xls or something and save the updated spreadsheet the name that Access expects and will load.

For example a listing of the files in your data file's directory might look like this:
DataFile_CURRENT.xls <-- the latest data file for September, 18
DataFile_18_08.xls <-- Data file from August, 18
DataFile_18_07.xls <-- Data file from July, 18
DataFile_18_06.xls <-- Data file from June, 18
DataFile_18_05.xls <-- Data file from May, 18

And because you set up your Access database to *Link* to DataFile_CURRENT access will always load the data in that filename. Would this work for your situation?

[vha7]

Ohhh ok, this is perfect - exactly what I was looking for. And sorry, I should have clarified the specifics right off the bat.

I originally imported the data via: "External Data" --> "Excel" --> "Import the source data into a new table in the current database". But now I will test out the last method of selecting "Link to the data..."

I will test it out soon but thank you so much - it is deeply appreciated.

[vha7]

It does matter...
Then when you get your monthly updated spreadsheet you can rename last months spreadsheet to DataFile_18_08.xls or something and save the updated spreadsheet the name that Access expects and will load.

For example a listing of the files in your data file's directory might look like this:
DataFile_CURRENT.xls <-- the latest data file for September, 18
DataFile_18_08.xls <-- Data file from August, 18
DataFile_18_07.xls <-- Data file from July, 18
DataFile_18_06.xls <-- Data file from June, 18
DataFile_18_05.xls <-- Data file from May, 18

And because you set up your Access database to *Link* to DataFile_CURRENT access will always load the data in that filename. Would this work for your situation?

So I tried the linking table method, however it doesn't seem to work. Whenever I change the file names i.e update the new monthly file name to "CURRENT" and change the previous month's to the original date, it pop up an error where it says the name of my sheet has an invalid character or what have you.

Am I changing the file name within the folder that I have, or am I changing it in Access, or... need some help please!

[kd2017]

What gives an error, access or windows explorer? Post exact error messages and error numbers.

Are the Excel files in the exact same format each month? That is same sheet names, "table" structure, etc?

To start I would swap the data files around in Windows explorer with access closed. Don't mess with them while your access app is open. When everything is working then you can look at scripting to manage the files.

[vha7]

What gives an error, access or windows explorer? Post exact error messages and error numbers.

Are the Excel files in the exact same format each month? That is same sheet names, "table" structure, etc?

To start I would swap the data files around in Windows explorer with access closed. Don't mess with them while your access app is open. When everything is working then you can look at scripting to manage the files.

Perfect, I got it to work by ensuring the same format - thanks!
Two more questiosn - with the linked table, I noticed that the ID/primary keys are gone. Just out of curiosity, is there a way to add it back?

Additionally, lets say I wanted to share my access database with multiple users, would there be a difference in terms of using the linked table as compared to using a normal table?