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

 

[Ziggy]

You should see if you are able to connect from access using ODBC this will let you link the tables

I am a bit confused with why sometimes the field names are prefixed, normally you would be dealing with consistently named fields, if they are different but the data is the sane then maybe you could ignore the column headings

 

[Gary McMaster]

Thanks for the reply Ziggy. Much appreciated.

I am a bit confused with why sometimes the field names are prefixed

This has been done intentionally by the vendor of the ERP package. Documentation states that the fields are no longer used by their latest version of the software but they have been left in place to provide backwards compatibility.

I have proven beyond any doubt that these are the fields causing the problem. I am also confused as to why they had to tag them this way. One would think they could have just left them alone and ignored them in the newer version.

I also suspect that some tables may have more than 256 fields. Most tables have dozens of user definable fields of different data types. In Excel I am also able to omit the UD fields to keep the column count down.

You should see if you are able to connect from access using ODBC

I tried ODBC (months ago) although I was clueless when I did. I had never even seen the Access IDE. I believe it still wanted to import the entire table and failed as described. Maybe I didn't try hard enough because I got what I needed via Excel & ODBC. I'll give that another try. Maybe I can modify SQL or something and pick the fields that way.

The database is OpenEdge 10.1B (Epicor). We have an ODBC driver provided by OpenEdge. ADO & DAO also seem to work.

Thanks again for your reply.

Gary

 

[Ziggy]

if you have the ODBC driver installed then first go to Control panel ( windows) and Administrative tools, then Data Sources, under User DSN select ADD, then find the driver... see how far you get, you might need to look up documentation for your ERP, but try this first as it might be straight forward.

Once source is created, from Access create a Linked table by selecting the source, if it works you should be able to see the list of tables from the ERP system for which you can link to.

When I have time I will look up your system and see what it talks about... but you may figure out by then.

==================================================================

what I mean with the fields is that for any given table, if it has some of the "prefixed" fields, then that should be constant right... for that table ?

 

[Gary McMaster]

you should be able to see the list of tables

The driver has been installed and the DSN in use for quite some time. I do get the list of tables and I am able to connect to some of them. That's the problem ... Access only lets me select the entire table. If the table happens to have one of these fields marked "Obsolete-" then it aborts with the error:

"Invalid field definition '<Some field name>' in definition of index or relationship."

I'm using the same DSN with Excel. I can open every table that fails in Access by simply telling the Excel Wizard to skip those fields. Access never gives me the chance to select the fields I want like Excel does.

Here is the error that is returned by MS Query / Excel if I try to include one of the obsolete fields.

"Array element value overflow. [12664]"

Gary

 

[Ziggy]

so just to clarify, in Access... you get the Error when attempting to "LINK" or "Import" ?

 

[Gary McMaster]

Yes, and thanks for your interest.

Gary

 

[Ziggy]

I'm guessing you have looked here...

http://communities.progress.com/pcom/docs/DOC-103525

http://documentation.progress.com/output/OpenEdge102b/pdfs/dmodb/dmodb.pdf

not sure if it helps?

 

[Gary McMaster]

Ziggy,

I hadn't seen those particular documents. We changed ERP systems 15 months ago (what a hassle). We do have a complete set of OpenEdge Docs but I believe they are 10.1. Thanks for the links they will be a nice addition to our library.

So I don't waste any more of your (or anyone elses) time, I believe I have found a workaround that does what I need.

From the "Query" pulldown menu select: SQL Specific > Pass Through

Paste an SQL statement into the resulting text box (without the troublesome fields) something like so:

SELECT OrderDtl_0.BasePartNum, OrderDtl_0.BaseRevisionNum, OrderDtl_0.CustNum FROM MFGSYS.PUB.OrderDtl OrderDtl_0

Right click on the SQL text box title bar and select "Properties"

Click inside the "ODBC Connection String" text box that appears.

Pick the small button with the ellipsis that appears.

Select the machine DSN that appears in the resluting dialog box.

Save the project.

It was very intuitive It's a small miracle that I got something that's usable.

There's got to be an easier way!

Thanks very much for your help and interest.

Gary

 

[Ziggy]

Visual basic will likely be your savior, here is an example....

http://www.techonthenet.com/access/queries/passthrough2.php

I can help with the coding if you get stuck