Share the Excel File with SQL connections

[manishc1989]

I need some urgent help.

I've created a search tool in excel which pulls some information based on the search criteria from SQL.

I've used SQL VBA method to retrieve the information.

It works fine on my system. However, I want to share this file on server or network so multiple users can access it on a same time.

I don't want to install Oracle/Sql on each of the workstations. Other than on which file get stored.

Is there any feasible solution to it?

 

[mole999]

need to set up the sql connection from within excel

The sql data needs to be accessible on the network

a generic log on for read only access and password as you may expose the server in excel

' set default connections
Dim adoConn As New ADODB.Connection
Dim adoRs   As New ADODB.Recordset
Dim sConn   As String
Dim sSql    As String
Dim var1 As String
Set rnStart = Sheets("REFERENCE LOOKUP").Range("J24") 'target for whete the data will go

'set varables
BEGINDATE = Sheets("REFERENCE LOOKUP").Range("A2")

'set the sqk connection string
    sConn = "Provider=SQLOLEDB; Data Source =table name ; Initial Catalog = database name; User Id = username; Password=password"
    
  ' set sel commands
  
  'go here
  'http://www.dpriver.com/pp/sqlformat.htm
  'paste your current sql in there and convert to VB
  ' copy from clipboard and inster into the excel
  
    var1 = "" 'ensures clean start
    var1 = var1 & "Select *"
    var1 = var1 & "       , from "
    var1 = var1 & "       , where BEGINDATE"
    var1 = var1 & "         order "

    sSql = var1

    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    adoConn.CommandTimeout = 60
    Set adoRs = New ADODB.Recordset
    adoRs.Open Source:=sSql, ActiveConnection:=adoConn
    On Error Resume Next
    If Not (adoRs.BOF Or adoRs.EOF) Then
        Do While Not adoRs.EOF
            rnStart.CopyFromRecordset adoRs
            adoRs.MoveNext
        Loop
        sOutput = Left(sOutput, Len(sOutput) - 1)
    Else
        sOutput = "NO MATCHED DATA"
        Sheets("REFERENCE LOOKUP").Range("A24") = sOutput
    End If
    adoRs.Close
    adoConn.Close
    Set adoRs = Nothing
    Set adoConn = Nothing
    BEGINDATE = ""
End Sub

That works for me, its not perfect and others will suggest tweaks

make sure your data runs in sql

then step through
and use debug.print sSql to check the output and test that in your server to ensure it compiles right

I might have left a reference out

 

[manishc1989]

I've tried the same solution. But not able to succeed. It's taking the reference for the same workstation from where the file is accessing.

I'm working on Oracle SQl and msdaora as provider. Please advise the better way of doing it.

 

[manishc1989]

So I thought there is better of way of achieving this in Excel.