Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm writing an inventory/payroll system, and I'm storing all the records on a remote server running SQL Server 2005 on Windows Server 2008 R2. I was wondering how I could start writing forms(very basic) in MS-ACCESS 2010 to upload things to the database. I just want to test it out to make sure that once I implement the entire database, things will work smoothly and without error. I want to figure out how to just make a single form that will be connected to the database, right now the form will just be a simple single text input with a submit button, that will be uploading barcodes(strings not images).

If I need to clarify anything, please just ask.

Thanks in advance for any help

share|improve this question

closed as off topic by jcolebrand May 2 '12 at 13:57

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

This isn't really on topic here, you might have better luck on Programmers but truth be told, if you're going to all the work of using Access to talk to a SQL Server2005 instance, I would ask why you don't just use VB.NET and Microsoft Web Pages (aka Razor and MVC3/4) and some of the dedicated tools they have there to make your life easier. Building an entire CRUD app in Access means everyone will have to use Access, which will end up using a lot of license fees, for not very much benefit. Web apps can be intranet only you know. – jcolebrand May 2 '12 at 13:57
Sadly the answer to that is often 'Access is all I have'. The killer app of tools like MS Access is that you can deploy an application to a small user base without having to jump through hoops. – ConcernedOfTunbridgeWells May 2 '12 at 17:15
I have to use access because my employer uses it for a bunch of stuff like quickbooks(I'm not even really sure what he uses it for). The forms I'm making are going to be dead simple, and a person won't even be using them. A guy will scan barcodes, and my form will update the database each time(automatically). There's going to be a total of about 5 clients. – Scriptonaut May 2 '12 at 17:25

Although Access has data bound controls, by and large you don't really want to use them for any sort of non-trivial application - partiuclarly if you want to do significant data validation before you save records.

You can use ADO through VBA on Access, much as the same as through VB. The code to populate a form from a record set is pretty straightforward, and Access comes with a usable set of controls that can be used on a form.

If you're using ADO, the life cycle is that you read the record by issuing a query to the database. Then you can populate the form from the recordset - this is straightforward to do with VBA. The 'save' button onclick method calls a validate and save procedure. You can add whatever other UI code you need to populate dropdowns, enable/disable controls etc.

You may also want to separate data access into a separate class, depending on the complexity of the application. This lets you have a M:M relationship between formns and data items with the data access modularised into one place (i.e. one form can gather data from two or more queries by using the appropriate modules).

Beyond this we're getting into the realms of application architecture, which is probably a bigger scope than you had in mind for this question. There are plenty of books on the subject, including ones specifically about application development with MS-Access.

share|improve this answer
Hmm, I'm not exactly sure if I'll be doing a lot of data validation before saving records. Most of the system I'm building will be an inventory system. A pallet of some product will be scanned into a certain sector of the factory, and I'll just need to make sure that it is deleted from any other sectors. People will be uploading things at the same time from about 4 different stations, so whatever I use needs to be good at handling multiple insertions. There'll be a payroll system as well, with about 50 employees I think. Thanks for the link, I'll check it out. – Scriptonaut May 2 '12 at 17:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.