SQL to Excel DB connections without DNS 1

[MJPPaba]

Hello Ladies and Gentlemen,

I have my spreadsheet with lots of lovely data being pulled into it from my sexy SQL databases using ODBC connex.

I move the spreadsheet to a friends pc who does not have the same odbc connections as I, funnily enough the query falls over. What I need is a piece of coded connection string to my sql server wich is embedded in the VBA.

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=myDSN;Description=myDSNDesc;UID=myUserName;PWD=myUserPassword;APP=Microsoft® Query;WSID=myWSID" _
, Destination:=Range("A1"))
.CommandText = Array("SELECT * " & Chr(13) & "" & Chr(10) & "FROM myTable")
.Refresh BackgroundQuery:=False
End With

any suggestions?

cheers,

Crazypabs

The only bad question is the question you dont ask!

 

[SkipVought]

Hi,

    Dim myDSN, myDSNDesc, myUserName, myUserPassword, myWSID, sConn
    
    sConn = "ODBC;"
    sConn = sConn & "DSN=" & myDSN & ";"
    sConn = sConn & "Description=" & myDSNDesc & ";"
    sConn = sConn & "UID=" & myUserName & ";"
    sConn = sConn & "PWD=" & myUserPassword & ";"
    sConn = sConn & "APP=Microsoft® Query;"
    sConn = sConn & "WSID=" & myWSID & ";"
    
    With ActiveSheet.QueryTables
        If .Count = 0 Then
            With .Add(Connection:=sConn, Destination:=Range("A1"))
                .CommandText = "SELECT * FROM myTable"
                .Refresh BackgroundQuery:=False
            End With
        End If
    End With

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[MJPPaba]

Sorry skippy,

Dis Duz Nut Verk.

It only opens the ODBC connection administration diddlydo, and prompts for a new connection.

crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[MJPPaba]

Howdy Howdy Howdy.

I had to adapt the code a little as follows...

Dim myDSN, myDSNDesc, myUserName, myUserPassword, myWSID, sConn
myDSN = "DSN007"
myDSNDesc = "LicencedToKill"
myUserName = "JamesBond"
myUserPassword = "octopussy"
myWSID = "ILoveQ"


sConn = "ODBC;"
sConn = sConn & "DSN=" & myDSN & ";"
sConn = sConn & "Description=" & myDSNDesc & ";"
sConn = sConn & "UID=" & myUserName & ";"
sConn = sConn & "PWD=" & myUserPassword & ";"
sConn = sConn & "APP=Microsoft® Query;"
sConn = sConn & "WSID=" & myWSID & ";"

With ActiveSheet.QueryTables
mycount = .Count
Debug.Print mycount

If .Count = 0 Then
With .Add(Connection:=sConn, Destination:=Range("A1"))
.CommandText = "SELECT * FROM FrenchCartoonStrip"
.Refresh BackgroundQuery:=False
End With
End If
End With




crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[MJPPaba]

Anyone got any thoughts?

crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[SkipVought]

and...

It dun't vork?

Plez xplane!

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[MJPPaba]

hiho,

Desired result...

User has no DSN's setup in ODBC connex Administration.
Workbook has a Query setup by myself using the above code and facilitated by an ODBC ADO OLEDB connection or whatever coded into my Globule Constantinoples.

When User opens workbook, I'm not bothered about initialising a workbook_open refresh or anything, but because the user is a complete halfwit and because I cant stand them, beside they smell of BO, I don't want to go anywhere near them to set up their ODBC connections.

How can the connection string be written in the code without the need of setting up anodbc in the admin environment.

Does that make more sense.

In 100 words or less please state why if you eat 60 cream eggs a day, you wonder why you are a fat git?




crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[SkipVought]

Then...

use a System DSN. Then just plug in a password or whatever if needed.

sConn = "ODBC;DSN=A010PROD;PWD=;DBQ=A010PROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;"

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[MJPPaba]

Off to pub now, I will try tomorrow morning, when life is a bit clearer.

Cheers, mines a Pint.

crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[SkipVought]

Mind you P's & Q's! ;-)

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[mikej336]

Tip from your old uncle mike,

You can create a file DSN then open the file (in editor).
Concantinate the lines and use that as your connection string.

If it works with the file, it will work with the string.

M

 

[MJPPaba]

EPIPHINY, hope everyone in the whole world will use this, as it will spawn the creation of the new industrial revolution. well, I like to think BIG.

I solved it using the following...

All code is placed in the Workbook object.

Sub workbook_open()
On Error GoTo myError
Dim fe
Dim fs As FileSystemObject
Dim myPrompt

Set fs = CreateObject("Scripting.FileSystemObject")
fe = fs.FileExists("C:\Program Files\Common Files\ODBC\Data Sources\ScoobySnax.dsn")
If fe = False _
Then myPrompt = MsgBox("DSN does not Exist on this computer do you want to create it?", vbYesNo, "Create DSN Connection"): GoTo myDSNExists
myPrompt = MsgBox("The DSN Already exists on this PC, do you want to recreate it?", vbYesNo)
If myPrompt = vbYes Then GoTo myDeleteDSN
MsgBox ("DSN Exists on this PC and has not been replaced")
GoTo myExit

myDeleteDSN:
fs.DeleteFile ("C:\Program Files\Common Files\ODBC\Data Sources\ScoobySnax.dsn")
GoTo myMakeDSN
myDSNExists:
If myPrompt = vbYes Then GoTo myMakeDSN Else MsgBox ("DSN Not created at this time"): GoTo myExit


myMakeDSN:
Call createDSNConnection
MsgBox ("DSN has been created on this PC")

myExit:
Exit Sub

myError:
MsgBox ("")

End Sub
Sub createDSNConnection()

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("C:\Program Files\Common Files\ODBC\Data Sources\ScoobySnax.dsn", True)
a.WriteLine ("[ODBC]")
a.WriteLine ("DRIVER=SQL Server")
a.WriteLine ("UID = myUser")
a.WriteLine ("WSID=myWorkstationID")
a.WriteLine ("APP=Microsoft Open Database Connectivity")
a.WriteLine ("Server = myServerName")
a.WriteLine ("Description = ScoobyScax")
a.Close
End Sub

It creates a filedsn on the local users pc. all the msgboxes are only for my development and checking procedures.

Works a treat.

Tell me what you think?

Cheers anyway Uncle / Skip.


crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[SkipVought]

MJPPaba,

I just learned something and I appreciate your sharing your [v]createDSNConnection[/b] procedure with your colleagues on Tek-Tips! I never knew what a .dsn looked like. This will be a helpful addition to my toolset.

==> *



Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[MJPPaba]

Thanks Skip.

crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...