Access 2007 to 2010 - Lost import specs (no object in this control) (1 Viewer)

[blocksey]

Hi

I am new to this forum and need some assistance please

I upgraded my database to 2010 and cant find my import specs I use to have. When I try import new data there is a "no object in this control" error message that comes up

I upload data daily that somehow picks these specs up (its referenced in the code for my ETL) but now that i want to make a change i cant find where it is

any help would be great thanks

 

[GinaWhipp]

The Import Specifications are in a hidden table *MSysIMEXColumns*. You may have to go to Navigation Options and select the option to show the hidden tables. Is that what you are looking for?

That said, I would suggest you check your References...
http://www.access-diva.com/d5.html

...and pay close attention to...
http://www.access-diva.com/d5.html#Alert

 

[blocksey]

Thanks Gina

I see the specs in the table now but how I do I make changes to them now? I want to add in a couple fields (to csv file) to the import spec but cant seem to do it anywhere.

When I go to import and try get to the advanced setting on the import wizard thats when it says "no object in this control2

 

[vbaInet]

Sounds like the problem doesn't necessarily relate to the import spec.

What version of Access did you upgrade from? And what sort of controls did you use?

 

[blocksey]

Hi

I upgraded from 2007 to 2010

I can see the import specs on the hidden table that Gina mentioned so I think I can make changes to that table if I need to add another table?

Would you agree with that?

As for the import wizard, the screen just goes blank (i.e. no options to choose other than "next" and Finished" at the bottom. when i select next the "no object in this control" error comes up

 

[vbaInet]

If you know what you're doing then yes but I would advise against it because that's a system table. Gina obviously had her reasons for pointing you to this table so wait for her response.

I still need to know about what sort of controls you have in your db? And how many forms do you have?

 

[blocksey]

Thanks vbaInet

attached are my controls which are in my backend (i split mine into 2 using the DB splitter)

The front end has many (about 20 or so) forms but none in the back end

 

[vbaInet]

Ok, what you've shown are the references but what I was after was a list of the sort of controls you have on your form. Is there any other type of control besides a textbox, tab, listbox, combo box, label, button and subform/subreport?

Also, we need to establish the source of the problem and see if we can eliminate it. You should still have the 2007 copy of your database right?

 

[blocksey]

Apologies

I use to have Ocx control for a calendar but didnt need in 2010 so took it out. otherwise i cant think of anything else other than what you mentioned. is there a way i can check?

I do still have a version of the 2007 DB and I can see my import specs there and use the import wizard but acnt do so now

 

[vbaInet]

I think that calendar control is what could be causing the problem. Do you remember which forms you used that on?

 

[blocksey]

It was all gone as i had removed the code once I upgraded as was giving me issues then. I checked again and cant find it anywhere

 

[vbaInet]

Yes you can't find the control but there might be traces of it left over. So again I ask, do you remember which forms you used the calendar control on?

 

[vbaInet]

If it would make things move quicker, upload just the front and I'll have a look.

 

[blocksey]

Apologies, yes I did use it in one form and just checked that it is not there any more. it was only for a small piece so I am confident i didnt use it anywghere else

 

[vbaInet]

So like I said, even if the control is no longer there, there might still be traces of it (i.e. a reference that you can't see, corruption or otherwise) in your db - just the front end, no data. That's why I need to see the db. On the other hand, I can tell you things to try.

 

[GinaWhipp]

Hmm, I see you missed the Conversion Alert section, remove the checkmark next to...
Microsoft Visual Basic for Applications Extensibility [vbe6.dll] 5.3

The above Reference has been known to cause issues. After unchecking Compile your database and try again.

To answer the question about the hidden table, you do NOT make changes there, it was just to show you they are there. If you want to make changes, re-import and then, using Advanced during the import, re-save the updated Import specifications using the same name as the *old* one.

 

[vbaInet]

Hmm, I see you missed the Conversion Alert section, remove the checkmark next to...
Microsoft Visual Basic for Applications Extensibility [vbe6.dll] 5.3

Good catch Gina!
That's the reference used for the VBA IDE itself. It should never be checked for deployment.

 

[blocksey]

I will need to remove some data as it is still quite large

Ultimately I just want to get to the import wizard so i can charge my import specs. Do you think that me potentially having the controls there might effect that?

Other than that how else can I change my import specs?

 

[vbaInet]

Don't worry about the import spec for now. We're trying to nip the problem in the bud. Gina's suggestion might be the ticket otherwise we'll seek alternatives.

 

[blocksey]

Thanks Gina. sorry didnt see your reply earlier

I unchecked the box but dont know what you mean by "compile your DB" where can i do this? after unchecking that box i still couldnt get to the "Adnvanced" bit as it still have the same error message