MS Access

[Gandhi]

Are there any MS Access 2003 gurus out there? I have some database work I could use some advice on.

[JAStewart]

I know it a little, but I do have a textbook here which has MS Access in it. Whats your questions?

[Fjool]

I've used previous versions fairly extensively, but not 2003; might be able to help.

[Gandhi]

Thanks for replying guys,

 

Basically I have two databases, one with product/supplier information and one which is used to track purchase orders. I would like to link the two together and create a module for raising purchase orders (to get away from paper). I don't know if there is anyone up here who specialises in database work. Problem is I'm not even sure if what I am looking to do is possible in access. I would really like to sit down with someone and discuss it, with a view to getting the work done.

[Fjool]

It is certainly possible to use what Access calls 'remote' tables, that is a table from another source (be it a spreadsheet or, in your case, another Access database). Once you've linked the tables, the rest becomes a question of writing the appropriate queries and forms. Certainly possible but...

 

The first problem I suspect that you're going to have is articulating your precise requirements and a face-to-face meeting with a developer is probably a good first step. Explaining exactly what you need is often one of the most difficult aspects of such a project. Only if your requirements are really simple should you contemplate getting someone to do a homer; especially if this is to be a system critical to your business.

 

It sounds as if you're looking for a bespoke system and, frankly, this kind of project can quickly become much more time consuming and costly than you first expect. One alternative might be to investigate off-the-shelf packages and migrating your data.

 

What sort of volume of data are you handling at present?

[Gandhi]

Yeah, I have looked at a variety of off the shelf programs and a couple of Access based ones but none really seem to do what I want. Most are overly complicated or are designed for control of purely stock items rather than the mix of stock and unique orders we process. Also there is an issue in that our goods/suppliers/price database was designed around our requirements and works well, so a seperate program would either have to link into that for supplier info or it would mean having to replicate data. The purchase form I have in mind is fairly simple. I might just have to get the books out again, but it takes a long time working with this stuff when you're not doing it all the time.

[TeeAyBee]

One way of approaching this is to not use the Access client itself and write some web code in ASP using the ADSI interface via ODBC. You should relatively easily be able to span queries across multiple tables and DBs. It works better with SQL Server but the basic principles of ODBC remain. As stated by another poster, this is not just something you can knock up quickly - it is something that might take a bit of investment.

 

Good luck.

[Gandhi]

Been having a good look at this tonight. As I alreay have both the databases on one switchboard from a previous experiment, it is actually quite easy to modify a form in the orders database to take supplier info from the relevant table via a drop box. I have actually managed to get the purchase order form working the way I want it to. Its going to take me a bit of thinking to update the relevant queries and subforms to incorporate the new data (as I say when you're not doing this often you tend to have forgotten how you did it last time!) My main sticking point is now that I have my purchase order form, I have saved it as a report, jigged it around a bit for aesthetics and want a command button on the form which will print the report but only containing the data just input in the form. Back to the books! Is it really sad to get exited about databases?