How do you make a read-write data connection between two Excel worksheets? (I am currently using an od.odc file and a connection that uses SQL)

Question

I have the following project started:

Inside of Master.xlsx I have (the worksheet is named MasterList):

I then go into CA_Accts.xlsx and make a data connection, with the following steps:

Next, I modify the properties of the data connection so that it uses SQL to retrieve only the records from MasterList that belong to California, I do that as follows:

This leaves me with my almost desired result:

The problem is that I cannot change the records in Master.xlsx from within CA_Accts.xlsx. For example, I would like change the Overdrawn field for AcctNum J132 to NO, but when I click refresh I get back the data from the MasterList (,my NO goes right back to being a YES). What I am really looking for here is a read-write, kind-of connection. Does anyone have any idea how I could do this? I tried making a table in Access that got its data from MasterList, and then using this as a connection, but Excel's wizard didn't recognize the table in Access, so I couldn't establish a connection this way. I am stuck your help would be greatly appreciated. Thanks in advance. (BTW, in the future I will change all of the file formats to xlsm files, so that I can write some event driven macros, to deal with my refreshing of the data.)

Answer 1

There is no such thing like a bi-directional external link, they are always "read only". As you already figured out, the only solution is running some VBA code, which would write the changed data back to the source.