Database developers?

[jonathanc]

Hi,

 

Just wondering if anyone he is (or know a) database developer or perhaps a good forum which I can go to to search more information about database developement?

 

Currently thinking about developing a client/server type database which Access (.accdb) would be used as front end and maaaaybe SQL or Oracle as master database but I am also considering a .mdb Access master.

 

Any pointers welcomed! Cheers!

[creative]

i can ask...

 

bro in law is an oracle databaser and also has a hand in sql...

[jonathanc]

i can ask...

 

bro in law is an oracle databaser and also has a hand in sql...

 

Cheers mate Fairly new so would like to find out more from the experts

[PJ]

Any reason for wanting to use Access as a front end tool? I'd use visual studio or similar to knock up a front end App and you can sit that on most databases, you can download a free trial version and have a play with it.

 

What sort of thing are you after doing?

[jonathanc]

It would be a fairly bespoke solution but basically I have to implement a more efficient MIS and deploy it across varies busineses which are somehow linked. Most of the database now is somewhat managed in Excel so I am hoping to further streamline things from here hence I think Access would be the next logical step.

[JustGav]

I've seen way too many small systems that start off on access, then get adopted as a corporate tool and grow, and access then just poses more of a problem. I'd personally suggest using MySQL with on linux or windows as it will allow a bit of growth. Don't bother with Oracle unless you have a DAMN good reason as it is pricey and probably WAY over engineered for what you want to do with it.

[PJ]

I've seen way too many small systems that start off on access, then get adopted as a corporate tool and grow, and access then just poses more of a problem. I'd personally suggest using MySQL with on linux or windows as it will allow a bit of growth. Don't bother with Oracle unless you have a DAMN good reason as it is pricey and probably WAY over engineered for what you want to do with it.

 

Totally agree with that, as an ex-Oracle developer I hate Access with a passion! Linux & MySQL is definately a better bet.

[Attero]

MySQL for the win. If anyone disagrees... Facebook runs on MySQL. Can't argue with that really, can you?

 

Many large organisations use Oracle.

[Angarak]

How many people will you expect to use this?

 

MS Access has a very low limit to the number of concurrent connections it can handle, as suggested the likes of MySQL would be better to future-proof the scalability of it, or you could use Microsoft SQL Server Express, a free and scaled down version of SQL Server (should you be limited to running on a Windows OS - though you can get MySQL running on Windows also).

[jonathanc]

I will only use Access as front end and will plan to administor the database using MySQL. So basically I plan to design the forms, reports etc using Access but the master database itself would be run on MySQL. However still in planning phase now so anything's game The database server would run on a virtual machine tho... so would need something efficient.

 

Thanks for all the advice chaps! Keep em coming

[Attero]

You could use MySQL Administator to do all your dirty work.

[JustGav]

How confident are you with coding? A guy at work has written a change control system for me as well as a mini intranet using something called JQuery and JGrid I believe, I can find out more details for you. Runs on a linux mysql backend. He is a PM and not a coder and he has done some really clever stuff so it might not be too difficult either.

[jonathanc]

You could use MySQL Administator to do all your dirty work.

 

lol more info please? I've quickly had a look at MySQL, looks pretty nice. Not sure to go with Enterprise or Cluster tho... (if that's even needed) Thing is to really harness power of MySQL I would either need a good server hardware to host it on or maybe consider virtualisation?

[jonathanc]

How confident are you with coding? A guy at work has written a change control system for me as well as a mini intranet using something called JQuery and JGrid I believe, I can find out more details for you. Runs on a linux mysql backend. He is a PM and not a coder and he has done some really clever stuff so it might not be too difficult either.

 

I know C/C++ but not an expert at it really. I can write some simple games in OpenGL or maaaybe DirectX (hate it with passion ) Be cool if you can get some information mate. Just running through some ideas to the boss at the moment, showing him what's possible etc.

 

Thing is they have a mixture of different stuff they are using now but mainly in MS Excel or Access so hoping to develop from there. It's a fairly big company with multiple divisions but I am not sure if they wouold need MySQL or other solutions atm. I would estimate around 30 people max logging on the system at same time. They might have around 120ish people who would need access to database on day-to-day basis. Records wise it varies. I haven't had a good look but I think I can limit them to less than 100k records per database or something well within the size restriction of Access database.

 

Thanks for all the suggestions tho Keep em coming guys hehe

[stevie_b]

I also echo what others have said about the limitations of an MS Access database, although you said you wouldn't be using Access for the actual back-end storage (a good thing generally).

 

The typical usage data is helpful, and it sounds like MySQL or SQL Server Express would be good choices for the back-end. Additional questions to ask are:

1) Who's going to be maintaining it?

2) Are the users on the same site geographically? On the same intranet?

3) What IT skills do the people have who are likely to be maintaining it?

 

Bear in mind that writing a nice easy-to-use GUI can take at least as much time as developing the back-end tables.

[jonathanc]

I also echo what others have said about the limitations of an MS Access database, although you said you wouldn't be using Access for the actual back-end storage (a good thing generally).

 

The typical usage data is helpful, and it sounds like MySQL or SQL Server Express would be good choices for the back-end. Additional questions to ask are:

1) Who's going to be maintaining it?

2) Are the users on the same site geographically? On the same intranet?

3) What IT skills do the people have who are likely to be maintaining it?

 

Bear in mind that writing a nice easy-to-use GUI can take at least as much time as developing the back-end tables.

 

I might think about SQL Server Express as that's free I will be maintaining the relevant databases and develop further if needed. Users same site just few minutes away Server will physically just sit feets away from me lol. IT skills, well this varies. Most of them are ok with data entry site. However, the managers and directors wants something that can generate reports they want but that as you know is easy to achieve in Access.

 

I think I would be tasked to develop something on a small scale first before moving to heavier stuff. They are thinking of buying a custom software to handling big chunk of costing, estimating etc but being a highly bespoke business they need several other "supporting" databases too.

 

I *think* for now I would be basically creating and possibly linking databases in .mdb format. I will split each database so the back end stays on server and the front end (forms,reports,queries etc) is distributed. Not too sure yet I am just asked to give a detailed analysis and report.

[stevie_b]

I might think about SQL Server Express as that's free

As is MySQL (possibly not the most heavyweight versions, but they would be overkill for you I think)

 

I will be maintaining the relevant databases and develop further if needed. Users same site just few minutes away Server will physically just sit feets away from me lol. IT skills, well this varies. Most of them are ok with data entry site. However, the managers and directors wants something that can generate reports they want but that as you know is easy to achieve in Access.

 

I think I would be tasked to develop something on a small scale first before moving to heavier stuff. They are thinking of buying a custom software to handling big chunk of costing, estimating etc but being a highly bespoke business they need several other "supporting" databases too.

 

I *think* for now I would be basically creating and possibly linking databases in .mdb format. I will split each database so the back end stays on server and the front end (forms,reports,queries etc) is distributed. Not too sure yet I am just asked to give a detailed analysis and report.

 

Access is hard to beat for quick front-end development on Windows.

[jonathanc]

As is MySQL (possibly not the most heavyweight versions, but they would be overkill for you I think)

 

 

 

Access is hard to beat for quick front-end development on Windows.

 

Yeah, I've noticed the free versions of MySQL but are they comparable in performance or feature set to the inbuilt Access 2010 database? I am still thinking if replication is needed here as well... (only .mdb databases support replication not the newer accdb files). Wonder if it is possible to split a database as easily then if I plan to use the older .mdb format... questions questions

 

I guess I should probably download the free version of MySQL and have a play with it to see how easy would it be to setup. No prior experience in MySQL but I have some programming background so hopefully it wont be too hard.

[johnny g]

mySQL everyday. It's part of the very common LAMP architecture, so you'll be able to host it almost anywhere as well.

 

Linux - win

Apache - win

mySQL - win

PHP - win

 

If you're looking for a MIS tool, you'll want something either front-end driven, in which case, I can see the pro for Access, but Visual Studio will be better, but personally, I'd go with a PHP driven web app on LAMP. You'll get your coder easier

[stevie_b]

I agree with johnny g that MySQL is a strong contender. My knowledge of Access is a few years out of date now but AFAIK MySQL scales up better, runs more efficiently, and even the free ones are more powerful and robust than Access.

 

Access' strengths lie in being an easy to use, easy to install RDBMS for very low, localised use: basically on someone's desktop as a local database for their own personal use. MS used to claim that the number of concurrent users that Access could support was something like 255. In reality it tended to struggle beyond 10 concurrent users (for older versions of Access, not sure if they've imporived it dramatically since then). All other RDBMSs are bound to be a bit trickier to install, and may have less intuitive development interfaces but once you overcome that, they will give you a more robust database.

 

If you're interested in using MySQL and you're on Windows, I'd suggest SQLYog (free) or Navicat (Lite=free, Premium= not free) as development interfaces. MySQL Workbench contains some useful tools during the design phase. I've not used MySQL Administrator much: I couldn't get on with it.

[johnny g]

phpMyAdmin can be ok for decent mySQL admin

[stevie_b]

LAMP is a good path to follow for web-delivered applications, but of course coding something to run on a browser imposes lots of limitations about what can and can't be done. This is another example of why the OP needs to clearly specify what he wants his new app to be capable of and how it's likely to be used. It's why I asked the questions about who is likely to use it and where they are based.

[JustGav]

JQuery -> http://jquery.com/

 

and matched with JQGrid apparently

[jonathanc]

wow, thanks for all the input. So much to absorb. I've stumbled upon another gem called MS SQL Server 2008 R2 (Express for the free version). Seems like a simple enough database system.

 

I did have a quick look at the tutorials for MS SQL Server 2008 and I am taking in the coding ok but does seem like a lot of manual work bit of learning curve there.

 

I have one question here : Can I create everything I want in MS Access 2010 and then import the database itself in MS Server 2008 while maintaining the use of Access as front end? (forms, reports, all the GUI stuff).

 

I am just looking for something quick and easy to deploy. If I can develop in Access and then import the database straight into SQL Server 08 with minimal problems then it would be great! I can pick up administration procedures on the SQL Server side fairly quickly its just building the database itself in SQL Server would take some time for me.

 

I do realise MySQL and other applications are popular but the pressure is on me to produce something visible rather than just reports So fingers cross I can rely on MS products this time!

 

edit: When I say Database it is strictly in terms of Excel spreadsheets. I don't think it would be used in more advanced areas such as web development. Just need something efficient to store and manage massive bundle of Excel spreadsheets if you like. The company is growing and is finding it more inefficient to just rely purely on spreadsheets. Do you guys reckon SQL Server is overkill or good platform to start on?

[stevie_b]

I have one question here : Can I create everything I want in MS Access 2010 and then import the database itself in MS Server 2008 while maintaining the use of Access as front end? (forms, reports, all the GUI stuff).

I'm pretty sure you can. IIRC Access has an "Export to SQL Server" type of functionality. Or it might be SQL Server that could import from Access. Either way, I did that sort of thing in the early 2000s so I'd be surprised if it wasn't straightforward using later versions.

 

edit: When I say Database it is strictly in terms of Excel spreadsheets. I don't think it would be used in more advanced areas such as web development. Just need something efficient to store and manage massive bundle of Excel spreadsheets if you like. The company is growing and is finding it more inefficient to just rely purely on spreadsheets. Do you guys reckon SQL Server is overkill or good platform to start on?

Web development isn't necessarily more advanced: in some ways it's less advanced than conventional applications like a C++/C#/Java program. Web development gives you the ability to easily roll out the software (including updates/bugfixes) to users all over the world. If a user anywhere in the world has a PC with a connection to your webserver, chances are they'll be able to start using your application with little or no faffing about. It's a double-edged sword though. You'd have to protect against prying eyes, and you don't have full control over exactly how your application will look to the users (you'd be at the mercy of the browser writers: Mozilla, MS, Apple, Opera, etc).

 

From what you've said, I think a SQL Server backend with an Access frontend would be a good first stab. Just bear in mind that the frontend (the bit that you're most likely to need to upgrade if the application takes off and grows) isn't easily portable, and would need to be re-written if Access was deemed not to be suitable anymore (main reasons: used by geographically diverse users; used by staff in the field; users don't have MS Access and can't/won't install it). As Gav said, lots of applications start off as Access databases, and stay that way because they're too cumbersome to port to something more suitable.

 

The Reporting features in MS Access were traditionally a weak point. Maybe they've been improved now, but it's worth checking that MS Access reports give you sufficient functionality to display what you want.