Access 2013 NAVIGATION PANE REFRESH

[sumdumgai]

The title I am using may not explain my problem, but I don't know how else to start this post. Intermittently (some days it's not a problem), whenever I import a new table into Access, the table doesn't immediately show in the navigation pane. I have to first click on 'Custom' and then click on 'Object Type' in order to have the new table appear. It's as if the navigation pane isn't being refreshed immediately. There are other quirky things that happen when Access is in this particular state. And it is not database dependent. On other days, when a table is created, it appears immediately. Does anyone know what may be causing this and how to fix it. Kind of annoying. Thanks.

 

[sumdumgai]

No replies yet, so I'll embellish. There seems to be at least two states that Access may be in. In one, when you create a new table (or query, or macro), it shows immediately in the Navigation pane. In another, the table doesn't show until you either change the view of the Navigation pane (i.e., show a different set of entities like tables, or queries, or macros), or exit the database and reopen. Is there a setting that determines which state Access is in? Thanks.

 

[vbaInet]

I suppose one thing that's puzzling is the fact that you're constantly importing tables. Are you developing at the moment or is this something that happens in production?

Do you have a custom grouping of objects?

 

[sumdumgai]

Thanks for reply. A new table is created each week from an Excel sheet that contains imported data. It's a rolling process, where the latest 13 weeks are reported via a query (i.e., the oldest table is removed from the query and the newest imported table is added). When the data is imported, a new table is created. This new table is sometimes not immediately seen in the navigation pane. To see the new table, I have to click the down arrow on 'All Access Objects', click 'Custom', and then click 'Object Type'. The same thing happens when I copy objects from another database, or when I create a new table by running a query. I have to toggle the 'Custom' - 'Object Type' steps to see the new objects.

 

[vbaInet]

A new table is created each week from an Excel sheet that contains imported data.

You're better off working on this aspect. You don't need to keep creating/deleting tables, it's not a good design. Are the field names always the same in the spreadsheet?

To see the new table, I have to click the down arrow on 'All Access Objects', click 'Custom', and then click 'Object Type'.

What I wanted to know was whether you created some custom groups in the Nav pane?

 

[sumdumgai]

It's too complicated to explain, and besides, Excel reports have already been written to expect that the first table in the query output is always the most recent week's data. The design may not be the best but it works. Whether I'm importing new tables or not, my issue and reason for this post is that new objects introduced into the Access database, whether copied from another database, created by query, or imported from Excel, are not being seen in the pane until I force Access to show them by toggling pane views, or exiting and reentering the database. Thanks. And there are no custom groups.

 

[vbaInet]

Well, it's up to you but I can guarantee that in the future you'll be looking for better alternatives.

Use Application.RefreshDatabaseWindow to refresh your Nav Pane.

 

[sumdumgai]

Thanks for your comments but I think you're totally misunderstanding my question (and it's probably my fault). You're suggesting a VBA command, and this is not a VBA related question.

Anyway, back to your comment about design. We get chunks of data once a week, and it only comes in Excel format. Actually, we go to a site to produce a report and then export the resultant report in Excel. After massaging the Excel report to get it into the right format, we import it into Access as a new table. We then modify a query to delete the oldest table (now the 14th table) and add the new one (now the 1st). This gives us our rolling 13 weeks.

Given that, how would you set up the database so that you don't have to create new table and delete old ones?

 

[vbaInet]

How do you import it into Access, in code or through the Import/Export wizard?

All your Excel spreadsheets should have the same field names so appending to one table should suffice. You just need to create an Import Spec that you can use over and over again whenever you need to import that spreadsheet.
Create one master table and one staging/temp table, import to the staging table and once you can confirm that it imported successful append those records to the master table.

 

[sumdumgai]

The spreadsheet is imported manually, via the 'External Data/Excel' tabs. The sheet name dictates the new table name and reflects the week-ending date. Here's the problem. Imagine you have 13 tables, each representing sales for a particular week. Excel programs are run that execute queries to fetch this weekly data to produce reports. The reports show the most recent sales data in column x, the next most recent data in column x+1, and so on. So, to avoid creating a new table each week and deleting the oldest table, you'd have to either rename the original 'table12' to 'table13', 'table11' to 'table10' and so on, or actually move the data from table to table (i.e., move data in T1 to T2, T2 to T3, ...12 times and then import to T1).

Is there another way?

 

[vbaInet]

I'm sure that there are other ways of handling that. If you create a new thread for this question with some sample Excel files we can look into it.

By the way, back to your original query, you can either try clicking inside the Nav Pane and hitting F5 or use the Refresh code I gave you earlier. Yes I know it's code but sometimes when objects are deleted or created the pane needs to be refreshed manually to reflect the change(s).

 

[sumdumgai]

Thanks for your help.