Restructuring tables

[JString]

Hello everyone.

I'm currently working on a project that's... a real pain in the posterior.
My employer assigned me with the task of converting their 10 year old Lotus
database to work with the Access platform.

I would have preferred to start from scratch with the new database, but he
needed it asap so I'm now stuck with an Access database that contains tables
with up to 65 or so fields (lucky me).

That didn't stop me from at least trying to do things the right way though,
and I've built a small empty database with a set of optimized tables using
complex relationships.

The MAIN question now is how do I go about sorting/splitting/transferring
all 200,000 or so records from three massive, disorganized tables to my new
project? If it comes down to it I guess I could write out some code that
could handle the transfer process, but that would be alot of work. If anyone
knows of a solution or utility out there that can handle this situation, I
would be extremely grateful for a heads up.

 

[Tom van Stiphout]

On Thu, 2 Oct 2008 16:33:03 -0700, JString

There are few generic answers. I would not immediately turn to code,
but I would start with some clever queries. Of course you need to fill
the tables following the relationships hierarchy: the one side before
the many side. Also write queries to identify records that are not
going to make it because of previous lack of referential integrity or
required fields.

-Tom.
Microsoft Access MVP

 

[Peter Hibbs]

The Excel to Access Converter Utility program at :-
http://www.rogersaccesslibrary.com/...p?FID=21&SID=5debzz2c87c6957859f13b1d2zcccfb4
might help.

Peter Hibbs.

 

[JString]

I gave it a shot and designed something like a dozen queries to handle the
transfer of a third of the database, and already the relationships window in
the data conversion mdb looks like a freaking spider web... I wish I could
post a screen shot.