Best DB Program

[speakers_86]

I am trying to design a database for the company i work for, and I am wondering what is the best program to use.

My database will be shared on a network, and many different users will be using it at once. Also, I need this database to allow vpn connections so that certain people can access the db from outside of the office. I am not sure if db programs would deny a vpn user access, it might not make a difference how people are accessing info, but i think its worhtwile to mention that.

I appreciate your help, even though you opinions may be just a little biased. but thanks none-the-less.

 

[KeithG]

How many users? How much data are you going to be storing. MS Access is a good file server database but if you are going to have alot of concurrent users I would suggest a client sever db such as SQL Server. There is a free version of SQL server which is SQL Server Express but it has a limit of 4 gb of data.

 

[speakers_86]

There will be as many as 5 users on the db. I dont know how many gigs the info being stored will take up, but we will be storing information on work in marketing, collections, and billing. Im sure that after some time we would fill 4 gigs.


Also, the primary key is a unique number we call a dispatch number. On the shared folder in the office, there is a folder called pictures. In this folder there is one folder for every dispatch number. In this folder are pictures that were taken at the job site. Whatever pictures are in these folders are i would like the db program to associate with the job record, so that we can view the pictures from within the db program, possibly as a thumbnail view. It would be very convenient if this is possible.

 

[ptr_turbo]

Linking pictures to despatch numbers

I had a similar problem recently. However we found that having a folder for each job number quickly became very cumbersome. Soon we had hundreds of sub folders.
We overcame the problem by prefixing the picture file name with the job number, eg if the job number is say 0700987, simply save the file as 0700987This is a picture of rah rah rah.jpg or whatever. Then have just one folder called "PICTURES" and save it in that folder. In our system when the user clicks on the PICTURES command button, an openfiledialog box is instanstiated with the initial folder name set to \\server\\c:\companyname/PICTURES and the initial filename is set to (in this example) 0700987*.* The openfiledialog object will display (in thumbnail view if desired) only those files in that PICTURES subfolder that match the initialfilename specs. This has proved to be an excellent time saver and with only one sub folder. We also have the same deal for emails, autocad drawings, general correspondence and PDF files all with their own subfoldeer called emails, autocad etc etc. Hope this is helpful to you.

 

[Len Boorman]

Access has many advantages that you may wish to make use of to start with

1) I have had 1.5 Million records in a single table and never got withing tpuching distance of 1Gb, so Access can handle a lot of data when correctlystructured.

2) As a development tool it is probably the best and fastest

3) It is cheap

4) You can do in Access basically whatever you can do in SQL/Oracle in terms of functionality

5) You can upsize to SQL later as required

If you know exactly what you want then maybe SQL is the way to go if you feel it necessary.

Access does have advantages as above when you do not know exactly what you want.

Len

 

[The_Doc_Man]

Access can perhaps handle the data. To me, the question will be defined by network transfer load, not by database size per se.

A VPN is USUALLY implemented via a special router that handles the encryption externally to your server, so there is no load for that part. Machines these days are fast enough that even if your server does its own IPSEC management, it will be able to encrypt and decrypt on-the-fly. But the amount of stuff coming over the wire does not change in size between clear-text networks and virtual private networks. The amount of data in motion is going to make a very big difference.

The issues you need to consider will require a thorough evaluation of the amount of data to be stored in your main (or most commonly used) tables and the complexity of the searches being performed. Whether you are going to fill up the database or not is immaterial. The question is how big will your main table get (the one that you need to query most often) during normal use?

Simple searches involving only the table indexes are ALWAYS faster than searches that require data from the table outside of an index. Here is the key to understanding: Access runs on workstations but the data is stored in a shared folder elsewhere. To query an Access table, you must bring the data to where Access is running (on the workstation), which means bring the WHOLE TABLE* across the net. (* Unless you query something completely contained within the indexes.) See, the server holding shared Access data is acting just a file server. It knows from nothing about queries.

You can grow Access data beyond 2 Gb in several ways. If you can archive that 4 Gb of data or in some other way logically subdivide it into chunks no one of which is over 2 Gb, Access is not out of the question - but the network will still be heavily impacted either way.

An alternative is SQL Server or variants that hold the data on the server AND query it locally... then pass only the result-set to the workstation. This vastly reduces the network load. Also, if you had an ORACLE server that supported SQLnet connections and your terminals have ORACLE ODBC drivers already on them, you have a ready-made path there, too.

The problem you will have is estimating the operational size of the database for the purpose of determining network load. It requires you to design the tables in abstract before you can compute the sizing and network loading requirement. Once you have a guesstimate of THAT size - and an estimate of how often the main tables get touched, you can compute network load. Then, it is merely a matter of the Mb/sec of the network vs. typical transfer sizes to compute whether Access will do the job ON YOUR NETWORK.

If you have a gigabit VPN and no slow routers from point A to point B, you might get by with Access. If you have a dial-up modem that tops out at 56 Kb/sec, maybe Access isn't your solution.

 

[jal]

DocMan, I came across this thread using Search. Here is an article that seems to confirm what you said:

http://searchdatamanagement.techtarget.com/generic/0,295582,sid91_gci1261380,00.html

But I am confused as to what "file server" would mean for Accesss. The writer says there are three choices:
(1) Run Microsoft Access from a file server across a network. Neither the data nor the Access runtime is installed on the workstations.
(2) Install the Access runtime on each workstation. (With the actual data stored as a shared database located on a file server).
(3) Use Windows 2003 Terminal Service as an application server for Access.

Choice 1 doesn't seem logically possible to me. Wikipedia defines a "file server" as a machine which stores shared DOCUMENTS and does NOT provide runtimes. Doesnt' this mean that each user merely has a shortcut link on his Desktop to a shared drive or folder? In which case he can't run the application? For instance if a remote computer has a Word document, a workstation would still need Word installed in order to view and edit the document within the regular Word environment. Same thing with Access, I would imagine.

I'm just trying to understand the different options for installing Access. If anyone can clarify...

 

[Pat Hartman]

Accepted practice is to always split Access applications into a front-end/back-end setup. The front-end database is usually distributed as an .mde and contains all the forms, reports, code, queries, and macros and the back end contains only tables with data. The back-end database resides on a network share and the .mde is distributed to each individual workstation so each user has his own copy. Giving each user a separate copy minimizes the potential exposure to corruption due to problems with one user's computer impacting an open database shared by other users.

If you correctly design the front-end part of the application, you will be able to switch the back-end part to SQL Server, Oracle, DB2, etc if you have to up-size due to too many concurrent users or too much data for a Jet back-end.

 

[Banana]

Jal, I looked at the article and was just as confused as you were. I decided that only way it could be correct is if she was referring to using Citrix, where users have their own local session with a temporary copy of the database itself. However, this isn't 'file server' (and actually more like a terminal server). If she had meant to allow end users manage Access on a server, I would be very very afraid.

I have her book on developing MS Access and while it's a great reference for everything about Access, I've found the chapters on deploying Access with SQL Server to be spotty (e.g. she recommends that all forms be unbound after upsizing, which Pat Hartman will certainly attests to not being the case).


As to OP, believe me, I've been looking and I've yet to find anything as good and easy to use as Access. There's OpenBase and Kexi, but I find them to be too immature and wouldn't even bother suggesting them to anyone for production. Then there's Filemaker, which I find to be very easy and intuitive but very limited in terms of extensibility. As soon as anyone want to introduce complex business logic, FileMaker fizzles. Even worse, you have to pay for plugins to extend the FileMaker. There's also JDeveloper, which GeorgeDWilkinson has praised, but I cannot vouch for it as I've yet to try it. Then there's Visual Studio, which provide lot and lot of power, but also lot of works. Everything that Access did, you have to do on your own.

Furthermore, last project I did was basically an Access front-end referencing .NET libraries and using MySQL as backend. If this doesn't tell you how flexible and extensible Access is and how it can leverage several things, then you're missing out big time.

 

[HiTechCoach]

VPN connections are great for a tunnel for Remote Sessions.


Also see:
Using a wan with ms-access? How fast, how far?

 

[jal]

Accepted practice is to always split Access applications into a front-end/back-end setup. The front-end database is usually distributed as an .mde and contains all the forms, reports, code, queries, and macros and the back end contains only tables with data. The back-end database resides on a network share and the .mde is distributed to each individual workstation so each user has his own copy. Giving each user a separate copy minimizes the potential exposure to corruption due to problems with one user's computer impacting an open database shared by other users.

If you correctly design the front-end part of the application, you will be able to switch the back-end part to SQL Server, Oracle, DB2, etc if you have to up-size due to too many concurrent users or too much data for a Jet back-end.

Thanks. I am beginning to see the possible benefits of this type of approach.

 

[jal]

Jal, I looked at the article and was just as confused as you were. I decided that only way it could be correct is if she was referring to using Citrix, where users have their own local session with a temporary copy of the database itself. However, this isn't 'file server' (and actually more like a terminal server). If she had meant to allow end users manage Access on a server, I would be very very afraid.

I have her book on developing MS Access and while it's a great reference for everything about Access, I've found the chapters on deploying Access with SQL Server to be spotty (e.g. she recommends that all forms be unbound after upsizing, which Pat Hartman will certainly attests to not being the case).

Glad I'm not the only one who found the article unclear.

As to OP, believe me, I've been looking and I've yet to find anything as good and easy to use as Access. There's OpenBase and Kexi, but I find them to be too immature and wouldn't even bother suggesting them to anyone for production. Then there's Filemaker, which I find to be very easy and intuitive but very limited in terms of extensibility. As soon as anyone want to introduce complex business logic, FileMaker fizzles. Even worse, you have to pay for plugins to extend the FileMaker. There's also JDeveloper, which GeorgeDWilkinson has praised, but I cannot vouch for it as I've yet to try it. Then there's Visual Studio, which provide lot and lot of power, but also lot of works. Everything that Access did, you have to do on your own.

You're right that Visual Studio and VS.Net are harder to use than Access. I am guessing, though, that once you've written some reusable .Net code, ease of use eventually becomes similar (assuming you copy and paste your reusable code into each new project). I could be wrong.

Furthermore, last project I did was basically an Access front-end referencing .NET libraries and using MySQL as backend. If this doesn't tell you how flexible and extensible Access is and how it can leverage several things, then you're missing out big time.

Here again, your commentary is more helpful than the article - and much more concise. This is encouraging, as I was wondering about interoperability of Access and .Net.

 

[jal]

VPN connections are great for a tunnel for Remote Sessions.


Also see:
Using a wan with ms-access? How fast, how far?

Very enlightening article.

 

[Lightwave]

Very enlightening article.

Yep very good article.. Best description that I've seen

 

[jal]

You're right that Visual Studio and VS.Net are harder to use than Access. I am guessing, though, that once you've written some reusable .Net code, ease of use eventually becomes similar (assuming you copy and paste your reusable code into each new project). I could be wrong.

Well I gave this some more thought. I guess I was wrong. I was reflecting on the datagridview in .Net. Although I like it because it seems more flexible than Access listboxes and subforms, updating the data to the database usually does take a lot of work (depending on the situation).

Now I'm beginning to suspect that our company could have made better use of Access. (They've been doing everything in Visual Studio for ten years). The startup cost for Visual Studio is probably less, though. Installing Access on 200 computers takes a little cash, even though it would be a worthwhile investment. I don't know if startup cost was a factor in our company's decision.

 

[gemma-the-husky]

one benefit of Access is that it is inexpensive, and also very accessible

it is really easy to amend Access tables - it takes a bit more effort to manage SQL

I would be inclined to use Access, and then reassess it after wards. if there are performance issues, then consider upgrading to SQL - although my understanding is that using SQL does require a further change of mindset to take advantage of things like stored procedures etc, already referred to.

 

[speakers_86]

Thanks Husky. But, this is an old thread, Ive since went on using Access and have most of my db complete. Im now debating posting it in the samples since it doesnt seem to be much use to me, besides implementing it at work.

 

[Simon_MT]

The only other consideration with VPN is speed and the serving site needs descent UP bandwidth, although the TS Session is only transmitting screens and keystrokes I found with images that synchronous connection was required and business contentions 1:20 all round. Depending on the business model you can resolve speeds even further with lower contentions or even bonding SDSL lines together. In essence speed and performance can be resolved with money!

Simon

 

[The_Doc_Man]

Jal

(1) Run Microsoft Access from a file server across a network. Neither the data nor the Access runtime is installed on the workstations.
(2) Install the Access runtime on each workstation. (With the actual data stored as a shared database located on a file server).
(3) Use Windows 2003 Terminal Service as an application server for Access.

In your options, #1 is not possible at all because some of the things that would be required for using Access are in your machine's HKEY_LOCAL_SOFTWARE hive of your registry. So if Access wasn't installed on the machine to update the registry, you can't run it from a remote source. (Unlike some older game programs and some executable self-unzipping files, where such a thing actually IS possible.)

Your #2 is a common way to use Access.

Your #3 works but beware of your end-user license agreement (EULA). You cannot run an off-the-shelf version of Access this way because it violates one of the terms of the license agreement. You CAN get a license to overcome this requirement but probably not from your local software retailer.

But there are variants of #2, such as splitting the database out into two parts, the front end and back end (FE and BE). Put the data in the BE. Put forms, reports, query definitions, macros, modules in the FE. Once you split this database, you put the FE on everyone's workstation. Then when you go to load the form, it is local. But the data for the form is remote. Speeds up the process by a little bit.

The more important part of this is that if you get the FE/BE split working right, then migrating to some OTHER back-end process just got easier. Because you have already isolated the tables from the rest of the application.

The referenced article about using a WAN is not too bad because it really emphasizes the right questions about scale. However, it oversimplifies things sometimes. For instance, if you were a read-only user of a database, I don't think it is quite as likely that losing your connection corrupts the remote copy of the database. (Note I didn't say impossible. We're talking probabilities.)

"File server" for Access only means that the .MDB file is elsewhere than the workstation working on it. Nothing else than that. The previous comments about Access optimizing what parts of the file it downloads happen to be true, which is why Access works at all over a network.

 

[Simon_MT]

On a Terminal Server you can buy CAL Client Access Licence to satisfy the licencing requirements. This are not expensive under £100 for five licences. Even with a TS solution I would still have a front end and back end although the Terminal Server needs to separate from the File Server and if the file server is running Exchange it should not run Outlook as well. So each user profile on the Terminal Server may have Outlook included. The RAM has to be beefed up on the TS accordinging to the number of users the TS needs to be a high specification.

Simon