Solved

Mystery Table

Posted on 2013-12-03
13
216 Views
Last Modified: 2013-12-09
Hello~
I have a macro that deletes everything in a database and imports it all from another one. Basically it is a test database that I refresh from the production database.

Since I have upgraded from 2003 to 2010 I have some mystery table that does not exist that the macro is trying to delete and recreate:

f_8D747EB0995F4AC8A581B559AAA87112_data

I'm extremely confused. Has anyone ever seen this or know what it is? It doesn't exist in the database anywhere so my macro on delete gets stuck on it and I have to drag past it and my macro on import I have to drag past it in the code.

Appreciate any thoughts.

Thanks!
Deanna
0
Comment
Question by:dminx13
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39692982
Possibly something that was automatically created in the upgrade process.

Try, from the navigation pane ..

- Right-click the header bar on the navigation pane
- Click Navigation Options
- Check the boxes to show System Objects and Hidden Objects

... and check the contents of the table if it appears at that point.
0
 

Author Comment

by:dminx13
ID: 39693140
Nope. Isn't there :-( we have tons of hidden things so that was already checked.
0
 
LVL 32

Accepted Solution

by:
jadedata earned 500 total points
ID: 39693373
it shouldn't really matter if you have inventoried your objects to delete and reimport.

limit the deletions to your selections and don't let MSAccess make these decisions.

create a table of this inventory to work from or use naming conventions to constrain the tables you wish to act on .

MSAccess is still young and gets confused easily.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39693388
Wow!  A blast from the past.  Nice to see you around these parts, jadedata!
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39693790
If Compact & Repair doesn't eliminate the mystery table, make a new database and import all legitimate objects from the problem database into it; that should leave the problem table behind.
0
 
LVL 75
ID: 39693976
"MSAccess is still young and gets confused easily."

?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:dminx13
ID: 39694387
I'll give the making a new database and see if that works. The Compact & Repair did not.
0
 

Author Comment

by:dminx13
ID: 39696413
Tried the new database no go. Still said it existed. So then I dug further and it is referenceing the MsysObjects table to get things from. So why does the MSysObjects Table have this weird file? How do I get rid of it there? What is really odd is that it doesn't try to do everything in this table at all. So why pick this one up?
0
 

Assisted Solution

by:dminx13
dminx13 earned 0 total points
ID: 39696508
This is how I fixed it. I altered the code looking at the MSysTable to ignore the f_ table and it ran just fine now.

    sSQL = "SELECT MSysObjects.Name "
    sSQL = sSQL + "From MSysObjects "
    sSQL = sSQL + "WHERE (((MSysObjects.Name) Not Like ('MSYS*') And (MSysObjects.Name) "
    sSQL = sSQL + " Not Like ('f_*')) AND ((MSysObjects.Type)=1)); "
0
 

Author Closing Comment

by:dminx13
ID: 39705708
I ran with what Jade said about the separate table and found that I was already looking at one so I just needed to update the code to ignore the new weird table!
0
 
LVL 32

Expert Comment

by:jadedata
ID: 39706076
nice job on that ..

hey mbizup!  good to be back for a bit.

dminx13:  Access is a standalone operation,.. all objects in one container called an mdb or accdb..  as such, and in order to hide control objects from users (a long time microsoft tradition..) you will frequently find "weird" tables and things you didn't create in your mdbs.

If you use naming firm conventions on your objects, even those specific to the project and stick with that pattern as you build new projects you can navigate through your project and know at a glance what's yours and what is MSAccess junk objects  

Once it's a habit these utility chores of leger de main will be a cake walk
0
 

Author Comment

by:dminx13
ID: 39706172
Yes, we have standard nameing conventions so it was easy to identify the tables that come with access and that magically showed up! Even if your can't see it. Very odd. Not a hidden object. Access just thinks it exists!

Eventually we hope to convert to SQL. It would have been nice to do that now, but we were under tight time constraints to upgrade to Office 2010 so we just did this as an interim solution.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 39706265
best of luck on the conversion

may all your projects be easy and your code be bug free!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now