Import/Export Specifications

[whagar]

Hi all,
I'm working in an Access 97 DB that uses custom Import/Export Specifications defined in MSysImExSpecs. On quite a few of the databases I've noticed that the Spec_Id field in this table does not match the Spec_Id field within MSysImExColumns, yet import/export using this spec still functions in some cases and fails in others.

My question is this: When you create a custom Import/Export Specification using the advanced options screen and identify the import/export fields/types should it not create a record in both MSysImExSpecs and multiple records in MSysImExColumns with identical Spec_Id and the field names that you specified? That was my take on it but it appears that this specification still functions in some cases without any MSysImExColumns records. Also, uses delimiters other than the normal default so it doesn't appear to be using that instead.

If I do require Spec_Id to be the same then I guess my option is to re-create the custom spec. If I imported the corresponding data table into a blank database and then recreated the custom spec would it solve the problem by deleting those two system tables from the other databases and importing these new ones?

Any help would be greatly appreciated, there is very little info on custom specs or these two system tables anywhere.
Thanks,

Whagar [sig][/sig]

 

[whagar]

This thread seems to have been overlooked, any ideas? [sig][/sig]

 

[MichaelRed]

whagar,

The entire content of the MSys* tables appear to be largely undocumented. Many of the fields in these tables can be decoded by inspection, however you need to be very careful in the interpertation of information 'decoded' from them. I do not know of any specific relationship between the columns having the name "Spec_Id" in MSysImExSpecs and MSysImExColumns. I would suspect the different spec_id's refer to different versions (revisions) of import/export specs, but I could not "prove it".

When I have had problems with the Import side of these, It has usually been from the datasource being changed w/o anyone sending a corresponding change notice to me. This has occurred so often that I (now) always structure my import processes to check the format of imported files prior to the import, to check the record count of the import, and to ALWAYS import into a "TEMP" table, run some checks on the integrity of the imported "data" before actually using the data for anything!

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[whagar]

Thanks very much for your reply Michael. It seems that without knowing for sure the relationship between MSysImExSpecs and MSysImExColumns that the two tables should be recreated. I had assumed that since SpecId was a primary key in M..Specs and M..Columns contains this field as a primary key combined with the field name that the relationship was obvious. You make a valid point that this relationship might not be as it seems (Microsoft &quot;Logic&quot.
Thanks Again,

If anyone else has any ideas concerning these tables it would be muchly appreciated. [sig][/sig]

 

[MichaelRed]

I don't think you can &quot;re-create&quot; the MSys* tables. These are &quot;system&quot; generated and it is hazardous to alter them in any way. You may resolve the issue by copying everything (except the import/export specifications) in the database to a new database and then re-creating the import/export specs (manually) in the new data base. Then- of course - deleting the old db and renaming hte new one.

However, again, I would do some pretty exhaustive tests on the errant data/records before I went to all of the trouble to re-build the database.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]