Solved

ADO connection to Network share

Posted on 2004-10-29
204 Views
Last Modified: 2008-02-26
How would I create an ADO connection within Visual Basic 6 to an Access db on a Network share, i.e. \\SomeServer\SomeShare\SomeAccess.mdb?  The share requires a login and password, so I assume the ADO connection would have to accommodate this, but how?
0
Question by:arichexe
    7 Comments
     
    LVL 54

    Expert Comment

    by:nico5038
    0
     
    LVL 83

    Accepted Solution

    by:
    Check this web site for ADO connect strings:

    http://www.able-consulting.com/ADO_Conn.htm
    0
     

    Author Comment

    by:arichexe
    I used www.able-consulting.com's example below, but could never get it to work.  I think the problem is that the network share requires a login.  The login in the example below is for the database, not the share, I think.  I'm beginning to think I'll need to dynamically map a drive, before I attempt to connect, which will be really hairy.

    oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
                      "Dbq=\\myServer\myShare\myPath\myDb.mdb;" & _
                      "Uid=admin;" & _
                      "Pwd="


    0
     
    LVL 54

    Assisted Solution

    by:nico5038
    Guess you found the solution :-) The database needs to be "reachable" and thus the login to the networkshare has to be arranged before building the connection.

    Nic;o)
    0
     

    Author Comment

    by:arichexe
    Is there anyway to automate the login to the share?
    0
     
    LVL 26

    Assisted Solution

    by:Alan Warren
    Hi arichexe

    Can you browse to 'myServer\myShare\myPath\myDb.mdb' from your pc?
    If you can, then it is reachable. Simple test: paste the path into an explorer address bar and hit enter \\myServer\myShare\myPath\

    The folder must have modify permissions assigned for users/groups you wish to access this resource.
    For the sake of testing the following, I gave Everyone modify permissions on the network folder that contains the db.

    ' This works on my network.

    Sub someSub()
      Dim objRs As ADODB.Recordset
      Dim objCn As ADODB.Connection
     
      Dim strSql As String
      Dim strDataConnect As String
      Dim strDataPath As String
     
      strDataPath = "\\Mordor\shared\Data\Northwind.mdb"
      strDataConnect = adoConnectJet40(strDataPath, "password")
     
      Set objCn = New ADODB.Connection
      objCn.Open strDataConnect
     
      strSql = "select * from Employees"
      Set objRs = New ADODB.Recordset
      objRs.Open strSql, objCn, adOpenForwardOnly, adLockReadOnly
     
      With objRs
        While Not .EOF
          Debug.Print .Fields(0)
          .MoveNext
        Wend
        .Close
      End With
     
      Set objRs = Nothing
      Set objCn = Nothing
     
    End Sub


    Public Function adoConnectJet40(psDataPath, psFilePassword)
     
      ' returns Jet 4.0 ADO connect string:
      '   "Provider=Microsoft.Jet.OLEDB.4.0
      '     ;Data Source=C:\xxx\Some.mdb
      '     ;Jet OLEDB:Database Password=xxx"
     
      Dim sProvider, sDataSource, sDBPassword

      sProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
      sDataSource = ";Data Source=" & psDataPath
      If psFilePassword = "" Then
        sDBPassword = ""
      Else
        sDBPassword = ";Jet OLEDB:Database Password=" & psFilePassword
      End If

      adoConnectJet40 = sProvider & sDataSource & sDBPassword

    End Function


    Take care...


    Alan
    0
     

    Author Comment

    by:arichexe
    Don't I need to send a user id along with the passowrd?
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    856 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

    18 Experts available now in Live!

    Get 1:1 Help Now