DSN-Less connection using ADODB 1

[miked123]

I have tried to connect to my local server buts but can't wihout setting the prompt to ALWAYS.
Anybody have a clue? The code is right out of MS BOL.
Here is my code

m_ConnectionString=
"driver={SQL Server};server=MDHOME;database=BasePkg;Data Source=Select * from FD__CLIENT;trusted_connection=Yes"
Set cn = New ADODB.Connection
cn.ConnectionString = m_ConnectionString
'' cn.Properties("Prompt&quot = 1
cn.open

The error I get is:
Data sourse name not found and no default driver specified.

If I enable the prompt and get the connection and then inspect the connectstring:
?cn.ConnectionString
Provider=MSDASQL.1;Data Source=Select * from FD__CLIENT;Extended Properties="driver={SQL Server};server=mdhome;database=BasePkg;DSN=BasePkg"

If I use this, i get the same error.

Mike Davis
MSsql, VB and Crystal Reports Developer

 

[cmmrfrds]

Here is an example.
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As ADODB.Recordset, connString As String, bdate As Double
Set rs = New ADODB.Recordset
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=angelfish;" & _
"Persist Security Info=False"

sql1 = "select * from dbo.Employees "
rs.Open sql1, connString, adOpenForwardOnly, adLockReadOnly

It is easier to debug if you break the sql out of the initial string. The data source is the server name. Also, use the SQL provider in the example above since it is a direct connection to the server. The provider you are using goes through the ODBC layer in addition to ADO - less efficient and more to go wrong.

 

[miked123]

Using your suggestion, I get the error:
Provider not found or installed properly"

Am I typing it properly?
"Provider=SQLEDB.1"

Thanks MikeD Mike Davis
MSsql, VB and Crystal Reports Developer

 

[bperry]

Perhaps try SQLOLEDB.

(With and without the .1)?

 

[georgeous]

try this site

http://www.able-consulting.com/ADO_Conn.htm

 

[miked123]

Thanks to all, the problem was the Server=(local)

connectstring="Driver={SQL Server};" & _
"Server=(local);" & _
"Database=BasePkg;" & _
"Trusted connection=Yes"

Is there an updated or better driver to use than
Provider=MSDASQL.1;

Thank you,
MikeD Mike Davis
MSsql, VB and Crystal Reports Developer

 

[cmmrfrds]

You can check the providers available on your PC or the Server PC by using the user data link wizard. If you are using Win XX then maybe you need to download the latest MDAC from Microsoft.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the microsoft sql server provider - will bypass ODBC.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.