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.

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.

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.

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.

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

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.

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...

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.

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 (http://en.wikipedia.org/wiki/Citrix_XenApp), 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.

VPN connections are great for a tunnel for Remote Sessions.


Also see:
Using a wan with ms-access? How fast, how far? (http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html)

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, 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 (http://en.wikipedia.org/wiki/Citrix_XenApp), 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.

VPN connections are great for a tunnel for Remote Sessions.


Also see:
Using a wan with ms-access? How fast, how far? (http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html)

Very enlightening article.

Very enlightening article.

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

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.

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.

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.

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

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.

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

jal, as to the Access licensing issue, only developers need licenses for Access. All users can use the Access runtime engine which is free for A2007 and around $600 (only one copy is needed) for A2003 if you can find it.

All users can use the Access runtime engine which is free for A2007 and around $600 (only one copy is needed) for A2003 if you can find it.

Pat,

Could you look at this thread, from post 8 to about post 11

http://www.access-programmers.co.uk/forums/showthread.php?p=786499#post786499

I need to soon go down the track of having runtime and from the above thread I am understanding that I could use A2003 but no w not sure.

Mike, the biggest issue with using the runtime to run your application is that you will really need to get rid of all your macros and convert them to code. Runtime errors that occur in macros or unhandled errors in VBA for that matter will simply close the application. Think of it as the Access version of the blue screen of death. The other issue is that you need to create custom menus. I find these to be a royal pain. There isn't a really good tool to work with them in A2003 and there is no tool whatsoever in A2007. What you might want to do is to develop the app in A2003 and create an .mde but distribute the A2007 runtime. I don't like buying used software, you never know what you are getting and you have no recourse. Download the A2007 runtime it's free - and play with it.

Mike, the biggest issue with using the runtime to run your application is that you will really need to get rid of all your macros and convert them to code. Runtime errors that occur in macros or unhandled errors in VBA for that matter will simply close the application.

I am not selling what I use but make other smaller applications and use code for those, although I have been doing that since you can't hide macros in the MDE. But I will be soon expanding into areas where lost of people will only have MS Office Standard. In fact I have recently being replicating in Excel some of the things I make in Access but there are limitations.


I find these to be a royal pain. There isn't a really good tool to work with them in A2003 and there is no tool whatsoever in A2007.

So what happend when using A2007? I am assuming that the Access toolbar is missing. What I make for people at the moment does use any of the Access toolbar. The DB is started from a shortcut. Although for some people I show them how to use the Access Filter By Selection.

What you might want to do is to develop the app in A2003 and create an .mde but distribute the A2007 runtime. I don't like buying used software, you never know what you are getting and you have no recourse. Download the A2007 runtime it's free - and play with it.

I am not clear here. Are you saying I can download the A2007 runtime and use that to run what I make in A2003.

As to A2003 runtime, it was my understanding from the other poster that it contained runtime and a license was purhcased from Microsoft so as to be able to use it. I am putting of phoning Microsoft Australia until I have as many facts as possible since it is such a painful experience:D

Microsoft does not sell 2003 versions any longer. Your only option would be an after market sale from somplace like ebay and that runs the risk of licensing issues.

I have not tried the 2007 runtime but my client is using A2007 to run an .mde created by A2003 so I'm pretty sure that the runtime will do it also. That would mean that you don't even need A2007 on your development PC. You can work entirely in A2003 and as long as you create menus and/or toolbars, you should be ok with the runtime. You can also test the effect of using the runtime by opening your application with the \runtime switch. This allows the retail Access to simulate the runtime Access.

Also, since you created your app with A2003 and are using custom menus, the ribbon is hidden and only your menu/toolbar show which saves a lot of precious vertical space for those using laptops.

I do have 5 copies of MSOffice 2003 Professional.

I just downloaded the AccessRuntime but can't find it:)

The only thing a search turned up for AccessRuntime.exe was a thing in Windws/Prefetch

But it listed in that section from Control Panel for Unistall etc. 124 mb

I found it:D

It is in MSOffice/Office12

I was looking for RuntimeAccess.exe as that what the download calls the file but it is just MSAccess.exe and with a slightly different looking key for the logo.

Now I just have work out what to do with it:D

I went to an .mdb file and right clicked and then Open With and selected the A2007 key but only a bare screen opened with Powered by Microsoft Access at the bottom right and a couple of things at the top left and only Exit working.

Well Pat I think I hit paydirt:D

I tried my own DB but first compiled (that took some time:D)

The macro that I use to start, opens the forms etc, I changed to Autoexec and presto, all worked including all my Access to Word/Bookmarks.

I will uninstall MS Office 2003 and also convert a couple of the Word.docs that are used for Access to Bookmarks to RTF and see what happens.

With MSOffice unistalled I had to problems

One was "date undefined function" and "broken reference/link to MSWORD.OLB

To get it to open I changed the Autoexec to open just one form. That form has unbound textbox with =Weekday(Date()) and that shows #Name error.

So it look likes even though it is opened with A2007 Runtime it will use Access 2003 if it is there.

Oh well, my social calendar for Christmas/New Year was not too full:D

I reinstalled just Word from MSOffice and all worked perfectly from my own DB including Date().

I suppose MSWORD.OLB refers to references I have ticked.

It would appear that just installing Word also puts some other things in place. When I went to reopen the DB after reinstalling Word it wen through the process of configuring etc the A2000 Runtime.

Would installing only Excel, Word and PowerPoint from MSOffice Professional be the same as having MSOffice Standard.

As a side note, I see what you mean when there is an error.:D Although with a macro it stillbrough up the normal Halt box and with macro name and action.

My own DB all worked OK when Office was completely unistalled one the references were fixed. But of course Access to Word sections don't work.

One interesting little thing I noticed in this A2007 is on a date field a little calenda logo is there to open and intert a date. Also, opening a query with a macro and the query looks more like a tabular form in appearance.

jal, as to the Access licensing issue, only developers need licenses for Access. All users can use the Access runtime engine which is free for A2007 and around $600 (only one copy is needed) for A2003 if you can find it.

Thanks Pat. Honestly your comment further exposes my ignorance of Access deployment. (Partly because the IT dept at my job is striving to prevent me from deploying anything). After reading your post I went to a couple of articles, and now think I am beginning to get acquainted with some of the elements of Access deployment.

Mike,
You need to fix the reference error. You will then be able to open the database.

Mike,
You need to fix the reference error. You will then be able to open the database.

Pat,

I did that and all is well.

I also changed my code for do Access to Word so I don't need the reference to Microsoft Word Library 11

That was a great idea you had of giving the 2007 Runtime a go.

So far there has been only one thing that has not worked in my own data base but I have not spent anytime on it yet as it is not something I am likley to be giving to anyone.

I have just been using an autoexec macro for startup. I like it a lot as it looks nice and with the 2007 queries opened look like a form. I have yet to test in the real world of putting on a computer that has MSOffice Standard and not A2003.

I wonder if there is any way of getting it to disc so someone did not have to down load it. I tried find a file that I assume they use to extract. I assume if I could get to that then I copy "Office12" and then use the file.