Item not found in this connection

[koresnordic]

Probably me being a bit of a noob, but when I try to run this query

INSERT INTO data ( Type, Who, Collection, Item, Qty, [Date], [Time] )
SELECT MAPAMEND_ACCTRANS.F1, MAPAMEND_ACCTRANS.F2, MAPAMEND_ACCTRANS.F3, MAPAMEND_ACCTRANS.F4, MAPAMEND_ACCTRANS.F5, MAPAMEND_ACCTRANS.F6, MAPAMEND_ACCTRANS.F7
FROM MAPAMEND_ACCTRANS;


I get the "item not found in this collection" error.
I have checked to ensure all the fields exist in bith tables.
Not sure if relevent, but "mapamend_acctrans" is linked via ODBC and "data" is in another access database.

Any help appreciated



Graham

 

[jedraw]

Graham,

INSERT inserts a record into a Table. So if "data" is a second database that is an issue.
From Access HELP here is the syntax for INSERT INTO when dealing with an external database.

INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

The INSERT INTO statement has these parts:

Part Description
target The name of the table or query to append records to.
field1, field2 Names of the fields to append data to, if following a target argument, or the names of fields to obtain data from, if following a source argument.
externaldatabase The path to an external database . For a description of the path, see the IN clause.
source The name of the table or query to copy records from.
tableexpression The name of the table or tables from which records are inserted. This argument can be a single table name or a compound resulting from an INNER JOIN , LEFT JOIN , or RIGHT JOIN operation or a saved query.
value1, value2 The values to insert into the specific fields of the new record. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. You must separate values with a comma, and enclose text fields in quotation marks (' ').


Remarks
You can use the INSERT INTO statement to add a single record to a table using the single-record append query syntax as shown above. In this case, your code specifies the name and value for each field of the record. You must specify each of the fields of the record that a value is to be assigned to and a value for that field. When you do not specify each field, the default value or Null is inserted for missing columns. Records are added to the end of the table.

You can also use INSERT INTO to append a set of records from another table or query by using the SELECT ... FROM clause as shown above in the multiple-record append query syntax. In this case, the SELECT clause specifies the fields to append to the specified target table.

The source or target table may specify a table or a query. If a query is specified, the Microsoft Jet database engine appends records to any and all tables specified by the query.

INSERT INTO is optional but when included, precedes the SELECT statement.

If your destination table contains a primary key , make sure you append unique, non-Null values to the primary key field or fields; if you do not, the Microsoft Jet database engine will not append the records.

If you append records to a table with an AutoNumber field and you want to renumber the appended records, do not include the AutoNumber field in your query. Do include the AutoNumber field in the query if you want to retain the original values from the field.

Use the IN clause to append records to a table in another database.

To create a new table, use the SELECT... INTO statement instead to create a make-table query .

To find out which records will be appended before you run the append query, first execute and view the results of a select query that uses the same selection criteria.

An append query copies records from one or more tables to another. The tables that contain the records you append are not affected by the append query.

 

[koresnordic]

Tried using the IN without any change. The pain is that the data does get added to the table, but the error keeps coming up. There is no data lost, but the user is not happy with the error showing, even thou I assure them the dat ais all there (checked several times myself to confirm this).



Graham

 

[cheerio]

INSERT INTO data ( Type, Who, Collection, [b]Item[/b], Qty, [Date], [Time] )

Item may be an Access or SQL keyword in which case it needs to be in square brackets.

Type and Collection may also be key words and Date is certainly a keyword being the name of an Access function that returns the current date.

As a general rule keywords should not be used as table field names but if they have been used they must be protected with square brackets.


Finally I would check whether the select query runs without problem:

SELECT MAPAMEND_ACCTRANS.F1, MAPAMEND_ACCTRANS.F2, MAPAMEND_ACCTRANS.F3, MAPAMEND_ACCTRANS.F4, MAPAMEND_ACCTRANS.F5, MAPAMEND_ACCTRANS.F6, MAPAMEND_ACCTRANS.F7
FROM MAPAMEND_ACCTRANS;