Importing tables from ERP system?

[Gary McMaster]

Hi Everyone,

I'm not at all experienced with Access. We have Access 97 & a couple seats of Access 2000 in case it matters.

I regularly import tables from our ERP system in Excel using the "Query Wizard" / MS Query. Some of the tables contain fields prefixed with "Obsolete-<Old Field Name>" and both Excel and Access abort the import of the table when they encounter one of these fields.

Excel gives me the opportunity to choose which fields I want to import and I can "Un-Select" the obsolete fields (in the Wizard) and get any table I want. As far as I can tell it's all or nothing with Access. I can't get some of the tables I need because I can't find a way to choose individual fields and thereby eliminate the troublesome fields.

Is it possible to choose the fields you want Access to import without using code?

I have managed to connect to the ERP tables using ADO from within Access and select a few fields. Now I am faced with creating a new table using this technique. At the moment I'm just printing the data from the recordset to the debug window so I don't have much work into it and can ditch it without hesitation.

What is the best way to create this table with my selected fields and (if possible) maintain a "link" so I don't have to delete and recreate the table every time my ERP table changes?

Thanks in advance for any advice.

Gary

 

[Gary McMaster]

Can you import the data to Excel?

Yes, but I must get rid of the fields we have been discussing first.

Surprisingly, I can actually view the contents of some of these "obsolete" fields (if any) from within the Excel wizards "field selection" dialog box.

In the "OrderHed" table one these fields contains 0;0;0;0;0 despite the fact that the data dictionary says it's a 2 place decimal.

Gary

 

[Norie]

Gary

So you can get this data into Excel and you know the fields you are interested in and/or the ones you aren't interested in?

Why not just import everything into Excel and go through all the fields/columns names/headers?

If you come across one you want copy it to another worksheet, or alternatively if you find one you don't want delete it.

 

[Gary McMaster]

you know the fields you are interested in and/or the ones you aren't interested in?

Not always because most of the time we don't even know what these fields are for until a problem arises in Inventory, MRP, Shipping or whatever. There are more than 9,000 fields. As a wild guess I would say we barely use 10% of them. We study them only when things go wrong. There is really no way I can predict which ones are really needed to control our system operation.

Gary

 

[Norie]

Gary

But at some point you will know?

How will you determine which ones you need?

Is it totally arbritary, based on the what's been received, based on the current need?

Sorry for going on but I just can't quite help thinking there might be some way to deal with this.

 

[Ziggy]

I wonder if you need some sort of schema files to organize the tables?

One of our systems has hundreds of tables, but I only add the tables I know I will use to the "ini" file we have set up with ODBC.

 

[Gary McMaster]

When we switched ERP systems we were required (by the new ERP vendor) to supply any tables we wanted to import as .csv files. The new ERP vendor supplied Excel templates so we could match up fields from the old & new databases.

Management elected to convert only 3 of about 150 tables in the old database (there was a per table fee). Not that it matters, but those 3 were "Part Master", "Product Structure" & "Mfg Routings". Management decided to start over with everything else including the "Order Management" system. The table(s) I have been mentioning in this discussion are the "OrderHed" & "OrderDtl" tables. They are the ones that must interface with our special Access application.

The ERP vendor has software that converts the above mentioned .csv files to thier database format and, I assume, sets all the flags and other dependent data in other tables as required. Because of the "0;0;0;0;0" I found in a decimal field (also an "Obsolete" field), I'm beginning to suspect that this is a flaw in the conversion process. Maybe because we didn't supply any "Order Data" or maybe because their converter does not address "Obsolete" fields or both.

In any case, our new ERP system allows us to login to a "Live" & a "Test" (for training/experimenting) database. I have some ADO code that I have been using to read/write to these databases. I suspect this problem may disappear if I fill these "obsolete" data fields with valid data. That won't help with tables that are more than 256 fields but I've only found 1 of 800 that are that large so far.

I will post results of filling with valid data as soon as I can fit it in to my schedule. I hope those results will provide some answers and help someone else that may be following this thread.

Thanks for all your help and suggestions.

Gary