Click here to Skip to main content
14,735,180 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Posted

1 solution

Look at the SaveToDB add-in:
http://www.savetodb.com/help4/savetodb-getting-started.htm[^]

It allows using Microsoft Excel as a database data editor.
So your engineers can save and edit specifications using Excel.

In addition, SaveToDB can help to load data from the web into Excel if the web data are in regular tables.

The setup package contains multiple examples. You may use them as templates for your tasks.

If you plan to build your own app, build it as a Microsoft Excel add-in using Visual Studio 2010 or higher and VB.NET or C#, not VBA.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900