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

Hello hvisa,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

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

Please Login or Register  to view this content.