- Local time
- Today, 06:37
- Joined
- Oct 29, 2018
- Messages
- 22,506
Maybe you can setup some sort of automatic database update whenever the Excel file gets updated.
Link Access database to external Excel file
- Thread starter john_c
- Start date
Isaac
Lifelong Learner
- Local time
- Today, 06:37
- Joined
- Mar 14, 2017
- Messages
- 11,046
@john_c
You could try this:
Close the access database down and make sure everyone is out of it.
Make sure everyone is out of the Excel workbook.
Open the Excel workbook and cause it to become "Shared" (If using 365/2016 and up and don't see that option, see here)
Save and close the workbook.
Then see if you run into the same problem where the db can't read from the linked Excel sheet while someone has it open?
You could try this:
Close the access database down and make sure everyone is out of it.
Make sure everyone is out of the Excel workbook.
Open the Excel workbook and cause it to become "Shared" (If using 365/2016 and up and don't see that option, see here)
Save and close the workbook.
Then see if you run into the same problem where the db can't read from the linked Excel sheet while someone has it open?
isladogs
MVP / VIP
- Local time
- Today, 14:37
- Joined
- Jan 14, 2017
- Messages
- 18,797
@The_Doc_Man
PMFBI: You are correct. The restriction is still in place 20 years later
PMFBI: You are correct. The restriction is still in place 20 years later
Hi Isaac. Thanks for the suggestion. I tried that and still no joy unfortunately.
Isaac
Lifelong Learner
- Local time
- Today, 06:37
- Joined
- Mar 14, 2017
- Messages
- 11,046
Interesting. I'd never tried it so I wasn't sure - I've found a lot of problems with linking Excel in Access, so dropped it early on, in favor of creating import procedures, etc. It seemed to me that almost every technique in the book works better than linking directly to an Excel file.
Well, you have a lot of options, broadly speaking. One idea is to create some code in the Excel workbook that simply outputs a flat file every so often...and code your db to import the flat file. You might plop such code in the Excel ThisWorkbook module, in the AfterSave event. This means that every time someone thought the data was worth saving, it also gets updated to your database, then write import code in Access.
Not sure if you have access to a SQL server database, but leveraging that would be even better. Because then you could code your Excel workbook to dump its data-sheet data into a SQL table....then definitely link directly to the SQL table from Access.
Just a few ideas. Hope you find something that works for you!
Well, you have a lot of options, broadly speaking. One idea is to create some code in the Excel workbook that simply outputs a flat file every so often...and code your db to import the flat file. You might plop such code in the Excel ThisWorkbook module, in the AfterSave event. This means that every time someone thought the data was worth saving, it also gets updated to your database, then write import code in Access.
Not sure if you have access to a SQL server database, but leveraging that would be even better. Because then you could code your Excel workbook to dump its data-sheet data into a SQL table....then definitely link directly to the SQL table from Access.
Just a few ideas. Hope you find something that works for you!
Isaac, when you say flat file do you mean a CSV? I'm pretty sure the finance system can export a CSV file if that would make any difference?
From speaking to the suppliers of the finance system I have been told that the data is stored in an SQL database. I know nothing about SQL. What would I have to do to link my Access database to the SQL finance one? I don't even know where to start with that. Thanks for your help
From speaking to the suppliers of the finance system I have been told that the data is stored in an SQL database. I know nothing about SQL. What would I have to do to link my Access database to the SQL finance one? I don't even know where to start with that. Thanks for your help
- Local time
- Today, 06:37
- Joined
- Oct 29, 2018
- Messages
- 22,506
If the finance data is sensitive, I don't imagine you'll get easy access to the actual database. If they play nice, they might be able to give you "read" access to it.
Isaac
Lifelong Learner
- Local time
- Today, 06:37
- Joined
- Mar 14, 2017
- Messages
- 11,046
Sure, that would probably work. I just meant "any delimited" text file--if CSV is what they'll easily agree to, that would work. Make sure the delimiter that's chosen is something that won't naturally occur in the data - comma is fine if either it won't naturally occur in the data or the text is quoted (I always insist on using a more unique delimiter, like pipe or double pipe, that way even quotes can appear in the data!)
If you get permissions to the SQL database, connecting will be easy, I think. There are a variety of ways...you can create a DSN, or just build the connection string using the Access dialogue
I'm having an awful time trying to figure out the connection to the SQL server and am afraid to mess with it too much in case I break it. I tried linking to a csv file but that seems to suffer the same issue of not allowing multiple users. Is there any other way I can make this work??
Me. I don't know what I'm at with it. And I don't think my work laptop with network permissions has the necessary tools to set up OBDC connections. I've imported the data from the linked tables in to a local table on the back end for now and will update them manually for the time being. Not ideal though