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

 

[Norie]

Gary

I know you were able to solve this but in your first post you mentioned connection via ADO.

How exactly were you doing that if you weren't using code?

Surely at some point, however you were doing it, you should have been able to specify the SQL for what you wanted to import.

 

[Gary McMaster]

Hi Norie,

I was doing it with ADO / VBA code.

One of my colleagues is very good with Access from a non-programming point of view. He expects to be able to link or import tables without using code. He couldn't get that done so I was investigating the possibility of forcing it with code. I was not sure if a could produce a live link that he could just refresh or if I would have to delete and recreate the table every time. I was hoping it was just some switch or something in Access that would allow it to link these tables despite the bogus fields.

The workaround seems to have given him what he needs so far. It's a one time thing for him. However, this database has more than 800 tables containing more than 9000 fields. Many of the tables have one or more of these fields tagged as obsolete. This problem is going to continue to haunt me until I can figure out a real solution.

Thanks for your interest.

Gary

 

[Gary McMaster]

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

Thanks Ziggy. A pass through query is what I ended up with ... by hand. You must be a mind reader. Once we got it done, my first thought was that I had to figure out how to get this done with code.

Thanks very much. That will be very helpful.

Gary

 

[Norie]

Gary

So are you saying using ADO is not an option?

If it was you and you could get something working then you might be able to set things up to do what's needed with minimum user interaction.

The user could just be able to make whatever input is needed via forms for example.

 

[Gary McMaster]

Norie,

VBA coding is not an option for my colleague. He has what he needs for now. As I mentioned earlier, we switched ERP systems about a year ago.

We have a specialized Access application written by a consultant in the late 90s. It used a couple of sales order tables from the old ERP system. Those tables needed to be replaced by their counterparts in the new ERP system. He could not bring them in.

Everyone is a rookie with the new ERP system and there are constant problems. Someone is always asking to open a table to study the data. I usually open it in Excel and they can study it to their hearts content. This time they needed it in Access so they could actually interface with it.

I think some code, ADO or otherwise, that could embed and link these problem tables in Access with a couple mouse clicks would be fabulous. The workaround is tedious and error prone.

Ziggy provided a link with some valuable hints. I hope to start hacking away with that tomorrow.

Gary

 

[Norie]

Gary

I'm not suggesting your colleague uses VBA, the opposite in fact.

Create the code, forms etc to they don't need to - all they need to do is make some minimal input, click a few buttons etc.

 

[Ziggy]

Gary

I'm not suggesting your colleague uses VBA, the opposite in fact.

Create the code, forms etc to they don't need to - all they need to do is make some minimal input, click a few buttons etc.

I though Gary's problem was compounded by not being able to link the tables because of issues with certain fields

 

[Norie]

Ziggy

Perhaps I'm just confusing things here, just thinking that there might be some way to handle some of the problems using code.

Maybe something for another day.

 

[Gary McMaster]

It looks like the problem is even worse than I thought. At least one of the tables "OrderHed" has 294 fields ,two of them "obsolete" fields. I believe my version of Access only allows 256 fields. More than 100 of the 294 are for user defined data.

XL 2000 still imports the above table (after I remove the "obsolete" fields). It reports that it couldn't fit everything and brings in the first 256 fields. I don't know how Access would respond since I can't get around the "obsolete" fields via the link or import mechanism.

I guess I'm stuck with the workaround. Trying to automate this would either require mind reading or duplicating the Excel field selection mechanism. It seems it would be easier to just pick the fields I want to keep out of the data dictionary and write my own SQL field select for the workaround.

Gary

 

[Norie]

Gary

Can you import the data to Excel?