Question How to set up Access database as a back-end

[MilaK]

Hello,

I plan on using Excel as the front-end and Access as the back-end only. (all queries and forms are in Excel). A single Access database will be queried from multiple Excel files that will be located on the share drive (Linux server). Access tables contain memo, text and number fields. Data will be sent to Access tables and records will be appended/deleted; all via Excel.

There will be multiple users interacting with a single Access database via multiple Excel files located on the shared drive. Users will be interacting from different workstations with Windows 7, all have Access 2010 and Excel 2010 installed.

This process works for me from my workstation but I’m not sure what will happen when other 4 people will be sending/appending/deleting records to the same Database? How do I make this work? What settings do I need to check in Access to make this possible?

Any advice will be greatly appreciated since I’m fairly new to Access.

Thank you!

MilaK

 

[spikepl]

I plan on using Excel as the front-end

That really sounds like a lot of self-inflicted pain. Why?

 

[MilaK]

why do you thinks so?

It might not be the best way but I've already have everything built and working this way. I just want to make sure that it will be functional for multiple users and multiple workbooks as I described in my previous post. I also would like to know what problems I might be facing with this set up.

Thank you very much!

 

[spikepl]

Because in Access you have built-in mechanisms for:

• performing all operations on data in bound forms (no SQL or programming is necessary)
• creating a work flow, so that things get done in a sequence
• checking and rectifying input
• handling error conditions
• handling optimistic record locking relatively gracefully

Creating all these thing in Excel is doable but a lot of work

 

[MilaK]

Unfortunately, I don't know much about Access and not sure what you are describing in your post.

I've already spent three months building this project in Excel and it works ok for me but please suggest how to set this up for multiple users.

I use ADODB to add new records to a table. I also append records and delete them. I have few queries that work pretty well but I'm not sure how fast they will be when I have thousands of records in Access? That's pretty much it.

1. New data will be sent to Access once twice per week by a single user.

2. I suspect the database will get queried several times per day by several users.

Thanks

 

[Uncle Gizmo]

I agree with spikepl, you would be much better off moving over to MS Access.

However if you wish to continue to create your project using Excel then you are going to have to solve some unusual problems.

In other words when you ask questions like "how do I do this" in the MS Access forum "we" are going to be scratching our heads and thinking I don't know, because it's not something that we are familiar with. In other words you are very likely to find yourself working on your own with very little help unless of course you get very lucky and find someone that has done it before.

You might get better luck in an Excel related forum.

Spikepl has hinted at the sort of problems you might have to overcome, for instance if two of your users are working on the same record at the same time how are you going to decide which edit should be kept and which edit should be discarded?

A possible solution would be to check through the records that a user has access too, record this in a special control table, now before another user can get access to those particular set(s) of Records they will have to wait until the user has finished with them, but then that means you have to write code which keeps this control table up to date.

I don't even know if what I have explained above is the right way to go about it, there may be other much easier methods but I wouldn't know where to start to look to find out because it's not something that I see done regularly.

However I'm pretty sure it is something that has been done by someone some when, so I'm not completely dismissing your idea, I think it has merit and I think you will learn a lot in your undertaking. But I think as spikepl suggested, you are making a rod for your own back!

 

[Uncle Gizmo]

It might be worth setting up some tests. I would suggest Running 2 PCs accessing the table on your server. Change a record from one PC and see what it says on the other PC.