[mso] Re: Access Error: Unparsable Record :VSMail mx2

Robert,

Sorry it took me so long to get back to you on this, I was out of town =
(yep, I got to go on a rock trip).  Interesting technique he had you =
use.  Did he explain why he had you do it that way?  I would have =
thought that after breaking up the file into smaller chunks you could =
have imported one, then did a Compact and Repair to eliminate the =
temporary data it created during the import process.    Glad to see you =
getting into Access like this.  It can be a very powerful tool when you =
allow it to be.

James

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On =
Behalf Of Robert Carneal
Sent: Thursday, January 11, 2007 3:01 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access Error: Unparsable Record :VSMail mx2


James-

I am using Access 2000. You are right about the 2GB limits, so I wrote =
the
developer who constantly brags that you can have 10 billion locations in =
his
genealogy program- IF you have the hard drive space. I told him what I =
was
doing and he suggested the following:

A. Make the files to be imported *no* longer than 95,000 records. He =
said
Access, his program "cooperated" best at not importing more than 95,000. =
(I
had originally been trying to import 1.2Gb at a time)
B. Make each record no longer than seven fields per record. (I used =
five.)
I ended up with 21 files to import. 20 files containing 95,000 records =
and
the last file contained the remaining.

Then, do this:
1. Completely shut down.
2. Import the first file.
3. close Access.
4. Reboot
5. Import the next file.
6. close Access.
7. Reboot
8. Any more files to import? If so, go to step 5 and repeat. If not, you =
are
finished.

It took 39 hours to do it, but I got it done.  I love it. I ended up =
with a
file containing 1,846,334 locations, all recognized by at least one of =
the
following: United Nations, NOAA, USGS. This was my most ambitious =
involving
Access since I had your course. Bet you did not expect me to push the =
limits
of Access like this! <g>

Thank you, James.

Robert

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On =
Behalf Of
James LaBorde
Sent: 2007-01-10 15:09
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access Error: Unparsable Record :VSMail mx6

Robert,

I may have spotted your problem.  You said that the file was nearly 800 =
=3D
MB zipped and several times that unzipped.  If you are using Access 97 =
=3D
or before, your size is limited to 1GB and if Access2000 or above the =
=3D
size is limited to 2GB.  What you may find to be your best answer is to =
=3D
import some of the data into different databases and linking those =3D
tables into your main application.  (or better yet, upsize your backend =
=3D
to an MSDE or whatever it is they are calling the Free scaled down =3D
version of SQL these days).  That can usually handle much larger tables =
=3D
and the biggest limitation is usually number of concurrent connections.  =
=3D
It has the added advantage of using the SQL Server query engine rather =
=3D
than the Jet query engine. But if you want to go that way, we need to =
=3D
take  this off-list as that is no longer MS Office.

I remembered you were into genealogy, I was just surprised when the =3D
geology issue came up.  As a rockhound I am a geology buff and love that =
=3D
kind of stuff.  If you need help with the databases please feel free to =
=3D
let me know.

James

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On =3D
Behalf Of Robert Carneal
Sent: Monday, January 08, 2007 9:30 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access Error: Unparsable Record :VSMail mx6


Hello James-

Geology is rock hound to you, as geology is genealogy (cemetery =3D
research,
lost cemetery hunts).

Ok, to make a LONG story short, my genealogy program allows me to enter =
=3D
a
location. It has no idea if the location I enter is spelled wrong, or =
=3D
worse,
no such place. I could enter
Miami, Miami-Dade County, Floridi, USA and it would be happy. That =3D
bothered
me. After researching how the program stored its dates (in Access), I =
=3D
soon
realized that if the location is already in file, the program looks for =
=3D
it
first. If it finds it, it accepts it. If it does not find it, it =3D
questions
the user. I thought it would be wonderful to have such a =3D
super-comprehensive
database of locations, both current and valid locations and wells as
locations that no long exist, but did at one time, and therefore when I
enter where someone was born, married or died, it checks immediately. =
=3D
For
the example above, it would tell me that Miami, Miami-Dade County, =3D
Floridi,
USA is NOT in the list. Do you wish to add to list, change entry, or =3D
cancel?
That will stop me, and I will look closer- oops I misspelled it, and =3D
correct
it. By the same token, a place I have never heard of because it no =3D
longer
exists is already in the database, so the program accepts it and I know =
=3D
it
is valid. "Bureau, Logan County, Kansas" is one I had not heard of, but =
=3D
this
database has.

I am limiting this database to simply American locations and Canadian
locations. It would be too much hard drive space to list all such places
globally.  So far, those are the only two countries where my ancestors =
=3D
are
born. I do have some in Scotland, but just five at the moment. If /when =
=3D
I
get several hundred from Scotland, I will add all the Scottish locations =
=3D
I
can find.

Another reason I like this:  If I enter someone who died in Wayne Twp., =
=3D
Wick
County, Ohio, it already has that location in the database along with =
=3D
its
latitude and longitude.  Then I might go, "where the heck is Wayne Twp.
anyway?" and since the latitude and longitude are part of the =3D
information, I
can my genealogy program open up a mapping site and go there for me. =3D
(For
those interested, Wayne Twp. Existed 1890 through 1905 officially.

How big? *BIG.* The file lists over seven million locations in North
America. The zipped file is 827,506K !! Unzipped is several times that, =
=3D
but
I deleted that. I broke it up into 26 smaller files, and importing them =
=3D
as
files of 20.6K (about 20,654K). I hope to be done this week. James, this
software is available to anyone who wants to buy it. What I *thought* I
could do easily list the locations having the same latitude and =3D
longitude as
other locations and remove the duplicates.....  but some locations are
dually named. Oil Springs Methodist Church and Oil Springs Methodist
Cemetery have the same latitude and longitude. So I wanted them both- I =
=3D
can
list an ancestor buried there, and I can also indicate an ancestor who =
=3D
was
married / baptized there.

Still, there are duplications- and I wanted to remove them carefully. =
=3D
MIT
can be Massachusetts Institute of Technology, or Methodist Internal =3D
Training
center.  Only by looking at their locations do I know which it refers =
=3D
to. In
this case, I want both MITs. But I do not want seven OCHS, simply =3D
because
the "real" name is spelled seven different ways, six being incorrect. I =
=3D
just
want the correct one, so I deleted the six incorrect ones.

This is not an easy or fast job. Nonetheless, I have been working on it
since last March (2006), but I hope to finish by end of February this =
=3D
year.
I am anxious to use it!

If you want more information, please let me know. I hope this was not =
=3D
too
long.

Robert

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On =3D
Behalf Of
James LaBorde
Sent: 2007-01-08 17:37
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access Error: Unparsable Record :VSMail mx6

Robert,

Geology got my attention!  What kind of project are you working on with =
=3D
=3D3D
Geology? (I am a rockhound so geology always catches my attention!)  As =
=3D
=3D3D
for the size of the file being an issue, which version of Access are you =
=3D
=3D3D
using and just how large is the file you are trying to import?  Also how =
=3D
=3D3D
large is your Access file to begin with? =3D3D20

James

-strip-

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx =
=3D
or MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the =
=3D
dropdown menu at the top.  This will allow you to unsubscribe your email =
=3D
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

If you are using Outlook and you see a lot of unnecessary code in your =
=3D
email messages, read these instructions that explain why and how to fix =
=3D
it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
This message contains information that may be confidential and is =3D
intended only for the use of the=3D20
person/persons identified and intended as the recipient. If you are not =
=3D
the intended recipient, any=3D20
disclosure, copying, distribution or use of this information by anyone =
=3D
else is strictly prohibited.
If you have received this in error, please contact the sender =3D
immediately and delete the message=3D20
from your computer.
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx =
or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top.  This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

If you are using Outlook and you see a lot of unnecessary code in your =
email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx =
or MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the =
dropdown menu at the top.  This will allow you to unsubscribe your email =
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

If you are using Outlook and you see a lot of unnecessary code in your =
email messages, read these instructions that explain why and how to fix =
it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
This message contains information that may be confidential and is =
intended only for the use of the=20
person/persons identified and intended as the recipient. If you are not =
the intended recipient, any=20
disclosure, copying, distribution or use of this information by anyone =
else is strictly prohibited.
If you have received this in error, please contact the sender =
immediately and delete the message=20
from your computer.
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

Other related posts: