|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hi All,
I'm a newbie at access but have been doing hardware IT for computers
for about 15 years.
I've got a client that is using MS Access for a database that is
multiuser in the office, but in the field (6 supervisors) is is a
single user database.
The supervisors enter the new information or updated information and
twice a month they perform an 'export' to the main office to update
the main database. This export is in the form of two txt files.
The txt files are integrated into the main database with an 'import'
function written by the access programmer. After the six supervisor
updates are integrated the office then performs an 'export' that is
sent back to the 6 supervisors in the form of two txt files.
The problem i'm seeing is that unless the old txt files are erased
from the supervisor's database and then the new files are placed
in the 'import folder' and the 'import files procedure' is performed
the supervisors are not getting all the information. I have seen
all the supervisors get the same txt files but the database will
be missing from 1 to 50 records depending on the supervisor.
The way I found to make all the supervisors have the same record count
has been to have them erase the old 'import files' from the computer
before copying the new files to the database and performing the
import.
We have been using the following method of copying the files:
1. select all
2. copy
3. move to the database folder
4. paste and say overwirte the files if needed.
I hope I've explained this good enough, has anyone got any
ideas about why the record count would vary, even through all the
supervisors are getting the same two txt files but not erasing the old
files before doing the import??
thanks,
Bob Smith
Robert Smith Consulting
|
|
How is the export/import done?
The three things I would look for would be
1) Human error
2) EOF characters in the text file.
3) File size not updated correctly on export.
(david)
"Bob Smith" <na6t[ at ]na6t.com> wrote in message
news:84sqd2hqcqimqg6vvv5b35ajd5bmf4ei8f[ at ]4ax.com...
[Quoted Text] > Hi All,
>
> I'm a newbie at access but have been doing hardware IT for computers
> for about 15 years.
>
> I've got a client that is using MS Access for a database that is
> multiuser in the office, but in the field (6 supervisors) is is a
> single user database.
>
> The supervisors enter the new information or updated information and
> twice a month they perform an 'export' to the main office to update
> the main database. This export is in the form of two txt files.
>
> The txt files are integrated into the main database with an 'import'
> function written by the access programmer. After the six supervisor
> updates are integrated the office then performs an 'export' that is
> sent back to the 6 supervisors in the form of two txt files.
>
> The problem i'm seeing is that unless the old txt files are erased
> from the supervisor's database and then the new files are placed
> in the 'import folder' and the 'import files procedure' is performed
> the supervisors are not getting all the information. I have seen
> all the supervisors get the same txt files but the database will
> be missing from 1 to 50 records depending on the supervisor.
>
> The way I found to make all the supervisors have the same record count
> has been to have them erase the old 'import files' from the computer
> before copying the new files to the database and performing the
> import.
>
> We have been using the following method of copying the files:
> 1. select all
> 2. copy
> 3. move to the database folder
> 4. paste and say overwirte the files if needed.
>
> I hope I've explained this good enough, has anyone got any
> ideas about why the record count would vary, even through all the
> supervisors are getting the same two txt files but not erasing the old
> files before doing the import??
>
> thanks,
>
> Bob Smith
> Robert Smith Consulting
>
|
|
Hi David,
Thats for the reply. Your answers are just what I'm checking this
weekend. I'm getting no help from the Access programmer, she's
blaming DOS for the problem (actually she is blaming the computers
for giving the wrong answer). IE my work.
The human error has been eliminated on the supervisor side because I
have walked each on through the import/export on the phone to be sure
it was done correctly.
The fields in the text file are comma delimited, see below
"CGID","SSNo","ComID","CGFirst Name",
"CGLastName","Region","ShortName","Mailing Address"
The EOF character looks correct.
The file size looks right, but I saw this happen on the last export
from a supervisor:
I checked the file when it came in and it was missing data that was
suppose to be in the file. I called the supervisor and had her open
the text file that she sent from her notebook. the data was in that
file, but missing from the file that I received. I had the supervisor
send the same email again and lo and behold the second time she sent
the email the correct files were received. go figure?
all of the PC's have about 80-100 different exports/imports saved in
OUTLOOK 2003 divided between the 'sent folder' for exports and the
'INBOX" for imports. I've been looking at the way OUTLOOK 2k3 stores
attachments ( this is how the files are sent). The attachment is part
of one big PST file in the c:\documents and setting\name\application
data\temp settings.
I'm starting to think that maybe becuse of the amount of 'file
attachments' stored in this PST file maybe DOS is getting into the
picture.
My next diagnostic check is going to have the supervisors same the PST
file into a backup file, then go through the INBOX and SENT folders
and delete all Emails with attachments used for the Import and Export
functions. Then have them do an EXPORT and see if I receive the
correct files.
I'm in a little over my head because of the non-help of the
programmer, but at least I can eliminatte what might NOT be causing
the problem-- kinda like CYA,,, like I said, I'm a hardware person
when it comes to Access, not a programmer. I've got way to many
clients to start a 'new field for programming access'.
My first choice in the mess would be to convert it to an SQL
databases, install terminal services and have them update the database
like most people do.
oh welll,,
Bob Smith
Robert Smith Consulting
On Sat, 19 Aug 2006 22:43:25 +1000, <david[ at ]epsomdotcomdotau> wrote:
[Quoted Text] >How is the export/import done?
>
>The three things I would look for would be
>
>1) Human error
>
>2) EOF characters in the text file.
>
>3) File size not updated correctly on export.
>
>(david)
>
>"Bob Smith" <na6t[ at ]na6t.com> wrote in message
>news:84sqd2hqcqimqg6vvv5b35ajd5bmf4ei8f[ at ]4ax.com...
>> Hi All,
>>
>> I'm a newbie at access but have been doing hardware IT for computers
>> for about 15 years.
>>
>> I've got a client that is using MS Access for a database that is
>> multiuser in the office, but in the field (6 supervisors) is is a
>> single user database.
>>
>> The supervisors enter the new information or updated information and
>> twice a month they perform an 'export' to the main office to update
>> the main database. This export is in the form of two txt files.
>>
>> The txt files are integrated into the main database with an 'import'
>> function written by the access programmer. After the six supervisor
>> updates are integrated the office then performs an 'export' that is
>> sent back to the 6 supervisors in the form of two txt files.
>>
>> The problem i'm seeing is that unless the old txt files are erased
>> from the supervisor's database and then the new files are placed
>> in the 'import folder' and the 'import files procedure' is performed
>> the supervisors are not getting all the information. I have seen
>> all the supervisors get the same txt files but the database will
>> be missing from 1 to 50 records depending on the supervisor.
>>
>> The way I found to make all the supervisors have the same record count
>> has been to have them erase the old 'import files' from the computer
>> before copying the new files to the database and performing the
>> import.
>>
>> We have been using the following method of copying the files:
>> 1. select all
>> 2. copy
>> 3. move to the database folder
>> 4. paste and say overwirte the files if needed.
>>
>> I hope I've explained this good enough, has anyone got any
>> ideas about why the record count would vary, even through all the
>> supervisors are getting the same two txt files but not erasing the old
>> files before doing the import??
>>
>> thanks,
>>
>> Bob Smith
>> Robert Smith Consulting
>>
>
|
|
I'm probably a bit out of my depth here, but I think that you can utilize
terminal services without migrating the database to SQL. We use Citrix to
update a shared .mdb file every day. My understanding is that terminal
services is similar to citrix. If your client will spring for Terminal
Services, just make sure each user has a separate front end and that nobody
ever opens the back end directly. The users will like the arrangement a lot
better than a cumbersome import/export process, too.
Mike
Bob Smith wrote:
[Quoted Text] >Hi David,
>
>Thats for the reply. Your answers are just what I'm checking this
>weekend. I'm getting no help from the Access programmer, she's
>blaming DOS for the problem (actually she is blaming the computers
>for giving the wrong answer). IE my work.
>
>The human error has been eliminated on the supervisor side because I
>have walked each on through the import/export on the phone to be sure
>it was done correctly.
>
>The fields in the text file are comma delimited, see below
>
>"CGID","SSNo","ComID","CGFirst Name",
>"CGLastName","Region","ShortName","Mailing Address"
>
>The EOF character looks correct.
>
>The file size looks right, but I saw this happen on the last export
>from a supervisor:
>
>I checked the file when it came in and it was missing data that was
>suppose to be in the file. I called the supervisor and had her open
>the text file that she sent from her notebook. the data was in that
>file, but missing from the file that I received. I had the supervisor
>send the same email again and lo and behold the second time she sent
>the email the correct files were received. go figure?
>
>all of the PC's have about 80-100 different exports/imports saved in
>OUTLOOK 2003 divided between the 'sent folder' for exports and the
>'INBOX" for imports. I've been looking at the way OUTLOOK 2k3 stores
>attachments ( this is how the files are sent). The attachment is part
>of one big PST file in the c:\documents and setting\name\application
>data\temp settings.
>
>I'm starting to think that maybe becuse of the amount of 'file
>attachments' stored in this PST file maybe DOS is getting into the
>picture.
>
>My next diagnostic check is going to have the supervisors same the PST
>file into a backup file, then go through the INBOX and SENT folders
>and delete all Emails with attachments used for the Import and Export
>functions. Then have them do an EXPORT and see if I receive the
>correct files.
>
>I'm in a little over my head because of the non-help of the
>programmer, but at least I can eliminatte what might NOT be causing
>the problem-- kinda like CYA,,, like I said, I'm a hardware person
>when it comes to Access, not a programmer. I've got way to many
>clients to start a 'new field for programming access'.
>
>My first choice in the mess would be to convert it to an SQL
>databases, install terminal services and have them update the database
>like most people do.
>
>oh welll,,
>
>Bob Smith
>Robert Smith Consulting
>
>>How is the export/import done?
>>
>[quoted text clipped - 53 lines]
>>> Bob Smith
>>> Robert Smith Consulting
--
"We have met the enemy and he is us." -- Pogo Possum
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-multiuser/200608/1
|
|
So the export was not complete when the user attached
and sent the first mail message? How is the import/export
done? What is the code used for creating the export file?
From your description, it could be a mail problem, not a
Windows/Access problem. There are lots of places in a
store-and-forward mail system where a mail message could
get corrupted. Note that all Exchange Admins agree that
PST files are evil, but I never heard of a problem like this.
My PST files are 300MB, and I've never had a problem.
Get her to forward the original bad mail message again.
If it failed during transmission, it will come good the
second time. If it's still bad, the file was corrupt when it
was added to the mail client database (PST).
Note that Windows (not 'DOS') does file copy as a
background task, asynchronous to the file copy command.
If that is the cause of your problem, you may need to turn
off write caching.
(david)
"Bob Smith" <na6t[ at ]na6t.com> wrote in message
news:gipve29rgii6bqcrhoqc178bumomoclrah[ at ]4ax.com...
[Quoted Text] > Hi David,
>
> Thats for the reply. Your answers are just what I'm checking this
> weekend. I'm getting no help from the Access programmer, she's
> blaming DOS for the problem (actually she is blaming the computers
> for giving the wrong answer). IE my work.
>
> The human error has been eliminated on the supervisor side because I
> have walked each on through the import/export on the phone to be sure
> it was done correctly.
>
> The fields in the text file are comma delimited, see below
>
> "CGID","SSNo","ComID","CGFirst Name",
> "CGLastName","Region","ShortName","Mailing Address"
>
>
> The EOF character looks correct.
>
> The file size looks right, but I saw this happen on the last export
> from a supervisor:
>
> I checked the file when it came in and it was missing data that was
> suppose to be in the file. I called the supervisor and had her open
> the text file that she sent from her notebook. the data was in that
> file, but missing from the file that I received. I had the supervisor
> send the same email again and lo and behold the second time she sent
> the email the correct files were received. go figure?
>
> all of the PC's have about 80-100 different exports/imports saved in
> OUTLOOK 2003 divided between the 'sent folder' for exports and the
> 'INBOX" for imports. I've been looking at the way OUTLOOK 2k3 stores
> attachments ( this is how the files are sent). The attachment is part
> of one big PST file in the c:\documents and setting\name\application
> data\temp settings.
>
> I'm starting to think that maybe becuse of the amount of 'file
> attachments' stored in this PST file maybe DOS is getting into the
> picture.
>
> My next diagnostic check is going to have the supervisors same the PST
> file into a backup file, then go through the INBOX and SENT folders
> and delete all Emails with attachments used for the Import and Export
> functions. Then have them do an EXPORT and see if I receive the
> correct files.
>
> I'm in a little over my head because of the non-help of the
> programmer, but at least I can eliminatte what might NOT be causing
> the problem-- kinda like CYA,,, like I said, I'm a hardware person
> when it comes to Access, not a programmer. I've got way to many
> clients to start a 'new field for programming access'.
>
> My first choice in the mess would be to convert it to an SQL
> databases, install terminal services and have them update the database
> like most people do.
>
> oh welll,,
>
>
> Bob Smith
> Robert Smith Consulting
>
>
>
>
>
>
> On Sat, 19 Aug 2006 22:43:25 +1000, <david[ at ]epsomdotcomdotau> wrote:
>
> >How is the export/import done?
> >
> >The three things I would look for would be
> >
> >1) Human error
> >
> >2) EOF characters in the text file.
> >
> >3) File size not updated correctly on export.
> >
> >(david)
> >
> >"Bob Smith" <na6t[ at ]na6t.com> wrote in message
> >news:84sqd2hqcqimqg6vvv5b35ajd5bmf4ei8f[ at ]4ax.com...
> >> Hi All,
> >>
> >> I'm a newbie at access but have been doing hardware IT for computers
> >> for about 15 years.
> >>
> >> I've got a client that is using MS Access for a database that is
> >> multiuser in the office, but in the field (6 supervisors) is is a
> >> single user database.
> >>
> >> The supervisors enter the new information or updated information and
> >> twice a month they perform an 'export' to the main office to update
> >> the main database. This export is in the form of two txt files.
> >>
> >> The txt files are integrated into the main database with an 'import'
> >> function written by the access programmer. After the six supervisor
> >> updates are integrated the office then performs an 'export' that is
> >> sent back to the 6 supervisors in the form of two txt files.
> >>
> >> The problem i'm seeing is that unless the old txt files are erased
> >> from the supervisor's database and then the new files are placed
> >> in the 'import folder' and the 'import files procedure' is performed
> >> the supervisors are not getting all the information. I have seen
> >> all the supervisors get the same txt files but the database will
> >> be missing from 1 to 50 records depending on the supervisor.
> >>
> >> The way I found to make all the supervisors have the same record count
> >> has been to have them erase the old 'import files' from the computer
> >> before copying the new files to the database and performing the
> >> import.
> >>
> >> We have been using the following method of copying the files:
> >> 1. select all
> >> 2. copy
> >> 3. move to the database folder
> >> 4. paste and say overwirte the files if needed.
> >>
> >> I hope I've explained this good enough, has anyone got any
> >> ideas about why the record count would vary, even through all the
> >> supervisors are getting the same two txt files but not erasing the old
> >> files before doing the import??
> >>
> >> thanks,
> >>
> >> Bob Smith
> >> Robert Smith Consulting
> >>
> >
>
|
|
Hi david
I talked to the access programmer over the weekend. She said that she
would get me the import/export code to me by Monday nite. I'll post
it then, maybe you can see something,
thnaks for the interest and help,,
Bob Smith
Robert Smith Consutling
On Fri, 11 Aug 2006 23:19:25 -0700, Bob Smith <na6t[ at ]na6t.com> wrote:
[Quoted Text] >Hi All,
>
>I'm a newbie at access but have been doing hardware IT for computers
>for about 15 years.
>
>I've got a client that is using MS Access for a database that is
>multiuser in the office, but in the field (6 supervisors) is is a
>single user database.
>
>The supervisors enter the new information or updated information and
>twice a month they perform an 'export' to the main office to update
>the main database. This export is in the form of two txt files.
>
>The txt files are integrated into the main database with an 'import'
>function written by the access programmer. After the six supervisor
>updates are integrated the office then performs an 'export' that is
>sent back to the 6 supervisors in the form of two txt files.
>
>The problem i'm seeing is that unless the old txt files are erased
>from the supervisor's database and then the new files are placed
>in the 'import folder' and the 'import files procedure' is performed
>the supervisors are not getting all the information. I have seen
>all the supervisors get the same txt files but the database will
>be missing from 1 to 50 records depending on the supervisor.
>
>The way I found to make all the supervisors have the same record count
>has been to have them erase the old 'import files' from the computer
>before copying the new files to the database and performing the
>import.
>
>We have been using the following method of copying the files:
>1. select all
>2. copy
>3. move to the database folder
>4. paste and say overwirte the files if needed.
>
>I hope I've explained this good enough, has anyone got any
>ideas about why the record count would vary, even through all the
>supervisors are getting the same two txt files but not erasing the old
>files before doing the import??
>
>thanks,
>
>Bob Smith
>Robert Smith Consulting
|
|
Hi All,
I'm sorry for taking so long in getting the final information about
this problem, but I've got it fixed and below is what I found in
compleing the fix.
1. first the database was being updated from the remote offices and
back to the remote offices via e-mail attachements (5).
2. When I checked the office Outlook 2003 I found 219 e-mails from
6 remote offices with the same attachment names.
3. When I checked the remote office notebooks I found about 100
e-mails to the main office with the same attachment names
4. Outlook 2003 stores the e-mail in one big PST file (attachments
included). The attachment is only extracted when you highlite
the attachement and then copy it or view it. When this happens
a hidden directory is created (you have to find the name of the
directory via the registry , which is a trick in itself) and the
directory is deleted when the e-mail is closed.
5. what I finally did was have the supervisors delete all the e-mails
with the attachments and I did the same thing with the office
email. Then I backed up the e-mail for each supervisor and
did the final deletion of the e-mails from the deleted folder in
outlook 2003.
6. I then had the supervisors do the export to the office. Before
their export was created I made sure that the folder that the
export was created in was empty, no files.
7. when the export was received at the office I made sure that
the folder that the txt files were moved to was empty before
'pasting' the txt files.
8. the import was done and everything was perfect.
The only conclusion I can come to is that because of the amount of
e-mails with the same attachements somehow Outlook 2003 was getting
confused with what attachment was created last and what txt file
should be attached to the email.
I've done a full export and import twice and all the data is corrrect,
so I've told everyone doing the import export to be sure to delete the
old e-mails and be sure all directories are empty before doing the
import-export functions with Access.
I hope I've explained this so everyone can understand what happened, I
guess this is one way to update the access database from remote
offices, but it sure would be a lot easier to do it via terminal
services because everyone has hi-speed and it would only take a minute
or two.
Bob Smith
Robert Smith Consulting
On Fri, 11 Aug 2006 23:19:25 -0700, Bob Smith <na6t[ at ]na6t.com> wrote:
[Quoted Text] >Hi All,
>
>I'm a newbie at access but have been doing hardware IT for computers
>for about 15 years.
>
>I've got a client that is using MS Access for a database that is
>multiuser in the office, but in the field (6 supervisors) is is a
>single user database.
>
>The supervisors enter the new information or updated information and
>twice a month they perform an 'export' to the main office to update
>the main database. This export is in the form of two txt files.
>
>The txt files are integrated into the main database with an 'import'
>function written by the access programmer. After the six supervisor
>updates are integrated the office then performs an 'export' that is
>sent back to the 6 supervisors in the form of two txt files.
>
>The problem i'm seeing is that unless the old txt files are erased
>from the supervisor's database and then the new files are placed
>in the 'import folder' and the 'import files procedure' is performed
>the supervisors are not getting all the information. I have seen
>all the supervisors get the same txt files but the database will
>be missing from 1 to 50 records depending on the supervisor.
>
>The way I found to make all the supervisors have the same record count
>has been to have them erase the old 'import files' from the computer
>before copying the new files to the database and performing the
>import.
>
>We have been using the following method of copying the files:
>1. select all
>2. copy
>3. move to the database folder
>4. paste and say overwirte the files if needed.
>
>I hope I've explained this good enough, has anyone got any
>ideas about why the record count would vary, even through all the
>supervisors are getting the same two txt files but not erasing the old
>files before doing the import??
>
>thanks,
>
>Bob Smith
>Robert Smith Consulting
|
|
Hi All,
I'm sorry for taking so long in getting the final information about
this problem, but I've got it fixed and below is what I found in
compleing the fix.
1. first the database was being updated from the remote offices and
back to the remote offices via e-mail attachements (5).
2. When I checked the office Outlook 2003 I found 219 e-mails from
6 remote offices with the same attachment names.
3. When I checked the remote office notebooks I found about 100
e-mails to the main office with the same attachment names
4. Outlook 2003 stores the e-mail in one big PST file (attachments
included). The attachment is only extracted when you highlite
the attachement and then copy it or view it. When this happens
a hidden directory is created (you have to find the name of the
directory via the registry , which is a trick in itself) and the
directory is deleted when the e-mail is closed.
5. what I finally did was have the supervisors delete all the e-mails
with the attachments and I did the same thing with the office
email. Then I backed up the e-mail for each supervisor and
did the final deletion of the e-mails from the deleted folder in
outlook 2003.
6. I then had the supervisors do the export to the office. Before
their export was created I made sure that the folder that the
export was created in was empty, no files.
7. when the export was received at the office I made sure that
the folder that the txt files were moved to was empty before
'pasting' the txt files.
8. the import was done and everything was perfect.
The only conclusion I can come to is that because of the amount of
e-mails with the same attachements somehow Outlook 2003 was getting
confused with what attachment was created last and what txt file
should be attached to the email.
I've done a full export and import twice and all the data is corrrect,
so I've told everyone doing the import export to be sure to delete the
old e-mails and be sure all directories are empty before doing the
import-export functions with Access.
I hope I've explained this so everyone can understand what happened, I
guess this is one way to update the access database from remote
offices, but it sure would be a lot easier to do it via terminal
services because everyone has hi-speed and it would only take a minute
or two.
Bob Smith
Robert Smith Consulting
On Fri, 11 Aug 2006 23:19:25 -0700, Bob Smith <na6t[ at ]na6t.com> wrote:
On Fri, 11 Aug 2006 23:19:25 -0700, Bob Smith <na6t[ at ]na6t.com> wrote:
[Quoted Text] >Hi All,
>
>I'm a newbie at access but have been doing hardware IT for computers
>for about 15 years.
>
>I've got a client that is using MS Access for a database that is
>multiuser in the office, but in the field (6 supervisors) is is a
>single user database.
>
>The supervisors enter the new information or updated information and
>twice a month they perform an 'export' to the main office to update
>the main database. This export is in the form of two txt files.
>
>The txt files are integrated into the main database with an 'import'
>function written by the access programmer. After the six supervisor
>updates are integrated the office then performs an 'export' that is
>sent back to the 6 supervisors in the form of two txt files.
>
>The problem i'm seeing is that unless the old txt files are erased
>from the supervisor's database and then the new files are placed
>in the 'import folder' and the 'import files procedure' is performed
>the supervisors are not getting all the information. I have seen
>all the supervisors get the same txt files but the database will
>be missing from 1 to 50 records depending on the supervisor.
>
>The way I found to make all the supervisors have the same record count
>has been to have them erase the old 'import files' from the computer
>before copying the new files to the database and performing the
>import.
>
>We have been using the following method of copying the files:
>1. select all
>2. copy
>3. move to the database folder
>4. paste and say overwirte the files if needed.
>
>I hope I've explained this good enough, has anyone got any
>ideas about why the record count would vary, even through all the
>supervisors are getting the same two txt files but not erasing the old
>files before doing the import??
>
>thanks,
>
>Bob Smith
>Robert Smith Consulting
|
|
|