Connect to MSDE not using ODBC

Posted on 2006-03-31
Last Modified: 2010-04-07
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.


Question by:adriankohws
    LVL 52

    Accepted Solution

    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.
    LVL 10

    Author Comment

    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.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now