MS Access programming advice needed...

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  June 4, 2004 at 12:40 pm

  #66013

  I have remote users with access to my SQL Server 2000 database. They must use SQL Server authentication as they are not and will not be on a trusted domain.

  Currently the only way they can change their password is to come to my office and change it on the server.

  I have created a stored procedure, based on sp_password, that will validate and change their password.

  I want to create a form that they can use to change their password. MS Access is available to everyone, so I figure I can design a form in Access and have it pass the information to SQL Server.

  However, I don't use Access and the Help isn't very informative. Any suggestions on a good book/tutorial to guide me in creating the form for passing information to SQL Server?

  -SQLBill

• kaeg

  Old Hand

  Points: 378

  More actions

  June 5, 2004 at 8:23 am

  #509126

  If you use Access Project, you try this out.

  CurrentProject.Connection.Execute "EXEC sp_password '" & Me.txtOld & "', '" & Me.txtNew & "'"

  

   

   

• Chris Langley

  SSC Veteran

  Points: 202

  More actions

  June 6, 2004 at 9:33 pm

  #509170

  You could build a component that you create in VB and register it on the computer.  In that component try to open the connection with the user name and password.  Return weather or not the user name and password combo worked to open it. 

• Matt Simmons

  SSCrazy

  Points: 2846

  More actions

  June 7, 2004 at 5:52 am

  #509198

  While Access can certainly handle this job, everyone should also have a browser on their pc.  Write an ASP page or add another form to your VB app. 

  Either one of these solutions should fit in better with your main app. than adding in a seperate program in Access.

  Regards,
  Matt

• David Losiewicz

  SSC Enthusiast

  Points: 133

  More actions

  June 8, 2004 at 8:05 am

  #509476

  MS Access features something called a pass-through query.  You can execute a stored procedure on the SQL Server from MS Access using the pass through query. 

   

  MS Access help keyword = pass-through queries  

  MS Knowledge base Articles Q209116 also has some info regarding how you might create an MS Access form to get stored procedure parameters before sending the command to SQL Server.

  good luck

  dxl

   

   

• Alamir

  SSCertifiable

  Points: 6085

  More actions

  June 9, 2004 at 2:04 am

  #509591

  do something like this in Access

   dim cnn As ADODB.Connection

   Dim cmd As ADODB.Command

   Dim ErrorOnSQLProc As Long

   Dim DailyInvID_Out As Long

  Set cnn = New ADODB.Connection

  cnn.Open "DSN=" & DSNNAME

   

  If MsgBox("Do you want to change password", vbYesNo, "Info") = vbNo Then

    Exit Sub

  End If

     DoCmd.Hourglass True

     DoCmd.SetWarnings False

      

      

      Set cmd = New ADODB.Command

      cmd.ActiveConnection = cnn

      cmd.CommandTimeout = 300        'Timeout = 5 minutes for command and connection

      cmd.CommandText = "YourStoredProcedureName"

     

      cmd.CommandType = adCmdStoredProc

      cmd.Parameters("@YourInput") = Me.MyInput

      cmd.Execute

            

    Set cmd = Nothing   'Release Memory

    Set cnn = Nothing

        

     DoCmd.Hourglass False

     DoCmd.SetWarnings True

  
  Alamir Mohamed
  Alamir_mohamed@yahoo.com