DSN-Less Connections

[Sauron]

Hi All,

Having a spot of difficulty doing a DSN-Less connection would anyone b
able to see what is wrong with the following code please? It keep
coming back with;

"datasource name not found and no default driver specified"

when I try opening the connection;

ecpsrvConnection.Open ecpsrvConnect

I've tried http://www.able-consulting.com/ADO_Conn.htm and variou
other sites with example connection strings. Nothing works...

Thanks All, as always very much appreciated!

Sau

*-*-*-*

Function ecpsrvDBCons()

Dim ecpsrvLogin As String
Dim ecpsrvPassword As String
Dim ecpsrvname As String
Dim ecpsrvConnect As String
Dim ecpsrvConnection As ADODB.Connection

ecpsrvLogin = "sa"
ecpsrvPassword = vbNullString
ecpsrvname = "ECPSRV;"

ecpsrvConnect = "ODBC;Provider={SQL Server};" & _
"Server=" & ecpsrvname & ";" _
& "Database=Concorde" & ";" _
& "Uid=" & ecpsrvLogin & ";" _
& "Pwd=" & ecpsrvPassword & ";"


End Function
Call ecpsrvDBCons

Dim sQuery1 As String
Dim ecpsrvRecordset As ADODB.Recordset
Dim ecpsrvCommand As ADODB.Command

Set ecpsrvConnection = New ADODB.Connection
ecpsrvConnection.Open ecpsrvConnect

Set ecpsrvCommand = New ADODB.Command
Set ecpsrvCommand.ActiveConnection = ecpsrvConnect

sQuery1 = ""
sQuery1 = sQuery1 & " SELECT COUNT * "
sQuery1 = sQuery1 & " FROM " & "tbl_patient.lastname

 

[Sauron]

Anyone any ideas

 

[Jamie Collins]

Sauron wrote ...

Having a spot of difficulty doing a DSN-Less connection would anyone be
able to see what is wrong with the following code please? It keeps
coming back with;

"datasource name not found and no default driver specified"

when I try opening the connection

Omit the 'ODBC' clause and change 'Provider' to 'Driver'. I don't
think your server name should have a trailing semicolon. Your sql
should be COUNT(*), I think.

I amended your code to work with pubs (aside: consider using the OLEDB
provider for SQL Server);

Option Explicit

Sub ecpsrvDBCons()

Dim ecpsrvLogin As String
Dim ecpsrvPassword As String
Dim ecpsrvname As String
Dim ecpsrvConnect As String
Dim ecpsrvConnection As ADODB.Connection

ecpsrvLogin = vbNullString
ecpsrvPassword = vbNullString
ecpsrvname = "MYSERVER"

ecpsrvConnect = "Driver={SQL Server};" & _
"Server=" & ecpsrvname & ";" _
& "Database=pubs" & ";" _
& "Uid=" & ecpsrvLogin & ";" _
& "Pwd=" & ecpsrvPassword & ";"

Dim sQuery1 As String
Dim ecpsrvRecordset As ADODB.Recordset

Set ecpsrvConnection = New ADODB.Connection

ecpsrvConnection.Open ecpsrvConnect

sQuery1 = ""
sQuery1 = sQuery1 & " SELECT COUNT(*)"
sQuery1 = sQuery1 & " FROM Employee"

Set ecpsrvRecordset = ecpsrvConnection.Execute(sQuery1)

End Sub

Jamie.

--

 

[Sauron]

Thanks for the reply ad corrected code! I've made the changes yo
specified but it's still coming back with "Data Source Name not Foun
and no default driver specified"...

What else do you think I could be doing wrong?

Cheers Jamie, appreciated.
Phi

 

[Jamie Collins]

Sauron wrote ...

I've made the changes you
specified but it's still coming back with "Data Source Name not Found
and no default driver specified"...

Phil,
Are you sure you have the driver? It works for me (when I change
MYSERVER to the real server name). Same for the below, which specifies
the OLE DB provider:

ecpsrvConnect = "Provider=SQLOLEDB;" _
& "Data Source=" & ecpsrvname & ";" _
& "Initial Catalog=pubs" & ";" _
& "User Id=" & ecpsrvLogin & ";" _
& "Password=" & ecpsrvPassword & ";"

Jamie.

--

 

[Sauron]

Anyone any ideas??

Jamie

 

[Sauron]

I have MDAC v2.7 installed... do I need to point it at the drive
directly like C:\winnt\system32\'sqldriver' or add a reference?

I have always used DSNs before as you can see! this is the first tim
I've attempted DSN-Less so thanks for your patience

 

[Sauron]

I -know- I'm missing something here..

 

[Jamie Collins]

Sauron wrote ...

I -know- I'm missing something here...

Try this:

1. In Excel, paste the following code into a standard module:

Public Function BuildConnString()
Dim oDLink As Object
Set oDLink = CreateObject("DataLinks")
On Error Resume Next
BuildConnString = oDLink.PromptNew
On Error GoTo 0
End Function

2. In the VBE Immediate Window, type

? BuildConnString

and hit enter to run the code.

3. The 'Data Link Properties' dialog should show.

4. On the 'Provider' tab, choose Microsoft OLE DB Provider for SQL
Server

5. Hit the 'Next' button

6. In the 'Select or enter a server name' doprdown, choose your server
from the list (I'll choose MYSERVER).

7. For 'Enter information to log on to the server', choose 'Use a
specific user name and password', and enter the details (I'll enter
'sa' with a blank password, natch <g>).

8. Choose 'Select the database on the server' and select the database
in the dropdown list (I'll choose 'pubs').

9. Hit the 'Test Connection' button (I see a message, 'Test connection
succeeded' and I dismiss the message).

10. Hit OK to accept the details entered into the 'Data Link
Properties' dialog.

11. The corresponding connection string should now appear in the
Immediate Window. I get:

Provider=SQLOLEDB.1;
Persist Security Info=False;
User ID=sa;
Initial Catalog=pubs;
Data Source=MYSERVER

HTH,
Jamie.

--

 

[Sauron]

That works!!

Thanks Jamie, that's absolutely brilliant!