loop through linked tables? (1 Viewer)

I want to search and replace all part numbers within a pervasive database.


The part numbers are littered across what looks like hundreds of tables and views(query's).

It looks like I can import all of these with no problem, but would I be able to get access to search through all the linked tables find the listed number and replace with a modified number?

basically, in access the part numbers and revisions were stored in to fields "PartNo" and "rev" but in the new database(PSQL) they are both stored in the same field with the 17th char being the revision number.
(ie)
Access:
PartNo rev
"LA103200" A

PSQL:
PartNo
"LA103200 A"

Hi. If you're talking about changing data, you would do it using an UPDATE query. If you want to automate it, you can use code to loop through all the linked tables and execute an UPDATE query on them. However, storing part numbers in multiple tables doesn't sound like a good design. I would expect an ID number should be used to store the link to a particular part number in all the other tables in the database.

so which is pervasive access?
your goal replace partno of access with partno (padded with space to make it 16) + revno:

update table1 set [partno] = left$([partno],16) +
iif(len(left$([partno], 16)) < 16, string$(16 - len(left$([partno], 16)), " "), "") & [rev];

Pervasive is a RDBMS I presume.

As theDBGuy mentioned, this is a seriously poor design. Can you fix the design? Part Numbers are prone to being changed and so should NEVER be used as the primary key. The PK for the Part table should be an autonumber and the Part Number should simply be a data field albeit one with a unique index. That would eliminate this entire mess. And then there's the issue of mushing Part Number and Rev into a single field rather than two.

The PSQL database is from Globalshop solutions its an erp solution.
So they have control of the tables/layout.
Our accountant chose this software -_- and is why we are moving away from MS access.

I've started a ticket with these guys asking about this. Maybe they can confirm what I need to search for and replace as there are so many tables its difficult to tell.

thank you guys for replying.

Did the accountant who chose the software actually work for your company or was he just a consultant? Did he know anything about what you do at the nuts-n-bolts level? The decision to leave Access is going to come back and bite you at some point. If the accountant isn't a regular employee then he had no vested interest in any damage his decision would bring about.

If you are going to do searches inside a 3rd party database design, you need to see what tools THEY have for the kind of search-and-replace that you are facing. You have filed a ticket on that, so you are on the right path.

Well, thats the very frustrating thing about this whole situ.

He was brought in when our last account retired, he had lots of new and exciting ideas!
One of which was replacing our system, I pushed back on this quite hard having spent a good few months learning access. I was overruled, the MD's seemed to like his sales pitch!

It ended up with me doing literally ALL of the integration work, crystal reports, setting up the production flow, test, packing, everything! well apart from the accountancy side.

The package doesn't really suit us, I've had to make some pretty big changes to a lot of the forms, which wasn't cheap @£12k for their editor......

and the accountant?
He's handed his notice in and is going to work for Globalshop (shocker).

They have a tool for this part number change @ $500 -_- So I'll have to go with that. just wish there was away back to access..

Thanks for your help guys much appreciated.

Who are these people?

the MD's seemed to like his sales pitch!

Click to expand...

Many of us have seen this ---new approach to an existing business; just buy the software; and bingo your business will be smooth working and "save you resources".....

And the proponent leaves and goes on to that software vendor...

It's amazing how many viable businesses have so little regard to data management.

Good luck with whatever the project evolves into. Seems you are the one who knows more detail than others in the organization.