July 3, 2003 at 2:42 am
I want to build a fairly simple Excel DB UI (selects, inserts ,updates, deletes) for a mapping DB application. But I admit to not being a procedural developer (VB, .Net, ADO, OLE DB...) so I need some basic tips / pointers on relevant reading material & places to start looking (other than BOL).
The data types to be exposed in the client are fairly simple (varchars, ints (not identity)) and write access is restricted to a single user. I want to stick with Excel (rather than Access forms) because we have already deployed OLAP reporting and we want the same delivery metaphor.
For all the reasearch I've done so far it seems that getting Excel to select SQLServer tables and vice versa (linked servers) is trivial, but to initiate change from an Excel client/UI is not (and information on this appears scarce).
For those of you who have been down this path - how did you do it / whats invovled / must excel forms be used and what are the alternatives? Will this be a wizard / dialogue box and configuration session or more of a coding decathlon?
Given Im on the other side of the planet from most of you it may take a day or so for me to answer any questions.
Thank you kind souls,
Jason
July 3, 2003 at 4:50 am
Im not an Excel developer but I think you're going to have to write code to do it. Far as I know there is no data binding, that would be your only hope for a relatively codeless solution.
Andy
July 4, 2003 at 1:39 am
I'd agree, you're probably going to have to use VBA behind the scenes in Excel to go off and do any Updates or Inserts.
Growing old is mandatory, growing up is optional
July 7, 2003 at 12:01 am
Hi Jason,
quote:
I want to build a fairly simple Excel DB UI (selects, inserts ,updates, deletes) for a mapping DB application. But I admit to not being a procedural developer (VB, .Net, ADO, OLE DB...) so I need some basic tips / pointers on relevant reading material & places to start looking (other than BOL).
if you're only doing SELECT's you can also use MS Query, which ships with Office, but isn't installed by default. I think it should be capable to query SQL Server. After installation you invoke it by choosing DATA->EXTERNAL DATA...
Maybe you have to set up an ODBC source for your SQL Server.
quote:
For all the reasearch I've done so far it seems that getting Excel to select SQLServer tables and vice versa (linked servers) is trivial, but to initiate change from an Excel client/UI is not (and information on this appears scarce).
The EXCEL Object model is pretty straightforward. (once you get used to it :-))
quote:
For those of you who have been down this path - how did you do it / whats invovled / must excel forms be used and what are the alternatives? Will this be a wizard / dialogue box and configuration session or more of a coding decathlon?
The best way is a 'coding decathlon'. You don't need to use EXCEL forms. You can directly access the spreadsheet values. Maybe this snippet will help you!
Option Explicit
Private Sub WM_SQL()
On Error GoTo handler
Const ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Pafin;Data Source=DCCIV30002004"
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim dtNow As Date
Dim i As Integer
Dim SQL As String
Application.Cursor = xlWait
With cnn
.ConnectionString = ConnectionString
.Open
End With
dtNow = Date
'//Überprüfung, ob bereits vorhanden
SQL = "SELECT * FROM tblWochenmeldung WHERE date = '" & dtNow & "'"
rst.Open SQL, cnn, adOpenDynamic, adLockOptimistic
'//Löschen
SQL = "DELETE tblWochenmeldung FROM tblWochenmeldung WHERE date = '" & dtNow & "'"
cnn.Execute (SQL)
rst.Close
'//und neu machen
SQL = "SELECT * FROM tblWochenmeldung WHERE id=0"
Workbooks("marktd.xls").Worksheets("Status").Activate
rst.Open SQL, cnn, adOpenDynamic, adLockOptimistic
For i = 2 To 5
With rst
.AddNew
.Fields("date") = dtNow
.Fields("company") = i - 1
.Fields("avg_ka") = ActiveSheet.Cells(2, i)
.Fields("lfd_ertrag") = ActiveSheet.Cells(3, i)
.Fields("gains") = ActiveSheet.Cells(4, i)
.Fields("wa") = ActiveSheet.Cells(5, i)
.Fields("administration") = ActiveSheet.Cells(6, i)
.Fields("afa") = ActiveSheet.Cells(8, i)
.Fields("losses") = ActiveSheet.Cells(9, i)
.Fields("net_erg") = ActiveSheet.Cells(10, i)
.Fields("net_yield") = ActiveSheet.Cells(11, i)
.Fields("stated_yield") = ActiveSheet.Cells(12, i)
.Fields("gap") = ActiveSheet.Cells(13, i)
.Fields("sr_aktien") = ActiveSheet.Cells(16, i)
.Fields("sr_spezfonds_aktien") = ActiveSheet.Cells(17, i)
.Fields("sr_pubfonds") = ActiveSheet.Cells(18, i)
.Fields("sr_genuss") = ActiveSheet.Cells(19, i)
.Fields("sr_renten") = ActiveSheet.Cells(20, i)
.Fields("sr_spezfonds_renten") = ActiveSheet.Cells(21, i)
.Fields("sr_gesamt") = ActiveSheet.Cells(22, i)
.Fields("afa_aktien") = ActiveSheet.Cells(25, i)
.Fields("afa_spezfonds_aktien") = ActiveSheet.Cells(26, i)
.Fields("afa_pubfonds") = ActiveSheet.Cells(27, i)
.Fields("afa_genuss") = ActiveSheet.Cells(28, i)
.Fields("afa_renten") = ActiveSheet.Cells(29, i)
.Fields("afa_spezfonds_renten") = ActiveSheet.Cells(30, i)
.Fields("afa_gesamt") = ActiveSheet.Cells(31, i)
.Update
End With
Next
If Err = 0 Then MsgBox "Transfer komplett"
handler:
Application.Cursor = xlDefault
Err.Clear
Exit Sub
End Sub
To get this going, you must build a new macro and point it to this Sub.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply