1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2000 to 2010 convert ADODB.Command error on page load

Discussion in 'Business Applications' started by SiobhanP, Dec 20, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    I have an Access 2000 database that I'm having some problems with. First, it's using replication, and I need to get away from that. So I'm creating a new database and bringing all the existing objects into it. The new database is in Access 2002-2003 format (according to Access) at this point, but I am working on it in Access 2010 (every copy we had of Access 2003 running older machines was destroyed in Tropical Storm Irene). I note at this point that I didn't write this code or create this database. It was built a long time ago by one employee who retired and doesn't remember much about the database, and another who retired and had added on it I can't even get to answer my emails right now. This database was originally written in Access '97, then converted to an Access 2000 database and it hasn't been touched with conversions since then. So, to the actual problem:

    When I try to open up the first, most important form in the database, I get this error:
    Compile Error: User-defined type not defined.

    And it's highlighting a line of code that reads:
    Dim Cmd As ADODB.Command

    I tried doing some searching for this error and added a reference to ActiveX Data Objects 6.0 library based on some of what I read. That didn't help. I'm not entirely sure what this sub is trying to accomplish, and so it's hard for me to figure out if these ADODB commands even make any sense to be doing when everything is self-contained (this database is NOT a front end for an SQL backend).

    Here's the entire sub:
    Code:
    Private Sub Form_Current()
        FsubFuncEvalFloodStor.Visible = False
        fsubFunctionalEvalWQProt.Visible = False
        fsubFunctionalEvalFish.Visible = False
        fsubFunctionalEvalWildHab.Visible = False
        fsubFuncEvalHydroVeg.Visible = False
        fsubFuncEvalEndgSpp.Visible = False
        fsubFuncEvalEduRes.Visible = False
        fsubFuncEvalRecEcon.Visible = False
        fsubFuncEvalOpenSpace.Visible = False
        fsubFuncEvalErosion.Visible = False
    'MsgBox "Wetl " & IIf(IsNull(Form_frmProjects2.txtProjectNo), "Null", Form_frmProjects2.txtProjectNo)
      On Error GoTo PROC_ERR
    Dim Cmd As ADODB.Command
    Dim strSQL As String
    Dim strProjNo As String
    
    'continue if VarType(8) is a valid string
    If VarType(Form_frmProjects2.txtProjectNo) = 8 Then
        If Form_frmProjects2.chkWetPresent Then
            If DCount("ProjectNo", "Wetlands2", "ProjectNo = '" & Form_frmProjects2.txtProjectNo & "'") = 0 Then
                strProjNo = Form_frmProjects2.txtProjectNo
                Set Cmd = New ADODB.Command
                strSQL = "INSERT INTO Wetlands2 (ProjectNo, WetlandNo) VALUES ('" & strProjNo & "', 1);"
                Debug.Print strSQL
                    With Cmd
                    .ActiveConnection = CurrentProject.Connection
                    .CommandText = strSQL
                    .CommandType = adCmdText
                    .Execute
                    End With
                Set Cmd = Nothing
                If DCount("ProjectNo", "tblSamplePoints", "ProjectNo = '" & Form_frmProjects2.txtProjectNo & "'") = 0 Then
                Set Cmd = New ADODB.Command
                strSQL = "INSERT INTO tblSamplePoints (ProjectNo, WetlandNo, SampleNo) VALUES ('" & strProjNo & "', 1, 1);"
                Debug.Print strSQL
                    With Cmd
                    .ActiveConnection = CurrentProject.Connection
                    .CommandText = strSQL
                    .CommandType = adCmdText
                    .Execute
                    End With
                Set Cmd = Nothing
                End If
            End If
        End If
    End If
      
    PROC_EXIT:
      Exit Sub
    PROC_ERR:
      MsgBox "ADODB Save in Wetlands2 Error: " & Err.Description
      Resume PROC_EXIT
    
    End Sub
    
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    If this was originally written in Access 97 there are more than likely lots of missing references. It may be simpler to rewrite the code for the functionality in Access 2010 and save a lot of heartache down the road. It should work after adding the ActiveX reference. You may have to close out of the database and open it again.
     
  3. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    I don't believe the code in question was originally written in Access 97. I believe it was added sometime after 2003 by someone who retired in 2009.

    I've been working on this for weeks. I've closed and opened the database several times. The problem here is I have NO IDEA how to rewrite this for Access 2010. I don't know what the heck it's supposed to be doing. Do you have any suggestions at all for how this might need to be rewritten?
     
  4. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Look up replacing ADODB with DAO. You should only have to rewrite the connections to the db.

    Does it work in front end/back end mode in Access 2010 instead of your unified database structure?
     
  5. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    I've never tried it with front end/back end. I suppose I could give that a go. :) And thanks :)
     
  6. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Wasn't the database originally built that way? I am assuming that is what you meant by replication. If it is in a multi-user environment that is the best way to keep this database.
     
  7. SiobhanP

    SiobhanP Thread Starter

    Joined:
    Jun 22, 2010
    Messages:
    26
    No, the database was built entirely in a single .mdb file. Then there's a "feature" of Access called "replication" where you create copies of the database in other directories. The original database is called the master database, and it's linked inextricably with the slave databases. When you make changes in the database, you have to make them in the master, then sync up the slave databases to it afterwards.

    This feature was deprecated in, I think, 2007, possibly 2010, but in any case, I have got to get rid of it because my master database has become utterly corrupted, and if I do any work in it then sync, it'll destroy the slave. This is an unsustainable model at this point. There is no way to stop a database from being either a master or a slave once they've been made one or the other. The only way to get rid of this replication is to make a new database and import all your objects into it.

    So I've taken a copy of the slave, made a fresh, blank database, then imported all of the objects from the slave into the fresh copy of the database. And now this code is giving me the error as described above. But it all still resides in a single .mdb file.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1032150

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice