Moving Up From Access For Data Search

[Breezwell]

Hi everyone,

I realize this is a question that involves a tremendous amount of analysis based on numerous factors, but I am simply interested in getting some opinions from the forum.

I have spent a considerable amount of time developing a search application in Access for use by a total of ten people in my company. Well, the rest of the company has caught wind of the app and would like suggestions as to how to scale it up for closer to 1,000 people. Now, I realize there is a ton to consider, but I was wondering what solutions others have used when asked to scale up an Access application used primarily for data search solutions (SQL Server, Oracle, MySQL, etc...) I think I will have the ability to chose the technology I want, but I am not sure which is the best option long-term.

The key here is that I want to keep the tool out of IT's hands as it will get morphed into something useless very quickly without any consideration for usability and data value focus.

I know this is a vague question (I don't want to ramble on in the event nobody finds this topic remotely interesting) and I am willing to discuss this in more detail if anyone is interested here in the forum.


Thanks

[NeoPa]

With a fairly limited understanding of your requirement, it occurs to me that releasing a Front End database (Front-End / Back-End (FE/BE)) to each user's PC (stored locally on HDD) may suit your purposes.

[Breezwell]

NeoPa,

Thanks for the reply.

I guess I have three concerns with the FE / BE approach:

1.) Is an Access application actually capable of supporting up to, and maybe over, 1,000 users? I am thinking worst case scenario with say all 1,000 users searching at the same time. I know, it's extremely unlikley but.....

2.) Would speed be an issue? As Access is isn't technically a 'server', could this pose problems long term if the data collection grows substantially very quickly?

3.) How easy is it to tie Access into other back end databases? Say I wanted to link my data with data in an Oracle database. Is this something that can even be done?

The solution sounds great with respect to keeping control of the application locally with regional user access.

Thanks

[NeoPa]

1. As Access will only ever need to handle one at a time, the Back-End is all that needs to handle multiple accesses, this shouldn't be a problem.
2. Using Access as the Back-End may cause some bogging down. I suggest considering a different Back-End.
3. Pretty easy really. Simply link to a table provided by one of the many RDBMS servers that Access supports. Most / all of the names you've heard of I expect.
   If Oracle provides an ODBC driver (It does of course) then Access can link to it with ease.

Give it a shot. I think it can work for you.

[ADezii]

@Breezwell
I know much of what I am stating is repetitive, so please bear with me. Even the 'remote' possibility of 1,000 Users accessing the Database at the same time effectively eliminates Access as a Back End Database since it can only handle 255 Users concurrently. Personally, I would look into the possibility of an Access Data Project (*.adp) which is an Access Front End consisting of Forms, Reports, Modules, etc. Linked to a Database residing on a preferably Dedicated Server running SQL Server, a true Client/Server architecture. It is a simple matter to connect an Access front End to a SQL Server Back End usually by using the modern OLE DB approach. Here is a very brief summary of an ADP straight from the Help Files:

A Microsoft Access project (.adp) is a new type of Access file that provides efficient, native-mode access to a Microsoft SQL Server database through the OLE DB component architecture. Using an Access project, you can create a client/server application as easily as a file server application. This client/server application can be a traditional solution based on forms and reports, or a Web-based solution by using data access pages, or a combination of both.

[Breezwell]

Thank you both for the great input.

I am currently trying the FE / BE method to see how things respond in terms of access speed, etc. without investing in IT support at this time.

I will also look into the Access Data Project option as it really sounds ideal from a core data security / separation perspective. The ability to keep the current UI controls that my users and accustomed too is very nice.

I will try and remember to follow up on this thread with results for others to reference.

Again, thanks for the help!

[NeoPa]

Sounds good :)

I think ADezii's info sounds very reliable btw.

Let us know how you get on.

[Stewart Ross]

Just reinforcing what ADezii said about 255 users - that is indeed Access's absolute limit. Add one more user and Access will fail - not an if or a but or a maybe, it will fail. Other posters have experienced the results - update failures, loss of the normal sharing capabilities, and so on.

This happens for the most part because Access can not maintain its .LDB lock file beyond 255 users in the list. Splitting the database into a FE/BE structure does not change the limit - the back-end DB when connected via the front end DB will have an associated lock file open which is subject to the normal 255-user absolute limit regardless.

I also hesitate to think what the performance of the DB would be like with substantial numbers of concurrent users...

As ADezii indicated, if there is the slightest possibility that more than 255 users will be involved you MUST use a proper back-end DB such as SQL Server. In my opinion, Access is at its best when handling much smaller numbers of concurrent users - so I'd not even consider doing anything other than going the 'proper server' back-end route if the number of concurrent users was over a hundred at all.

The following link is to an MSDN article on developing Access client-server projects: http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

-Stewart

[NeoPa]

@NeoPa
Please ignore number 1.

I'm sorry to say I misled you there. I overlooked an important point. The others are absolutely correct on this point.