Importing an excel file that must be updated daily

[apetriella]

Hello all,

I have a slight problem with access. Currently I am required to create a database that requires the source table to be updated on a daily basis. Initially, I used a linked database from excel and a specific file path on my computer. This proved to be problematic due to the fact that whenever I needed to update the file, I would have to re-import it from excel with the new file.

I also need to connect two fields in a form together (One is an identification number and the second is the facility that it identifies). Currently the only way to work with it is to manually input the identification number as well as the facility name separately, I would like to get it to the point where I just have to enter the identification number and it would automatically pull the facility name that matches it. Any help would be appreciated. Thank you

[June7]

What method are you using for getting the Excel data - are you just linking to the file or are you importing the data? Is the Excel file cumulative or is it only new data?

Why are you saving both facility ID and facility name? Where would the facility name be pulled from?

Not sure what you need, maybe a multi-column combobox. http://www.datapigtechnologies.com/f...combobox3.html

[apetriella]

What method are you using for getting the Excel data - are you just linking to the file or are you importing the data? Is the Excel file cumulative or is it only new data?

Why are you saving both facility ID and facility name? Where would the facility name be pulled from?

Not sure what you need, maybe a multi-column combobox. http://www.datapigtechnologies.com/f...combobox3.html

1. The file is being linked to Access from an excel spreadsheet and it is a cumulative file

2.Facility ID and Facility Name are being saved on an Access form, facility name is being pulled from the previously mentioned excel file. I should mention that everything is being pulled from the excel file.

[June7]

Okay, you have a link to Excel file. I am not sure what you want to do with it. Do you want to transfer that data to an Access table?

[apetriella]

Okay, you have a link to Excel file. I am not sure what you want to do with it. Do you want to transfer that data to an Access table?

If it is possible I want to be able to update the file and have it automatically update in access. For example, if I have 100 new facilities that have to be added to the excel file, I would download my excel file from its source and replace the old excel file with the new one and have access automatically recognize it, if this is not possible I'm open to an idea that would give me similar results.

[June7]

As long as the Excel download is saved with the same file name and has the same structure, Access link should work.

[apetriella]

I have found a simple solution to the first problem I had (of constantly updating an excel file). Now, I still have the issue of connecting two field values together. The problem I'm facing is still the same one. I need a certain facility ID number to match a facility name value when it is automatically put into a form. The current method is to put in the value (say facility number 1) and then going into the form and manually putting in the facility (say facility name Joe's hardware store). I would like it so that when I go to the facility number field and put in 1, it will automatically pull up Joe's hardware store in the facility name field.

[June7]

Did you view the link about multi-column combobox?

If user selects facility from a combobox, the facility name can be a column of that combobox. Then the name is available for viewing. Code can reference the column.

I still don't see need to save the name as well as the number.

[apetriella]

Did you view the link about multi-column combobox?

If user selects facility from a combobox, the facility name can be a column of that combobox. Then the name is available for viewing. Code can reference the column.

I still don't see need to save the name as well as the number.

Combo Boxes rule, thank you! It works.

[Rw9122]

I have found a simple solution to the first problem I had (of constantly updating an excel file). Now, I still have the issue of connecting two field values together. The problem I'm facing is still the same one. I need a certain facility ID number to match a facility name value when it is automatically put into a form. The current method is to put in the value (say facility number 1) and then going into the form and manually putting in the facility (say facility name Joe's hardware store). I would like it so that when I go to the facility number field and put in 1, it will automatically pull up Joe's hardware store in the facility name field.

I have the same situation. Could you tell me the solution you have for the 1st problem?