Microsoft Access questions

[Nerf]

I've been asked at work to see if I can put together a service management database so we can actually track things like vehicle (golf cars) history, locations they're assigned to, etc, etc.

Can I do this in access?  I haven't touched the program in probably 6 or 7 years and really don't remember much about it, but was told by one of the vendors for car shop management software that he's had some smaller shops use it with great success.

So umm, Halp?

[NiX]

Access can do this easily. I'm sure there are other people here who could direct you to tutorials or something. All I have are a few school notes scattered here and there.

[Viin]

LAMP (Linux, Apache, MySQL, PHP) is probably the best option, but will take some learning and doesn't have a GUI interface like Access.

Access would work fine if you can put it on a shared drive or only need one computer to run it - and don't get crazy on the amount of data. Access is really a glorified spreadsheet, but works in scenarios where you need to track a limited amount of data and want to build a form to input the data with.

[Cheddar]

I've been asked at work to see if I can put together a service management database so we can actually track things like vehicle (golf cars) history, locations they're assigned to, etc, etc.

Can I do this in access?  I haven't touched the program in probably 6 or 7 years and really don't remember much about it, but was told by one of the vendors for car shop management software that he's had some smaller shops use it with great success.

So umm, Halp?

It will work, just make sure you learn how to repair the database.

[NiX]

It will work, just make sure you learn how to repair the database.

Key words of advice there. People failed our final because they didn't bother to come to the last few classes, one of them being how to repair your database if anything goes wrong. You'd be surprised what can fuck up an access DB.

[Engels]

LAMP (Linux, Apache, MySQL, PHP) is probably the best option, but will take some learning and doesn't have a GUI interface like Access.

As someone who was handed a LAMP project with only the most basic *nix training, I can tell you that this is a trail of tears to follow. You'd have to learn php, sql and fundamental *nix operating commands from scratch, and even with a bunch of help, the whole process is daunting. I 'succeeded', in the end, but if its a mission critical project, you probably want to go with something with a GUI, at the very least.

[Viin]

You'd have to learn php, sql and fundamental *nix operating commands from scratch ..

Shouldn't everyone learn these? 

[Nerf]

They aren't paying me nearly enough to work very hard on this, I found a few templates that come with access that if merged would do what I want, is there any way to just combine them so I can say "here, make it work"

[slog]

Make lots a of backups

[Salamok]

Just get some cheap web hosting and the only parts of lamp you will need to learn for this project are the M & P, if you have any exposure with programming on the most rudimentary level you can google your way through it.  If you have never had any exposure to programming you shouldn't be attempting this at all (access or otherwise).

Shit the fact that you are using MySQL actually makes this easier to learn than if it was done purely in php with flat files.

[Rasix]

I've been asked at work to see if I can put together a service management database so we can actually track things like vehicle (golf cars) history, locations they're assigned to, etc, etc.

Can I do this in access?

I did something pretty similar when I worked in a traffic engineering department keeping track of traffic signals/signs and their maintenance history.  Pretty easy to just treat it as a RDB with a graphical front end.

It's been in the hands of complete morons for almost 5 years now and still works. 

[Tarami]

Relational databases are all glorified spreadsheets. It's what they're modelled from - spreadsheets. Row, column, data types. The rest is just about speed and efficiency of the database engine.

I'm not very familiar with Access, but it can do alot of stuff if you can clench your teeth and put your back to it. Last time I looked it seemed fairly arcane, likely from the lengths MS tend to go to keep things backwards compatible. Knowing a bit of Visual Basic (VBA) helps but isn't strictly neccessary. Knowing SQL helps alot, obviously.

Developer portal:
http://msdn.microsoft.com/en-us/office/aa905409.aspx

You might want to take a look at http://www.filemaker.com/ in case Access doesn't let you do what you want. From the sound of it however, all you really need is one input form for the cars, one for creating car assignments and a report to show the accumulated statistics and Access can do all those easily.

[Yegolev]

I was surprised that one of the first things I did when learning C# was to create a DB and frontend GUI.  The first section of Head First C# walked me through it and it was pretty damn easy.  You will, of course, need to get both Visual Studio C# Express 2008 and MS SQL Server 2005, but those are both free.  And .NET 3.5 as well.  The downside is that you will need to learn some C#, but that's probably better than learning four other things if you are in a hurry or lazy.

Apparently I have Filemaker Pro 5 installed on my laptop.  Hmm.

[Tarami]

I was surprised that one of the first things I did when learning C# was to create a DB and frontend GUI.  The first section of Head First C# walked me through it and it was pretty damn easy.  You will, of course, need to get both Visual Studio C# Express 2008 and MS SQL Server 2005, but those are both free.  And .NET 3.5 as well.  The downside is that you will need to learn some C#, but that's probably better than learning four other things if you are in a hurry or lazy.

Apparently I have Filemaker Pro 5 installed on my laptop.  Hmm.

Personally I think C# in .NET is amazing, it's what I do for a living, after all. It has hit a nice balance between RAD (rapid application development) and raw muscles and it supports so much of the typical Windows needs right out of the box.

It's really beyond the scope of Nerf's needs, but if I would recommend one environment to start in, it would be all-round-M$, with SQL Server Express, VS.NET Express and so on. You can attach Reporting Services (also free) to generate PDF reports and when it comes to online tutorials, documentation and similar, it's abound if you know the Google-Fu.

Plus, with 3.5 you get the built-in O/RM and LINQ2SQL. It's awesome for those quick hacks where you don't have to concern yourself with speed or design standards.

Oh, and C# got XNA - Microsoft's game development framework for Windows/XBox. You won't be creating Tournament 4 in it, but it's a quick way to get introduced to the basic concepts. You'll be drawing (D3D accelerated!) sprites on the screen in 5 lines of code.

All in all, Microsoft offers a VERY solid package with C# and .NET, from intermediate level and up.

</praise>

[Yegolev]

Actually the path I took to get to C# started off with me looking at Torque, which uses XNA.  I was doing fine in the tutorial until it got to the part that said "Now you put your C# code here..." and I was like "Well, shit."  Turns out C# is pretty neat and, dare I say, easy as far as real programming languages go.  If I were going to do this project, and assuming I was constrained to Windows, I'd use C# and MS SQL, possibly with LINQ even.  This is from someone who has a heavy AIX/ksh/perl background.  I'd probably be doing much better with C# if it pertained to my job in any way.

[Lantyssa]

I like using mySQL and PHP.  It's probably not as fancy, but works well enough for my needs.

[Yegolev]

I like using mySQL and PHP.  It's probably not as fancy, but works well enough for my needs.

I did put that "constrained to Windows" bit in there for a reason.

[Lantyssa]

I have it running in Windows on the web server behind me.  It works with IIS just fine.

Of course I use web pages for the interface which may not be ideal for everyone's needs, but it has the side-benefit of making it platfrom independent for the end users.

[Tarami]

C# replaced PHP for me as primary web language, but regardless of how much anyone totes LAMP, .NET or any other development environment as the superior choice, it nearly always comes down to tradition/availability. It also depends on how much you rely on the environment for your daily work - as a fulltime (and overtime ) web developer I enjoy the enterprise scope of Microsoft's products. The costs are not really relevant - licenses are, relatively speaking, cheap. This is of course not the case if the costs really mattered, if I were tinkering with a personal project or got by on donations.

I will admit that MS' comes as a package deal - it doesn't integrate very well with other softwares than their own, which, I think, is unfortunate because I'd like to see more flexibility in .NET as a web platform. There is support for pretty much everything via third-party libraries, but the neatness of .NET goes away and you're really better off doing PHP or JSP as neither is as clunky as .NET is in a non-MS environment. The efficiency of the language is really a minor point if the platform itself doesn't tango well with the rest of the system.

I don't think I had a point, except that most environments are, in the long run, roughly as efficient to code and maintain. It's mostly about flavour and what code paradigms you are comfortable with.

Unless it's Ruby on Rails.

[Yegolev]

It's mostly about flavour and what code paradigms you are comfortable with.

I did put that "constrained to Windows" bit in there for a reason.

[Trippy]

Relational databases are all glorified spreadsheets. It's what they're modelled from - spreadsheets. Row, column, data types. The rest is just about speed and efficiency of the database engine.

No. Relational databases are based on relational algebra. Database tables look like spreadsheets.

[CharlieMopps]

You could write an access database to do what you want in about 30min.

Where access really runs into problems is when you start getting multiple people using the same DB at the same time. It has a nasty habit of locking records as someone edits them... and not only does it lock the record the person is working on, but it also locks several surrounding records. So then you get "Resource locked" and then, often, Access crashes on the user. Basically, if you are using it for 1 person to update records your fine... or even a couple of people, as long as they aren't constantly making changes. But if you write a DB for 10 people in a phone center to track their calls on where each person is making an update every 5 to 10 minutes (like I did) you will run into lots of problems.

If the database is only accessed via 1 computer/person at a time, you will probably never run into a problem.

[Tarami]

Relational databases are all glorified spreadsheets. It's what they're modelled from - spreadsheets. Row, column, data types. The rest is just about speed and efficiency of the database engine.

No. Relational databases are based on relational algebra. Database tables look like spreadsheets.

It was hyperbole. My point was that there's not anything major intrinsical that makes Access different from MySQL or MSSQL or Oracle or Postgre, they're all relational and as such they all use schemas. They're just better or worse implementations of the same concepts. Calling them modelled was perhaps a too hefty stretch, but they're none the less designed to store data of that character.

[Salamok]

Relational databases are all glorified spreadsheets. It's what they're modelled from - spreadsheets. Row, column, data types. The rest is just about speed and efficiency of the database engine.

No. Relational databases are based on relational algebra. Database tables look like spreadsheets.

It was hyperbole. My point was that there's not anything major intrinsical that makes Access different from MySQL or MSSQL or Oracle or Postgre, they're all relational and as such they all use schemas. They're just better or worse implementations of the same concepts. Calling them modelled was perhaps a too hefty stretch, but they're none the less designed to store data of that character.

I get your point but you really shouldn't make statements like "there's not anything major intrinsical that makes Access different from MySQL or MSSQL or Oracle or Postgre" when comparing a desktop database solution to a server database solution.  The main reason why being the CharlieMopps post right before yours.  I suppose if you look at it in a scewed manner the database services handling all the data access for MySQL or MSSQL or Oracle or Postgre could be considered as external from the database but I don't think there are many people out there that think of it that way.

[Nerf]

You could write an access database to do what you want in about 30min.

Where access really runs into problems is when you start getting multiple people using the same DB at the same time. It has a nasty habit of locking records as someone edits them... and not only does it lock the record the person is working on, but it also locks several surrounding records. So then you get "Resource locked" and then, often, Access crashes on the user. Basically, if you are using it for 1 person to update records your fine... or even a couple of people, as long as they aren't constantly making changes. But if you write a DB for 10 people in a phone center to track their calls on where each person is making an update every 5 to 10 minutes (like I did) you will run into lots of problems.

If the database is only accessed via 1 computer/person at a time, you will probably never run into a problem.

30 minutes? Jesus, I couldn't even figure out how to get fucking forms working the other night.

Looks like I might be off the hook though, the service managers computer died, and when he told me it was broken the owner starting yelling "YOU'RE NOT SPENDING ANY OF MY GODDAMN MONEY ON ANY COMPUTERS! THERE WASN'T A COMPUTER BACK THERE FOR 25 GODDAMN YEARS AND IT WORKED JUST FINE!"

So, umm, Hooray!   

[NiX]

Luck is on your side.

[Salamok]

You could write an access database to do what you want in about 30min.

Where access really runs into problems is when you start getting multiple people using the same DB at the same time. It has a nasty habit of locking records as someone edits them... and not only does it lock the record the person is working on, but it also locks several surrounding records. So then you get "Resource locked" and then, often, Access crashes on the user. Basically, if you are using it for 1 person to update records your fine... or even a couple of people, as long as they aren't constantly making changes. But if you write a DB for 10 people in a phone center to track their calls on where each person is making an update every 5 to 10 minutes (like I did) you will run into lots of problems.

If the database is only accessed via 1 computer/person at a time, you will probably never run into a problem.

30 minutes? Jesus, I couldn't even figure out how to get fucking forms working the other night.

Looks like I might be off the hook though, the service managers computer died, and when he told me it was broken the owner starting yelling "YOU'RE NOT SPENDING ANY OF MY GODDAMN MONEY ON ANY COMPUTERS! THERE WASN'T A COMPUTER BACK THERE FOR 25 GODDAMN YEARS AND IT WORKED JUST FINE!"

So, umm, Hooray!   

hmmm, nerf you in Austin?  I know someone out this way that recently purchased a golfcart service company... 

[Yegolev]

"YOU'RE NOT SPENDING ANY OF MY GODDAMN MONEY ON ANY COMPUTERS! THERE WASN'T A COMPUTER BACK THERE FOR 25 GODDAMN YEARS AND IT WORKED JUST FINE!"

Oh, man, that's classic.

[Nerf]

Negative, I'm up in Arlington (Dallas/Fort Worth area), although if he's got some software he wouldn't mind sending over it'd really help me out.  I had a little meeting with the GM and owner's son that basically consisted of them telling me "listen, he's been like this for years, we don't know why he's been so nice to you up until this point, but now you know.  We don't put that shit on the books so he'll stfu about it"

So I might be back on the hook, and really not looking forward to learning Access just to do this.   

[Salamok]

Negative, I'm up in Arlington (Dallas/Fort Worth area), although if he's got some software he wouldn't mind sending over it'd really help me out.  I had a little meeting with the GM and owner's son that basically consisted of them telling me "listen, he's been like this for years, we don't know why he's been so nice to you up until this point, but now you know.  We don't put that shit on the books so he'll stfu about it"

So I might be back on the hook, and really not looking forward to learning Access just to do this.   

I'll ask him but I kind of doubt it as they don't seem to be rolling in hi tech (I think their single comp has win 98 on it still).  Why don't you just lay out a spreadsheet and use that...  Using access is basically just sticking a GUI on a spreadsheet and adding a shit ton of breakable moving parts to the equation.

If it is worth the headache of using Access to create some little app then it is worth doing this right and using a solution that isn't based on a total peice of shit.  If you have a website I bet it even has php/mysql or asp.net on it.  If so then use one of those and not only will you have a solution but it will be multiuser and be available from anywhere there is net access.

Setting up a db in phpmyadmin is easier than setting up a simple excel spreadsheet and once you have that it is only a few lines of code to access it. my typical add/edit record page consists of about 50 lines of php and a few hundred lines of html, if you use dreamweaver to set up your form then most of the html is written for you.  if you find you do have access to a php/mysql solution then i'll be more than happy to post an example of whatever add/edit entry form I happen to be working on atm and explain it to you.

[Nerf]

I don't think our website has been updated in at least a year and a half, only one person in the office could even tell me what the URL is.  We're fucking highspeed.

[Lantyssa]

Setting up a db in phpmyadmin is easier than setting up a simple excel spreadsheet and once you have that it is only a few lines of code to access it. my typical add/edit record page consists of about 50 lines of php and a few hundred lines of html, if you use dreamweaver to set up your form then most of the html is written for you.  if you find you do have access to a php/mysql solution then i'll be more than happy to post an example of whatever add/edit entry form I happen to be working on atm and explain it to you.

If any sensitive data would be used, be wary of external web access.

[Nerf]

No worries on sensitive data, 3 or 4 receptionists ago, one of them set the administrative password on Peachtree, assigned usernames/passwords, and then quit without telling anyone the admin password.  Everyone in the office has the login info to the same full user account, and could go in and view/change whatever they like with absolutely zero way for it to be traced back to them.  Every computer in both buildings (5!) has this access.

I'm just laughing at this point and wondering how long it'll take them to fire me.  The boss doesn't like to be told "No, I can't fix your computer with a gum wrapper, rubber band and 3 toothpicks, you need to buy shit that isn't 5 years old when you get it, just because you got it a few months ago doesn't make it 'new'"

[Yegolev]

Sounds like one of those character-building jobs.

[Salamok]

I'd say put your foot down and explain to them in no uncertain terms that running a business on equipment passed the end of it's lifecycle isn't something you are willing to support but that may get you fired.  If you do manage to fix this PoS with "a gum wrapper, rubber band and 3 toothpicks" and it later breaks it is going to be your fault.

So it sounds like it is time to look for work, it's always easier to find a job when you have a job.