Write data to Access table with INSERT when table has auto number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to write Excel table data to an access table using INSERT but need to
skip the first field because it has datatype 'AutoNumber' which doesn't
accept bing written to. Below is where I'm at, trying to write nothing (i.e.
' ') to the first field in the Access Table where the AutoNumber data type is
defined.

sSQL = "INSERT INTO tblTest VALUES ('','CatD8N', '4.5', '4', 'RMM1',
'10/10/2005', 'Comemtn 1');"

cnAccess.Execute sSQL
 
Hi Hokie

The SQL command needs to be modified a little because you are not inserting
a complete row.

The syntax for insert is

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)

So when you form your SQL string you need to specify the table columns:

sSQL = "INSERT INTO tblTest (field2, field3, field4, field5, field6, field7)
VALUES ('CatD8N', 4.5, 4, 'RMM1', '10/10/2005', 'Comemtn 1');"

I assume that the numbers are defined as numbers and I have removed the
quotes as they are not needed.

You need to change the field2.... to te actual name of the fields in the
database.
 
Thank you so much.you helped me a lot.:thumb:


=?Utf-8?B?TWFydGluIEZpc2hsb2Nr?= said:
Hi Hokie

The SQL command needs to be modified a little because you are not inserting
a complete row.

The syntax for insert is

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)

So when you form your SQL string you need to specify the table columns:

sSQL = "INSERT INTO tblTest (field2, field3, field4, field5, field6, field7)
VALUES ('CatD8N', 4.5, 4, 'RMM1', '10/10/2005', 'Comemtn 1');"

I assume that the numbers are defined as numbers and I have removed the
quotes as they are not needed.

You need to change the field2.... to te actual name of the fields in the
database.


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Hokievandal" wrote:

> I want to write Excel table data to an access table using INSERT but need to
> skip the first field because it has datatype 'AutoNumber' which doesn't
> accept bing written to. Below is where I'm at, trying to write nothing (i.e.
> ' ') to the first field in the Access Table where the AutoNumber data type is
> defined.
>
> sSQL = "INSERT INTO tblTest VALUES ('','CatD8N', '4.5', '4', 'RMM1',
> '10/10/2005', 'Comemtn 1');"
>
> cnAccess.Execute sSQL
Click to expand...
 
Back
Top