TransferText to Table

[DaLiMan]

Hi,

I'm trying to make a macro which imports a .txt file to a table.
I have created an Import Specification so it is imported a pre-defined way, but I can't seem to figure out how to created an extra column with an Auto-numbered Primairy Key in my Macro.

Can somebody help me, please?

 

[Andrew Fergus]

The autonumber field would not be set up / populated in the macro, rather you would do this in the table design (i.e. add the autonumber field field to the table) and not append any values to that field because it will populate itself.
HTH, Andrew.

 

[DaLiMan]

Is there no way of automating this task?

 

[Andrew Fergus]

In your first post you mentioned this :

I'm trying to make a macro which imports a .txt file to a table

Have you set up the table yet? If so, what fields does it have? If it doesn't have an autonumber field then add an autonumber field to the table. Every time you add a record to the table through your macro then the autonumber will populate itself - unless I'm misunderstanding your question I thought that was automatic.

 

[DaLiMan]

Ah, yes I'm sorry.
Let me explain a bit more then.

I have a txt file which has to be imported in a table, but before importing the table must be emptied. (sort of refresh data)
I tried linking the file, but that way you can't add a column at all.
So I'm deleting the table before importing/creating the new one.

Maybe there is a way of deleting only the data so the stucture remains in tact and then import the txt file.

(edit little typo)

 

[Andrew Fergus]

Hi

Yes you can clear out the just the data and leave the structure intact. This will also ensure that you use the same table each time you do an import which means you won't have to change your macro each time you run it.

Create a new query that you can run as the first step in your macro (i.e. it flushes out the table prior to the "import"). Create a new query -> add the table you want to clear -> Click Query -> Delete -> Select all records from table (using the *), Save.

Add it to your macro and that should do it. You might want to turn off the setwarnings at the start of the macro and then turn them back on before the macro stops - this will stop the Access messages confirming the record delete etc.

Andrew.

[Late Edit] P.S. you might want to change your existing make table query into an append query.

 

[DaLiMan]

Thanx, that works great.

But now the next obstacle appears.....
Access remembers the numbers you deleted and everything wich is added is numbered where it ended the prevouis time.

i.e.:
1 - 100 after deleting and importing will be
101 - 200 after deleting and importing will be
201 - 300 etc...

Is it possible alway to start with 1,2,3....?

 

[Norie]

How are you actually importing the data?

Can't you just copy the existing table then paste it and select structure only?

 

[DaLiMan]

Yes, there are different solutions possible.
But not the way I want it to be.

I want it to be an automated task, with no manual interfering.
(Well just maybe to click a button.)

But I created a script in another program, since I couldn't get Access bend on it's knees to my wishes....

Thanx for all the help though guys.
Did learn a few things again.....