Re: Populating Databse

On Sat, 8 Apr 2006 11:35:50 -0400, "News" <vinootz@xxxxxxxxxxx> wrote:

I have just recently re-designed a Database that I inherited.
The old had Major problems, one table, 500 Fields.

Well, that wasn't an Access database for sure! (limited to 255 fields
in any one table). And good on ya for redesigning it - that's just
what it needed.

It was too confusing, for me anyway, to fix.
I instead designed a new one, many of the same fields, but naturally, with
many more relating tables and different field names than the old one (i.e:
Old- [Last Name] New-[txtLastName]

My question is:
What is the best method to Import the data from the old DB to the new
database?
I was thinking exporting from old in csv file and then importing to new (am
I totally off?)

I'd suggest using File... Get External Data... Link (rather than
Import) to connect to the monster table (what was it, two one-to-one
related tables??), and running Append queries.

Issues:
Old was mostly Text format, even Date Fields were written in text.
New one using Date/Time (Medium Date). Same with SSN and so on.

SSN *SHOULD* be text, as should telephone numbers, zipcodes, and any
other identifiers which just happen to consist of digits. You'll never
ever be doing arithmatic with SSN or zipcode values - just use Text(9)
for SSN, etc.

Should expect a decent amount of errors?

Depends on whether you had angels, human beings, or demons doing data
entry in the old database. Angels will have made very few or no
errors. The only difference between the human and the demonic errors
is that most of the demonic errors will look perfectly REASONABLE and
be very hard to catch. <wry grin>

Yes. You'll have errors. You may want to (at least try to) fix some of
them up first. You can run a Query on your table such as

SELECT thisfield, thatfield, datefield1, datefield2, datefield3
FROM wideflat
WHERE IsDate([Datefield1])=False
OR IsDate([Datefield2])=False
OR IsDate([Datefield3])=False;

to identify records which will cause errors when appending data into a
date field. Alternatively, you can use the Append query; rather than
appending a dubious quality text date into your new datefield you
could use an expression like

AppDate: IIF(IsDate([datefield]), [datefield], #12/31/9999#)

to append some clearly unreasonable date into the datefield (which you
can then fix up manually).


John W. Vinson[MVP]
.

Relevant Pages

  • Re: Populating Databse
    ... that wasn't an Access database for sure! ... Same with SSN and so on. ... you can use the Append query; ... to append some clearly unreasonable date into the datefield (which you ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Populating Databse
    ... How do go about using the GetExternalData when the table youre importing to ... Same with SSN and so on. ... you can use the Append query; ... to append some clearly unreasonable date into the datefield (which you ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Parameter dialog box pops up
    ... > I need to update my Access database from an Excel list. ... The question is how do I append each field from ... > append query for each table from my TableN and run them separately). ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: form to email document
    ... >> append the Session ID for that user Session to the file name. ... >> name uploaded.This can come from a simple text file or a database. ... >>> I was hoping for something like the procedure used to upload a form, ...
    (microsoft.public.frontpage.programming)
  • Re: [OT] PostgreSQL: bytea help needed.
    ... >> Well, you draw the data in, and then append it to the field. ... > the database from my local filesystem for example. ... > Well I wanted to grab binary data off my filesystem directly from the ... > COPY command which is used to pull text data in. ...
    (comp.os.linux.misc)