Splitting Database Access 2010

[devo96]

I have some questions about working with a split database.

First off...i'm using Access 2010 and am planning to put the back end on a Network Accessible Storage drive and installing Access 2010 Runtime for my users. I can have as many as 10 users at once, with 3 of them needing access daily. The database will be updated daily by several (6-10) Excel files updated over the internet through dropbox/skydrive from my company's remote operations. The remote operations do not need access to the database. (But the NSA needs more data to sift through.)

I have a working model that updates the database perfectly...though i'm not split yet. There is a table that contains the filenames of the excel files. The excel files themselves will probably reside on at least different computers.

How should these paths be formatted? I'm assuming they should be network addresses (example "\\acomputer\path\filename.xls") and as long as the computer containing the file is sharing the path, all will be well, right? And since the code will be executed on the local host, drive letters should be out because each can have its own different drive letters, right? Does putting the back end on a NAS drive make sense? Or should it reside on a computer? or does splitting even make sense (vs. shared database)?

do i have good assumptions? am i on the right track?

thank you in advance for your response.

 

[Mr. B]

First, by all means, if you are going to have multiple users using your database, you must split it. The back-end file must be located on a shared network drive where all users have read/write capability.

Next, each user must have a copy of the front-end file located in a folder on their own computer. Although the actual path to the location of the front-end file is not required to be the same on each user's computer, it is a good idea to be consistent and put the front-end file in the same folder on each computer if possible. (This will just make things simpler for you later when you need to do any maintenance.)

As for the Excel files, I would suggest that instead of trying to have your application access the files on multiple user's computers, it would be simpler and easier to manage if you have each user that has an Excel file that will need to be imported into your database, copy or move the Excel files to a network shared folder. This way your application can know the exact location to where the files should be located and can then check for the existence of files.

I think you are on the right track but these are the things that I would suggest you try to accomplish.

Hope this helps.

 

[devo96]

Mr. B.,
Thank you very much for your response.
I will definitely incorporate your suggestions....it makes perfect sense!

thanks again!
devo