CODEWITHSUNDEEP
exceldatabase-connectionconnection-stringvba
406LQE
406LQE

Reputation: 53

Excel internal database connection

I built a data entry UserForm to populate a worksheet that will serve as the raw database. The raw data requires further manipulation and analysis in order to be reported, so I set up a database connection using Get External Data>From Microsoft Query>Excel Files, pointed it to the file I was already working in, selected the fields I wanted and performed basic functions on those I wanted aggregated. This creates an Excel table where I then use formulas that to complete the analysis. It works great for me; I can add entries to the database, Refresh the summary table, the new entries are added and the formulas populate automatically.

The problem is that no one else can refresh the table because it's looking locally for the file. The connection string is: DSN=Excel Files;DBQ=C:\Users\MyName\Desktop\Folder 1\Results.xlsm;DefaultDir=C:\Users\MyName\Desktop\Folder 1;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

I have a very basic understanding of the database connections, but I need this file to be as automated as possible by request of my colleague. Can I fix the connection string so that the file is "flexible" and can be refreshed on any computer? Is this the best solution? If not, what else can I do that does not involve downloading additional plugins or 3rd party add-ins?

Upvotes: 3

Views: 1640

Answers (1)

EttoreP
EttoreP

Reputation: 414

If what you need is a file containing the raw data (a Database) AND one or more excel files connected to it that pick up the data from the database and work with this data, you need to split the two things. You can do the database with an access file located on a shared directory with an appropriate table and you can reproduce the user form in this file so the insertion of the data will be made in this file. Then you connect one or more excel files (using connection Mode = Share Deny None, so you can update the data and at the same time work with them from the excel files), the data will be imported in the files in tables and here you do all the proessing you need.

If one file is enough for you (you don't need to have a database with the row data separated and you don't need to use the file from different location simultaneously) and all the problem is that if the file is opened from a different location from the one specifyed in the connection string it does not work...well in this case (that seems the case) i don't know why to use a connection to the same file.
If what you need is a table for work with, just create it selecting the range with the data you already have inserted (Create a table - quick start guide) and then when you add data through the form instead of adding them in a "normal" row, add them to a new row of the table with something like WorkSheets("name").ListObjects("table_name").ListRows.Add and add the data in the new table row.

Upvotes: 1

Related Questions