Updateing a table from another table in the same database

[chrisgreen]

I have a table which is linked to a Navision database via an ODBC connection.&nbsp;&nbsp;I want to update another table that has roughly half of the same fields as the linked table.<br>I have tried using queries but with no success.<br><br>Once I have updated the table I want the process to happen every 30 mins automatically.<br><br>Is this all possible?<br>

 

[kathryn]

Chris,<br><br>As far as the timing goes, you can design a form that has timing set in it.&nbsp;&nbsp;You would set the TimerInterval Property of the form to 30 minutes, which needs to be translated to milliseconds for the property, and in the Timer Event you can write code which will run the query.&nbsp;&nbsp;Look at the TimerInterval Property and Timer Event in Help.&nbsp;&nbsp;It is pretty straightforward. <br><br>However, as far as the query goes, could you give some more details?&nbsp;&nbsp;I am assuming that this is the same situation you posted about yesterday on the (old) Access list.&nbsp;&nbsp;Two tables, one tblImport and the other tblExport.<br><br>Were you able to set up the update query?&nbsp;&nbsp;If so, what happened?&nbsp;&nbsp;<br><br>Is there a common field in the two tables that will tell Access which records in tblImport correspond to what records in tblExport?&nbsp;&nbsp;Can you give us some specific field names to make the discussion clearer?<br><br>What you want to do is totally possible, but as always, the devil is in the details!&nbsp;&nbsp;;-)<br><br>Hope I can be of some help.<br><br>Kathryn

 

[chrisgreen]

There are four tables:<br>Activites, Contact Management, Contact, Prospect<br><br>These are linked to tables in a Navision database via a ODBC connection.<br><br>My company has external salesmen that work from home.&nbsp;&nbsp;Their mailboxes are on our server.&nbsp;&nbsp;I plan to set up more tables that are linked to their mailboxes (contacts). This is to keep there contacts in outlook up to date.<br><br>Every time data is changed in Navision the access table is modified and every time data is changed in the export tables in access the contacts in the mailbox is changed.<br><br>I need to find a way of pulling certain data out of the tables that are linked to Navision.<br><br>There is a field in all the tables that are linked to Navison that determines where the data is to be sent.&nbsp;&nbsp;This is salesperson ID.&nbsp;&nbsp;This field determines what records should be sent to what export tables.&nbsp;&nbsp;The other thing is that only certain fields in a record need to be sent.&nbsp;&nbsp;This is because some fields in the Navision DB are not used or are not needed by the salesperson.<br><br>I want the data from the import tables to update the export tables by the press of a button or at regular intervals.<br><br>I appreciate your help.

 

[kathryn]

OK, let's do a real simple update and see what happens.<br><br>Let's assume that the data in Contact (your Navision table) has changed and that the table it needs to change on your server is tblServer.<br><br>You have X number of fields in Contact, but only Y need to be used to update tblServer.<br><br>Add both tables to a query and join the tables by SalespersonID.&nbsp;&nbsp;Change the query to an update query.&nbsp;&nbsp;Double-click on the Y number of fields fields in tblServer that need to be updated.&nbsp;&nbsp;That will move them down to the qrid.&nbsp;&nbsp;The thrid row of the grid is Update To.&nbsp;&nbsp;On that line, enter the name of the field you want to use from Contact to update the respective tblServer field. Use the form [Contact]![YourFieldName].&nbsp;&nbsp;You will have a value in each column.<br><br>To do this as a test, you should make copies of the tables. Actually in your case, do an import instead of a link on the two tables.&nbsp;&nbsp;That will create stand alone copies of the tables, and if you keep the same names when you actually link them, then the query will work seamlessly.&nbsp;&nbsp;Just a thought.<br><br>You can limit your update to one record, by adding the SalespersonID to the query and enter and ID number on the fourth line, the criteria line, of the grid.&nbsp;&nbsp;<br><br>Let me know what happens.&nbsp;&nbsp;If it doesn't work, let me know and I will send you a sample Access db off-list with an example.<br><br>Kathryn

 

[chrisgreen]

I can't get the criteria to work.<br><br>Can I update and append without doing seperate queries?<br><br>Thanks again for your help.<br><br>Please could you send me the ACCESS DB off-list.<br><br>Thanks again.<br><br>Chris

 

[chrisgreen]

I have got it working.<br><br>Thank you Kathryn.

 

[bxgti4x4]

I should like to follow-up on Chrisgreen's problem and hope I am doing it in the right place. I have two tables in the same database, one of which uses historical and current data, the other uses only current data. The format of each is the same. Data for the current table is entered or amended via a form. As I enter, or amend data in the current table, I need it to automatically transfer to the historical data table. I have created an update query which will add new records but will not amend previously entered records. Is there any way that I can make this happen automatically and without any messages appearing on the screen?

Any ideas would be much appreciated.
bxgti4x4

 

[MBNelson]

Chris,

Have you ever worked with Crystal Reports and Navision? Just wondering... ***************************************
Mike Nelson