Link Access database to external Excel file (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 13:22
Joined
Oct 29, 2018
Messages
21,358
@theDBguy - Just sticking in my two cents' worth, probably not worth that much but...

Do I remember correctly that when Access LINKS to an Excel table that it cannot update that table? And thus if a JOIN is made using the Excel table with an Access table, wouldn't that JOINed result be non-updateable?

Or did a later version of Access/Office lift that restriction?
Hi Doc. You remember correctly. In Post #15, I did sort of mention the possibility of the form being read only and provided an alternate approach if that was the case. However, there has been a recent revelation where you can make a linked Excel file updatable, not because the limitation was removed, but only because of a loophole in making the connection. In any case, it doesn't apply in this situation, because there is no need to update the Excel file, since it will just get overwritten by the new export from the finance system anyway. Cheers!
 

CarlettoFed

Member
Local time
Today, 21:22
Joined
Jun 10, 2020
Messages
119
The Excel file, from which you receive the financial source data, how you receive it:
you receive it by email
you are connected to a folder on server​
 

john_c

Member
Local time
Today, 20:22
Joined
Jun 9, 2020
Messages
38
The Excel file, from which you receive the financial source data, how you receive it:
you receive it by email​
you are connected to a folder on server​
I am hoping the Finance System will autoreplace the file in a folder on the server. Alternatively I may need to replace it myself when necessary.
 

CarlettoFed

Member
Local time
Today, 21:22
Joined
Jun 10, 2020
Messages
119
Can you have the Access file and the Excel file, which is exported from the Financial System, both without sensitive data, in order to create a test file?
 

john_c

Member
Local time
Today, 20:22
Joined
Jun 9, 2020
Messages
38
Can you have the Access file and the Excel file, which is exported from the Financial System, both without sensitive data, in order to create a test file?
There is unfortunately a lot of "sensitive data" incorporated in to the design of the database, would take me an age to clean it up. I will see how I go with it myself before I take that step. Thanks for your interest though.
 

john_c

Member
Local time
Today, 20:22
Joined
Jun 9, 2020
Messages
38
Okay, I may have to do some testing to make sure what I'm about to say actually works, but this is how I see it. Let's say you have a linked table called FinanceSystemCSV, which is linked to the Excel/CSV file. Let's also say it has 10 rows of customers in it. Now, you also have a Customers table with only the 10 Account Codes in it, matching the ones from the finance system, plus some fields you want to keep track, that are not included in the finance system. Okay, so if you export a new CSV file from the finance system, and it now has 11 customers listed, then your FinanceSystemCSV table should show all 11 customers. However, your Customers table will still only have the previous 10 customer. To get the new customer added to your local table, you will have to execute an APPEND query. Likewise, if the new CSV file has less customers, and you want to remove the extra one from your table, then you will have to execute a DELETE query. You can perform these actions automatically, using a macro or procedure, when your db opens up.
@theDBguy Can you possibly expand on how I would use an APPEND query to update a new customer from the Finance System. I have followed your advice thus far and managed to link the Finance System data to the Access database. I have had to spend the scouring every form and changing all the connections and formulas to suit the new set up. What a pain!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:22
Joined
Oct 29, 2018
Messages
21,358
@theDBguy Can you possibly expand on how I would use an APPEND query to update a new customer from the Finance System. I have followed your advice thus far and managed to link the Finance System data to the Access database. I have had to spend the scouring every form and changing all the connections and formulas to suit the new set up. What a pain!
Hi. Try the following (make sure to use the actual names of your tables and fields)...

This query should return all matching customers between the two tables:
SQL:
SELECT C.AccountCode
FROM Customers C
INNER JOIN FinanceSystem F
ON C.AccountCode=F.AccountCode

This query should return all new customers from the finance system:
SQL:
SELECT F.AccountCode
FROM FinanceSystem F
LEFT JOIN Customers C
ON F.AccountCode=C.AccountCode
WHERE C.AccountCode Is Null

If the above works, let me know, and we'll try creating an APPEND query next.

Hope that helps...
 

john_c

Member
Local time
Today, 20:22
Joined
Jun 9, 2020
Messages
38
Hi. Try the following (make sure to use the actual names of your tables and fields)...

This query should return all matching customers between the two tables:
SQL:
SELECT C.AccountCode
FROM Customers C
INNER JOIN FinanceSystem F
ON C.AccountCode=F.AccountCode

This query should return all new customers from the finance system:
SQL:
SELECT F.AccountCode
FROM FinanceSystem F
LEFT JOIN Customers C
ON F.AccountCode=C.AccountCode
WHERE C.AccountCode Is Null

If the above works, let me know, and we'll try creating an APPEND query next.

Hope that helps...

OK I've set up both these queries and the function as you say ie the first one shows the matching Account Codes and the second one shows the new Account Codes. However this still doesn't add the new Account Codes to the Customers table. Is there a way I can do this automatically as the database does seem to recognise the new customer unless it is added to the Customers table manually?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:22
Joined
Oct 29, 2018
Messages
21,358
OK I've set up both these queries and the function as you say ie the first one shows the matching Account Codes and the second one shows the new Account Codes. However this still doesn't add the new Account Codes to the Customers table. Is there a way I can do this automatically as the database does seem to recognise the new customer unless it is added to the Customers table manually?
Hi. Glad to hear we're making good progress. As I said earlier, those queries were just to "test" whether we're on the right track or not. Now, we can construct the APPEND query. Try the following:
SQL:
INSERT INTO Customers (AccountCode) SELECT F.AccountCode
FROM FinanceSystem F
LEFT JOIN Customers C
ON F.AccountCode=C.AccountCode
WHERE C.AccountCode Is Null
Hope that helps...

PS. Make sure you have a backup copy of your db before trying the above. Cheers!
 

john_c

Member
Local time
Today, 20:22
Joined
Jun 9, 2020
Messages
38
Hi. Glad to hear we're making good progress. As I said earlier, those queries were just to "test" whether we're on the right track or not. Now, we can construct the APPEND query. Try the following:
SQL:
INSERT INTO Customers (AccountCode) SELECT F.AccountCode
FROM FinanceSystem F
LEFT JOIN Customers C
ON F.AccountCode=C.AccountCode
WHERE C.AccountCode Is Null
Hope that helps...

PS. Make sure you have a backup copy of your db before trying the above. Cheers!

I'm getting a warning box saying "Could not find file 'C:\Users\johnc\Documents\tblCustomers.mdb'."

tblCustomers is the Access table with additional customer info

For reference my linked table is tblCustomersLinked
 

john_c

Member
Local time
Today, 20:22
Joined
Jun 9, 2020
Messages
38
I'm getting a warning box saying "Could not find file 'C:\Users\johnc\Documents\tblCustomers.mdb'."

tblCustomers is the Access table with additional customer info

For reference my linked table is tblCustomersLinked

I figured out the issue...turns out I'm an idiot

Ok so I ran that query and it adds the Account Codes to the Customers table. You do get a warning message saying you are about to insert rows, no going back and all that. Any way around that? Will probably freak out the endusers of the database!
 

john_c

Member
Local time
Today, 20:22
Joined
Jun 9, 2020
Messages
38
Right I disabled the warning for Actions in the Access Options. And I've added an AutoExec macro to run the query when the database is opened which seems to do the job. As far as I'm aware the Finance team never delete customer accounts so the current query will hopefully suffice.

Thank you so much for your help DBguy, thought I'd never get it working! (Although I won't know if any of it works until I put it on the server, if this lockdown ever ends...)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:22
Joined
Oct 29, 2018
Messages
21,358
Right I disabled the warning for Actions in the Access Options. And I've added an AutoExec macro to run the query when the database is opened which seems to do the job. As far as I'm aware the Finance team never delete customer accounts so the current query will hopefully suffice.

Thank you so much for your help DBguy, thought I'd never get it working! (Although I won't know if any of it works until I put it on the server, if this lockdown ever ends...)
Hi John. You're very welcome. Yes, there is a way to avoid the warning, but we'll leave that one for now until you need it. Good luck with your project.
 

john_c

Member
Local time
Today, 20:22
Joined
Jun 9, 2020
Messages
38
Right so this project kind of faded away with the lockdown but its back going again now. I have run in to another problem related to the linked excel table. I have split the database and have the back end on the server. It all works fine when only one person is using it. However when someone else tries to open the front end file they get the following message:

"The Microsoft office database engine cannot open or write to the file '....xlsx'. It is already opened exclusively by another user etc".

From what I can see this is because of the limitations of excel with multiple users. Is there a work around for this? I still want the excel table to update automatically as I can not trust anyone in the office to do it if I'm not there.

@theDBguy have you any ideas?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:22
Joined
Oct 29, 2018
Messages
21,358
Right so this project kind of faded away with the lockdown but its back going again now. I have run in to another problem related to the linked excel table. I have split the database and have the back end on the server. It all works fine when only one person is using it. However when someone else tries to open the front end file they get the following message:

"The Microsoft office database engine cannot open or write to the file '....xlsx'. It is already opened exclusively by another user etc".

From what I can see this is because of the limitations of excel with multiple users. Is there a work around for this? I still want the excel table to update automatically as I can not trust anyone in the office to do it if I'm not there.

@theDBguy have you any ideas?
Hi. When you said you have split the database, do each user gets their own copy of the front end on their local machine?
 

john_c

Member
Local time
Today, 20:22
Joined
Jun 9, 2020
Messages
38
Yes, back end on the server and everyone has their own front end on their desktop. I think it would work fine if it weren't for the link excel tables.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:22
Joined
Oct 29, 2018
Messages
21,358
Yes, back end on the server and everyone has their own front end on their desktop. I think it would work fine if it weren't for the link excel tables.
If that's the case, maybe you can eliminate the link to Excel and simply "copy" the data from Excel into the local copy of the FE for each user. Just a thought...
 

john_c

Member
Local time
Today, 20:22
Joined
Jun 9, 2020
Messages
38
The excel file updates every day so it wouldn't be practical to copy data into everyone's FE file. Very annoying, everything works great with just one user. Would it be possible to have the back end update a local table at the same time every day from the excel file rather than using a linked table?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:22
Joined
Oct 29, 2018
Messages
21,358
The excel file updates every day so it wouldn't be practical to copy data into everyone's FE file. Very annoying, everything works great with just one user. Would it be possible to have the back end update a local table at the same time every day from the excel file rather than using a linked table?
Hi. Unfortunately, I can't do any tests right now. I imagine what you have set up should work, but I can't test it for multiple users. So, I was just saying maybe you can remove that dependency for now, if it will make your life easier. If you don't want to copy the data from Excel into the FE, then perhaps you can copy it to the BE, and just have everyone link to it, instead of to Excel. You can have that process work manually or automatically, once every day.
 

john_c

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

Users who are viewing this thread

Top Bottom