ADO connection to Network share

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?
arichexeAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Check this web site for ADO connect strings:

http://www.able-consulting.com/ADO_Conn.htm
0
 
arichexeAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
nico5038Connect With a Mentor Commented:
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
 
arichexeAuthor Commented:
Is there anyway to automate the login to the share?
0
 
Alan WarrenConnect With a Mentor Commented:
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
 
arichexeAuthor Commented:
Don't I need to send a user id along with the passowrd?
0
All Courses

From novice to tech pro — start learning today.