I am trying to figure what the best path is for my business. I would say that I am an intermediate Filemaker programmer. I dont really have too much experience in anything else. I have an office that uses access and my store I programmed a filemaker point of sale system for.

I want to have the store and office synced together. right now they both have different databases with the same info. If i want to update a product it has to be done in both databases. I would like to get some input on a solution.

maybe i could have the store query the office for all info and the store just displays the info, any new records or modifications are stored at the office.... I dont know. Any ideas?

Also, can you make forms with SQL? I was thinking, since the office uses access, I could make forms that grab their info via, sql commands, from the office.

OR, maybe the store has its own database and both the office and store update their info with the others info every half hour or something....

There are a practical infinity of ways to deal with this kind of problem. You need to pick one that looks good and run with it because you could spend eternity trying to find the "perfect" answer, and about an hour after you make a choice a better solution will be introduced!

The obvious solution to me would be to set up one database server and have both Filemaker and MS-Access manipulate the data from that server. That way everyone is working with the same data, with minimal overall effort.

I'd suggest that you avoid replicated solutions (where every x minutes you copy data from place to place) for this kind of problem. First of all, you end up with serious business problems when you aren't sure if the data is consistent. You also run into ugly technical issues resolving problems when there are conflicts such as when a customer buys something, trundles over to the office to "pay up in full" and gets there before the data on their purchase does!

There are about a zillion ways to solve this kind of problem, and the net result will depend on many factors. We can offer advice, but you really are the one that will have to make the decision about what solution works the best for you.

-PatP

For me, it will either be MS SQL Server or Oracle. I've spoken once to a fellow colleague, he told me that any RDBMS software would do. It is the design (software and database) that weights the most

The selling point of the two RDBMS above is that they either guarantee performance or recovery which is unlikely to happen in Access

I guess my confusion came with SQL (Standard Query Language). I didnt know if it actually stored the information somewhere.
I guess I would use SQL Server to store and manipulate the info on the server and I would use SQL on the remote systems to query it but how would I display the retrieved info on the remote computers? does SQL server have forms that I can make as well?

you use access.. that can talk to SQL server. and for that matter any other Server based db engine.

I don't know filemaker.. but i wouldn't be surprised if that was using SQL to retrieve data, heck it may even support external server databases (id be surprised if it didn't)

If you are on a budget then there are several options (MS SQl Server, DB2, Oracle & and the like wouldn't be high on my list), MySQL, Postgres would be, failing that JET (what people tend to call Access), SQL Lite and possibly Filemaker. Of the various options Postgres & MysQL stand out so far above the file based systems of JET/FileMaker or SQL LIte.

JET is a fine solution for small volumes.. it rapidly runs out of grunt around 10..15 users

In my view Access is a pretty good db front end.. it ain't perfect (what is), it has lots of holes and lots of problems..... however you get a very capable front end development environment that can deliver relatively complex applications without some of the learning curve associated with VB, VC++, C++, Java or whatever is the preferred programming environment of the day/week/month. It has real benefits in the reporting section (yes if you want to layout your reports your way and that isnt the same as the Access way then Access reporting can be a pain.. if not impossible.

I would also recommend PostgreSQL as the server. It scales very well, offers very good performance, has offered a TON of features for a long time, and is 100 percent free of charge (it's truly open source.)

If the anticipated database size won't exceed 2 GB, MSDE should work well for you also. It is also free, and is 100 percent compatible with SQL Server if/when you need to scale up.

Note that neither of these database offer any sort of performance guarantee. They're free, after all. However, if you are currently using Access/Filemaker, You wouldn't need to worry about this. Even MSDE (which is SQL Server, throttled to approximate Access' multi-user performance) is a step up, bercause it won't get corrupted the way Access can with multi-user apps. And, it offers true database server features like stored procedures, triggers, and real security.

You would install the database server on a dedicated server. (the dedicated part isn't required, but it makes sense to do so.) The data is stored to locations defined by the server (it can be on server local drives, network drives, or a combination. Typically, on SCSI/SATA drives with RAID.

SQL commands are sent to the server, and the database service intrepets the SQL to store/edit/retrieve the data in the locations defined by the database configuration. How you actually access the database can cover a wide range. As you mention, Access as a front end, with linked tables and passthrough queries. (if you don't use passthrough queries, there's a lot of network traffic involved, as query processing is handled on the Access side.)

Or, you could use a programming language (VB/VB.Net/C#/C++) to write your apps specifically matched to your requirements. More work, but, you have full control of everything on the front end.

Or, you could use a web server, and build an ASP/PHP middle tier and use web browsers to access the data.

wow, lots of great info. It helped very much! I guess I am going to research PostgreSQL first then look into a front end. Thanks again guys!

PostgreSQL, 2nd edition, (2006) by Douglas & Douglas is a good reference.

Download the latest version of pg & install it on your desktop. There's no better way of learning how to use it...

Also, take a look at the Access - pg conversion documentation here.