[AccessD] Import Bad data?

Gary Kjos garykjos at gmail.com
Mon Feb 9 14:50:23 CST 2009


Could it be the size of the contents? I do a lot of extracting from
Oracle tables into Access and Oracle allows some larger values than
Access does. If I hit one of those fields I blow up. Usually that is
not based on the contents of the field though jsut the field type that
doesn't translate from Oracle into Access. CLOB's and BLOB's I think
is what Oracle calls them.

GK

On 2/9/09, Mark A Matte <markamatte at hotmail.com> wrote:
>
> I don't think its corrupt.  The DBA can export the records just fine...there is just some offending character(s) that Access absolutely is choking on.  Anytime I try to bring in or reference that field of the record in question( a linked table)...Access dies.  I was just trying to avoid having the dba do an export.  I have a manual work around...just was curious if anyone had a suggestion about programming around such a catastophic failure(detect the failure and STOP or skip the record instead of crashing)...right now...I'm using the failure to isolate the records in question.
>
> Thanks,
>
> Mark
>
> ----------------------------------------
> > Date: Mon, 9 Feb 2009 11:31:44 -0800
> > From: cfoust at infostatsystems.com
> > To: accessd at databaseadvisors.com
> > Subject: Re: [AccessD] Import Bad data?
> >
> > Does the record contain a memo field, perhaps? If so, it sounds like
> > it's been corrupted. One option is to import all the other fields but
> > not the memo field into your database maintaining the table structure.
> > Then do an append of the memo fields on the key joins between the two
> > tables. You should be able to get everything except the bad fields.
> >
> > Charlotte Foust
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
> > Sent: Monday, February 09, 2009 8:42 AM
> > To: accessd at databaseadvisors.com
> > Subject: Re: [AccessD] Import Bad data?
> >
> >
> > I can't even pull this record in with a select statement...as a query or
> > in vba as a recordset?
> >
> > I'm trying to avoid having the dba export to a text file...as they are
> > not fans of this process to begin with.
> >
> > Thanks,
> >
> > Mark
> >
> > ----------------------------------------
> >> Date: Mon, 9 Feb 2009 10:24:11 -0600
> >> From: garykjos at gmail.com
> >> To: accessd at databaseadvisors.com
> >> Subject: Re: [AccessD] Import Bad data?
> >>
> >> You could Export to a Text file and then read through that with vba
> >> text handling code to parse through and scrub the offending characters
> >
> >> and then save the corrected file and import that. I've done that when
> >> someone provided me with several huge exported data files that didn't
> >> have the correct combination of CR & LF at the end of records and so
> >> it fouled up a direct import for me.
> >>
> >> Longtime AccessD contributor Seth Galitzer has some code samples on
> >> his website for "FILE I/O in VB" that you can download and have a look
> >
> >> at.
> >>
> >> http://seth.galitzer.net/node/11
> >>
> >> Once you get the file open parsing through the text and either
> >> ignoring characters or replacing them with something else isn't too
> >> tough. It's been a long time since I've done it but I could MAYBE hunt
> >
> >> up my code if you are really stumped by that.
> >>
> >> GK
> >>
> >> On 2/9/09, Mark A Matte wrote:
> >>>
> >>> Hello All,
> >>>
> >>> I'm not expecting an answer I'll like...but I'll try.
> >>>
> >>> I'm importing a large number of records from Informix to Access via
> > odbc. There is a large text/memo field...that apparently has some
> > characters access does not like. I do not get an error message...access
> > just says "an error has occurred...do you want to tell MS"...then
> > closes.
> >>>
> >>> I'm trying to think of a way to detect/program around this. So
> > far...its a very manual process. I loop through 2 days worth at a
> > time...that way I don't have to start over from the beginning everytime.
> > When it crashes...I pull in just the PKs for that date range...loop
> > through them...imort the text field 1 record at a time...store in temp
> > table...when it crashes...I compare the PKs to the temp table...the next
> > PK not in the temp is the offender.
> >>>
> >>> This is very manual and time consuming...any ideas/suggestions?
> >>>
> >>> Thanks,
> >>>
> >>> Mark A. Matte
> >>>
> >>>
> >>> _________________________________________________________________
> >>> Windows Live(tm): E-mail. Chat. Share. Get more ways to connect.
> >>> http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_AE_Faster_
> >>> 022009
> >>> --
> >>> AccessD mailing list
> >>> AccessD at databaseadvisors.com
> >>> http://databaseadvisors.com/mailman/listinfo/accessd
> >>> Website: http://www.databaseadvisors.com
> >>>
> >>
> >>
> >> --
> >> Gary Kjos
> >> garykjos at gmail.com
> >>
> >> --
> >> AccessD mailing list
> >> AccessD at databaseadvisors.com
> >> http://databaseadvisors.com/mailman/listinfo/accessd
> >> Website: http://www.databaseadvisors.com
> > _________________________________________________________________
> > Windows Live(tm): E-mail. Chat. Share. Get more ways to connect.
> > http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_allup_explore_022009
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> _________________________________________________________________
> Windows Live™: E-mail. Chat. Share. Get more ways to connect.
> http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_allup_howitworks_022009
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
Gary Kjos
garykjos at gmail.com




More information about the AccessD mailing list