Problem reading data from access database in Excel

[Chky071]

I have created an Access database that stores a significant amount of information about our products. I have an excel spreadsheet that performs calculations on that data. I have used the import data from Access tool in Excel to import the data. The problem I have is when the excel spreadsheet is open no one can modify my Access database. Is there a way to have Excel open and still be able to work on the database in Access? When the user on Excel needs updated information they can just hit the "refresh" button and everything up to that point that has been done in Access will update. I do not want Excel to be able to manipulate or change any values in my Access database.

I am using Access 2007



Thanks,
Chad

[rpeare]

My question is... why are you doing these calculations in excel and not access? I don't think I've encountered something that could be done in excel that could not be done in access.

[Chky071]

The reason for doing in Excel is I already have everything setup, previously all the data was stored in excel. I am very new to Access and decided it would be a lot easier to track all the data inside of Access and then do all the calculations in Excel. I am not opposed to doing the calculations in Access, it would just take me a significant amount of time to set it all up.

[rpeare]

The problem sounds like you are not actually exporting data to excel, it sounds like you are linking an access table into an excel spreadsheet, when you do that the tables are going to be tied up. Either that or you are not terminating your connection to the Access database once the import is complete. You may also want to split your database (if you haven't) into a front end/back end, typically these sort of record locking problems go away when you separate your data from your user interface.

[Chky071]

rpeare, thanks for your your help. Since I am new to all of this I am very interested in what the best practices are. What would be the best way to separate my data from my user interface?

[Chky071]

Rpeare, I read up on how to split a database. I think this may have fixed the problem. Things seem to be working a lot better. Thanks for your help!