Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    8

    Unanswered: Code issues after upgrading from 2003 to 2007

    I just started with a new company which is using Access 2007. I had an application at my previous company that I'm attempting to reproduce, but the working application used Access 2003. I'm having 2 main issues...

    Apparently, 2007 is like having to learn a new language. This code worked fine in Access 2003...

    Public Sub SetEnvironmentLabel()
    Dim strSQL As String
    Dim rsEnv As New ADODB.Recordset

    strSQL = "SELECT E.Name, TC.TestCaseName " & _
    "FROM Environment E, TestCase TC " & _
    "WHERE TC.EnvironmentID = E.EnvironmentiD " & _
    "AND TC.TestCaseID = " & Me.cboTestCaseName.Value

    rsEnv.Open strSQL, CurrentProject.Connection
    Me.lblCurrentEnvironment.Caption = "Environment: " & rsEnv!Name

    rsEnv.Close
    Set rsEnv = Nothing
    End Sub


    First, when compiling this in 2007, I get a User-defined type not defined error on the rsEnv Dim statement. I tried following the 2007 Help text and modified it to lose the "New" and switched ADODB to DAO. That would compile, but then I got a Method or data member not found error on the rsEnv.Open statement.

    Second, while I can cut and paste this code into place, any time I try to enter a new line of code directly (such as typing in Dim strSQL As String manually, or typing in Me.cboTestCaseName.Value manually), Access crashes with a Microsoft Office Access has encountered a problem and needs to close. We are sorry for the inconvenience. error.

    The crashes occur when I press the Space Bar after the "As" clause or when I press the "." key after the "Me" keyword.

    How would this subroutine be written for Access 2007? The Help text hasn't been any help at all. Also, what would cause the crashing issue?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are using ADO objects then use ADO syntax, if you are using DAO objects then use DAO syntax.

    I'd also suggest you avoid using reserved words such as NAME for your columns
    List of reserved words in Access 2002 and in later versions of Access
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2009
    Posts
    8
    Thanks, Jaded.

    I tried updating the code to this...

    Public Sub SetEnvironmentLabel()
    Dim strSQL As String
    Dim rsEnv As DAO.Recordset

    strSQL = "SELECT E.Name, TC.TestCaseName " & _
    "FROM Environment E, TestCase TC " & _
    "WHERE TC.EnvironmentID = E.EnvironmentiD " & _
    "AND TC.TestCaseID = " & Me.cboTestCaseName.Value

    Set rsEnv = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    Me.lblCurrentEnvironment.Caption = "Environment: " & rsEnv!Name

    rsEnv.Close
    Set rsEnv = Nothing
    End Sub


    This time, I got an Error in loading DLL error. I've got MDAC 2.8 SP1 loaded on XP SP3. I also downloaded MSADOX.DLL and ran regSvr32 on it. (I dodn't know if it had to go into any particular directory, though, so I dumped it into C:\Program Files\Microsoft Office\Office12) This error isn't real informative. Any ideas as to what the problem is? (Besides me, I mean.)

Posting Permissions

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