|
 |
access thread: Access 2000: TransferText Function Problem
Message #1 by "Maqsood Ahmed" <maqsood2000@h...> on Tue, 26 Feb 2002 23:18:33
|
|
Hi,
I have converted Access 97 based code to Access 2000. I am having problem
on TransferText function (even it works fine with Access 97 Version). The
following code line giving me error.
DoCmd.TransferText acImportfixed, "NEWwinrpts.spc", "Audit", file_name
and the Error is:
Error 3442: In the Text of file 'NEWwinrpts.spc" The "LINE" option is
invalid.
Actually LINE is a column in Audit table but somehow after conversion it
is not working. Also the amazing thing is that i didn't find the SPC file
anywhere on the system or even in the Access database but somehow code is
trying to read from it (Even Access97 code working fine on this line and
find this spc file as well). I tried to go into the File->Import and
advance and there is no SPC file. Even i tried to change the SPC file name
then the system gives error that "SPC file doesn't exist".
It would be really appreciated if anybody can help me in this regard.
Thanks,
Message #2 by "John Ruff" <papparuff@c...> on Tue, 26 Feb 2002 15:32:17 -0800
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0039_01C1BEDA.C6D83290
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Hopefully you have a backup of your Access 97 database. When you upsize
to Access2K or AccessXP, any file specs that were created for the
Access97 database ARE NOT imported. (That is what NEWwinrpts.spc is).
They must be imported manually.
Follow these steps to import.
1. File > Get External Data > Import
2. Select the old Access97 database.
3. When the "Import Options" window opens, select the last button on the
right "Options >>"
4. From the "Import" group select Import/Export Specs.
If you don't have a backup of the Access97 database, you are going to
have to recreate the file spec.
John Ruff - The Eternal Optimist J
Always looking for Contract Opportunities
9306 Farwest Dr SW
Lakewood, WA 98498
papparuff@c...
-----Original Message-----
From: Maqsood Ahmed [mailto:maqsood2000@h...]
Sent: Tuesday, February 26, 2002 11:19 PM
To: Access
Subject: [access] Access 2000: TransferText Function Problem
Hi,
I have converted Access 97 based code to Access 2000. I am having
problem
on TransferText function (even it works fine with Access 97 Version).
The
following code line giving me error.
DoCmd.TransferText acImportfixed, "NEWwinrpts.spc", "Audit", file_name
and the Error is:
Error 3442: In the Text of file 'NEWwinrpts.spc" The "LINE" option is
invalid.
Actually LINE is a column in Audit table but somehow after conversion it
is not working. Also the amazing thing is that i didn't find the SPC
file
anywhere on the system or even in the Access database but somehow code
is
trying to read from it (Even Access97 code working fine on this line and
find this spc file as well). I tried to go into the File->Import and
advance and there is no SPC file. Even i tried to change the SPC file
name
then the system gives error that "SPC file doesn't exist".
It would be really appreciated if anybody can help me in this regard.
Thanks,
Message #3 by "Maqsood Ahmed" <maqsood2000@h...> on Wed, 27 Feb 2002 15:58:04
|
|
Thanks Jhon,
I got it but the problem is that in the "NEWwinrpt.spc" is defined like
that:
Field Name Data Type Start Width
-----------------------------------------------------------
CALLID Text 1 9
LINE Text 1 3
STARTTIME Text 4 6
The above specification works with Access 2.0 ( and it save even both LINE
and CALLID field starts with 1) but with Access 2000 it is going to be
failed on the second line LINE (in TransferText function :
DoCmd.TransferText A_IMPORTFIXED, "NEWwinrpts.spc", "Audit", file_name
)
and gives the following error.
In the text file specification 'NEWWinrpts.spc', the 'LINE' option is
invalide. (Error 3442)
So, the problem is that Access 2 or Access 97 works ok with TransferText
even specification file start with the same starting point for two fields
but not works with Access 2000.
Even i tried to change the starting point for two field and the code works
ok (but definately data is not correct in report) and then i tried to
change it back then Access 2000 denied to save it and gave me error that
Fields overlapped.
Please help me in this regards It would be really appreciated.
Thanks,
Message #4 by "John Ruff" <papparuff@c...> on Wed, 27 Feb 2002 08:26:35 -0800
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0037_01C1BF68.78CE3B10
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
It's been a while since I created a file spec but I'm sure that the
start should be the character position. Try changing your spec to:
Field Name Data Type Start Width
-----------------------------------------------------------
CALLID Text 1 9
LINE Text 10 3
STARTTIME Text 13 6
John Ruff - The Eternal Optimist J
Always looking for Contract Opportunities
9306 Farwest Dr SW
Lakewood, WA 98498
papparuff@c...
-----Original Message-----
From: Maqsood Ahmed [mailto:maqsood2000@h...]
Sent: Wednesday, February 27, 2002 3:58 PM
To: Access
Subject: [access] RE: Access 2000: TransferText Function Problem
Thanks Jhon,
I got it but the problem is that in the "NEWwinrpt.spc" is defined like
that:
Field Name Data Type Start Width
-----------------------------------------------------------
CALLID Text 1 9
LINE Text 1 3
STARTTIME Text 4 6
The above specification works with Access 2.0 ( and it save even both
LINE
and CALLID field starts with 1) but with Access 2000 it is going to be
failed on the second line LINE (in TransferText function :
DoCmd.TransferText A_IMPORTFIXED, "NEWwinrpts.spc", "Audit", file_name
)
and gives the following error.
In the text file specification 'NEWWinrpts.spc', the 'LINE' option is
invalide. (Error 3442)
So, the problem is that Access 2 or Access 97 works ok with TransferText
even specification file start with the same starting point for two
fields
but not works with Access 2000.
Even i tried to change the starting point for two field and the code
works
ok (but definately data is not correct in report) and then i tried to
change it back then Access 2000 denied to save it and gave me error that
Fields overlapped.
Please help me in this regards It would be really appreciated.
Thanks,
Message #5 by "Maqsood Ahmed" <maqsood2000@h...> on Thu, 28 Feb 2002 15:10:59
|
|
Hi John!
Its works fine after that but the problem is that the code comes from
Access 2 and it accepts same start point in Access 2. So the text file
designed to start both line start from the same point and its not work
with Access 2000. I can't change the format of text file as it is
generated by other application.
Can I find any article that describe that why Access 2000 doesn't support
it now.
Thanks,
Maqsood Ahmed
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0037_01C1BF68.78CE3B10
> Content-Type: text/plain;
> charset="us-ascii"
> Content-Transfer-Encoding: 7bit
>
> It's been a while since I created a file spec but I'm sure that the
> start should be the character position. Try changing your spec to:
>
> Field Name Data Type Start Width
> -----------------------------------------------------------
> CALLID Text 1 9
> LINE Text 10 3
> STARTTIME Text 13 6
>
>
>
> John Ruff - The Eternal Optimist J
>
> Always looking for Contract Opportunities
>
>
>
> 9306 Farwest Dr SW
>
> Lakewood, WA 98498
>
> papparuff@c...
Message #6 by "John Ruff" <papparuff@c...> on Thu, 28 Feb 2002 08:02:59 -0800
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0020_01C1C02E.57007180
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Magsood,
I don't understand the problem.
Let's say you have a text file with the following data:
111John Ruff02:30p
112Mags Ahmd04:00a
123Joan Arc 11:00p
The CallID is stored in columns 1-3
The Line is stored in columns 4-12
The StartTime is stored in columns 12-18
The file spec in Access2K must be created to read the above data in the
following manner.
Field Name Data Type Start Width
-----------------------------------------------------------
LINE Text 1 3
CALLID Text 4 9
STARTTIME Text 13 6
Once you import the data to your table, the records will look like this:
LINE CALLID STARTTIME
---------------------------------
111 John Ruff 02:30p
112 Mags Ahmd 04:00a
123 Joan Arc 11:00p
Is this what the data is suppose to look like?
John Ruff - The Eternal Optimist J
Always looking for Contract Opportunities
9306 Farwest Dr SW
Lakewood, WA 98498
papparuff@c...
-----Original Message-----
From: Maqsood Ahmed [mailto:maqsood2000@h...]
Sent: Thursday, February 28, 2002 3:11 PM
To: Access
Subject: [access] RE: Access 2000: TransferText Function Problem
Hi John!
Its works fine after that but the problem is that the code comes from
Access 2 and it accepts same start point in Access 2. So the text file
designed to start both line start from the same point and its not work
with Access 2000. I can't change the format of text file as it is
generated by other application.
Can I find any article that describe that why Access 2000 doesn't
support
it now.
Thanks,
Maqsood Ahmed
Message #7 by "Maqsood Ahmed" <maqsood2000@h...> on Thu, 28 Feb 2002 17:29:53
|
|
Thanks Jhon!
Let me explain again. Like you describe:
111John Ruff02:30p
112Mags Ahmd04:00a
123Joan Arc 11:00p
The Line is stored in columns 1-3
The CallID is stored in columns 1-9 (Problem is here)
The StartTime is stored in columns 12-18
The file spec in Access2K must be created to read the above data in the
following manner.
Field Name Data Type Start Width
-----------------------------------------------------------
LINE Text 1 3
CALLID Text 1 9
STARTTIME Text 13 6
so the data would be like this:
LINE CALLID STARTTIME
---------------------------------
111 111John Ruff 02:30p
112 111Mags Ahmd 04:00a
123 111Joan Arc 11:00p
Because the other program generate it like that and the program which I am
running needs CALLID like that what I explained.
Hope this explains.
I have posted two more issues regarding Access if you have any clue please
do let me know.
Thanks, Jhon
Maqsood Ahmed
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0020_01C1C02E.57007180
> Content-Type: text/plain;
> charset="US-ASCII"
> Content-Transfer-Encoding: 7bit
>
> Magsood,
>
> I don't understand the problem.
> Let's say you have a text file with the following data:
>
> 111John Ruff02:30p
> 112Mags Ahmd04:00a
> 123Joan Arc 11:00p
>
> The CallID is stored in columns 1-3
> The Line is stored in columns 4-12
> The StartTime is stored in columns 12-18
>
> The file spec in Access2K must be created to read the above data in the
> following manner.
>
> Field Name Data Type Start Width
> -----------------------------------------------------------
> LINE Text 1 3
> CALLID Text 4 9
> STARTTIME Text 13 6
>
> Once you import the data to your table, the records will look like this:
>
> LINE CALLID STARTTIME
> ---------------------------------
> 111 John Ruff 02:30p
> 112 Mags Ahmd 04:00a
> 123 Joan Arc 11:00p
>
> Is this what the data is suppose to look like?
>
>
>
> John Ruff - The Eternal Optimist J
>
> Always looking for Contract Opportunities
>
>
>
> 9306 Farwest Dr SW
>
> Lakewood, WA 98498
>
> papparuff@c...
Message #8 by "John Ruff" <papparuff@c...> on Thu, 28 Feb 2002 10:21:01 -0800
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0001_01C1C041.9F3FCB90
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Now I understand.
Here's what you need to do.
1. Setup the file spec as I have described already.
2. Import the file into a temp table
3. Create an Append query from the temp table. Your query will look
something like this:
INSERT INTO YourTable ([YourTable].[Line], [YourTable].[CallID],
[YourTable].[StartTime])
SELECT tblImportTemp.Line, [Line] & [CallID] AS CallerID,
tblImportTemp.StartTime
FROM tblImportTemp;
4. Run the query to update the table.
John Ruff - The Eternal Optimist J
Always looking for Contract Opportunities
9306 Farwest Dr SW
Lakewood, WA 98498
papparuff@c...
-----Original Message-----
From: Maqsood Ahmed [mailto:maqsood2000@h...]
Sent: Thursday, February 28, 2002 5:30 PM
To: Access
Subject: [access] RE: Access 2000: TransferText Function Problem
Thanks Jhon!
Let me explain again. Like you describe:
111John Ruff02:30p
112Mags Ahmd04:00a
123Joan Arc 11:00p
The Line is stored in columns 1-3
The CallID is stored in columns 1-9 (Problem is here)
The StartTime is stored in columns 12-18
The file spec in Access2K must be created to read the above data in the
following manner.
Field Name Data Type Start Width
-----------------------------------------------------------
LINE Text 1 3
CALLID Text 1 9
STARTTIME Text 13 6
so the data would be like this:
LINE CALLID STARTTIME
---------------------------------
111 111John Ruff 02:30p
112 111Mags Ahmd 04:00a
123 111Joan Arc 11:00p
Because the other program generate it like that and the program which I
am
running needs CALLID like that what I explained.
Hope this explains.
I have posted two more issues regarding Access if you have any clue
please
do let me know.
Thanks, Jhon
Maqsood Ahmed
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0020_01C1C02E.57007180
> Content-Type: text/plain;
> charset="US-ASCII"
> Content-Transfer-Encoding: 7bit
>
> Magsood,
>
> I don't understand the problem.
> Let's say you have a text file with the following data:
>
> 111John Ruff02:30p
> 112Mags Ahmd04:00a
> 123Joan Arc 11:00p
>
> The CallID is stored in columns 1-3
> The Line is stored in columns 4-12
> The StartTime is stored in columns 12-18
>
> The file spec in Access2K must be created to read the above data in
> the following manner.
>
> Field Name Data Type Start Width
> -----------------------------------------------------------
> LINE Text 1 3
> CALLID Text 4 9
> STARTTIME Text 13 6
>
> Once you import the data to your table, the records will look like
> this:
>
> LINE CALLID STARTTIME
> ---------------------------------
> 111 John Ruff 02:30p
> 112 Mags Ahmd 04:00a
> 123 Joan Arc 11:00p
>
> Is this what the data is suppose to look like?
>
>
>
> John Ruff - The Eternal Optimist J
>
> Always looking for Contract Opportunities
>
>
>
> 9306 Farwest Dr SW
>
> Lakewood, WA 98498
>
> papparuff@c...
|
|
|
View Cart
