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

[GAJITCS]

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

 

[Legacy 456155]

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]

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.