Import delimeter file into existing table?

[Niklas Östergren]

Hi!

I have tryed to import a delimeter textfile into an existing table i A2003
with following command:

DoCmd.TransferText acImportDelim, , "tblPerson", "C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

But when I run this code I get a runtime error 2391 (Field F1 doesn´t exist
in tabel). How do I use this method to import a delimeter textfile into a
table that allready exist and have it´s field configurated?

TIA!
// Niklas

 

[Ken Snell [MVP]]

You don't post the details about the specification, but I'm guessing that
the table into which you are trying to import the data does not have a field
named F1, but the spec is telling ACCESS to create its own primary key field
(which ACCESS will name as F1).

 

[Niklas Östergren]

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
....... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01
00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;;
;;0;0;
3;;"Fredrik";"Hansson";;1973-03-10 00:00:00;;"Man";;;;0;;;0;;;;;0;0;
4;1;"Petra";"Wingård";;1974-06-12 00:00:00;;"Kvinna";1;;;0;;;0;;;;;0;0;
5;;"Rolf";"Carlsson";;;;"Man";;;;0;;;0;;;;;0;0;

So yes you are right I don´t have any field named F1 in my table. So why is
Access trying to look for one if I have specifyed which table to import the
text to. Shouldn´t it be enought with the semicolon to tell in which field
to import the text to?

Thanks for helping out!
// Niklas

 

[Ken Snell [MVP]]

The F1 field is coming from the import specification, details about which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01
00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;;
;;0;0;
3;;"Fredrik";"Hansson";;1973-03-10 00:00:00;;"Man";;;;0;;;0;;;;;0;0;
4;1;"Petra";"Wingård";;1974-06-12 00:00:00;;"Kvinna";1;;;0;;;0;;;;;0;0;
5;;"Rolf";"Carlsson";;;;"Man";;;;0;;;0;;;;;0;0;

So yes you are right I don´t have any field named F1 in my table. So why is
Access trying to look for one if I have specifyed which table to import the
text to. Shouldn´t it be enought with the semicolon to tell in which field
to import the text to?

Thanks for helping out!
// Niklas

You don't post the details about the specification, but I'm guessing that
the table into which you are trying to import the data does not have a field
named F1, but the spec is telling ACCESS to create its own primary key field
(which ACCESS will name as F1).

into

a

 

[Niklas Östergren]

Aha, I get it!

This is the optional part which I left out. But how am I going to use this
to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow here. I
just can´t understand how to apply this on my example?

// Niklas

The F1 field is coming from the import specification, details about which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;;

;;0;0;
3;;"Fredrik";"Hansson";;1973-03-10 00:00:00;;"Man";;;;0;;;0;;;;;0;0;
4;1;"Petra";"Wingård";;1974-06-12 00:00:00;;"Kvinna";1;;;0;;;0;;;;;0;0;
5;;"Rolf";"Carlsson";;;;"Man";;;;0;;;0;;;;;0;0;

So yes you are right I don´t have any field named F1 in my table. So why is
Access trying to look for one if I have specifyed which table to import the
text to. Shouldn´t it be enought with the semicolon to tell in which field
to import the text to?

Thanks for helping out!
// Niklas


into

 

[Ken Snell [MVP]]

I'm sorry, I don't understand. "Optional part which [you] left out"? What
specifically is the question?

--

Ken Snell
<MS ACCESS MVP>

Aha, I get it!

This is the optional part which I left out. But how am I going to use this
to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow here. I
just can´t understand how to apply this on my example?

// Niklas

The F1 field is coming from the import specification, details about which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;;

why
is import
the

 

[Rosco]

Count the max number of delimiters that is in your text file, and make sure
your table has sufficient number of fields.

Looking at the above you have null values after your final delimiter. Is
there a field in the table?

Does your text file have the field names? Try manually importing the file.
If you have extra data in your text file, the wizzard will show it to you.

Rosco

Aha, I get it!

This is the optional part which I left out. But how am I going to use this
to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow here. I
just can´t understand how to apply this on my example?

// Niklas

The F1 field is coming from the import specification, details about which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;;

;;0;0;
3;;"Fredrik";"Hansson";;1973-03-10 00:00:00;;"Man";;;;0;;;0;;;;;0;0;
4;1;"Petra";"Wingård";;1974-06-12 00:00:00;;"Kvinna";1;;;0;;;0;;;;;0;0;
5;;"Rolf";"Carlsson";;;;"Man";;;;0;;;0;;;;;0;0;

So yes you are right I don´t have any field named F1 in my table. So why is
Access trying to look for one if I have specifyed which table to import the
text to. Shouldn´t it be enought with the semicolon to tell in which field
to import the text to?

Thanks for helping out!
// Niklas


"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
You don't post the details about the specification, but I'm guessing that
the table into which you are trying to import the data does not have a
field
named F1, but the spec is telling ACCESS to create its own primary key
field
(which ACCESS will name as F1).


--

Ken Snell
<MS ACCESS MVP>

Hi!

I have tryed to import a delimeter textfile into an existing table i
A2003
with following command:

DoCmd.TransferText acImportDelim, , "tblPerson",
"C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

But when I run this code I get a runtime error 2391 (Field F1 doesn´t
exist
in tabel). How do I use this method to import a delimeter textfile into
a
table that allready exist and have it´s field configurated?

TIA!
// Niklas

 

[Niklas Östergren]

This is the syntax for DoCmd.TransferText:

TransferText([TransferType As AcTextTransferType = acImportDelim],
[SpecificationName], [TableName], [FileName], [HasFieldNames],
[HTMLTableName], [CodePage]

And in helpfile it says that [SpecificationName] is optional
(SpecificationName Optional Variant. A string expression that's the name of
an ...)

And I left it out in my code:

DoCmd.TransferText acImportDelim, , "tblPerson", "C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

Mostly because I don´t understand how to use it or what´s good with it. So I
guess that this is my Q. How shall I use it and what´s it good for?

I´m sorry for the inconvenience of me beeing unclear with my Q.

Thank´s for puting in so much time into my problem anyway. I do appreciate
it!

// Niklas

I'm sorry, I don't understand. "Optional part which [you] left out"? What
specifically is the question?

--

Ken Snell
<MS ACCESS MVP>

Aha, I get it!

This is the optional part which I left out. But how am I going to use this
to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow here. I
just can´t understand how to apply this on my example?

// Niklas

The F1 field is coming from the import specification, details about which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;; have

a

table

i

 

[Niklas Östergren]

Hi Rosco!

I have tryed that and it works just great. No problem there!

You see right now I´m just doing this to try this method out to see if this
is what I may use or if I have to try another way of doing this. So the data
which is in the textfile was created from the verry beginning from tblPerson
where I used DoCmd.TransferText acDelimeterExport to create the textfile
from the verry beginning. And now I´m trying, without changing anything in
the textfile, to import it back again. The only thing I have done is to
delete the records in tblPerson.

Thank´s for your help!
// Niklas

Count the max number of delimiters that is in your text file, and make sure
your table has sufficient number of fields.

Looking at the above you have null values after your final delimiter. Is
there a field in the table?

Does your text file have the field names? Try manually importing the file.
If you have extra data in your text file, the wizzard will show it to you.

Rosco

Aha, I get it!

This is the optional part which I left out. But how am I going to use this
to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow here. I
just can´t understand how to apply this on my example?

// Niklas

The F1 field is coming from the import specification, details about which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;;

;;0;0;
3;;"Fredrik";"Hansson";;1973-03-10 00:00:00;;"Man";;;;0;;;0;;;;;0;0;
4;1;"Petra";"Wingård";;1974-06-12 00:00:00;;"Kvinna";1;;;0;;;0;;;;;0;0;
5;;"Rolf";"Carlsson";;;;"Man";;;;0;;;0;;;;;0;0;

So yes you are right I don´t have any field named F1 in my table. So why
is
Access trying to look for one if I have specifyed which table to import
the
text to. Shouldn´t it be enought with the semicolon to tell in which field
to import the text to?

Thanks for helping out!
// Niklas


"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
You don't post the details about the specification, but I'm guessing
that
the table into which you are trying to import the data does not have a
field
named F1, but the spec is telling ACCESS to create its own primary key
field
(which ACCESS will name as F1).


--

Ken Snell
<MS ACCESS MVP>

Hi!

I have tryed to import a delimeter textfile into an existing table i
A2003
with following command:

DoCmd.TransferText acImportDelim, , "tblPerson",
"C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

But when I run this code I get a runtime error 2391 (Field F1 doesn´t
exist
in tabel). How do I use this method to import a delimeter textfile
into
a
table that allready exist and have it´s field configurated?

TIA!
// Niklas

 

[Ken Snell [MVP]]

OK - Now I understand.

The problem you're having is that you're trying to import data into a table
that has an autonumber field as the first field, and your text file does not
have that field in its record. Your text file begins with the field that is
the second field in the table. Thus, ACCESS is "off by one field" for your
data, as it's using the first field in your text file to put data into the
autonumber data field.

Import the data into a temporary table, and then use an append query to copy
the imported data into your permanent table.
--

Ken Snell
<MS ACCESS MVP>

This is the syntax for DoCmd.TransferText:

TransferText([TransferType As AcTextTransferType = acImportDelim],
[SpecificationName], [TableName], [FileName], [HasFieldNames],
[HTMLTableName], [CodePage]

And in helpfile it says that [SpecificationName] is optional
(SpecificationName Optional Variant. A string expression that's the name of
an ...)

And I left it out in my code:

DoCmd.TransferText acImportDelim, , "tblPerson", "C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

Mostly because I don´t understand how to use it or what´s good with it. So I
guess that this is my Q. How shall I use it and what´s it good for?

I´m sorry for the inconvenience of me beeing unclear with my Q.

Thank´s for puting in so much time into my problem anyway. I do appreciate
it!

// Niklas

I'm sorry, I don't understand. "Optional part which [you] left out"? What
specifically is the question?

--

Ken Snell
<MS ACCESS MVP>

Aha, I get it!

This is the optional part which I left out. But how am I going to use this
to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow

here.

I

just can´t understand how to apply this on my example?

// Niklas


"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
The F1 field is coming from the import specification, details about which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;;

So
why have primary
key table

 

[Niklas Östergren]

If I export the data with fieldnames and then import it setting
[HasFieldNames] = True then everything works great!

So I guess that I got to have fieldnames in the textfile when I´m importing
to an existing table!

Thank´s for helping out!
// Niklas

Count the max number of delimiters that is in your text file, and make sure
your table has sufficient number of fields.

Looking at the above you have null values after your final delimiter. Is
there a field in the table?

Does your text file have the field names? Try manually importing the file.
If you have extra data in your text file, the wizzard will show it to you.

Rosco

Aha, I get it!

This is the optional part which I left out. But how am I going to use this
to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow here. I
just can´t understand how to apply this on my example?

// Niklas

The F1 field is coming from the import specification, details about which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;;

;;0;0;
3;;"Fredrik";"Hansson";;1973-03-10 00:00:00;;"Man";;;;0;;;0;;;;;0;0;
4;1;"Petra";"Wingård";;1974-06-12 00:00:00;;"Kvinna";1;;;0;;;0;;;;;0;0;
5;;"Rolf";"Carlsson";;;;"Man";;;;0;;;0;;;;;0;0;

So yes you are right I don´t have any field named F1 in my table. So why
is
Access trying to look for one if I have specifyed which table to import
the
text to. Shouldn´t it be enought with the semicolon to tell in which field
to import the text to?

Thanks for helping out!
// Niklas


"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
You don't post the details about the specification, but I'm guessing
that
the table into which you are trying to import the data does not have a
field
named F1, but the spec is telling ACCESS to create its own primary key
field
(which ACCESS will name as F1).


--

Ken Snell
<MS ACCESS MVP>

Hi!

I have tryed to import a delimeter textfile into an existing table i
A2003
with following command:

DoCmd.TransferText acImportDelim, , "tblPerson",
"C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

But when I run this code I get a runtime error 2391 (Field F1 doesn´t
exist
in tabel). How do I use this method to import a delimeter textfile
into
a
table that allready exist and have it´s field configurated?

TIA!
// Niklas

 

[Niklas Östergren]

OK!

So that´s why I got the runtime error!

Thank´s for explaining and beeing patiant with me ;-)

Thank´s a lot Ken!

// Niklas

OK - Now I understand.

The problem you're having is that you're trying to import data into a table
that has an autonumber field as the first field, and your text file does not
have that field in its record. Your text file begins with the field that is
the second field in the table. Thus, ACCESS is "off by one field" for your
data, as it's using the first field in your text file to put data into the
autonumber data field.

Import the data into a temporary table, and then use an append query to copy
the imported data into your permanent table.
--

Ken Snell
<MS ACCESS MVP>

This is the syntax for DoCmd.TransferText:

TransferText([TransferType As AcTextTransferType = acImportDelim],
[SpecificationName], [TableName], [FileName], [HasFieldNames],
[HTMLTableName], [CodePage]

And in helpfile it says that [SpecificationName] is optional
(SpecificationName Optional Variant. A string expression that's the

name

of
an ...)

And I left it out in my code:

DoCmd.TransferText acImportDelim, , "tblPerson", "C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

Mostly because I don´t understand how to use it or what´s good with it.

So

I
guess that this is my Q. How shall I use it and what´s it good for?

I´m sorry for the inconvenience of me beeing unclear with my Q.

Thank´s for puting in so much time into my problem anyway. I do appreciate
it!

// Niklas

I'm sorry, I don't understand. "Optional part which [you] left out"? What
specifically is the question?

--

Ken Snell
<MS ACCESS MVP>

Aha, I get it!

This is the optional part which I left out. But how am I going to

use
this

to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow

here.

I
just can´t understand how to apply this on my example?

// Niklas


"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
The F1 field is coming from the import specification, details about
which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;; table.

So not
have

 

[Niklas Östergren]

Is there a limitation of how many fields it´s possible to import to from a
delimeter textfile?

I have gót it to work but added another field in my table which I´m
importing to. And I also added the same fieldname on the textfile and added
som data by adding a new semicolon (corresponding to the new field name) and
some data in quoutes.

I now hav 23 fields which I´d like to import to my table. But now everything
is imported accept the last newly added field. If I try to import it
manually (not using any VBA code) then the data will be imported.

I have tryed to export the data and creating a new texfile which I then
import back again. And the data with the fieldname is in the exported
texfile but when I import it back again the field in the table is empty.

Any clue of why this happens or what I can do about it?

TIA!
// Niklas

OK!

So that´s why I got the runtime error!

Thank´s for explaining and beeing patiant with me ;-)

Thank´s a lot Ken!

// Niklas

OK - Now I understand.

The problem you're having is that you're trying to import data into a table
that has an autonumber field as the first field, and your text file does not
have that field in its record. Your text file begins with the field that is
the second field in the table. Thus, ACCESS is "off by one field" for your
data, as it's using the first field in your text file to put data into the
autonumber data field.

Import the data into a temporary table, and then use an append query to copy
the imported data into your permanent table.
--

Ken Snell
<MS ACCESS MVP>

This is the syntax for DoCmd.TransferText:

TransferText([TransferType As AcTextTransferType = acImportDelim],
[SpecificationName], [TableName], [FileName], [HasFieldNames],
[HTMLTableName], [CodePage]

And in helpfile it says that [SpecificationName] is optional
(SpecificationName Optional Variant. A string expression that's the

name

of
an ...)

And I left it out in my code:

DoCmd.TransferText acImportDelim, , "tblPerson", "C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

Mostly because I don´t understand how to use it or what´s good with

it.

So

I
guess that this is my Q. How shall I use it and what´s it good for?

I´m sorry for the inconvenience of me beeing unclear with my Q.

Thank´s for puting in so much time into my problem anyway. I do appreciate
it!

// Niklas



"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
I'm sorry, I don't understand. "Optional part which [you] left out"? What
specifically is the question?

--

Ken Snell
<MS ACCESS MVP>

Aha, I get it!

This is the optional part which I left out. But how am I going to use
this
to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow here.
I
just can´t understand how to apply this on my example?

// Niklas


"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
The F1 field is coming from the import specification, details about
which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;;

table.

 

[Ken Snell [MVP]]

255 fields is the limit for a single record.

My guess is that there is a miscount of fields between your table and the
text file.

--

Ken Snell
<MS ACCESS MVP>

Is there a limitation of how many fields it´s possible to import to from a
delimeter textfile?

I have gót it to work but added another field in my table which I´m
importing to. And I also added the same fieldname on the textfile and added
som data by adding a new semicolon (corresponding to the new field name) and
some data in quoutes.

I now hav 23 fields which I´d like to import to my table. But now everything
is imported accept the last newly added field. If I try to import it
manually (not using any VBA code) then the data will be imported.

I have tryed to export the data and creating a new texfile which I then
import back again. And the data with the fieldname is in the exported
texfile but when I import it back again the field in the table is empty.

Any clue of why this happens or what I can do about it?

TIA!
// Niklas

OK!

So that´s why I got the runtime error!

Thank´s for explaining and beeing patiant with me ;-)

Thank´s a lot Ken!

// Niklas

OK - Now I understand.

The problem you're having is that you're trying to import data into a table
that has an autonumber field as the first field, and your text file

does
not

have that field in its record. Your text file begins with the field

that
is

the second field in the table. Thus, ACCESS is "off by one field" for your
data, as it's using the first field in your text file to put data into the
autonumber data field.

Import the data into a temporary table, and then use an append query

to
copy

the imported data into your permanent table.
--

Ken Snell
<MS ACCESS MVP>



This is the syntax for DoCmd.TransferText:

TransferText([TransferType As AcTextTransferType = acImportDelim],
[SpecificationName], [TableName], [FileName], [HasFieldNames],
[HTMLTableName], [CodePage]

And in helpfile it says that [SpecificationName] is optional
(SpecificationName Optional Variant. A string expression that's the name
of
an ...)

And I left it out in my code:

DoCmd.TransferText acImportDelim, , "tblPerson", "C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

Mostly because I don´t understand how to use it or what´s good with

it.

So
I
guess that this is my Q. How shall I use it and what´s it good for?

I´m sorry for the inconvenience of me beeing unclear with my Q.

Thank´s for puting in so much time into my problem anyway. I do appreciate
it!

// Niklas



"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
I'm sorry, I don't understand. "Optional part which [you] left out"?
What
specifically is the question?

--

Ken Snell
<MS ACCESS MVP>

Aha, I get it!

This is the optional part which I left out. But how am I going

to
use

this
to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow
here.
I
just can´t understand how to apply this on my example?

// Niklas


"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet
The F1 field is coming from the import specification, details about
which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;; table

to does
not (Field
F1

 

[Niklas Östergren]

Thank´s a lot Ken!

You where right about miscount of foelds in the textfile. Now I´ve got
everything to work so now I know that this is my method of importing the
data that repeatedly are going to be imported to my application.

Now I just have to work this out with the guy who have done the web-page and
make him to produce a textfile with the data and automaticly send it with
e-mail.

Thank´s a lot Ken! You have realy been helpful and I want you to know that I
realy apreciate it!

// Niklas

255 fields is the limit for a single record.

My guess is that there is a miscount of fields between your table and the
text file.

--

Ken Snell
<MS ACCESS MVP>

Is there a limitation of how many fields it´s possible to import to from
a
delimeter textfile?

I have gót it to work but added another field in my table which I´m
importing to. And I also added the same fieldname on the textfile and added
som data by adding a new semicolon (corresponding to the new field name) and
some data in quoutes.

I now hav 23 fields which I´d like to import to my table. But now everything
is imported accept the last newly added field. If I try to import it
manually (not using any VBA code) then the data will be imported.

I have tryed to export the data and creating a new texfile which I then
import back again. And the data with the fieldname is in the exported
texfile but when I import it back again the field in the table is empty.

Any clue of why this happens or what I can do about it?

TIA!
// Niklas

OK!

So that´s why I got the runtime error!

Thank´s for explaining and beeing patiant with me ;-)

Thank´s a lot Ken!

// Niklas


"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet
OK - Now I understand.

The problem you're having is that you're trying to import data into a
table
that has an autonumber field as the first field, and your text file does
not
have that field in its record. Your text file begins with the field that
is
the second field in the table. Thus, ACCESS is "off by one field" for your
data, as it's using the first field in your text file to put data
into the
autonumber data field.

Import the data into a temporary table, and then use an append query to
copy
the imported data into your permanent table.
--

Ken Snell
<MS ACCESS MVP>



This is the syntax for DoCmd.TransferText:

TransferText([TransferType As AcTextTransferType = acImportDelim],
[SpecificationName], [TableName], [FileName], [HasFieldNames],
[HTMLTableName], [CodePage]

And in helpfile it says that [SpecificationName] is optional
(SpecificationName Optional Variant. A string expression that's
the
name
of
an ...)

And I left it out in my code:

DoCmd.TransferText acImportDelim, , "tblPerson",
"C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

Mostly because I don´t understand how to use it or what´s good with it.
So
I
guess that this is my Q. How shall I use it and what´s it good for?

I´m sorry for the inconvenience of me beeing unclear with my Q.

Thank´s for puting in so much time into my problem anyway. I do
appreciate
it!

// Niklas



"Ken Snell [MVP]" <[email protected]> skrev i meddelandet
I'm sorry, I don't understand. "Optional part which [you] left out"?
What
specifically is the question?

--

Ken Snell
<MS ACCESS MVP>

Aha, I get it!

This is the optional part which I left out. But how am I going to
use
this
to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow
here.
I
just can´t understand how to apply this on my example?

// Niklas


"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet
The F1 field is coming from the import specification, details
about
which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;;

;;0;0;
3;;"Fredrik";"Hansson";;1973-03-10
00:00:00;;"Man";;;;0;;;0;;;;;0;0;
4;1;"Petra";"Wingård";;1974-06-12
00:00:00;;"Kvinna";1;;;0;;;0;;;;;0;0;
5;;"Rolf";"Carlsson";;;;"Man";;;;0;;;0;;;;;0;0;

So yes you are right I don´t have any field named F1 in my
table.
So
why
is
Access trying to look for one if I have specifyed which

table

to
import
the
text to. Shouldn´t it be enought with the semicolon to tell in
which
field
to import the text to?

Thanks for helping out!
// Niklas


"Ken Snell [MVP]" <[email protected]> skrev
i
meddelandet
You don't post the details about the specification, but I'm
guessing
that
the table into which you are trying to import the data does
not
have
a
field
named F1, but the spec is telling ACCESS to create its
own
primary
key
field
(which ACCESS will name as F1).


--

Ken Snell
<MS ACCESS MVP>

message
Hi!

I have tryed to import a delimeter textfile into an existing
table
i
A2003
with following command:

DoCmd.TransferText acImportDelim, ,
"tblPerson",
"C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

But when I run this code I get a runtime error 2391 (Field
F1
doesn´t
exist
in tabel). How do I use this method to import a delimeter
textfile
into
a
table that allready exist and have it´s field configurated?

TIA!
// Niklas

 

[Ken Snell [MVP]]

You're welcome. Good luck.
--

Ken Snell
<MS ACCESS MVP>

Thank´s a lot Ken!

You where right about miscount of foelds in the textfile. Now I´ve got
everything to work so now I know that this is my method of importing the
data that repeatedly are going to be imported to my application.

Now I just have to work this out with the guy who have done the web-page and
make him to produce a textfile with the data and automaticly send it with
e-mail.

Thank´s a lot Ken! You have realy been helpful and I want you to know that I
realy apreciate it!

// Niklas

255 fields is the limit for a single record.

My guess is that there is a miscount of fields between your table and the
text file.

--

Ken Snell
<MS ACCESS MVP>

Is there a limitation of how many fields it´s possible to import to from
a
delimeter textfile?

I have gót it to work but added another field in my table which I´m
importing to. And I also added the same fieldname on the textfile and added
som data by adding a new semicolon (corresponding to the new field

name)
and

some data in quoutes.

I now hav 23 fields which I´d like to import to my table. But now everything
is imported accept the last newly added field. If I try to import it
manually (not using any VBA code) then the data will be imported.

I have tryed to export the data and creating a new texfile which I then
import back again. And the data with the fieldname is in the exported
texfile but when I import it back again the field in the table is empty.

Any clue of why this happens or what I can do about it?

TIA!
// Niklas


"Niklas Östergren" <[email protected]> skrev i meddelandet
OK!

So that´s why I got the runtime error!

Thank´s for explaining and beeing patiant with me ;-)

Thank´s a lot Ken!

// Niklas


"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet
OK - Now I understand.

The problem you're having is that you're trying to import data into a
table
that has an autonumber field as the first field, and your text file does
not
have that field in its record. Your text file begins with the field that
is
the second field in the table. Thus, ACCESS is "off by one field" for
your
data, as it's using the first field in your text file to put data
into
the
autonumber data field.

Import the data into a temporary table, and then use an append

query
to

copy
the imported data into your permanent table.
--

Ken Snell
<MS ACCESS MVP>



This is the syntax for DoCmd.TransferText:

TransferText([TransferType As AcTextTransferType = acImportDelim],
[SpecificationName], [TableName], [FileName], [HasFieldNames],
[HTMLTableName], [CodePage]

And in helpfile it says that [SpecificationName] is optional
(SpecificationName Optional Variant. A string expression that's
the
name
of
an ...)

And I left it out in my code:

DoCmd.TransferText acImportDelim, , "tblPerson",
"C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

Mostly because I don´t understand how to use it or what´s good with
it.
So
I
guess that this is my Q. How shall I use it and what´s it good for?

I´m sorry for the inconvenience of me beeing unclear with my Q.

Thank´s for puting in so much time into my problem anyway. I do
appreciate
it!

// Niklas



"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet
I'm sorry, I don't understand. "Optional part which [you] left out"?
What
specifically is the question?

--

Ken Snell
<MS ACCESS MVP>

Aha, I get it!

This is the optional part which I left out. But how am I

going
to

use
this
to specify how my table looks like?

I have looked in help file for this but I may be a little bit slow
here.
I
just can´t understand how to apply this on my example?

// Niklas


"Ken Snell [MVP]" <[email protected]> skrev i
meddelandet
The F1 field is coming from the import specification, details
about
which
you did not post....

--

Ken Snell
<MS ACCESS MVP>

Sorry!

My table looks like this:

tblPerson
[PersonID]: AutoNumber, Primary key
[fkNewMemberShipEntryID]: Long integer
[FirstName]: String of max 30 charachters
[LastName]: String of max 30 charachters
...... etc.


An the textfile looks like this:


2;;"Niklas";"Östergren";"Nicke";1965-05-01

00:00:00;"6979";"Man";1;"#http://www.google.se/#";"Systemavdelning";0;;;0;;;

;;0;0;
3;;"Fredrik";"Hansson";;1973-03-10
00:00:00;;"Man";;;;0;;;0;;;;;0;0;
4;1;"Petra";"Wingård";;1974-06-12
00:00:00;;"Kvinna";1;;;0;;;0;;;;;0;0;
5;;"Rolf";"Carlsson";;;;"Man";;;;0;;;0;;;;;0;0;

So yes you are right I don´t have any field named F1 in my
table.
So
why
is
Access trying to look for one if I have specifyed which table
to
import
the
text to. Shouldn´t it be enought with the semicolon to

tell
in

which
field
to import the text to?

Thanks for helping out!
// Niklas


"Ken Snell [MVP]" <[email protected]> skrev
i
meddelandet
You don't post the details about the specification, but I'm
guessing
that
the table into which you are trying to import the data does
not
have
a
field
named F1, but the spec is telling ACCESS to create its
own
primary
key
field
(which ACCESS will name as F1).


--

Ken Snell
<MS ACCESS MVP>

message
Hi!

I have tryed to import a delimeter textfile into an
existing
table
i
A2003
with following command:

DoCmd.TransferText acImportDelim, ,
"tblPerson",
"C:/Program/Tuffa
Viktor/ExportedDocs/tblPerson.txt", False

But when I run this code I get a runtime error 2391 (Field
F1
doesn´t
exist
in tabel). How do I use this method to import a delimeter
textfile
into
a
table that allready exist and have it´s field
configurated?

TIA!
// Niklas