• Welcome to Overclockers Forums! Join us to reply in threads, receive reduced ads, and to customize your site experience!

MS ACCESS help

Overclockers is supported by our readers. When you click a link to make a purchase, we may earn a commission. Learn More.

Neuromancer

Member
Joined
Oct 11, 2005
Location
Tau'ri
I am building a database with MS access for work.

The information contains, job numbers, addresses etc


The problem is that everyone that searches the data base (with their own access 2k3 or 2k7) tends to save their searches in it so I have to go through and clean it up periodically.

What I would like to do is to create something that will allow people (3-4 tops) to be able to search the database for any one of the columns. and get all results. We generrlly do searches with variables. (IE filter by form street = wash* instead of typing washington.


a) What is the best method for giving searchable access to the database, aside from having hte users run MS access? Is a webpage the only option?

I have been adding lat and long in decimal format so that I can export the mdb to google earth, to make a simple visual reference for nearby jobs.

b) Would building a webpage with the Earth API make do more than show points on the map? IE will it show the data in each record or make them clickable or even grabbing a few together and displaying results?


Lastly, I added another column to the database that includes associated files (mostly scans of surveys or documents that I have digitized or I have found on the current server) I would also like to make these links to networked files.

c) Is there a simple method to generating links to associated files? Or will I have to provide the full network path to each individual file?

I am rewriting the db in access 2k7, however the new server is probably going to be running Linux. My boss (as much as he loves his unix boxes) does not like OOo. Hopefully the compatibility has improved over the last decade :)

The current unfinished DB has over 18K records, but for now I am only interested in handling this millenium. (The current hard copies go back to hte mid 70s)


Example of the info

Work ORder number, house address, Street, block, lot, city (abbr), where pysical file is located, jobs performed, important notes, lat, long, associated digital files.

I toyed around with adding customer names, but it is something that changes too often to be worthwhile. It would be nice, but not necessary.


I am at a bit of a loss googling this information for myself, as my keywords are lacking since I never used access before. In my readings I did find out about google earth integration which I think is great. However dialing down my searches has been a bit difficult. So even if you do not know hte answers specifically, pointing me to a good resource or even helping out with keywords would be great!

Thanks In Advance :)
 
We have similar at work here.
We separated the Database and the Interface (Two .mdb files) and give everyone access to the Interface file then develop that to be able to return search results.

This means the database remains unchanged by grubby hands but users can still have their own reports/output as required.

Check this out - Microsoft Website
 
Last edited:
The other way around this - which would be very easy on a *nix system is to install LAMP (Linux, Apache, MySQL, PHP/Perl/Python) - quick and easy with a day or two learning a web language of choice.

(I'd suggest PHP in this situation - easy, well documented, loads of examples and a great PDO library to ensure data is cleared of any nasties)

Then a simple DNS entry to this server spits out a website - I do heaps of these at work as they're quick and easy.
 
I like the second option better I think. Seems to be more along the lines of what I want.

Idiot proof is the way to go, I know that means more work on my end but ultimately, I want to put the resources on a laptop for field work.

I can already read HTML and PHP, css is a little tougher since it uses so many call backs, but still understandable. I can see when things are wrong, but do not necessarily know how to fix them (Learned to program back in the days of BASIC, never evolved much past it, all the languages are the same, just the syntax changes).

So generating script on my own is tougher as I have to google each line for proper syntax.

Having an intranet webpage does seem like the best way to go, do you do your code from a text editor or do you have a WYSIWYG editor you recommend?

I know the problem with WYSIWYG is bloated code, but might be easier for something that looks nice for the nubs.
 
Re: Portability - This means the site either needs to be public facing or some kind of VPN setup. If you can't get these you'll be best off with MS Access.

Myself - I use Sublime 2 and write the code manually - for a small project like this you're really only talking a simple table looping through data and maybe a form - use raw HTML (Skip CSS - CSS is mostly about making it pretty) and go from there.
 
For portability it would just be me and the database most likely. As a backup situation for when markers/reference points have been moved/destroyed.
 
In that case - go with the link from the Microsoft Website above.
It shows how to separate the data and interface layers so all you need to do is create reports that will access the data and return the results.

You can put in forms to add / edit data as well and even secure them with username/passwords.
 
As an exercise of personal growth, I'd go the MySQL route. The skills you learn in Access aren't really transferable to any other RDBMS (unless you're actually writing the SQL, then it translates decently to MS SQL Server). SQL is an extremely powerful tool, and it's very easy to master the basics.

The benefits in this situation are far to many to continue to use a MS product, IMO. Exposure/experience installing, configuring, and maintaining software on Linux is invaluable. Then you get the added benefit of learning to write some rudimentary SQL, then you get the benefit of implementing some sort of UI to access your database.. win win win!

If none of these things interest you (or if they intimidate you) then it might be best to stick with whatever solution was linked above. It's not going to be as easy or user friendly (or timely) as Access, but it'll be much more gratifying!
 
Time is a key factor.

It takes me about 20 hours to enter each year of data and the other employees are basically useless. I get nothing done while in hte office because they are constantly asking me to do their jobs for them.

I just want a quick and easy UI that lets them search the DB, and jobs displayed on Google Maps/ Earth.

I do agree that programming in SQL would be beneficial if I intended to go that route professionally, but software is my least favorite aspect of computers. I only want to know enough code to get by :)

I do intend to continue my education this year though once I get some free time, I would like at least a BS degree, but will probably go civil engineering /Land surveyor concentration.
 
Well if time is a key factor, sticking with Access is probably the only reasonable option. Keep the other options on the backburner until you get some downtime and feel up to taking on the task. Coding is fun! If you're going to a linux server, you'll have to move off of Access at some point anyway.
 
My reply to this is probably too late as your last response was about two weeks ago. However, for anything spatial, I use Postgres where possible. It's a better database than MySQL anyway, but it has spatial data types so if someone wanted to search for entries within 1km of a particular longitude-latitude, you can. Of course you can write your own code for this, but only some people have the maths for that and even when you've written it, on large recordsets it will still take substantially longer in performance terms than Postgres' compiled and in-built version. So when Geography forms part of my datasets, I really push for Postgres.

It is harder to learn than MySQL though. Fewer shortcuts and assumptions built into it. And compared to Access, even MySQL is harder to learn. The chief issue being that MySQL and Postgres are pure databases. Access is actually a full on GUI and bunch of interface hooks wrapped around a database. If you picture writing all of your Access work just in the SQL editor of sub-component of Access (including creating all your tables that way), then that's MySQL or Postgres. You have to build the whole GUI side yourself.

It's very worthwhile learning a real database system. But from the sounds of the situation you are in, trying to do that on the job right now would be a mistake. A system like you're talking of, is actually a not insignificant project. I have fifteen years experience in software development. Trust me. Do NOT be afraid to sit there for an entire afternoon with a pile of paper and a pencil and sketch out your plans. It might be the only way you make it through this. If your boss gives you trouble because you're not "typing", ask her if she'd be happy to see builders start making an office block without having any blueprints. It's the best analogy.

Much of this advice might be too late, but one thing isn't. If you keep having to do other people's jobs for them (and I've been there so I know of what I speak) - TRACK THAT TIME. Seriously, put a little bit of paper by your PC with everybody's name on it, and create a little tally chart for every half hour you spend helping someone. Or every interruption. Do this EVERY DAY. At some glorious point, you will be in a meeting where you're challenged about your performance, or you're in your annual review, or someone asks why you lost your temper when someone asked you to fix their paragraph in Word, or negotiating for a pay rise. And then you will be able to turn around and say: "40% of the time I'm supposed to be working on your database, I'm doing other people's jobs for them". Or "Tony takes up 80% of my support time out of thirty people - for the sake of **** send him on a remedial IT training course".

I'm not kidding. I cannot emphasize this enough. I would happily tell you to do this one-hundred times and murder your parents if I thought it would motivate you to keep this list and tally chart by your side. DO IT. It is one of the best things you can do for yourself right now and it's really simple. Darren: III, Sally: I, Tony IIIII IIIII II

That information and way to actually show how much you are doing other people's work is hugely valuable to you and carries a thousand times more weight when you want more money than "I'm always helping out other people". It's also vital when you're explaining why the project is overdue. Management values only that which it can see.
 
Last edited:
My reply to this is probably too late as your last response was about two weeks ago. However, for anything spatial, I use Postgres where possible. It's a better database than MySQL anyway, but it has spatial data types so if someone wanted to search for entries within 1km of a particular longitude-latitude, you can. Of course you can write your own code for this, but only some people have the maths for that and even when you've written it, on large recordsets it will still take substantially longer in performance terms than Postgres' compiled and in-built version. So when Geography forms part of my datasets, I really push for Postgres.

Never too late. Learning is an ongoing process. This is a surveying company. I love the work, so tedious with lots of research and exactness is so important. But with the artistic flexibility, because, it is not about about accurately representing things, but accurately representing what the legal documents say.


It is harder to learn than MySQL though..[snip].

Yeah MS is famous for clicking a button to do a whole bunch of commands. I believe it is called a program, tie a bunch of them together like. something simple say MS notepad, and you have an application. Tie that into another set of applications and you have a full suite (M%$ Word to your mothah).But an app? Whoa, no PCs cant handle crappy little flash games. You gotta have a smart phone for that. I hate smart phone users... no offense to our ocf family.. but comeon... your family and friends annoy you too...


If you like control you give up simplicity, and you get MS server. 3 days, creating IIS websites, falsifying CALs, and creating my own SSL certificates to get Remote desktop to work over WAN.

Or 30 seconds installing any-VNC "app"

Guess which way I chose to go? Give you a hint. going on 4 days.

very worthwhile learning a real database system. But from the sounds of the situation you are in, trying to do that on the job right now would be a mistake. A system like you're talking of, is actually a not insignificant project. I have fifteen years experience in software development. Trust me. Do NOT be afraid to sit there for an entire afternoon with a pile of paper and a pencil and sketch out your plans. It might be the only way you make it through this. If your boss gives you trouble because you're not "typing", ask her if she'd be happy to see builders start making an office block without having any blueprints. It's the best analogy.

I call myself a janitor (in relation to computers). Yes I have 35 years experience with them and only know software as far as how to make the hardware do what I want. But I prefer the hardware side. Mechanic is a bit to important, its like engineer which I have never spent the 80-100 grand to get one of those degrees, I am a hobbyist at best. Which is why my reviews focused on them. (Why when a speed control chip was used on a motherboard was only rated down to above freezing temps I pointed it out,,, looking at you gigabyte...not all their boards just their post-OC raised prices success midrange stuff. Cuz most people I know use LN2 to overclock...


Much of this advice might be too late, but one thing isn't. *snip ...

Yup, thanks again :)

Sorry for the TL;DR post
 
Last edited:
Back