Ado Connectivity problem

[sunny]

Hi friends..
I am trying to connect my Ms access 2003 database with an interface (interface is also build in access) but,its giving error called "provider can not found and it may not be installed properly".I am not able to solve this problem and one help is my interface contain two radio button( one for normal user and one for admin).I wanted to normal user as a default one.Below I have included my code for ado connectivity .please check it.its very important for me....waiting for your response.
I have attach my database,please find it.

Private Sub Submit_Click()
On Error GoTo ErrorPoint
   Dim strPath As String
   Dim strAccDir As String
   Dim strAccPath As String
   
   Dim rs As ADODB.Recordset
   Dim strConnection As String
   Dim cn As ADODB.Connection
  
   strConnection = "D:\UserData.mdb;"
  ' Create a new ADO Connection object
   Set cn = New ADODB.Connection
   With cn
      .Provider = "DSN"
      .Properties("Data Source").Value = strConnection
      .Open
   End With
   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT User Id,Password from UserLogin where User Id=txtUserId.value and Password=txtPassword"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .CursorLocation = adUseClient
      .Open
   End With
   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   
   Set cn = Nothing
 
   If Len(Nz(Me!txtUserId, "")) = 0 Then
       ' User Name box is empty
       MsgBox "Please enter your User Name before continuing.", _
       vbInformation, "Enter User Name"
       Me.txtUserId.SetFocus
       GoTo ExitPoint
   End If
   If Len(Nz(Me!txtPassword, "")) = 0 Then
       ' Password box is empty
       MsgBox "Please enter your Password before continuing.", _
       vbInformation, "Missing Password"
       Me.txtPassword.SetFocus
       GoTo ExitPoint
   End If
   strAccDir = SysCmd(acSysCmdAccessDir)
   strAccPath = strAccDir & "MSACCESS.EXE"
   strPath = Chr(34) & strAccPath & Chr(34) & " " _
   & Chr(34) & "<Full Path To Database File Here>" & Chr(34) & " " _
   & "/wrkgrp " & Chr(34) & "<Full Path To MDW File Here>" & Chr(34) & " " _
   & "/User " & Chr(34) & Me.txtUserId & Chr(34) & " " _
   & "/Pwd " & Chr(34) & Me.txtPassword & Chr(34)
   Shell strPath, vbMaximizedFocus
   Application.Quit
ExitPoint:
   Exit Sub
ErrorPoint:
   MsgBox "The following error has occurred:" _
   & vbNewLine & "Error Number: " & Err.Number _
   & vbNewLine & "Error Description: " & Err.Description _
   , vbExclamation, "Unexpected Error"
   Resume ExitPoint
End sub

Thanks

[Access_Blaster]

Hi Sunny,

I don't know much about connection strings like you have here. I use ODBC Data Source Admin to make my connections. But after removing your error traps, the code opened on this line:

Private Sub Submit_Click()
On Error GoTo ErrorPoint
   Dim strPath As String
   Dim strAccDir As String
   Dim strAccPath As String
 
   Dim rs As ADODB.Recordset
   Dim strConnection As String
   Dim cn As ADODB.Connection
 
   strConnection = "D:\UserData.mdb;"
  ' Create a new ADO Connection object
   Set cn = New ADODB.Connection
   With cn
      .Provider = "DSN"
      .Properties("Data Source").Value = strConnection
      .Open
   End With
   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT User Id,Password from UserLogin where User Id=txtUserId.value and Password=txtPassword"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .CursorLocation = adUseClient
      .Open
   End With
 
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
 
   Set cn = Nothing
 
   If Len(Nz(Me!txtUserId, "")) = 0 Then
       ' User Name box is empty
       MsgBox "Please enter your User Name before continuing.", _
       vbInformation, "Enter User Name"
       Me.txtUserId.SetFocus
       GoTo ExitPoint
   End If
   If Len(Nz(Me!txtPassword, "")) = 0 Then
       ' Password box is empty
       MsgBox "Please enter your Password before continuing.", _
       vbInformation, "Missing Password"
       Me.txtPassword.SetFocus
       GoTo ExitPoint
   End If
   strAccDir = SysCmd(acSysCmdAccessDir)
   strAccPath = strAccDir & "MSACCESS.EXE"
   strPath = Chr(34) & strAccPath & Chr(34) & " " _
   & Chr(34) & "<Full Path To Database File Here>" & Chr(34) & " " _
   & "/wrkgrp " & Chr(34) & "<Full Path To MDW File Here>" & Chr(34) & " " _
   & "/User " & Chr(34) & Me.txtUserId & Chr(34) & " " _
   & "/Pwd " & Chr(34) & Me.txtPassword & Chr(34)
   Shell strPath, vbMaximizedFocus
   Application.Quit
ExitPoint:
   Exit Sub
ErrorPoint:
   MsgBox "The following error has occurred:" _
   & vbNewLine & "Error Number: " & Err.Number _
   & vbNewLine & "Error Description: " & Err.Description _
   , vbExclamation, "Unexpected Error"
   Resume ExitPoint
End sub

In my case the provider is a driver SQLSRV32.dll . Maybe this will jog someones memory best of luck.


Richard

[sunny]

hi,
Thanks..for your help.can you please tell me the steps for connection through ODBC and what are the changes required in this code.

thanks

[Access_Blaster]

Hi Sunny,

Is your database split into front end / back end? If not see this link:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm
If your database is split, has the back end been exported to the (SQL) Server? If it has then it’s pretty straightforward. Basically you open your control panel in XP. Look for Administrative tools, open it then look for Data Source (ODBC) this should be done of course at work or where the server resides. Open data source program. You would then use the ADD button and follow the wizards. I use SQL but you may have different requirements. You really can't get into to much trouble here because you can always delete the configuration and start again. There are people on this site that can help you with password protection if you decide to go this route.