Disconnect direct connectiont to table with switchboard

[Voltron]

Hi. I am new to "in-depth" Access programming and structure creation. I have done some programming already and have made switchboards before, but nothing super complex by any means.

I am not sure this question goes here, but it looked like the best fit to me.

I am making a switchboard in Microsoft Access 2000. This switchboard, to begin with, is going to connect to four (4) tables that can be updated/editted and added. My superiors would like me to make it so that the data entry is not directly connected to the table itself. I have never done this before and am not even sure as to where I should begin.

Can anyone give me some guideance on this issue? Do I need to program it with VBA code? Is it a setting or...?

 

[RuralGuy]

My superiors would like me to make it so that the data entry is not directly connected to the table itself.

You may wish for additional clarification as to what is desired here. Are they just talking about using queries or something more isolated and complex?

 

[Voltron]

Sorry about that.

I am not extremely sure myself because I did not realize that it might be possible to not directly enter data into a table.

I was given very little direction at the beginning of the project. I was told to make it possible to use forms for data entry for the four (4) tables that I am working with. I completed this without a switchboard and now they would like me to redo my work and use a switchboard for the menu options for getting to the tables.

With the forms that I created everything was directly connected to a field in the table that the form was for. I had a submit button which, upon clicking, would send the data to the table, whereupon it was stored.

My superiors would like me to make it so that the information is not directly entered into the table upon hitting the submit button. They would like me to create something that would allow the user to enter in information into the form for one or many different records and then have a master submission where all of the data is transferred to the table at once. They don't want the data to be submitted everytime a record is filled out.

If anymore clarification is needed please let me know.

 

[gemma-the-husky]

it depends what you are doing really

effectively this is old style stuff - lets say you are posting a batch of purchase invoices - then many systems will require you to pre-batch oyur invoices and enter a control total - you then input to a temporary table, and assume it agrees with your expected input, you commit the whole batch to the main database.

this sort of idea is possibly valid for some functions, but not many. Even with this sort of invoice entry, a more modern way would be to enter each invoice individually, controlling the detail and analaysis. At the end of a session its trivial in a dbs to query the database and get a screen print, or print out, of all the documents just entered, to confirm the total. (whereas this used to be hard to do - hence the pre-batching)

if you need to track data, then the main thing really is to be able to confirm the data integrity in terms of expected inputs and outputs.

i recently did a warehouse system - when they recive goods, they do this with a batch mechanism - they pre-enter the total lines, and hash total of goods items - at the end of the batch, the whole batch is reconciled to the pre-total - and if OK committed - but realy this sort of thing is not normal.

what sort of things do they want you to batch input


----------
having said that - you should NEVER allow any interaction with a table. You should design a form to interact with the table, and normally via a query. And the users should only interact with the FORM>

you only ever need to see the tables for problem-solving, and system developemnt. EVER (I cant think of any other reason)

 

[RuralGuy]

Sounds like some sort of BATCH update scheme. Maybe using transactions would fill the bill for you. What version of Access will you be using? Is it split to FrontEnd (FE) and BackEnd (BE)? If so, is Access the BE or do you have something else. Is it on a network?

 

[Voltron]

I put this thread in the Forms section as well. Sorry about that. I tried to find the move thread function but was unable to. I am really short on time so I was trying to do what I could.

I will be dealing with this in the Forms section after this post.

To answer the questions...

What I am trying to enter is essentially employee information. User ID's, First Name, Last Name, some other ID's (including autonumber fields), and a couple of dates. The main purpose of this, as far as I have been told, is to help keep track of the employees in the field as well as on the corporate site as to who has access doing what.

There are work orders that come in to corporate that are regarding different maintenance or construction of different sites out in the field and these work orders need to be opened, approved, and/or closed depending on what the people in the field are sending us.

I will be using Access 2000 for all intents and purposes right now even though I technically have Access 2003, but the company is in the process of a major IT upgrade program.

I am really sorry for any confusion. I will not do the double posting again. I can see that I made a mess of things.

 

[gemma-the-husky]

well you definitely do not want to batch input employee information.

just enter it into a form based on the table

if you want to track changes to employee data, then you really just need to add a suitable audit function.

the main bit is easy (entering the data) - the audit trail is straightforward, but awkward if you have no previous experience - and its one thing storing the audit - its another interrogating it.

 

[Guy Boswell]

----------
having said that - you should NEVER allow any interaction with a table. You should design a form to interact with the table, and normally via a query. And the users should only interact with the FORM>

you only ever need to see the tables for problem-solving, and system developemnt. EVER (I cant think of any other reason)

Hi Dave, I totally understand the idea that users should never interact with tables. I don't even interact with my own tables if I can help it.

But why a form based on a query rather than a form based on a table. Is that just for forms using data from linked tables? Is that to do with efficiencies on very large databases? Is it easier than locking records when there are multiple users? Or is there some other advantage Do you build the queries within the source property of the form or build the queries first and then build the forms on the queries? Or does that vary depending on circumstance?

 

[RuralGuy]

Queries do not have the locking problems that you have working directly with tables. They are also far more powerful and can do calculations if required. A form can not tell the difference between being bound to a table and being bound to a query of that table. There is no penalty for using queries since they are only a string of characters and a plan. If you upsized to an SQL backEnd you would be required to convert to queries anyway.

 

[pbaldy]

If you upsized to an SQL backEnd you would be required to convert to queries anyway.

Allan, are you speaking of an ADP (which I haven't used)? In an MDB, a form can be bound to an ODBC linked SQL Server table just as easily as an Access table.

 

[RuralGuy]

Hi Paul,
No, I was obviously talking through the top of my head and am now wiser than before. Thanks for the correction. I still think queries are far better than directly binding a form to a table.