1

i have 2 questions when i use access:

  1. i create a form with comboBox and calenders, i want to choose an employee from combobox and from date and to date and when i click ok i will send these parameters to a query to return the result in a query (result is the calculation of it's salary).

  2. i know how to release an access project to be useful to user that can't access tables and queries only forms. is there any way to change the access project from release mode to development one, because supposed that an error occurred, how to solve it without loosing my data.

Note: i don't have client/server i develop a program and i release it and give this release to the user, after a specific time this user tell me that an error occurred, and he need data inserted from this program to database. i can solve this problems and release another version of program, but the main problem is how to take all data from the old program to the new one.

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58

1 Answers1

0

-- You can reference form control in a query:

 SELECT FROM MyTable 
 WHERE EmployeeID = Forms!MyForm!cboEmployee
 AND SomeDate BETWEEN Forms!MyForm!txtDateStart And Forms!MyForm!txtDateEnd

You could also build an SQL string and use it as the record source for a form or in VBA.

-- Access should be split into front-end (forms, reports, etc) and back-end (data). When you make changes to the front-end, you create a new mde or accde and send that to the users. The data stays on a server in the back-end.

See: http://msdn.microsoft.com/en-us/library/aa167840(v=office.11).aspx

EDIT

From your comments, it seems that each application has a single user, if this is the case, splitting is not essential, but it can still be a good idea. The user will get two databases, one for data and one for forms etc and only the one for forms gets replaced. You will need to include a routine to locate and link the back-end tables.

However, if this is not possible, an mde or accde does not hide the data, you can send your revised copy and include a routine to import from the previous mde/accde.

EDIT 2

There are wizards that will split your database for you and link the tables. Where you find them varies slightly from version to version, but they are under the menu item Database Tools. The only problem with this is that the linked table holds the location for the back-end, which is on your computer, not on your users computer. Linked tables are how you access data in the second database. These act as if there are tables in the first database, except you cannot change them. Unfortunately, linked tables hold the location of the back-end, so this will have to be changed if you are sending it to a users. You can either write code, or show your user how to use the linked table manager. This may lead to confusion and may not be worth the effort for one PC. (See also http://www.alvechurchdata.co.uk/accsplit.htm)

Alternatively, you can split the database on your PC and make all the changes to forms etc that you want, then add some code that will import the tables and other data for the user into your new copy. The user will follow the instructions in your code to import the tables. As an aside, you will find that development is a lot safer on a split database. You should also decompile from time to time, which you can find at http://www.granite.ab.ca/access/decompile.htm.

If you want to protect your code, you can create a compiled version of this new copy, the extension for a compiled Access database is *.accde, for 2007 onward and *.mde for prior versions. This is what I thought you meant by 'i know how to release an access project'.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • can you explain the second point briefly, i don't have client/server i develop a program and i release it and give this release to the user, after a specific time this user tell me that an error occurred, and he need data inserted from this program to database. i can solve this problems and release another version of program, but the main problem is how to take all data from the old program to the new one –  Sep 28 '11 at 08:49
  • I would say splitting is essential, since the application part needs to be updated independently from the data. – David-W-Fenton Sep 28 '11 at 23:36
  • If there is a single copy of Access run by a single user, it is possible to import the tables into an updated copy, so a split copy is very handy, but not essential. – Fionnuala Sep 28 '11 at 23:45
  • yes the application has a single user, you mean that i will give user to access files one for data base and one for forms???? if this the case, how can i access data from the other access file, and how can use splitting and do you mean by mde/accde. sorry for these questuins but ia new in access –  Sep 29 '11 at 05:43
  • @danny-london I have added some notes. – Fionnuala Sep 29 '11 at 11:58
  • @Remou: I don't see how adding an extra step to the process (importing the tables, compacting) is a good idea. Splitting is by far the easiest approach. And if the alternative is a single monolithic file, you can have the tables automatically relink if you store the back end in the same folder as the front end. – David-W-Fenton Sep 30 '11 at 19:04
  • @David-W-Fenton Splitting is easier for the developer, it can be very confusing for the user on a single PC, especially for the less computer-literate user. Importing the tables is only difficult for the developer, it makes life very easy indeed for the user. – Fionnuala Sep 30 '11 at 19:08
  • i try SELECT FROM MyTable WHERE EmployeeName = Forms!MyForm!cboEmployeeName AND SomeDate BETWEEN Forms!MyForm!txtDateStart And Forms!MyForm!txtDateEnd but this query didn't return any record while it will return because of data in database, is there a problem with send data may form name is MainForm and controls are txtFromDate txtTodate and comboName –  Oct 01 '11 at 05:41
  • Check all the names you have used to make sure they are right for your form and table, for example, you would say `Forms!MainForm!comboName`. You can build your query in the query design window, that will guide you along, and when it is working properly, you can switch to SQL view to see the SQL. – Fionnuala Oct 01 '11 at 09:38
  • thx Remou its correct but i have another question if you can answer it http://stackoverflow.com/questions/7631545/how-to-create-datesheet-form-microsoft-access-to-insert-data –  Oct 03 '11 at 06:06
  • 1
    @Remou: I dispute your assertion that splitting is confusing for the user. They should never need to know one way or the other what files their data is actually stored in! – David-W-Fenton Oct 05 '11 at 17:46
  • @David-W-Fenton So what happens the user on his own or her own PC deletes or moves one half because it seems unnecessary? The OP stated that this is for a single user. You may be blessed with very educated users, but not everyone is. – Fionnuala Oct 05 '11 at 18:29