Link Access database to external Excel file (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 05:34
Joined
Oct 29, 2018
Messages
21,357
From googling it it seems it is a common problem when linking to excel tables do to multi user limitations. I will have a go at what you suggested but long term the database really needs to be linked to the excel file in real time as the file could be updated 10 times a day. Nothings ever easy!!
Maybe you can setup some sort of automatic database update whenever the Excel file gets updated.
 

Isaac

Lifelong Learner
Local time
Today, 05:34
Joined
Mar 14, 2017
Messages
8,738
@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?
 

isladogs

MVP / VIP
Local time
Today, 12:34
Joined
Jan 14, 2017
Messages
18,186
@The_Doc_Man
PMFBI: You are correct. The restriction is still in place 20 years later
 

john_c

Member
Local time
Today, 12:34
Joined
Jun 9, 2020
Messages
38
@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?
Hi Isaac. Thanks for the suggestion. I tried that and still no joy unfortunately.
 

Isaac

Lifelong Learner
Local time
Today, 05:34
Joined
Mar 14, 2017
Messages
8,738
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!
 

john_c

Member
Local time
Today, 12:34
Joined
Jun 9, 2020
Messages
38
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:34
Joined
Oct 29, 2018
Messages
21,357
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
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.
 

john_c

Member
Local time
Today, 12:34
Joined
Jun 9, 2020
Messages
38
I managed to blag the username and password for the database. Problem is I don't even know where it is!
 

Isaac

Lifelong Learner
Local time
Today, 05:34
Joined
Mar 14, 2017
Messages
8,738
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?
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
1600356647063.png


1600356664526.png
 

john_c

Member
Local time
Today, 12:34
Joined
Jun 9, 2020
Messages
38
Thanks Isaac I will have a look when I'm back in the office tomorrow
 

john_c

Member
Local time
Today, 12:34
Joined
Jun 9, 2020
Messages
38
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??
 

Isaac

Lifelong Learner
Local time
Today, 05:34
Joined
Mar 14, 2017
Messages
8,738
What seems to be the problem with linking to the SQL server?
 

john_c

Member
Local time
Today, 12:34
Joined
Jun 9, 2020
Messages
38
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
 

Users who are viewing this thread

Top Bottom