Access-Web-Quickbooks (2007)

Topic

New Reply

I am about to start work on a project and although I have a conceptual idea of what I want, I don’t really know how to approach this. I would appreciate your collective wisdom and thoughts please.

MODERATOR – this may belong in the web development forum – if so, please move it. I really think it belongs in both but I elected to start here.

— The Project —

For a business that rents equipment and operators for events, I need to create a database for managing events, inventory, bookings, and operators (who are independent contractors) that interfaces with a website and with Quickbooks (a small business accounting system). This I will do in Access.

My problem is three-fold:

Problem one: There will also be a website (hosted by some other company – not yet chosen) for advertising and also for contractors to login, view upcoming events and indicate a desire to work on a particular event(s). This same website should allow contractors to register for future employment consideration. All the information captured (registration and the desire to work events) needs to find its way back into the Access database previously mentioned. This same website should allow registered contractors to view the past events they have worked and which equipment they operated at that event. This means there must be some tables or other information that comes from the Access database to the website and is viewable by the contractors (upcoming events and past events worked).

The main question from Problem one is how do I manage moving the table data back and forth from the website to Access?

Problem two: Once an event is booked, Access needs to generate and send confirmation emails to all the contractors with email address and produce a list with telephone numbers of those who do not.

The question from Problem two is how do I get Access to generate emails… this may be a ‘non-issue’ as I believe I have read about this before.

Problem three: Access needs to interface with Quickbooks so that event revenue/expense and contractor expense will be captured in Quickbooks for accounting.

The question from Problem three is whether or not this is even possible. I don’t recall hearing of this but I suspect it is possible… just how to do it?

Sorry for so much writing but I am just trying to get my head around this and I may be over complicating things. I expect the web hosting company will provide mySQL or something similar.

Reply | Quote

Replies

1) There are several ways to let the user view and edit data from a database in a web page, for example ASP. See Connecting To An Access Database Using ASP for an introductory example. If you want more info about this subject, you can post a question in the Web Design, Coding and Scripting forum.

2) It is possible to send e-mails from Access, but I don’t think that’ll work in this situation – the database will be stored on a web server, but Access will not be running. No doubt it’s possible to run code behind a web page to generate an e-mail (using data from the database), but again that’s a question for the Web Design, Coding and Scripting forum.

3) There is an ODBC driver for Quickbooks: QODBC (not free). This driver lets you read and write Quickbook data files using code.

Reply | Quote

Thanks for the response. I will have to pass on the Quickbooks – the ODBC driver is much too expensive for a small business.

I have deleted the need for contractors to view future events. Instead, I will use an email form to send a request to be considered for future work. For existing contractors, I would like them to be able view past events worked.

The Access database will actually reside on a local machine, not on a remote web host server as implied in the initial posting. I was thinking the solution might be a simple matter of creating a ‘night job’ to copy a table (of events and which contractor worked them) from the local machine with the Access database to the web server. That way it would be fresh each day.

It seems my question is now only one… what is the best way for the contractors on the web site to look at the records within the table containing their names?

Reply | Quote

The contractors would have to enter their name or ID, and you’d use this in the script behind the web page to generate a SQL statement that selects the relevant records. For details, I refer you to the Web Desing etc. forum.

Reply | Quote