Hi everyone, I need suggestions. A little corporate needs to track its data with 2-3 employees making a daily data entry/updating of few records (20-30). Data must be available to other employees. They want to store data in a SQL Server db that has been already implemented. The whole db doesn't reach 10,000 records.
They would like to implement a Web interface but they should pay someone external for this; internal resources proposed to use a shared Excel spreadsheet to be modified daily and a SQL Server job running at intervals to import Excel data.
Any problem in using Excel spreadsheets in such a scenario? Any help will be appreciated.
Thank you! Cris
What I don't like is "shared" and "intervals". Even in small scale shared workbooks can cause user frustration and problems. Running a job at intervals reading a shared workbook, that users might be still editing - but haven't saved or have saved half-way sounds like bad idea.
May I propose an Excel macro using ADODB to insert records directly in SQL ?
I've used this in past to read & write to SQL and you simply give a template to the user to fill in and when done he/she has to press a button and the magic happens.
The code required should be :
Public Sub create_request()
Dim conn As New ADODB.Connection
Dim comm As New ADODB.Command
Dim rs As New ADODB.Recordset
conn.ConnectionString = "Your connection string here"
comm.ActiveConnection = conn
comm.CommandText = "insert into table (column1, column2, column3) values ('" &
Sheets("Sheet1").Range("A2").Value & "','" & Sheets("Sheet1").Range("B2").Value & "','" & Sheets("Sheet1").Range("C2").Value & "')"
If you have multiple records to insert, you can either loop through records or union them in a select.
I prefer it this way, because users know instantly if their insert succeeded and the template can be nice, with vlookups, data validation, calculations and whatever Excel supports. You also can save with data from several worksheets and can validate before the insert.
Thank you all. Importing into SQL Server could be done overnight, as they told me that real-time update isn't needed, so they could avoid concurrency issues; in the meantime, I realized that some entered values must be checked against the database (there are uniqueness constraints), so inserting the values directly in SQL through Excel spreadsheet, as adam_k explained, could be the best solution. I'll share with them your suggestions so they can begin to work on the solution. Thank you again! Cris