Re: Running Update Macro disables switchboard

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Smilin Jack,

Do you have an action at the end of the macro to reset Echo on?

By the way, not related to your problem I think, but these actions:
OpenTable
RunCommand [SelectAllRecords]
RunCommand [Delete]
… could be replaced by a single OpenQuery action to run a Delete Query.

What are the reports and query that you are deleing and importing? Are the imported objects different from the deleted ones? Just curious, but can't imagine why you would be doing this.

--
Steve Schapel, Microsoft Access MVP

Smilin Jack wrote:
Steve -

The macro begins with 'setwarnings' to 'no,' and echo turned off. It then deletes several reports & 1 query using the 'delete object' command.
Next, it runs a saved import, bringing the replacement data into new tables in the database. The import also includes replacement reports and the replacement query.
It then runs several sets (4) of the following:
OpenTable
RunCommand [SelectAllRecords]
RunCommand [Delete]
OpenQuery [Append Query] (this is from the imported data)
Close [Table]
Next, it runs 4 deletes:
DeleteObject [Imported Tables]
Lastly, a message box:
MsgBox ['Updates Complete']
As mentioned in my first message, I placed a final line in ['Exit'] to close the database, as a work around.

I had originally placed command buttons for these macros (2 - not exactly the same, but similar - both causing the same error) on the switchboard, but the system completely locked up after running them. I settled for leaving them in the navigation pane as a double click. That way I can at least close the database properly - the only issue is that none of the switchboard commands work. After re-opening the database, everything works as it should, and the updates are in place.

.

Relevant Pages

  • Re: Combine Multiple tables into One Master table
    ... You can use a macro with 3 TransferText actions, one for each table, to get the data imported into your Access tables. ... the Open event of a form which always opens when the database opens - but then what if today's import has already been done. ... And then make an Update Query to add the data to the other fields from the related records in the other two import tables. ...
    (microsoft.public.access.macros)
  • Re: Newbie question about formula based values
    ... I agree this query only needs to be run once a day. ... In this macro have a single item that calls RunCode. ... Go into the task and edit it, telling it to open your database. ... Update MyTable Set Level=1 Where EndDate<DateAND Level=2 ...
    (comp.databases.ms-access)
  • RE: Showing data based on other values
    ... Importing and linking have two different purposes. ... data not in the local database and that you do not want in the database. ... To create an append query you start out creating a regular ...
    (microsoft.public.access.dataaccess.pages)
  • Re: Best design for importing montlhly data
    ... Can I link excel files to the database even if the xls is a new file each ... should I run a make table query and overwrite it? ... If the data you are importing ... I have basically the same question for the excel files. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: VBA Program to run a macro
    ... a query just like the one below but I have set it can ... to modify your macro so that every monday the scheduler ... autoexec that opens a password protected form. ... >set the scheduler to open the database in the early ...
    (microsoft.public.access.formscoding)