importing data to a query

[avikohl]

I need to import data from a text file to an access2000 db.
The text file is obviously in a flat format, ie non-relational and the access file is relational.

I thought that if I create a query which displays all the data in the two related tables I would get the same data structure as the text file and could import the data to this query, which would update the two related tables in the acces db.

Unfortunately, when I got to the stage in the import external data process, access did not give me the option of appending data to a query, only to a table.

So my question is, how would I go about importing data from a text file to a relationally structured access db seeing that the data import wizzard doesn't seem to support it in the way I envisioned it?

 

[JeroenNL]

Remember that a query is only a command to select/update/delete/append certain data from/of/from/to a table. It does not hold any data itself. Therefore you can't append data to the query, you can only append to a table.

What I would do is import your textfile into a table and then use a query to get the data you need from that table. Let's say you import the textfile to a table called "tblimport". Then create a query that retreives data from that table and appends it to your other 2 tables.

Something like might work.

Bye,
Jeroen

 

[avikohl]

Thanks. That sounds like good advice. I'll try it.

What I'm confused about is that with asp and ado you can create recordsets based on queries and when you update those recordsets the tables, upon which the select query was based, get updated. So I figured you could do the same thing in Access.

 

[JeroenNL]

Yes, you're right about the recordsets. But you can do the same thing in Access. If you view the results of your query, you can update the records at the same time too. Just remember that a query is only a command to get certain data, it's not a table in itself.

However you cannot import text/excelsheets to a query, because a query is only a command. Therefore you import your data to tables. To solve your problem: write a macro that imports data from a textfile (using tranferText or something) and then call a query to update the 2 tables in your database. I think that should work.

Goodluck.

Jeroen

 

[avikohl]

The problem is I don't know the first thing about writing macros. so I think what I'll do is import the data into a table, write an asp script that assigns the imported table to a recordset, and scrolls down the record set inserting the data into the the existing tables by executing sql insert commands.

I didn't really understnd what you wrote about queries. Its not actually a table so you cant import data to it. But its enough of a table data thing to update a table which it is updated from within access?

 

[sdk]

Hi,

Without being certain of the vargancies, you can append to a query, and from the data source you have specified. This is because the SQL string is considered as a virtual table. I have used this to import Excel data to overcome an identical problem.

Options might include using the "IN" clause in the insert SQL to specificy a data source outside the database, or linking the text file as a table (take care by explicitly specifying the specifications table for the link), or by importing the text file into Access & then append. You will need to ensure that datatypes are compatible with the destination tables.

Your problem may have more to do with your destination query: is it updatable? are the foreign/primary keys specified? are the fields correctly assigned from the append qry, in particular the primary key (the foriegn will set itself once the record is created)?.

I think you will find your problem something simple.

Cheers

Steve