How to update an autonumber using vba

I have two tables in sql

One is a supplier table with an autonumber supID,supname
Another is a product table with productID,supname,supaddress,start year and end year.

Now I get data for supname,supaddress,startyear and endyear using inputbox in vba.

I have to use ADO connection to insert it into sql tables.

I don;t know how to populate the autonumber field.
Below is the code..
let me know if it will work...

Thanks


SupName = InputBox("Enter the SupplierName", "Name?", "")
SupAddress = InputBox("Enter the Address", "Address?", "")
Startyr = InputBox("Enter the Start Year", "StartYear?", "")
EndYr = InputBox("Enter the End Year","EndYear?","")


Set cnn = CreateObject("ADODB.Connection")
connstring = "ODBC;DRIVER=SQL Server;SERVER=servname;DATABASE=dbname;Trusted_Connection=YES"
cnn.Open
Set rs = CreateObject("ADODB.Recordset")


sSQL = "INSERT INTO supplier (SupID,SupName"
sSQL = sSQL & ") VALUES ("," "& SupName &


sSQL = "INSERT INTO product ("productID,SupName,supplieraddress,StartYear,EndYear"
sSQL = sSQL & ") VALUES (" ," "& SupName & "," & SupAddress & "," & Startyr & "," & EndYr & ","


rs.Close
cnn.Execute sSQL

Set rs=Nothing
cnn.close