An Access Q, but I think it will have a VBA solution

[The Idea Dude]

I am importing a text file into a table. Sometimes the fields (first line in text file) change. Is there a way that Access can automatically detect if there are new fields and then add them to the table if required?

Thanks

 

[The Idea Dude]

Thanks Andrew, that did the trick.

2 Questions.

1. Can Access get external dat from an online file (like Excel - Query)?

2. When importing data into an existing table from a text file, does Access just insert from left to right, or does it look at the field names. - The reason for this Question is that I am trying to import into an existing table with about 10 fields, but I only have 5 of these fields in my txt file, and Access spits the dummy

Thank you

 

[Andrew Poulsom]

I'll have to pass on those 2 questions. I know next to nothing about Access!

 

[The Idea Dude]

On 2002-10-21 06:37, Andrew Poulsom wrote:
I'll have to pass on those 2 questions. I know next to nothing about Access!

Me too

 

[dk]

1. Yes, Access can import data. File, Get External Data, Import.

2. It depends. You can specify that the file you're importing has headers in which case the order doesn't matter. If it doesn't have headers then Access will import based on the order of fields in your text file.

What is the message you're getting when you try and import?

 

[The Idea Dude]

On 2002-10-21 08:24, dk wrote:
1. Yes, Access can import data. File, Get External Data, Import.

2. It depends. You can specify that the file you're importing has headers in which case the order doesn't matter. If it doesn't have headers then Access will import based on the order of fields in your text file.

What is the message you're getting when you try and import?

OK, here is an overview of what I am trying to do (I will try to be as specific as possible, without rambling on).

I want to click a button in a form which activates the get external data application to retrieve data from an online text file (which is the responses from a questionanire). Note: The questions in the survey are likely to change from time to time - in numbers and actual questions).

I then want Access to import these answers into an existing table under the relevant field, and if the field does not exist, then create it in the table (which code posted earlier in this thread does - great).

I then want to put the field names from this table into a separate table so that I can use them in a combo box.

So a scenario would be; Get data from online text file, detect that there are 4 headings that are not fields in the destination table, add these headings as new fields to the table. Place the answers in the relevant field. (It is likely that there will be more fields in the table - as some are added - than there will be different headings of answers. So the table may have 15 fields, but the text file may only have 8 different types of answers)

So much for the brief overview

Needless to say, I am running into some problems.

To make things easier, I would say that the thing that I have accomplished thus far is to add new fields to the table, Everything else ??????

Any suggestions, examples etc would be great.

Thanks again for all your help so far!

P.S. The error when importing data is something about a key violation.
This message was edited by The Idea Dude on 2002-10-21 21:33