Excel as a frontend for SQL 2

[travisbrown]

I've moved this here from the MS Office Forum.

I'm trying to set up Excel as a data entry front end for a SQL2K table. I see there are various ways to do this.

The end user will probably want to keep the .xls on their desktop, and the file may move around from user to user. All the examples I've seen have the workbook on a share and you set up a linked server on SQL Server with a declaration of the .xls location and use in combination with a select macro in xls.

What's the best approach for this? Can this be done without setting up a linked server - just a dsn-less macro in the workbook that will insert and select on the db table? I can query the table with the sub below, but how would one update the table.

Sub PSSPROD_MASTER_TMI_TEMPLATE()
    Dim qt As QueryTable
    Dim servername, database, username, password
    servername = "servername"
    database = "db"
    uid = "abc"
    pwd = "def"
    
    ' Set up the SQL Statement
    sqlstring = "SELECT col1,col2... FROM table"
    connstring = "odbc;Driver={SQL Server};Server=" & servername & ";Database=" & database & ";UID=" & uid & ";PWD=" & pwd & ""
    With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
     .Refresh
    End With
End Sub

 

[SkipVought]

As I stated before, ActiveX Data Objects.

1. Add a reference to the Microsoft ActiveX Data Objects n.m Library

2. Here's some sample code I use in a function, to geet data from Oracle. Modify the CONNECT string accordingly...

Function GetInvData(sPN As String, Optional sNG As String = "") As Variant
'Skip Vought/2005 Aug 29
'--------------------------------------------------
' Access: DWPROD.FRH_MRP.READ
'--------------------------------------------------
'this function returns the sum of On Hand, On Dock & In Transit
' for a given part number and optional netting group
' where the store type is not 'W'
'--------------------------------------------------
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    sServer = "DWPROD"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT Sum(STKITQTY_233+STKOHQTY_233+QTYONDOK_233) AS Inv_Qty "
    sSQL = sSQL & "FROM FRH_MRP.PSK02233 A "
    sSQL = sSQL & "Where PARTNO_201='" & Trim(sPN) & "' "
    sSQL = sSQL & "  And STORETYP_233<>'W' "
    If sNG <> "" Then
        sSQL = sSQL & "  AND NETGRP_233='" & sNG & "'"
    End If
    
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    rst.MoveFirst
    If IsNull(rst(0)) Then
        GetInvData = 0
    Else
        GetInvData = rst(0)
    End If

    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Does not do UPDATES, but shows how to get connected and select query.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

You'll need to open the recordset object with a CursorType and LockType that will allow for UPDATE.

Read up on the Recordset Object and check out Help on

http://www.microsoft.com

Post back with specific questions regarding your code.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[travisbrown]

Forgive my apparent slowness.

I'm familiar enough with ADO update commands from using VBS, but how would you reference the active cell for the update value, and would you feed the whole row for each update/insert, or write a dynamic query that updated only the current/corresponding field in the db?

I'm unfamiliar with working with Excel macros and how they fire. What is the event trigger?

 

[SkipVought]

It does not necessarily need to be the ActiveCell.

How does the user enter the necessary data?

Is the data entered in a UserForm or on a worksheet?

When the user is ready to update, do they have a BUTTON or other control? That would be the event that could fire your update query.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[travisbrown]

I was trying to set it up so they could just edit the worksheet, like you would in a linked Access table or in Enterprise Manager. Maybe that's not possible and why I'm so confused. I am trying not to change their current method of work. Maybe setting them up with an Access application would be better.

Even if they made all the changes needed and updated on Save or other explicit or periodic event.

 

[SkipVought]

You could use the Worksheet_Change event and send a field at a time.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[travisbrown]

That's a thought. Worksheet_Change fires on Enter, right? Is there an equivalent to javascript on blur? People would often use Tab or arrows to navigate through cells.

 

[xlbo]

Worksheet change does not fire on enter.
It fires on change
A cell is changed when the foucs moves away from the cell and the content is different from before
This should be perfect for what you want I would've thought

Easiest way to see is to test - set up a worksheet change sub and enter

msgbox "Cell " & target.address & " was changed to " & target.value

make some changes to the sheet and see when it fires...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[travisbrown]

Ah thanks. I'd read a reference that said it was only on Return, but I think they maybe meant for Worksheet_SelectionChange, or maybe Return was their catchall term for any change of focus.

 

[SkipVought]

As Geoff stated, ANY change to the value in any cell on the sheet. Could even be a PASTE operation, which has nothing to do with LEAVING a cell.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[travisbrown]

Yeah, tested it out. Good stuff.

Thanks guys.