Gowator Posted July 20, 2004 Report Share Posted July 20, 2004 ODBC = open database connectivity. and true to form MS can read it but not be read by it! Its a compatibility layer so mysql can write to a Oracle database etc. You have lots of options, that possibly the problem. For instance a simple php form would probably serve client queries/displays, a more detailed one internal queries but you might need something more detailed for data entry/admin. In oracle terms you use triggers etc. to make a certain event happen if conditions are met. Its just like adding a if... then... else onto a field. You chose where you add these for iunstance to the field itself or to a form for the field only. more later... off to work Quote Link to comment Share on other sites More sharing options...
Gowator Posted July 20, 2004 Report Share Posted July 20, 2004 OK more time now... 175,000 records shouldnt be much of a challenge to a real db like mysql. You might want to see how the database itself will look in terms of drawing up the main tables and what they will hold and look into php and/or PERL:DBD to access. As far as accessing it is concerned what I was saying is if you use linked ODBS tables for Access then the forms can stay the same internally for now letting you work on migrating the data and web interface! You need this odbc connector(s) mysql to connect so access can read/write the tables in mysql.... Its an interim migration step IMHO not suitable for long term use but its a good sideways step away from having the data and forms locked in the same file. Quote Link to comment Share on other sites More sharing options...
kilimanjaro Posted July 20, 2004 Author Report Share Posted July 20, 2004 I'm going to set up a database on my computer and see if I can start transfering data from the work computers. Has anyone had any experience with the OpenOffice database? Thanks for all of your help! Quote Link to comment Share on other sites More sharing options...
Gowator Posted July 21, 2004 Report Share Posted July 21, 2004 OpenOffice as far as i know doesnt have a database it does have connectivity to databases but I think only StarOffice has an actual database integrated. adabas However the choice of database is important to a point. That is 175,000 records is reasonable, perhaps even small for mysql but large for something like Access. Its hard to explain why without lots of DBA tech lingo.. but they are just designed differently. One is the consumer model SUV with road tyres and leather seats and the other is the utilitarian land cruiser/land rover with bare insides but 100% designed as an off road tool! Sure the SUV looks nice, easy to drive and stuff but the minute you actually take it seriously off road you'll wish you had the serious stuff! You should start out by sketching the tables and working out relationships. Like will it be taxonomy based or environment based? Would you present plants by where they grow, soil conditions (or water) flowering, biennials and perennials etc. Will you use the database for clients to order and keep client details related to purchases and even details such as their gardens? How about stock control, will you keep stock numbers int he database etc. All of this will give you an idea of how many tables and how many relationships between them and also how you wanna present this data (php forms etc.) If I were doing this I would probably choose mysql... This is partly bias becuase i know Oracle well and mysql is quite similar but also its more standard so you can use webmin etc. to admin it. Bah, I just found this which says it better than I can! migrating from MS dbs to MySQL Quote Link to comment Share on other sites More sharing options...
Guest grc Posted July 22, 2004 Report Share Posted July 22, 2004 (edited) Based on your specs I'd say Access is a bad choice. It is made to be a simple easy to use database for 1 person on 1 computer. You can push it past that but it gets hairy. Using Access for a web database is like moving your house with a motorcycle, you can get a side car for it but that doesn't make it a moving van. The advantage to access is that it is easy to make a database and make an interface for one person to use while he's at that computer. It is hard to make that database available on the web. That's where a more powerful database system comes in. What you want is an SQL database. What you want is mysql on linux. it's free. As an SQL database there is a learning curve. you need to know SQL to talk ito it. Instead of using the graphical interface to define data fields and display them like with access you need to do it with SQL, HTML and PHP in a text editor and a terminal. Yes, there are graphical programs that work like kind of like Access. The pay off is that once you get the hang of it it is very straight forward and you can make it do ANYTHING you need it to do. Put in on the web, make it a firewall/gateway for your office, have varying levels of security for different users in different locations, etc. Get a copy of "teach yourself SQL in 24hrs", a mysql dummies book, or go through a tutorial on the web. There are probably many examples of something like what you are trying to do already online. free HTML and PHP code, database samples, tutorials, etc. look around. As for the machine, any $200 used PC will do the job. If it is going to sit in the corner and be a server then a pentium2 with 64 MB and a cheap ethernet card is all you need, seriously. There may be one sitting unused in the basement. Spend the money on a big, new, fast, hard drive. With a database that's where all the work happens. You'll need a little more juice for a workstation. If you can't talk them into linux at least talk them out of access - make them go with MS SQL server or Informix or something. Access is OK for simple stuff, for 1 or 2 people and a couple thousand records, anything more and you are in for a headache. Edited July 22, 2004 by grc Quote Link to comment Share on other sites More sharing options...
Gowator Posted July 22, 2004 Report Share Posted July 22, 2004 Based on your specs I'd say Access is a bad choice. It is made to be a simple easy to use database for 1 person on 1 computer. You can push it past that but it gets hairy. Using Access for a web database is like moving your house with a motorcycle, you can get a side car for it but that doesn't make it a moving van. The advantage to access is that it is easy to make a database and make an interface for one person to use while he's at that computer. It is hard to make that database available on the web. That's where a more powerful database system comes in. What you want is an SQL database. What you want is mysql on linux. it's free. As an SQL database there is a learning curve. you need to know SQL to talk ito it. Instead of using the graphical interface to define data fields and display them like with access you need to do it with SQL, HTML and PHP in a text editor and a terminal. Yes, there are graphical programs that work like kind of like Access. The pay off is that once you get the hang of it it is very straight forward and you can make it do ANYTHING you need it to do. Put in on the web, make it a firewall/gateway for your office, have varying levels of security for different users in different locations, etc. Get a copy of "teach yourself SQL in 24hrs", a mysql dummies book, or go through a tutorial on the web. There are probably many examples of something like what you are trying to do already online. free HTML and PHP code, database samples, tutorials, etc. look around. As for the machine, any $200 used PC will do the job. If it is going to sit in the corner and be a server then a pentium2 with 64 MB and a cheap ethernet card is all you need, seriously. There may be one sitting unused in the basement. Spend the money on a big, new, fast, hard drive. With a database that's where all the work happens. You'll need a little more juice for a workstation. If you can't talk them into linux at least talk them out of access - make them go with MS SQL server or Informix or something. Access is OK for simple stuff, for 1 or 2 people and a couple thousand records, anything more and you are in for a headache. <{POST_SNAPBACK}> Agreed 100% plus You can always post up here for help on SQL syntax Indeed you can even just use Access to make a query then look at it in SQL mode. Access uses some non -ANSI SQL (surprise surprise) and the inner joins and things are weird but the best way is a bit of trial and error and post if you get stuck.... also check out the link it shows how to do a conversion and why you should and even lists a few cases where to use Access instead! (mainly single user on a single PC) Quote Link to comment Share on other sites More sharing options...
kilimanjaro Posted July 23, 2004 Author Report Share Posted July 23, 2004 I found something called opengroupware0 and I assumed it was an OO database system. Name: opengroupware-database Version: 1.0-20040204.1mdk Size: 759 KB Medium: contrib Currently installed version: (none) Summary: OpenGroupware.org Database API Description: This package contains the OpenGroupware.org database API. Quote Link to comment Share on other sites More sharing options...
Gowator Posted July 23, 2004 Report Share Posted July 23, 2004 From the top of my head opengroupware is a php front end for collaborative work. when talking about databases its good to define the engine (background programme) and the schema (layout of tables and relationshiops etc.) For Access the engine is called MsJet : others are Oracle and mysql. The problem (or a problem) with access is the fact these are all mixed together in the mdb file. I'd really recommend like grc says getting a basis in SQL and then choose how to access this (php/[perl etc.) It sounds intimidating but its not hard really and i can help ... Quote Link to comment Share on other sites More sharing options...
Guest grc Posted July 23, 2004 Report Share Posted July 23, 2004 I'd agree with that. SQL and mysql does look scary (complicated) at first but it is really not that hard. You'll find the UNIX stuff is very straight forward. None of that "why didn't it do what the instructions said it would do?" stuff you get with MS . And once you set it up right it just goes and goes. The almost complete lack of "the server is down again" headaches is worth the short learning curve. everything you need is here: http://dev.mysql.com/doc/mysql/en/index.html Quote Link to comment Share on other sites More sharing options...
kilimanjaro Posted July 23, 2004 Author Report Share Posted July 23, 2004 I am will ing to give it a shot. I have download mysql , but I am going camping for the weekend, so I'm not going to mess with it until Monday. There are alot of relationships set in access currently, you can search by: species name, genus, family, collector, habitat, county, and about 10 other catagories. Will numerous relationships cause problems in the future with access? Quote Link to comment Share on other sites More sharing options...
Gowator Posted July 23, 2004 Report Share Posted July 23, 2004 I am will ing to give it a shot. I have download mysql , but I am going camping for the weekend, so I'm not going to mess with it until Monday. There are alot of relationships set in access currently, you can search by: species name, genus, family, collector, habitat, county, and about 10 other catagories. Will numerous relationships cause problems in the future with access? <{POST_SNAPBACK}> Try the primer becuase thats not quite right for a relationship :D the question is if they are sort keys I think ... the answer is they dont have to be keyd to sort and for a small database it might not make much speed difference anyway. All that will happen is php/perl will send a query like select species name, genus, family, collector, habitat, county from plant_table where habitat like '%ACID%' and family = 'hedera' and name like 'poision*' order by collector,county decending; you can play about in Access by doing drag n drop queries then select the mysql box! Also dont forget the link i gave for the ODBC driver... it will let you mix n match as your developing ... Im going to Amsterdam this weekend .. I dont think you will want me answering any technical questions! (anna= see you this w'end then) Quote Link to comment Share on other sites More sharing options...
kilimanjaro Posted July 27, 2004 Author Report Share Posted July 27, 2004 I downloaded a book "Learn MySQL in 21 days" I did day 1 this morning. Sometimes I almost want to change my area of study to computers instead of ecology. Quote Link to comment Share on other sites More sharing options...
Gowator Posted July 27, 2004 Report Share Posted July 27, 2004 cool where from I might read it myself! Quote Link to comment Share on other sites More sharing options...
Cannonfodder Posted July 27, 2004 Report Share Posted July 27, 2004 One thing I didn't see from skimming this topic is how does the end user interface with the database? Is it a search system that can be satisified by simple queries? Or is it the type of search that requires the end user to know SQL and be able to create ad-hoc queries? How is data entered? How is it presented? All these questions are just as important as trying to decide which database engine to use. Another thing to keep in mind, is if your department develops this, you can develop it to keep the different layeres separate and non-dependant on each other. Here's an example: If you use access to connect to the server through the background and develop screens and reports, you have developed a database plus an interface through access. Now you want to change your database to a different engine, you are stuck with changing the interface as well. If you separate the interface from the database (SQL is supported by almost all database engines so this is possible), you can develop the interface and have it call the database engine using standard SQL to get yoru results. Additionally, if you were really doing a bigger system, you would have a middle area which could be business logic. When I say business, I mean the logic that handles what is actually happening. So, at work for example: I would develop the interface for a web browser (very little decision making code inside, zero database). This area just calls the business engine (any server or local software can be the business engine). The business engine makes all the decisions. Oh, user clicked on report, well lets gather the info and pass the report data back to the interface. The database just sits there, it holds the data in an organized format. In some cases, for heavy demand systems, it too may have business logic stored within the database (doesn't sound like your requriements) So you have 3 independant systems that can be swapped out. Let's say someone wants to provide a different type of interface with a different business engine but that accesses the same database, can do! Something to stress with the department is that by going linux on the server, and using a web interface, you can support any type of end user computer. You can also provide server software, databases, and any unforseen server tools without spending money. Moneys is always a big point.. Quote Link to comment Share on other sites More sharing options...
Gowator Posted July 28, 2004 Report Share Posted July 28, 2004 MadDog is right and this is something you should address... how will the users retreive the data and will all users retreive the same data...will some 'power' users populate the db and run adhoc queries like for instance do peoples planting habits react to thre economy or weather. i dunno but these are the business queries as opposed to end user web queries and might require a deeper level of query like adhoc SQL.... You mention the existing Access db, what are people using it for, doing with it? just a few points becuase like MadDog says this is as important as how you choose a engine. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.