Linked Excel Table for Access

Question

I have a split database on a server. The front end has several excel files as linked tables residing in the same folder.

The front end has been deployed to several users and all is working fine.  However, if someone opens one of the excel files directly, it locks the whole 'table' so that other users cannot use the database.

On researching, i see that most folks think importing the excel file into a table is a better plan.  My users do not want to do this, as they update the spreadsheets routinely (some monthly, some weekly) and don't want to update the information in access. 

My question is, if I write a vba procedure to import these xls files to a table as part of the auto-exec macro, then won't each person re-import the files and could that lead to corrupt tables?  Do i delete the tables when the database closes.  

I seem to be lost in the weeds here.  Any pointers would be greatly appreciated.

---

MS - Teach me to fish

Answer 1

Hi. I'm not sure your idea will work either. I suspect if someone has the Excel file open at the time a user opens the database and your code tries to import the data into a table, it might fail because Access can't lock the file to read it. Just a thought...

PS. I thought there was a way to "read" a linked Excel file while it is open for editing. I'll have to dig up some old notes.  

Answer 2

Hi lismeta,

>> if I write a vba procedure to import these xls files to a table as part of the auto-exec macro, then won't each person re-import the files and could that lead to corrupt tables?

If you use auto-exec to import excel data, it will re-import for each person. In my option, the table would not be corrupt, but it may lead some confused that some data changes after refreshing in FE due to re-importing.

>> Do i delete the tables when the database closes. 

Which tables do you want to delete? Do you mean you import data to temporary tables? If so, you could delete these tables.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 3

Thanks.  I am thinking that once the file is linked, it is just like a table in the database.  When you open it up in excel, it is like opening a table in design mode.  The excel should be opened for maintenance only and then during 'down time'.  There is really no editing these particular files except for monthly updates.  I am interested in finding about read only if you can point me in the right direction.

---

MS - Teach me to fish

Answer 4

Thanks Edward. 

I read some old posts that talked about importing the table real time, but it seems as though it would be confusing. 

I was talking about deleting the temporary excel table each time.  It would be fine for a single user app, but not the solution for the current setup.

---

MS - Teach me to fish

Answer 5

You (well, your users) are trying to eat the cake and yet keep it. The answer here is education together with a well-formed plan how your data structure should be.

1. Decide if you want an Excel-based database or an Access-based database.
2. Decide that no import/export (or the like) routines will be used. It will only cause problems.
3. Educate your users what database plan they need to comply with.

---

Best regards, George