Importing text

[Guest]

I need to import a text file into a table with an autonumber index so the
data order can be maintained through the subsequent manipulations. Importing
manually, the wizard gives you the choice of adding an access index or not.
Programmatically, this choice seems to have disappeared. Is there a way to
import text into a table using an import spec (already set up) and still get
access to add it's own index? Alternatively, can I add an autonumber index to
the table once it's created, or do I need to use a shell table where I delete
all rows and import into it each time?

I've tried adding a GUID field after the import, but of course it doesn't
autopopulate. Do I just have to write the code to populate it?

 

[Allen Browne]

Assuming the field names/types don't change, the best idea is your shell
table.

To empty Table1, and then import programatically:
Dim db As DAO.Database
Dim strSql As String
Set db = dbEngine(0)(0)
strSql = "DELETE FROM [Table1];"
db.Exeute strSql, dbFailOnError
strSql = "INSERT INTO ...
db.Exeute strSql, dbFailOnError
Set db = Nothing

Adding an AutoNumber manually after the import should work, but is less
clean than setting up the table you want exactly as you want it.

 

[Guest]

Here's the problem I'm finding: I'm using Transfertext to import a text
file. I have import specifications, with all the index checkboxes turned
off. But I'm finding that the imported files have indexes on some fields and
throw off the order of the input file, which must be maintained. At that
point, it's too late to append the data into a table with an autotext because
the records are already out of order.

Doing it manually with File | Import... works fine, even without having
Access build the autonumber index for me.

Any ideas?

Assuming the field names/types don't change, the best idea is your shell
table.

To empty Table1, and then import programatically:
Dim db As DAO.Database
Dim strSql As String
Set db = dbEngine(0)(0)
strSql = "DELETE FROM [Table1];"
db.Exeute strSql, dbFailOnError
strSql = "INSERT INTO ...
db.Exeute strSql, dbFailOnError
Set db = Nothing

Adding an AutoNumber manually after the import should work, but is less
clean than setting up the table you want exactly as you want it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I need to import a text file into a table with an autonumber index so the
data order can be maintained through the subsequent manipulations.
Importing
manually, the wizard gives you the choice of adding an access index or
not.
Programmatically, this choice seems to have disappeared. Is there a way
to
import text into a table using an import spec (already set up) and still
get
access to add it's own index? Alternatively, can I add an autonumber index
to
the table once it's created, or do I need to use a shell table where I
delete
all rows and import into it each time?

I've tried adding a GUID field after the import, but of course it doesn't
autopopulate. Do I just have to write the code to populate it?

 

[Allen Browne]

Go to:
Tools | Options | Tables/Queries
and delete everything from the box:
AutoIndex on Import/Create

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Here's the problem I'm finding: I'm using Transfertext to import a text
file. I have import specifications, with all the index checkboxes turned
off. But I'm finding that the imported files have indexes on some fields
and
throw off the order of the input file, which must be maintained. At that
point, it's too late to append the data into a table with an autotext
because
the records are already out of order.

Doing it manually with File | Import... works fine, even without having
Access build the autonumber index for me.

Any ideas?

Assuming the field names/types don't change, the best idea is your shell
table.

To empty Table1, and then import programatically:
Dim db As DAO.Database
Dim strSql As String
Set db = dbEngine(0)(0)
strSql = "DELETE FROM [Table1];"
db.Exeute strSql, dbFailOnError
strSql = "INSERT INTO ...
db.Exeute strSql, dbFailOnError
Set db = Nothing

Adding an AutoNumber manually after the import should work, but is less
clean than setting up the table you want exactly as you want it.

I need to import a text file into a table with an autonumber index so
the
data order can be maintained through the subsequent manipulations.
Importing
manually, the wizard gives you the choice of adding an access index or
not.
Programmatically, this choice seems to have disappeared. Is there a
way
to
import text into a table using an import spec (already set up) and
still
get
access to add it's own index? Alternatively, can I add an autonumber
index
to
the table once it's created, or do I need to use a shell table where I
delete
all rows and import into it each time?

I've tried adding a GUID field after the import, but of course it
doesn't
autopopulate. Do I just have to write the code to populate it?

 

[Guest]

Who woulda thunk there was an option for that? Thanks alot.

Go to:
Tools | Options | Tables/Queries
and delete everything from the box:
AutoIndex on Import/Create

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Here's the problem I'm finding: I'm using Transfertext to import a text
file. I have import specifications, with all the index checkboxes turned
off. But I'm finding that the imported files have indexes on some fields
and
throw off the order of the input file, which must be maintained. At that
point, it's too late to append the data into a table with an autotext
because
the records are already out of order.

Doing it manually with File | Import... works fine, even without having
Access build the autonumber index for me.

Any ideas?

Assuming the field names/types don't change, the best idea is your shell
table.

To empty Table1, and then import programatically:
Dim db As DAO.Database
Dim strSql As String
Set db = dbEngine(0)(0)
strSql = "DELETE FROM [Table1];"
db.Exeute strSql, dbFailOnError
strSql = "INSERT INTO ...
db.Exeute strSql, dbFailOnError
Set db = Nothing

Adding an AutoNumber manually after the import should work, but is less
clean than setting up the table you want exactly as you want it.

I need to import a text file into a table with an autonumber index so
the
data order can be maintained through the subsequent manipulations.
Importing
manually, the wizard gives you the choice of adding an access index or
not.
Programmatically, this choice seems to have disappeared. Is there a
way
to
import text into a table using an import spec (already set up) and
still
get
access to add it's own index? Alternatively, can I add an autonumber
index
to
the table once it's created, or do I need to use a shell table where I
delete
all rows and import into it each time?

I've tried adding a GUID field after the import, but of course it
doesn't
autopopulate. Do I just have to write the code to populate it?