Access 2007 macro works but not on a form (1 Viewer)

[jalverson]

I have a simple macro that makes a copy of table. The macro runs correctly by itself. However, I then added a button to a form to run the macro. When I click the button, I receive an error "the database engine could not lock table because it is already in use by another person or process." When I close the form and run the Macro directly, it works as designed. Is there a security setting that I need to change on the form? Has anyone seen a similar problem when a attaching a macro to a button on a form? I'm using Microsoft 2007. I appreciate your help.

Jeff

 

[RuralGuy]

Try binding your form to a query of the table instead of the table directly.

 

[boblarson]

I have a question -

Why are you creating a copy of the table????

 

[jalverson]

Thanks for the responses. We are using a sharepoint site linked to an access 2007 database for tracking container shipments. I have written a query which compares the expected receipt date to the receipt dates on the purchase orders. The purchase orders are in an Oracle database as part of our company ERP software. If the dates are different, then I have an update statement that updates the PO Line dates to match the container tracking dates. I ran into problems when I linked the sharepoint table directly to the Oracle table. When the update statement runs I received Oracle constraint violations. However, if I make a table of only the rows where differences exist. I can link this static table to the Oracle table and the update statement runs with no problems. The query for the date comparison, the make table query and the update query to the Oracle table is all wrapped into one macro.

The form that I mentioned only has one button which allows the user to run the macro. The form is not bound to any table or query. I will try this suggestion and let you know if the problem is solved.

Thanks,
Jeff

 

[RuralGuy]

And the plot thickens.

 

[jalverson]

Problem solved. The suggestion to create a query and then bind the form to the query worked. Both the form and the macro are now working as designed. Thanks for the help.

Jeff

 

[RuralGuy]

Glad we could help.