2 Replies - 3228 Views - Last Post: 19 February 2014 - 01:09 AM Rate Topic: -----

#1 vishal anand   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 28-August 13

how to connect vb6 adodb with sql server 2008 in mdi form of vb6

Posted 18 February 2014 - 09:38 PM

hi my name is for past 3 days i have been nuts trying on how to connect vb6 adodb with sql server 2008. I have done the same with vb6 adodb with ms access. Given is connection code below on how i connected vb6,adodb with ms access in mdi form in vb6.:
Dim i As Integer
       Dim AccessConnect As String
       Dim dBOOL As Boolean
       Dim dMstr As String
       If (Left(App.Path, 1) = "\") Then
            dMstr = Dir(App.Path & "tuscanDB.mdb")
       Else
            dMstr = Dir(App.Path & "\tuscanDB.mdb")
       End If
       dBOOL = IIf(dMstr = "", False, True)
       If (dBOOL) Then
           AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                         "Dbq=tuscanDB.mdb;" & _
                         "DefaultDir=" & App.Path & ";" & _
                         "Uid=Admin;Pwd=;"

           '---------------------------
           ' Connection Object Methods
           '---------------------------
           If (adoDatabase.State <> 0) Then
                adoDatabase.Close
            End If
           adoDatabase.ConnectionString = AccessConnect
           adoDatabase.Open
        Else
            Dim mPath As String
            CommonDialog1.CancelError = True
            CommonDialog1.Flags = cdlOFNHideReadOnly + cdlOFNPathMustExist + cdlOFNFileMustExist
            ' Set filters
            CommonDialog1.Filter = "All Files (*.*)|*.*|RTF (*.rtf)|*.rtf|Text Files (*.txt)|*.txt"

            ' Display the Save dialog box
            CommonDialog1.FileName = ""
            CommonDialog1.ShowOpen
            dMstr = Dir(CommonDialog1.FileName)
            mPath = Left(CommonDialog1.FileName, InStr(mPath, dMstr) - 2)

            If (CommonDialog1.CancelError = False) Then
                AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                "Dbq=" & dMstr & ";" & _
                "DefaultDir=" & mPath & ";" & _
                "Uid=Admin;Pwd=;"

                '---------------------------
                ' Connection Object Methods
                '---------------------------

                adoDatabase.ConnectionString = AccessConnect
                adoDatabase.Open
            End If
        End If


The code above is code for connecting vb6 adodb with ms access in mdi form. adoDatabase is name of my connection of recordset in vb6.
Code below is connection between vb6 adodb with ms access in Login form:
On Error GoTo errhandler
    If (App.PrevInstance = True) Then
        MsgBox "DRRS Application is already running!", vbCritical
        End
    End If
   Dim i As Integer
   Dim dBOOL As Boolean
   Dim dMstr As String
   If (Left(App.Path, 1) = "\") Then
        dMstr = Dir(App.Path & "tuscanDB.mdb")
   Else
        dMstr = Dir(App.Path & "\tuscanDB.mdb")
   End If
   dBOOL = IIf(dMstr = "", True, False)
   If (dBOOL = False) Then
       AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                     "Dbq=tuscanDB.mdb;" & _
                     "DefaultDir=" & App.Path & ";" & _
                     "Uid=Admin;Pwd=;"

       '---------------------------
       ' Connection Object Methods
       '---------------------------

       adoDatabase.ConnectionString = AccessConnect
       adoDatabase.Open
    Else
        Dim mPath As String
        cmdDlg.CancelError = True
        cmdDlg.DialogTitle = "Select database"
        cmdDlg.Flags = cdlOFNHideReadOnly + cdlOFNPathMustExist + cdlOFNFileMustExist
        ' Set filters
        cmdDlg.Filter = "All Files (*.*)|*.*|MS Access (*.mdb)|*.mdb"
        
        ' Display the Save dialog box
        cmdDlg.FileName = ""
        cmdDlg.ShowOpen
        dMstr = Dir(cmdDlg.FileName)
        mPath = Left(cmdDlg.FileName, InStr(cmdDlg.FileName, dMstr) - 2)
        
        
        AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
        "Dbq=" & dMstr & ";" & _
        "DefaultDir=" & mPath & ";" & _
        "Uid=Admin;Pwd=;"
        
        '---------------------------
        ' Connection Object Methods
        '---------------------------
        
        adoDatabase.ConnectionString = AccessConnect
        adoDatabase.Open
    End If
    Exit Sub
errhandler:
    If Err = 32755 Then '  Dialog Cancelled
        If MsgBox("Database file not found. Contact Admin", vbOKOnly) = vbOK Then
            Unload Me
        End If
    End If



Now since on my boss order i have to connect vb6 adodb with sql server 2008.
I have done some coding below in my mdi form in vb6 :
 Dim i As Integer
       Dim SqlConnect As String
       Dim dBOOL As Boolean
       Dim dMstr As String
       If (Left(App.Path, 1) = "\") Then
            dMstr = Dir(App.Path & "Tuscan77.mdf")
       Else
            dMstr = Dir(App.Path & "\Tuscan77.mdf")
       End If
       dBOOL = IIf(dMstr = "", False, True)
       If (dBOOL) Then
           SqlConnect = "Driver={SQL Server (*.mdf)};" & _
                        "Server=NPD-4\SQLEXPRESS;" & _
                         "Database=Tuscan77.mdf;" & _
                         "DefaultDir=" & App.Path & ";" & _
                         "User name=NPD-4\TUSCANO;Password=;"

           '---------------------------
           ' Connection Object Methods
           '---------------------------
           If (adoDatabase.State <> 0) Then
                adoDatabase.Close
            End If
           adoDatabase.ConnectionString = SqlConnect
           adoDatabase.Open
        Else
            Dim mPath As String
            CommonDialog1.CancelError = True
            CommonDialog1.Flags = cdlOFNHideReadOnly + cdlOFNPathMustExist + cdlOFNFileMustExist
            ' Set filters
            CommonDialog1.Filter = "All Files (*.*)|*.*|RTF (*.rtf)|*.rtf|Text Files (*.txt)|*.txt"

            ' Display the Save dialog box
            CommonDialog1.FileName = ""
            CommonDialog1.ShowOpen
            dMstr = Dir(CommonDialog1.FileName)
            mPath = Left(CommonDialog1.FileName, InStr(mPath, dMstr) - 2)

            If (CommonDialog1.CancelError = False) Then
                SqlConnect = "Driver={SQL Server (*.mdf)};" & _
                "Server=NPD-4\SQLEXPRESS;" & _
                "Database=Tuscan77.mdf" & dMstr & ";" & _
                "DefaultDir=" & mPath & ";" & _
                "User name=NPD-4\TUSCANO;Password=;"

                '---------------------------
                ' Connection Object Methods
                '---------------------------

                adoDatabase.ConnectionString = SqlConnect
                adoDatabase.Open
            End If


And similarly in my Login form:
On Error GoTo errhandler
    If (App.PrevInstance = True) Then
        MsgBox "DRRS Application is already running!", vbCritical
        End
    End If
   Dim i As Integer
   Dim dBOOL As Boolean
   Dim dMstr As String
   If (Left(App.Path, 1) = "\") Then
        dMstr = Dir(App.Path & "Tuscan77.mdf")
   Else
        dMstr = Dir(App.Path & "\Tuscan77.mdf")
   End If
   dBOOL = IIf(dMstr = "", True, False)
   If (dBOOL = False) Then
        SqlConnect = "Driver={SQL server (*.mdf)};" & _
                        "Server=NPD-4\SQLEXPRESS;" & _
                         "Database=Tuscan77.mdf;" & _
                         "DefaultDir=" & App.Path & ";" & _
                         "User name=NPD-4\TUSCANO;Password=;"

       '---------------------------
       ' Connection Object Methods
       '---------------------------

       adoDatabase.ConnectionString = SqlConnect
       adoDatabase.Open
    Else
        Dim mPath As String
        cmdDlg.CancelError = True
        cmdDlg.DialogTitle = "Select database"
        cmdDlg.Flags = cdlOFNHideReadOnly + cdlOFNPathMustExist + cdlOFNFileMustExist
        ' Set filters
        cmdDlg.Filter = "All Files (*.*)|*.*|SQL Server(*.mdf)|*.mdf"
        
        ' Display the Save dialog box
        cmdDlg.FileName = ""
        cmdDlg.ShowOpen
        dMstr = Dir(cmdDlg.FileName)
        mPath = Left(cmdDlg.FileName, InStr(cmdDlg.FileName, dMstr) - 2)
        
        
        SqlConnect = "Driver={SQL Server (*.mdf)};" & _
        "Server=NPD-4\SQLEXPRESS;" & _
        "Database=Tuscan77" & dMstr & ";" & _
        "DefaultDir=" & mPath & ";" & _
        "User name=NPD-4\TUSCANO;Password=;"
        
        '---------------------------
        ' Connection Object Methods
        '---------------------------
        
        adoDatabase.ConnectionString = SqlConnect
        adoDatabase.Open
    End If
    Exit Sub
errhandler:
    If Err = 32755 Then '  Dialog Cancelled
        If MsgBox("Database file not found. Contact Admin", vbOKOnly) = vbOK Then
            Unload Me
        End If
    End If



where NPD-4\SQLEXPRESS is name of my sql server 2008 and Tuscan77 is name of my database in sql server 2008.
Now when i execute code above in vb6 adodb with sql server 2008 i get error telling:
"[Microsoft][ODBC DRIVER Manager] Data source not found and no default driver specified"
Can anyone tell me where i am going wrong or what should i do in terms of fixing this error? Any help or guidance in solving this error would be greatly appreciated. Can anyone help me please.

Is This A Good Question/Topic? 0
  • +

Replies To: how to connect vb6 adodb with sql server 2008 in mdi form of vb6

#2 VB6er   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 42
  • Joined: 27-January 14

Re: how to connect vb6 adodb with sql server 2008 in mdi form of vb6

Posted 19 February 2014 - 12:46 AM

Hi Vishal,

I'm not sure from your code whether you are trying to use ODBC to connect to your SQL database ? If you are you need to set up an ODBC connection first.

It is often a good idea when you are struggling to connect to a SQL database to first set up a connection from Microsoft Access to the SQL database. I assume from what you say you are familiar with Access. Set up a new Access database linked (it is important to 'Link', do not 'Import' the data) to the SQL database. Use External Data>Link Tables - select an ODBC connection (or however you prefer to connect). This will create an Access database that points to the SQL database, it will appear to be just like a normal Access database but is just a front-end to the SQL database.
If you can't get Access to connect to SQL, then you are unlikely to be able to get VB6 to do so.

Once you get a working Access you then have 2 approaches:
You can look at the settings Access is using (DatabaseTools>Linked Table Manager will show details) and then use these in your VB6 code.
Or you can use this new Access database (which points to the SQL database) in your application, using the method for connecting to Access you show in your first example.
Was This Post Helpful? 0
  • +
  • -

#3 maj3091   User is offline

  • D.I.C Lover
  • member icon

Reputation: 331
  • View blog
  • Posts: 2,005
  • Joined: 26-March 09

Re: how to connect vb6 adodb with sql server 2008 in mdi form of vb6

Posted 19 February 2014 - 01:09 AM

I'm no expert in this area, but have a look at the connection string examples in the link below:

ConnectionStrings.Com

Looking at your code, your connection string doesn't appear correct for connecting to the DB file directly.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1