Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Question Unanswered: Trouble when converting from A97 to A2K

    Hi all,

    I have a database that was created in A2K, but used the wizard to save as A97. However, it will not open here on the A97 and get an error when it tries to open. The error message says, Compile error: Method or data member not found. Then it goes to the module and highlights the text below in BOLD. I did open the module and did the Debug/ Compile and Save All Modules inside of A97, but still doesn't work. What is the problem and how can I fix it so that I may open the database???

    Code:
     ' Open the table of Switchboard Items, and find
        ' the first item for this Switchboard Page.
        Set con = Application.CurrentProject.Connection
        stSql = "SELECT * FROM [Switchboard Items]"
        stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
        stSql = stSql & " ORDER BY [ItemNumber];"
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open stSql, con, 1   ' 1 = adOpenKeyset

    thanks in advance for any help,
    BUD

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I believe you can take out the wording Application and just have:

    set con = currentproject.connection
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have you made sure your references are set to ADO instead of DAO?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You can try adding the ADO reference to your database. But I think Access is still going to have problems with the CurrentProject property. In Access 97 there was no such property.

    It looks like the code is behind the Switchboard form. If so, you might want to rename the Switchboard table, delete the form, and then have Access (97 version) recreate the Switchboard. Then delete the new Switchboard table and rename the old Switchboard table. I am assuming there aren't any differences between the Switchboard table structure.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    CurrentProject property. In Access 97 there was no such property.
    Correct, Access 97 doesn't have it. As an alternative, you can probably get what you want by taking the Connection property of a table:
    Code:
    con = CurrentDB.TableDefs("tblWhatEver").Connection
    Also, I'm not sure how well Access 97 works with ADO, even when it is referenced. That being said, when using DAO and ADO in the same environment, make sure any reference to RecordSet objects is explicit:
    Code:
    Dim rs As ADODB.RecordSet
    Actually, it's a good idea to make those types of declarations explicit all the time anyway.

    tc

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by tcace
    Correct, Access 97 doesn't have it. As an alternative, you can probably get what you want by taking the Connection property of a table:
    Code:
    con = CurrentDB.TableDefs("tblWhatEver").Connection
    Also, I'm not sure how well Access 97 works with ADO, even when it is referenced. That being said, when using DAO and ADO in the same environment, make sure any reference to RecordSet objects is explicit:
    Code:
    Dim rs As ADODB.RecordSet
    Actually, it's a good idea to make those types of declarations explicit all the time anyway.

    tc
    Thanks to all, I will give it a try when I get to work in the morning. Sure wish they would upgrade to at least A2K, this is beginning to bug me.

    BUD

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by pkstormy
    I believe you can take out the wording Application and just have:

    set con = currentproject.connection
    Hi Paul,

    Didn't work. When I did that all it did was lock me into the Code section in a way that I had to just Close Access itself as I couldn't get it to let me back into the database. Once I re-opened it again, I got the error that took me back to the same error point. Really a bummer. Uncle Bill needs to quit meddling with things like that and let them all work efficiently together, old and the new. Going to try the other things suggested.

    tcace, what Table do I use actually? The table for the switchboard is the one listed in code already. as the Select statement. Should I put that table there? Heck, I'll just try it and see.......news at eleven.....lol

    thanx

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Unhappy

    Quote Originally Posted by tcace
    Correct, Access 97 doesn't have it. As an alternative, you can probably get what you want by taking the Connection property of a table:
    Code:
    con = CurrentDB.TableDefs("tblWhatEver").Connection
    Also, I'm not sure how well Access 97 works with ADO, even when it is referenced. That being said, when using DAO and ADO in the same environment, make sure any reference to RecordSet objects is explicit:
    Code:
    Dim rs As ADODB.RecordSet
    Actually, it's a good idea to make those types of declarations explicit all the time anyway.

    tc
    Just tried the con = CurrentBD.TableDefs ("Switchboard Items").Connection and it opened the DB in debug and highlighted Connection. Not sure what to do other than try to just recreate it all in A97. Been so long since I did it can't remember all I did. Bummer.

    thanks much,
    BUD

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by DCKunkle
    You can try adding the ADO reference to your database. But I think Access is still going to have problems with the CurrentProject property. In Access 97 there was no such property.

    It looks like the code is behind the Switchboard form. If so, you might want to rename the Switchboard table, delete the form, and then have Access (97 version) recreate the Switchboard. Then delete the new Switchboard table and rename the old Switchboard table. I am assuming there aren't any differences between the Switchboard table structure.
    DC,
    Going to try this one next, as soon as I get some time here. Just got busted doing this when I should have been doing my other stuff.....my oh my.

    BUD

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ok, you're in Access 2000, working an an app that will run in Access 97?

    I recommend not using any ADO. Make sure you are referencing DAO - that may explain why the Connection property isn't recognized - the TableDef object of CurrentDB is a DAO object.
    Code:
    Dim tblDef As DAO.TableDef
    Dim tblDefs As DAO.TableDefs
    Dim sCon As String
    
    Set tblDefs = CurrentDB.TableDefs
    For Each tblDef In tblDefs
        If tblDef.Name = "Switchboard" Then Exit For
    Next
    sCon = tblDef.Connection
    I've noticed that sometimes the TableDefs object of CurrentDB doesn't like calling a TableDef by name. Also, if you have the wrong name, the object isn't set since there' no matching object, and then the property doesn't exist and you get the error you described.

    Hope this helps. If it's any consolation, my biggest customer is STILL using Access 97. Let me tell you the version conflict problems I've had to tackle as they upgrade the REST of Office, but not Access, so now I have multiple version of Office to contend with.

    tc

  11. #11
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by tcace
    ok, you're in Access 2000, working an an app that will run in Access 97?

    I recommend not using any ADO. Make sure you are referencing DAO - that may explain why the Connection property isn't recognized - the TableDef object of CurrentDB is a DAO object.
    Code:
    Dim tblDef As DAO.TableDef
    Dim tblDefs As DAO.TableDefs
    Dim sCon As String
    
    Set tblDefs = CurrentDB.TableDefs
    For Each tblDef In tblDefs
        If tblDef.Name = "Switchboard" Then Exit For
    Next
    sCon = tblDef.Connection
    I've noticed that sometimes the TableDefs object of CurrentDB doesn't like calling a TableDef by name. Also, if you have the wrong name, the object isn't set since there' no matching object, and then the property doesn't exist and you get the error you described.

    Hope this helps. If it's any consolation, my biggest customer is STILL using Access 97. Let me tell you the version conflict problems I've had to tackle as they upgrade the REST of Office, but not Access, so now I have multiple version of Office to contend with.

    tc
    Precisely my situation here TCace. They don't seem to realize just how much trouble they cause by not upgrading Access along with the rest. Funny, being as Access can do so much for the company and to me a real necessity, that it always gets last consideration. They will not give us the upgrade to Access and leaving that up to the IT people. Said that if we need a database to put in a request with them, then red tape, red tape , blah blah blah and 1 yr. later you still don't have it. I had created one database already and in full use. They said they don't want a lot of rogue databases running around everywhere and when the person that created it leaves, there is no way to maintain it. My case is I create it to make MY job easier, and mine alone. But, that's how the company thingy goes. I will give your option a try when I get back from my days off. I'll let you know how it works and what I come up with.

    have a nice one,
    BUD

Posting Permissions

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