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;" & _
                 "Uid=sms;Pwd=x;"
  Conn.ConnectionString = Path
  Conn.Open

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

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

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


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

Indicate here that I have 4 datafiles;

SMS_Data1.mdf;
SMS_Data2.mdf;
SMS_Data3.mdf
SMS_Data4.mdf

But presently only used up to first datafile.

Thanks.

LVL 10
adriankohwsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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;"

Or:

     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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

Thanks.:D
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.