Connect to MSDE not using ODBC

Hi Experts,

I tried so many ways and methods found on the web but still failed to connect to my MSDE DB unless using ODBC DSN.

I have tried. OLEDB, SQLOLEDB. Anyone....

1) First type - doesn't work
Public Con as New ADODB.Connection
Path = "Driver={SQLOLEDB.1};" & _
                 "Initial Catalog=SMS;CursorLocation = adUseClient;Data Source=IBM\MSDEINS;" & _
  Conn.ConnectionString = Path

2) Second type - doesn't work
   Con.Open "Data Source=http://IBM;" & _
   "Provider=SQLOLEDB;" & _
   "Initial Catalog=sms;" & _
   "User ID=sms;" & _

3) Third type - doesn't work
    Set Con = CreateObject("ADODB.Connection")
    Con.ConnectionString = "C:\MSDE\Data\MSSQL$MSDEINS\Data\SMS.mdf;sms;x"

4) Fourth type - doesn't work
Path = "Driver={SQLOLEDB};" & _
                 "Dbq=sms_data1;" & _
                 "DefaultDir=C:\MSDE\Data\MSSQL$MSDEINS\Data;" & _
  Con.ConnectionString = Path

A mixture of those also doesn't work...

Indicate here that I have 4 datafiles;


But presently only used up to first datafile.


LVL 10
Who is Participating?
Carl TawnSystems and Integration DeveloperCommented:
Have you tried:

     Public Con As New ADODB.Connection
     Con.Open "Provider=SQLOLEDB.1;Server=IBM;Initial Catalog=SMS;UID=sms;PWD=x;"


     Public Con As New ADODB.Connection
     Con.Open "Provider=SQLOLEDB.1;Server=IBM\MSDEINS;Initial Catalog=SMS;UID=sms;PWD=x;"

Although its a bit unclear from your samples as to what the server you are connecting to is called. The second sample "IBM\MSDEINS" seems a little odd because I wasn't aware that MSDE supported multiple instances.
adriankohwsAuthor Commented:
Fantastic my dear friend, the second one works... Urgghhh.... I have tried so many mixtures without
meeting with one you have indicated. Works Perfect!

I am using MSDE Plus 3.2 as a management tool. Nice tool although having a small bug. I think you can create multiple instances with the latest version.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.