Need a macro to run to update a table

[smichaels1234]

I need a macro to run everyday to update a table but not sure on how to get started and really not sure how to do it at all. I haven't really worked with macros that much. Mostly in Excel but that's it. It's a lot different in Access. Please can anyone help.

 

[pl456]

Could do with a bit more detail, What data are you updating the table with?

 

[smichaels1234]

Ok. Actually what I am trying to do is append a field. So what I did is I created a Join for the 2 tables. I want to append the data from one table to another. It is basically only filling that field. Here is my code:

INSERT INTO dbo_Trocar_Master ( Standard_Cost_at_First_Shipment )
SELECT dbo_Item_Master_Table.Accum_Material_Cost
FROM dbo_Trocar_Master INNER JOIN dbo_Item_Master_Table ON dbo_Trocar_Master.Trocar_Part_Number = dbo_Item_Master_Table.Item_Number;

And here is the error that I am getting:

Microsoft Access can't append all the records in the appent query.

Microsoft Access ser 0 field(s) to Null due to a type conversion failure, and it didn'e add 10 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

 

[pl456]

It says you are losing 10 records, is that all you are appending or are there more records that have been appended / accepted?
key violations are usually down to primary key field data and referential inegrity, probably duplicating primay key field values or may be you need to edit the join.

 

[smichaels1234]

I don't think I need to edit the join because that's what's keeping it equal in value. And I am only appending 10 records. So the record count is correct but I am unable to go any further. Very strange.

 

[pl456]

Perhaps you could try a different route, depends how you have designed the database. This is an example of adding values to a table running from a form, text boxes and command button.

Private Sub Command1_Click()

DoCmd.RunSQL "UPDATE stafftable SET [age]='" & Me.txt1.Value & "' Where [name]= '" & Me.txt2.Value & "' ;"

'adds age that is entered into textbox txt1 by matching name in stafftable with text entered into textbox txt2

End Sub

 

[sunilvedula]

Can we write a macro to append to the table??

After looking at your reply i understand we can write the code but is it possible to create the macro which does it. i was able to copy a table but was unsuccesful in appending using macro. any suggestions?

 

[pl456]

You can run a query from a macro, so you could create an update query. Not sure it quite fits with what you want to do.
When you run the update query it will want criteria for the field in which you want the new data, I assume is not the same data for each record. So you would need to pass variable criteria to the query and update each record individually, probably passing the criteria from controls on the forms using [Forms]![Formname]![controlname]

 

[sunilvedula]

got it resolved; Append query

hi ,

i dont know why it did not striek me earlier. i wrote an append query to the backup table and inserted that in acess macro and it works fine. everytime i backup it append to the backup table.

thank you