Excel row insert into DB as a record

See more:

VB

SQL

MS-Excel

macros

Hi there - I am new to CP and was wondering if I could get some guidance on what to do about a Excel record to SQL DB item, I'm working on.


There is a tab called: “output values” in a excel spec tool.

Row 1 are the table columns

Row 2 is are values populated by another tab in the workbook

SQL part:

There is a SQL DB that I have created, we can call in “GPackComp”

There is a table I have created with all the columns that are represented in row 1 of the excel tab, mentioned above.

Need:

I need the 2nd row, in the excel row to be inserted as a record or a row in the SQL DB.


scenario:

A Spec Engineer grabs this excel spec template from a location on the network.

Engineer saves this spec to a local location on his machine

Spec Engineer starts filling out data

Engineer works on spec for x amount of days or weeks, locally, until all the data is filled in and ready to be uploaded and store in DB

Engineer connects to SQL DB (in some sort of way – data connection?)

Engineer updates DB with the row 2 data on the represented line in the above screen shot.

Row 2 is stored in DB as a record (new row)

Updates to spec are a NEW row/record

Modify to spec is a NEW row/record

Delete is a NEW row/record

Note:

There are 12 Spec Engineers on the team. They are not technical

Its not realistic to thing that only 1 copy of the excel sheet will be open at any given time.

Each Engineer is expected to “upload” the row 2 data in some sort of fashion.

Engineers are not allowed to remote into the SQL Server

Security:

Auth is done with Network creds

Engineer must be connected to network to upload data/record.

My thoughts:

Create a Excel VB macro to "upload" into SQL. Can this be done?

Should I write a stored procedure? Never done this before.


Any help in a step by step article or design thoughts, would be awesome.


Thanks in advance,

Jennifer