non relational database

[Phillef]

Hi,

I face the daunting task of converting a fully non relational database
in something hopefully a bit more helpful. The old database made full
use of memo fields, with data elements (for instance, dates, or
ducument number) separated with "carriage return". I already cretated
the new database structure, relations and forms and I was wondering
whether I could find some help out there before manually key-entring
all data (not a very pleasant perspective, as one can imagine).

Best Regards,

Phil

 

[Daniel Pineault]

If there is a way for you to import your non-relational db info into an
access table... then you could easily create a custom function to loop
through the records one by one and copy the information into the proper
tables. This has been my approach in the past and it has always worked
nicely.

It is hard to give you any more details without knowing more about the
specifics of your databases.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Tom van Stiphout]

On Thu, 11 Dec 2008 01:34:57 -0800 (PST), Phillef

I would probably attach that file, so now I have an attached table.
Then I would use Append queries to copy the data to the "real" tables.
For fields with composite information you may well need to write some
VBA and DAO code to pick them apart and update the database. Re-keying
data would not be needed, unless the current data is gobbled together
in an illogical way.

-Tom.
Microsoft Access MVP

 

[David H]

I commend you for letting the person who designed the database live - very
human of you.

In terms of splitting the strings, if each bit of data is delimited
(separated) by a carriage return, you could write a VBA function that reads
the value and the splits the value into its component parts. The Split()
function is quite handy for that, however given that a carriage return is a
special character you may not be able to use it. If not its just a matter of
checking each character to see if it matches the ASCII value of a carriage
return and at that point break off the string from the rest. An alternative
is to explicitly replace the carriage returns (using code) from carriage
returns to a character that isn't likely to appear in the text such a '|' or
'~' and then using the Split().

Once you figure out which way you want to go and populate the underlying
table, I'd certainly go through each row to check for any obvious errors. I'd
also recommend keeping the memo field as is in the new table as a reference
in the event that you discover something wacky.