• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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.

0
adriankohws
Asked:
adriankohws
1 Solution
 
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.
0
 
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now