Link Access database to external Excel file (1 Viewer)

john_c

Member
Local time
Today, 12:03
Joined
Jun 9, 2020
Messages
38
Hi I'm new here and am hoping some of you might be able to help me.

I'm in the process of creating an Access database Customer Relationship Management database to log customer details, contacts, complaints, visits and communications. I have the bulk of the database completed and am happy with the functionality that I have managed to achieve with my limited Access knowledge. So far I have 5 tables and 20 forms and subforms.

The issue I'm having is with the main table which the rest of the database is built from, the "Customers" table. This table contains customer information such as account code, customer name, address, contact info, account type, and other customer info.

It is company policy that all customer accounts are set up on the finance accounts system and this is the main customer account management system. I need the Access database to be able to piggy back on the finance system. The finance system can export a spreadsheet of all customer info to Excel. My hope is that I can link the "Customers" table to this spreadsheet and then have the finance system auto export an up-to-date customer info spreadsheet every day/week.

I'm hoping someone can help me with the best way to do this. The "Customers" table contains some customer info not included on the finance system so I would like to be able to link the excel spreadsheet to the relevant fields in the "Customers" table.

I hop someone here can make sense of what I'm saying. Reading back through this I'm confusing myself!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
Hi. Do you know if the "finance system" doesn't allow you to link Access into it directly? If not, does it provide a primary key value for the exported Excel file data to allow Access to associate the "additional" info with the correct customer?
 

john_c

Member
Local time
Today, 12:03
Joined
Jun 9, 2020
Messages
38
I have very little dealings with the finance system but from speaking to the person in charge of it, the only options are to export that customer data as an excel file or CSV file. I was wondering if I could maybe create a linked table in Access to the excel spreadsheet and then somehow link the relevant values in the "Customers" table to the corresponding values in the linked table. Up to now I have just used imported the customer info from the excel sheet to the "Customers" table. However this won't work for new customers (I think). Also I should mention at some point when/if the database is completed there will be multiple users. Thats why I really need to to auto update the customer info.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
I have very little dealings with the finance system but from speaking to the person in charge of it, the only options are to export that customer data as an excel file or CSV file. I was wondering if I could maybe create a linked table in Access to the excel spreadsheet and then somehow link the relevant values in the "Customers" table to the corresponding values in the linked table. Up to now I have just used imported the customer info from the excel sheet to the "Customers" table. However this won't work for new customers (I think). Also I should mention at some point when/if the database is completed there will be multiple users. Thats why I really need to to auto update the customer info.
Yes, you should be able to link Access to an Excel file. The question is what information would you use to link the Excel data with your Customers table; that's why I ask about a "primary key" value in the CSV file.
 

john_c

Member
Local time
Today, 12:03
Joined
Jun 9, 2020
Messages
38
The customer info coming from the finance system is:

Account Code
Name
Address
Currency
ExWorks/Delivered

The "Customers" table includes all of the above plus info such as:

Email
Phone number
Active Y/N
Customer Type
Customer Sector
Customer Priority
Account Notes

Up to now I have linked all the tables using an autonumber Primary key. However I think I will have to use the Account Code now. This is a text field consisting of 3 letters and 3 numbers. The finance system will not allow an account to be set up without a suitable account code being assigned.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
The customer info coming from the finance system is:

Account Code
Name
Address
Currency
ExWorks/Delivered

The "Customers" table includes all of the above plus info such as:

Email
Phone number
Active Y/N
Customer Type
Customer Sector
Customer Priority
Account Notes

Up to now I have linked all the tables using an autonumber Primary key. However I think I will have to use the Account Code now. This is a text field consisting of 3 letters and 3 numbers. The finance system will not allow an account to be set up without a suitable account code being assigned.
Is the Account Code unique to each customer? That would be the key.
 

john_c

Member
Local time
Today, 12:03
Joined
Jun 9, 2020
Messages
38
Yes each customer is assigned a unique Account Code.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
Yes each customer is assigned a unique Account Code.
That wasn't my question (unless I misunderstood your answer). For instance, in the whole CSV file, are there any duplicate Account Codes? If so, then it's not unique enough for your purposes.
 

john_c

Member
Local time
Today, 12:03
Joined
Jun 9, 2020
Messages
38
Apologies for the confusion. Each Account Code is unique, no duplicates
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
Apologies for the confusion. Each Account Code is unique, no duplicates
In that case, you should be able to use it to link the linked CSV file table to your local Customers table.
 

john_c

Member
Local time
Today, 12:03
Joined
Jun 9, 2020
Messages
38
How do I go about doing this. Anything I've tried doesn't seem to work or doesn't update when the finance system file changes.
 

john_c

Member
Local time
Today, 12:03
Joined
Jun 9, 2020
Messages
38
My hope is that when new customers are added via the finance system by simply updating/replacing the old excel/csv file with a new one the new customers will be added to the Access database
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
My hope is that when new customers are added via the finance system by simply updating/replacing the old excel/csv file with a new one the new customers will be added to the Access database
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.
 

john_c

Member
Local time
Today, 12:03
Joined
Jun 9, 2020
Messages
38
Ok I will have to look in to APPEND queries! What about the linking of the FinanceSystemCSV table to the Customers table? It seems like something that should be fairly straightforward but I can't seem to make it work.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
Ok I will have to look in to APPEND queries! What about the linking of the FinanceSystemCSV table to the Customers table? It seems like something that should be fairly straightforward but I can't seem to make it work.
If you're trying to link the tables in the Relationship Window, don't bother - just use queries when you need them connected. To update your Customers table, use a Form with a Record Source of a query connecting the two tables. If you find the form is not updatable, then I would switch to using a form/subform setup.
 

john_c

Member
Local time
Today, 12:03
Joined
Jun 9, 2020
Messages
38
Are you saying I should just keep them (Finance System data and Additional Customer data) in two separate tables rather than merge in to one big Customers table? Thank you for you patience. I am an Access novice who has bitten off more than I can chew!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
Are you saying I should just keep them (Finance System data and Additional Customer data) in two separate tables rather than merge in to one big Customers table? Thank you for you patience. I am an Access novice who has bitten off more than I can chew!
Yes! There's no point in storing the same information in your database, if you care about having accurate data, since that information is maintained elsewhere. Just try to imagine the potential maintenance nightmare trying to keep both data in sync, when it's so much easier to just use a query to display the latest information from the original source.
 

john_c

Member
Local time
Today, 12:03
Joined
Jun 9, 2020
Messages
38
Yes I understand the aim is to prevent any repetition of data. Regarding the sync issue I had assumed/hoped the overall customers table would update when the Finance System table did. Hmm I will have to play around with this and the Ammend query. This database is gonna need some serious surgery. Thank you so much for your advice. I will report back how I fared
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:03
Joined
Oct 29, 2018
Messages
21,357
Yes I understand the aim is to prevent any repetition of data. Regarding the sync issue I had assumed/hoped the overall customers table would update when the Finance System table did. Hmm I will have to play around with this and the Ammend query. This database is gonna need some serious surgery. Thank you so much for your advice. I will report back how I fared
Hi. Good luck. You can update the local table and keep it in sync with the server, but that probably means a lot of work on your part. I would just use the original source data instead, probably because I'm lazy. Cheers!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:03
Joined
Feb 28, 2001
Messages
26,996
@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?
 

Users who are viewing this thread

Top Bottom