How to access the database from the other system or server?

Question

Using VB 6

How to access the database from the other system or server?

Code

Cn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; Data Source=" & _
                        App.Path & "\DC-CS.MDB"
Cn.Open

I don’t want to give the connection directly in my code, I want to select a connection and *.mdb file from the other system or same system or server.

What type of control I have to use in VB for connection testing and mdb file selection from the other system or server?

First thing I want to select a connection, if connection tested, then I want to select a *.mdb file from other system or server. How can I select a connection and *.mdb file in VB 6.

Please need VB 6 Code Help

Answer 1

Share your App.Path to the domain. Then on the other system, point it's App.Path to your share.

If you want to dynamically select your path and/or file, then use the FileOpen dialog/control.

As for code examples, I haven't used real VB6 in almost 5 years, so I do not have any true examples or anyway to make one. The closest I can come is Excel VBA 6.5. Here is an example of a VBA function that I use in Excel to browse for and open an Access database:

Public Function OpenDB() As Boolean
'Open the Database and indicate if successful'
 If IsOpen Then
    OpenDB = True   'we are already open'
    Exit Function
 End If

 If sFile = "" Then sFile = GetSetting("YourAppName", "History", "DBName")

 With Application.FileDialog(msoFileDialogFilePicker)
    'specify the file open dialog'
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Access Workbooks", "*.mdb"
    .Filters.Add "All Files", "*.*"
    .InitialFileName = sFile
    .Title = "Open TIP Database"

    .Show

    If .SelectedItems.Count > 0 Then
        sFile = .SelectedItems(1)
    Else    'user canceled ...'
        OpenDB = False
        Exit Function
    End If
 End With

 DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFile
 On Error Resume Next
    DB.Open
 If Err.Number <> 0 Then
    MsgBox "Error(" & Err.Number & "): " & Err.Description, vbOKOnly + vbCritical, "Error in OpenDB"
    OpenDB = False
    Exit Function
 End If

 'Opened ok, so finsh-up and exit'
 OpenDB = True
 SaveSetting "YourAppName", "History", "DBName", sFile
End Function

You will have to replace the "Application.FileDialog" with a reference to a VB Forms FileDialog control/component, which you should drop onto your VB from from the toolbox (its actually a component-control, so it's not really visible).

You should expect that it will have some differences because these are GUI features and the VB Forms GUI is radically different from the Excel GUI. So the properties & settings might be different and you'll have to play around with them or look them up in VB Help.

Note: the GetSetting and SaveSetting stuff is just saving the last file name and path used in the registry, so that it can use it as the default location for the next time.

Answer 2

RBarry is referring to the fact that you can "share" a particular folder on one computer, so that it is accessible to another computer.

If two computers are named computer1 and computer2, then computer2 can share a folder on it's C: drive giving it some name like "sharedfolder". Then computer1 can access that folder using the path "\\computer2\sharedfolder".

If an application on computer1 can't use that path, then you can "map" a drive letter (like F:) to the path "\\computer2\sharedfolder". Then it just looks like the F: drive on computer1.

Answer 3

You can use the Data Link Properties dialog to define an OLE DB connection string. You can start out by predefining the Provider and other attributes, and then let the user browse for an MDB file and choose it if you have predefined Jet 4.0 as the Provider.

Once this selection has been made you can persist the connection as a .UDL (Universal Data Link) file. UDLs were what replaced DSNs a long, long time ago. I'm amazed they get so little use.

---

Here is some sample code that lets your program specify a UDL and a default path for the user to browse for an MDB file. If the UDL does not exist, it opens the Data Link Properties dialog so the user can choose an MDB, and lets them Test Connection from that dialog before Oking or Canceling their settings. Once it has the connection fully defined it persists it as a .UDL file and opens the Connection object.

If the UDL file exists it opens the Connection using the specs in the UDL.

The key here is the DbOpenPromptSave() function.

'Requries references to:
'   Microsoft ActiveX Data Objects x Library (x >= 2.5)
'   Microsoft OLE DB Service Component 1.0 Type Library

Private Function DbOpenPromptSave( _
    ByVal Conn As ADODB.Connection, _
    ByVal UDLPath As String, _
    Optional ByVal MDBSearchStartPath As String = "") As Boolean
    'Returns True if user cancels the dialog.

    On Error Resume Next
    GetAttr UDLPath
    If Err.Number Then
        'No UDL, we need to prompt the user then create one.
        On Error GoTo 0
        Dim dlkUDL As MSDASC.DataLinks

        Set dlkUDL = New MSDASC.DataLinks
        Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                              & "Persist Security Info=False;" _
                              & "Jet OLEDB:Engine Type=5;" _
                              & "Data Source=" & MDBSearchStartPath & "\;" _
                              & "Window Handle=" & CStr(Me.hWnd)
        If Not dlkUDL.PromptEdit(Conn) Then
            DbOpenPromptSave = True
            Exit Function
        End If

        'Use a Stream as Unicode writer.  Using a relative path to save
        'respects the Current Directory of the process.
        Dim stmUDL As ADODB.Stream
        Set stmUDL = New ADODB.Stream
        With stmUDL
            .Open
            .Type = adTypeText
            .Charset = "unicode"
            .WriteText "[oledb]", adWriteLine
            .WriteText "; Everything after this line is an OLE DB initstring", _
                       adWriteLine
            .WriteText Conn.ConnectionString
            .SaveToFile UDLPath, adSaveCreateOverWrite
            .Close
        End With
        Conn.Open
    Else
        Conn.Open "File Name=" & UDLPath
    End If
End Function

Private Function DbActions() As Boolean
    'Returns True on cancel.
    Dim connDB As ADODB.Connection

    Set connDB = New ADODB.Connection
    If DbOpenPromptSave(connDB, "sample.udl", App.Path) Then
        MsgBox "User canceled!"
        DbActions = True
        Exit Function
    End If
    DoDbOperations connDB 'Whatever you need to do until closing.
    connDB.Close
End Function

The DbActions() function is simply an example of calling DbOpenPromptSave() to open the database. This function opens the database, calls DoDbOperations() (not shown) to actually work with the open database, and then closes the database Connection.

This example uses a relative path (current directory, usually the same as App.Path) for sample.udl and sets the MDBSearchStartPath (where the Select Access database dialog opens) to App.Path (because this dialog defaults to where the last CommonDialog had been opened).

So in other words...

It looks for/saves the UDL sample.udl in CD (usually App.Path), and the MDB selection dialog opens in App.Path. Whew.

I suppose just passing CurDir$() might have been clearer in this case.

---

I hope this comes close to what you were requesting, it was a little vague.

The MDB selection subdialog the user opens is pretty much a standard CommonDialog.ShowOpen dialog. The user should be able to browse for the MDB file on any drive including file shares on remote systems.