Excel data to SQL database

[Kevlar]

I have an excel application that estimates a price for our product.
want to be able to transfer that quote to our business software syste
(which uses SQL Server) by the click of a button _on_the_spreadsheet_
The software system has a quote module which utilizes two tables;QTMAS
and QTITEM. Creating the quote would be a two step process. 1st step
Create the Quote Master record from within the system. This would giv
me a quote number. And Step 2: import the Quote item details from th
spreadsheet via a click of the button into QTITEM.

How can this be done via VBA

 

[Jamie Collins]

I have an excel application that estimates a price for our product. I
want to be able to transfer that quote to our business software system
(which uses SQL Server) by the click of a button _on_the_spreadsheet_.
The software system has a quote module which utilizes two tables;QTMAST
and QTITEM. Creating the quote would be a two step process. 1st step:
Create the Quote Master record from within the system. This would give
me a quote number. And Step 2: import the Quote item details from the
spreadsheet via a click of the button into QTITEM.

You can use ADO in VBA code to connect to the database. You would
normally create a new row in the database using INSERT INTO (SQL DML)
using values from Excel. In this case it sounds like table QTMAST
auto-generates a quote ID and other default information. Therefore, it
may be better to use a recordset with a server-side cursor to add the
new row. Use the recordset's AddNew method, enter any required field
values using values from Excel and issue the Update (or UpdateBatch)
method. Under the covers, the recordset will issue an INSERT INTO
followed by a SELECT query to get the auto-generated and default
values into the current row of the recordset. You could then use the
details from the recordset to roll your own INSERT INTO to create the
required row in QTITEM.

That said, it seems possible a stored procedure could do all this in
one hit.

Jamie.

--