Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSN-Less Connections
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSN-Less Connections
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSN-Less Connections
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. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSN-Less Connections
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSN-Less Connections
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSN-Less Connections
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSN-Less Connections
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 -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSN-Less Connections
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSN-Less Connections
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. -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSN-Less Connections
That works!!
Thanks Jamie, that's absolutely brilliant! -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data connections | Excel Discussion (Misc queries) | |||
Data Connections | Excel Worksheet Functions | |||
Data connections | Excel Discussion (Misc queries) | |||
remote connections | New Users to Excel | |||
Deleting old connections | Excel Programming |