Importing data regularly

I want to import data regularly (the import format will always be the same)
and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then displays the
standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation with the
number of records imported displayed in the message.

I have got it to work using an import specification (on the click event of
the command button) :

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can anyone help?

Andy

For 1, see http://www.mvps.org/access/api/api0001.htm at "The Access Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be the same)
and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then displays the
standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation with the
number of records imported displayed in the message.

I have got it to work using an import specification (on the click event of
the command button) :

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can anyone help?

Andy

Thanks Doug for the quick response. I'm not really that up with VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the button? Where
does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The Access Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then displays the
standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation with the
number of records imported displayed in the message.

I have got it to work using an import specification (on the click event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can anyone
help?

Andy

Copy all of the shaded code (what's between Code Start and Code End), and
paste it into a new module. When you save the module, name it something like
"mdlFileDialog" (the name of the module isn't really critical, other than
the fact that you cannot name the module the same as any of the functions or
subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The Access Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then displays the
standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation with the
number of records imported displayed in the message.

I have got it to work using an import specification (on the click event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can anyone
help?

Andy

Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows (they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No problem,
however when I import the file the message I receive is correct with the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have something to
do with number of decimal places. Any clues? It also imports the first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in message
...
Copy all of the shaded code (what's between Code Start and Code End), and
paste it into a new module. When you save the module, name it something
like "mdlFileDialog" (the name of the module isn't really critical, other
than the fact that you cannot name the module the same as any of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then displays
the standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the click event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can anyone
help?

Andy

"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows (they
are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No problem,
however when I import the file the message I receive is correct with the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have something
to
do with number of decimal places. Any clues? It also imports the first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in message
...
Copy all of the shaded code (what's between Code Start and Code End), and
paste it into a new module. When you save the module, name it something
like "mdlFileDialog" (the name of the module isn't really critical, other
than the fact that you cannot name the module the same as any of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then displays
the standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the click
event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can anyone
help?

Andy

Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the 4
decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set to 4, but
it doesn't seem to work, even if I change the type to decimal.

Andy


"Douglas J. Steele" wrote in message
...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows (they
are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No problem,
however when I import the file the message I receive is correct with the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have something
to
do with number of decimal places. Any clues? It also imports the first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in message
...
Copy all of the shaded code (what's between Code Start and Code End),
and
paste it into a new module. When you save the module, name it something
like "mdlFileDialog" (the name of the module isn't really critical,
other
than the fact that you cannot name the module the same as any of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with VBA.
I
kind of understand the process.

I presume I need to place the code on the click event of the button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in
message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then displays
the standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the click
event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can anyone
help?

Andy

Long Integers are integers. Integers do not have decimal value: they're,
well, um, integers! g

Try changing the fields to Singles or Doubles or Decimals.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the 4
decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set to 4,
but it doesn't seem to work, even if I change the type to decimal.

Andy


"Douglas J. Steele" wrote in message
...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows (they
are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No problem,
however when I import the file the message I receive is correct with the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have something
to
do with number of decimal places. Any clues? It also imports the first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in message
...
Copy all of the shaded code (what's between Code Start and Code End),
and
paste it into a new module. When you save the module, name it something
like "mdlFileDialog" (the name of the module isn't really critical,
other
than the fact that you cannot name the module the same as any of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with VBA.
I
kind of understand the process.

I presume I need to place the code on the click event of the button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in
message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then displays
the standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the click
event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can anyone
help?

Andy

Oops - Sorry Doug!

My mistake (of course integers are whole numbers duh!) The way I meant to
word it was that it doesn't matter which filed type I use, the import get
condensed to whole numbers and misses the decimal places.

Andy

"Douglas J. Steele" wrote in message
...
Long Integers are integers. Integers do not have decimal value: they're,
well, um, integers! g

Try changing the fields to Singles or Doubles or Decimals.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the 4
decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set to 4,
but it doesn't seem to work, even if I change the type to decimal.

Andy


"Douglas J. Steele" wrote in message
...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows
(they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No problem,
however when I import the file the message I receive is correct with
the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have
something to
do with number of decimal places. Any clues? It also imports the
first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in
message
...
Copy all of the shaded code (what's between Code Start and Code End),
and
paste it into a new module. When you save the module, name it
something
like "mdlFileDialog" (the name of the module isn't really critical,
other
than the fact that you cannot name the module the same as any of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with VBA.
I
kind of understand the process.

I presume I need to place the code on the click event of the button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in
message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be
the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then
displays
the standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation
with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the click
event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can
anyone
help?

Andy

I've never heard of that before.

Look in the folder where the text file exists to see whether there's a file
schema.ini. If there is, look inside it to see whether it's got incorrect
definitions.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
news
Oops - Sorry Doug!

My mistake (of course integers are whole numbers duh!) The way I meant to
word it was that it doesn't matter which filed type I use, the import get
condensed to whole numbers and misses the decimal places.

Andy

"Douglas J. Steele" wrote in message
...
Long Integers are integers. Integers do not have decimal value: they're,
well, um, integers! g

Try changing the fields to Singles or Doubles or Decimals.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the 4
decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set to 4,
but it doesn't seem to work, even if I change the type to decimal.

Andy


"Douglas J. Steele" wrote in message
...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows
(they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No
problem,
however when I import the file the message I receive is correct with
the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have
something to
do with number of decimal places. Any clues? It also imports the
first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in
message
...
Copy all of the shaded code (what's between Code Start and Code End),
and
paste it into a new module. When you save the module, name it
something
like "mdlFileDialog" (the name of the module isn't really critical,
other
than the fact that you cannot name the module the same as any of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with
VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in
message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The
Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be
the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then
displays
the standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation
with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the click
event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can
anyone
help?

Andy