Excel as a Database - The problem of simultaneous user access.

GAJITCS

Board Regular
Joined
Apr 21, 2015
Messages
50
My client requested a level of automation based around an existing spreadsheet to allow a quoting system to be built. The requirement at the time was that a single user should be able to complete enter data directly onto a tab and by the wizardry of VBA, have that data written to another tab. This was all well and good, but them came issues like predictive lookups when entering data. This was all achieved and finalised.

On these same tabs used for data entry, they wanted to be able to recall previous quotations and edit them. This was also completed.

The issue now is that they have decided that they would like 5-10 users to be able to make use of the spreadsheet at the same time. While I can invoke a Shared Workbook (Legacy) option, this adversely affects the positioning of the active lookup boxes already coded. Not sure why.

If I move the file to Onedrive and share it, there is too much of a lag between notifications that the file has been updated and while user 1 has been slow in editing their new quote, user 2 has completed theirs. User 1 then loses their part complete quote and has to start again.

The answer I know, is going to be Access.

My question is has anyone else overcome this issue and been able to retain the work already done in Excel? and how?

TIA
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
L

Legacy 456155

Guest
I don't know enough about your project to know if I have overcome your specific hurdles, but I have used Excel "creatively" and effectively as a front end in the past because that was what they wanted and they did not have or want Access. It might not be as difficult as you might suspect to move your data to a few tables and then use a dynamic cursor in ADO. Access is not required BTW, though it's nice to use the table designer and importing functions to initially set up your DB. It's not necessary, but you can actually create a blank MDB file using the ODBC Data Source Administrator built into Windows and follow it with DDL SQL to work up your schema. ADO's ADOX is another method. There are probably other options that I am not aware of. Anyway, for a better answer, you may need to post more details, or better yet, an example file.
 

GAJITCS

Board Regular
Joined
Apr 21, 2015
Messages
50
I don't know enough about your project to know if I have overcome your specific hurdles, but I have used Excel "creatively" and effectively as a front end in the past because that was what they wanted and they did not have or want Access. It might not be as difficult as you might suspect to move your data to a few tables and then use a dynamic cursor in ADO. Access is not required BTW, though it's nice to use the table designer and importing functions to initially set up your DB. It's not necessary, but you can actually create a blank MDB file using the ODBC Data Source Administrator built into Windows and follow it with DDL SQL to work up your schema. ADO's ADOX is another method. There are probably other options that I am not aware of. Anyway, for a better answer, you may need to post more details, or better yet, an example file.
Thanks for the reply. Will look into some DB stuff.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,961
Messages
5,621,851
Members
415,862
Latest member
DemianGS

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top