Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Join Date
    Mar 2008
    Posts
    19

    Unanswered: Help With Access 97

    Hi All,

    I have created my database under Access 2003 but now i need to use it in Access 97. When i open it under Access 97 it show "compile error : Method or data member not found". It highlights " Set cn = Application.CurrentProject.Connection".I have attach my code for you. Can anyone help me with this.

    Private Sub cmdLogin_Enter()


    Dim strsql As String
    Dim strpw As String
    Dim flag As Integer

    Dim lr As LoginRecord

    'Setting connection for the Database

    Set cn = Application.CurrentProject.Connection
    Set rs = New ADODB.Recordset

    flag = 1 'Flag to handle the error



    If Me.txtloginid = "" Or Me.txtPassword = "" Then ' Checking if Login ID and Password is enterned or not

    MsgBox "Please enter Login id or Password"
    Exit Sub

    End If



    txtloginid.SetFocus
    Lgn = txtloginid.text

    strsql = "SELECT * FROM Employees WHERE LoginID = '" & txtloginid.text & "'" 'Setting the SQL command

    rs.Open strsql, cn, adOpenForwardOnly, adLockOptimistic 'Opening the recordset of the table Employees

    On Error GoTo errorhandler 'handling error when there is no records found in table




    strpw = rs.Fields("Password")
    Module1.pass = strpw ' Transferring the password in a global varilable defined in module1.

    Module1.Fname = rs.Fields("FirstName")
    Module1.Lname = rs.Fields("LastName")
    Module1.Acs = rs.Fields("Access")
    Module1.Pros = rs.Fields("Process")
    Module1.Acs_UK_Settlement = rs.Fields("Access_UK_SETTLEMENT")
    Module1.Acs_Queue = rs.Fields("Access_Queue")
    Module1.Acs_Review = rs.Fields("Access_Review")


    'Me.txtPassword.SetFocus

    If strpw = txtPassword.Value Then


    If txtPassword.Value = "password" Then

    Me.txtloginid.Value = ""
    Me.txtPassword.Value = ""
    Me.Visible = False

    Module1.ChngPass 'Calling the Change password function from Module1 to change the password
    Set rs = Nothing
    Set cn = Nothing
    Exit Sub

    End If



    If Acs = "User" Then

    Me.txtloginid.Value = ""
    Me.txtPassword.Value = ""
    Me.Visible = False
    'DoCmd.Close "First_Login"
    DoCmd.OpenForm "UserPage"

    'Form.Visible = False

    Else

    Me.txtloginid.Value = ""
    Me.txtPassword.Value = ""
    Me.Visible = False

    DoCmd.OpenForm "ManagePage"
    lr.LoginTime

    End If

    flag = 0


    Else

    MsgBox (" Password or Login Id is incorrect")

    flag = 0

    End If

    rs.Close
    Set rs = Nothing
    Set cn = Nothing

    errorhandler:
    If flag = 1 Then
    MsgBox "Invalid Login ID. Try Again"
    Exit Sub

    End If


    End Sub

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello abie!

    Look at "DemoOpenTblVBAa97.mdb" (attachment).
    Look at Form1 (VBA), I think it can help you.
    Attached Files Attached Files

  3. #3
    Join Date
    Dec 2007
    Location
    India
    Posts
    16
    Have you converted the db to '97 format using Database Utilities..

    Database: MS Access 2003

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You might need to check the References in VB too. I do a lot of coding in Access 97 and the standard libraries don't have CurrentProject within the Application object.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Mar 2008
    Posts
    19
    I have tried to convert using Database Utilities but it doesn't solve the problem

  6. #6
    Join Date
    Mar 2008
    Posts
    19

    Help needed in Access 97

    Quote Originally Posted by StarTrekker
    You might need to check the References in VB too. I do a lot of coding in Access 97 and the standard libraries don't have CurrentProject within the Application object.
    So what shall i replace with ??

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    CurrentDB.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Mar 2008
    Posts
    19
    I have done the changes but now it highlights "Set cn = Application.CurrentDB.AccessConnection Its says AccessConnection data memeber not found

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why did you change it to AccessConnection?

    I know there is a CurrentDB.Connection, but there is no AccessConnection, just as the error message is telling you.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I just thought I throw this out there (since it uses ADO code to open/close recordsets and does a password type system): http://www.dbforums.com/showpost.php...9&postcount=68

    If you get a chance, see if the example (especially the Admin modules), help in any way. I use this type of logon/password/security type checks in a lot of applications I design. Even if it's not what you're looking for, the example is something to consider as a design and worth looking at.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Mar 2008
    Posts
    19
    StarTreeker thank you for guiding me. I manage to solve the connection problem but now it prompt another error at "rs.Open strsql, cn, adOpenForwardOnly, adLockOptimistic" .The error is The connection cannot be used to perfom this operation.Below is the ammended code.

    Private Sub cmdLogin_Enter()


    Dim strsql As String
    Dim strpw As String
    Dim flag As Integer

    Dim lr As LoginRecord

    'Setting connection for the Database


    Set Connection = CurrentDb

    Set rs = New ADODB.Recordset

    flag = 1 'Flag to handle the error



    If Me.txtloginid = "" Or Me.txtPassword = "" Then ' Checking if Login ID and Password is enterned or not

    MsgBox "Please enter Login id or Password"
    Exit Sub

    End If



    txtloginid.SetFocus
    Lgn = txtloginid.Text

    strsql = "SELECT * FROM Employees WHERE LoginID = '" & txtloginid.Text & "'" 'Setting the SQL command

    rs.Open strsql, cn, adOpenForwardOnly, adLockOptimistic 'Opening the recordset of the table Employees
    On Error GoTo errorhandler 'handling error when there is no records found in table
    Last edited by abie16; 04-25-08 at 04:16.

  12. #12
    Join Date
    Mar 2008
    Posts
    19
    Thank you pkstormy for the reference but its in 2000 and 2002. Currently i doing in 97. I have done the database in 2003 but now need to convert it to 97 and its giving me so much of errors which i don't know how to fix.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Set Connection = CurrentDb
    ...
    rs.Open strsql, cn
    Notice anything?
    Last edited by gvee; 04-25-08 at 08:40.
    George
    Home | Blog

  14. #14
    Join Date
    Mar 2008
    Posts
    19
    I tried to change the cn to CurrentDb but it still shows the same error

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ok, I have edited my post highlighting what I was pointing out
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •