Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Unanswered: Converting VBA for access97 to access2000

    I am converting a DB that was originally designed in Access97 to Access2000. I am getting a compile error when opening the DB. It says: "Can't find project or library"

    and highlights this line:

    FY = Right(CStr(Year(Now())), 2)

    I have looked in the help files and it seems to be right to me. Any help will be appreciated.

    This is the entire function:

    Public Function PopulationUpdate()
    Dim FY As String
    If DCount("[ReportDate]", "[Population]", "[reportDate] > now()-30") < 1 Then
    If Month(Now()) < 10 Then
    FY = Right(CStr(Year(Now())), 2)
    Else
    FY = Right(CStr(Year(Now()) + 1), 2)
    End If

    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO POPULATION ( ReportDate, FY, UIC, Employer, Population ) SELECT Format(Now(),'Short Date') AS ReportDate, '" & FY & "' AS FY, PERSONNEL.UIC, PERSONNEL.Employer, Count(PERSONNEL.SSN) AS CountOfSSN FROM PERSONNEL GROUP BY Format(Now(),'Short Date'), PERSONNEL.UIC, PERSONNEL.Employer ORDER BY PERSONNEL.UIC, PERSONNEL.Employer;"
    DoCmd.SetWarnings True
    End If
    End Function

  2. #2
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91

    Re: Converting VBA for access97 to access2000

    Did you check the correct libraries in the tools/reference/libraries option in the MS VBA Editor´s ?

    Sometimes, it may ocurrs "nonsense" erros hen you do not check them.

  3. #3
    Join Date
    Oct 2003
    Posts
    15

    Re: Converting VBA for access97 to access2000

    Originally posted by Mixirica
    Did you check the correct libraries in the tools/reference/libraries option in the MS VBA Editor´s ?

    Sometimes, it may ocurrs "nonsense" erros hen you do not check them.
    When I go to Tools... the references menu is "greyed out". Does that mean it's not installed? If so, do you know what module needs to be installed?

    I found them all (Right, Year, and Now) in the VBA library using the "Object Browser".

  4. #4
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    Access 2000 automatically uses ADO as the preferred object model so to clear this error just go into references and uncheck the ADO check box and select the correct DAO model (probably 3.51)

    Paul

  5. #5
    Join Date
    Oct 2003
    Posts
    15
    Originally posted by Funkster
    Access 2000 automatically uses ADO as the preferred object model so to clear this error just go into references and uncheck the ADO check box and select the correct DAO model (probably 3.51)

    Paul
    When I try to access the references, it's greyed out. Do you know what module I need installed to make references available?

    What would I need to do to change my VBA to use ADO.

    Dave

  6. #6
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91
    I know it´s a silly question but...

    Is there any mdb opened ?
    Last edited by Mixirica; 10-31-03 at 12:54.

  7. #7
    Join Date
    Oct 2003
    Posts
    15
    Originally posted by Mixirica
    I know it´s a silly question but...

    Is there any mdb opened ?
    You mean when I get the error? Yes. I get the error when opening the mdb. It's also open when I am trying to access the references.

    I have determined that "references" is only inaccessible in this DB. If I open a new DB fresh and open the code, I can access the refernces command. Any idea why it would be "greyed out" in the problem DB?

  8. #8
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91
    The mdb file may be corrupted.

    Try to fix it: Tools/Database Utilitie/Compact&correct Database

    (I think this is the correct translation !)

    If it doesn´t work,:

    Try to create another mdb and import forms, queries, etc., from the first one.

  9. #9
    Join Date
    Oct 2003
    Posts
    15
    Originally posted by Mixirica
    The mdb file may be corrupted.

    Try to fix it: Tools/Database Utilitie/Compact&correct Database

    (I think this is the correct translation !)

    If it doesn´t work,:

    Try to create another mdb and import forms, queries, etc., from the first one.

    I tried both. The repair had no effect. Importing to a new database actually gave me another error: User-defined type not defined.


    HOWEVER, I figured out why I couldn't get to the refernces command. I had not stop the code. I believe my problem is steming from the missing "Microsoft DAO 2.5/3.5 Compatibility Library". Can anyone tell me where I can download the library? I am currently checking around on MS.com

  10. #10
    Join Date
    Oct 2003
    Posts
    15
    Originally posted by dgerler I believe my problem is steming from the missing "Microsoft DAO 2.5/3.5 Compatibility Library". Can anyone tell me where I can download the library? I am currently checking around on MS.com
    I found the library on another computer and have it loaded to this machine. That alleviated the original error of "can't find project or library". However, I am now getting a type mismatch error 13. When running debug it highlights this line:

    Set Mydb = DBEngine.Workspaces(0).Databases(0)

    I am new to VBA and even VB so any help with this is appreciated.

  11. #11
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68
    Originally posted by dgerler
    Set Mydb = DBEngine.Workspaces(0).Databases(0)
    What is the type of your Mydb variable? If ADO is your default access method, this statement will not work, as ADO doesn't have a Database object.

    You should include a reference to DAO and declare Mydb as:

    Dim Mydb as DAO.Database

    Then you can use your old DAO code as before, but I think a better (and a little more difficult) option is to port the old code to ADO.

  12. #12
    Join Date
    Oct 2003
    Posts
    15
    Originally posted by mashinovodja

    You should include a reference to DAO and declare Mydb as:

    Dim Mydb as DAO.Database

    I fixed the declaration but I'm still getting the type mismatch.

    Here's the full function in case something stands out:

    Function RegistrationData(Reqinfo As String) As String
    On Error GoTo error_registrationdata:


    Dim Mydb As DAO.Database
    Dim MyRS As Recordset


    Set Mydb = DBEngine.Workspaces(0).Databases(0)
    Set MyRS = Mydb.OpenRecordset("REGISTRATION")

    Select Case Reqinfo

    Case "CommandName"
    RegistrationData = MyRS.COMMAND

    Case "CommandCode"
    RegistrationData = MyRS.COM_CODE

    Case "CommandAddress"
    RegistrationData = MyRS.COM_ADDRESS

    Case "CommandManager"
    RegistrationData = MyRS.COM_MANAGER

    Case "CommandPOC"
    RegistrationData = MyRS.COM_POC

    Case "CommandPhone"
    RegistrationData = MyRS.COM_PHONE

    Case "CommandFax"
    RegistrationData = MyRS.COM_FAX

    Case "ClinicName"
    RegistrationData = MyRS.CLINIC

    Case "ClinicCode"
    RegistrationData = MyRS.CLI_CODE

    Case "ClinicAddress"
    RegistrationData = MyRS.CLI_ADDRESS

    Case "ClinicManager"
    RegistrationData = MyRS.CLI_MANAGER

    Case "ClinicPOC"
    RegistrationData = MyRS.CLI_POC

    Case "ClinicPhone"
    RegistrationData = MyRS.CLI_PHONE

    Case "ClinicFax"
    RegistrationData = MyRS.CLI_FAX

    Case "MailDir"
    RegistrationData = MyRS.MAILDIR

    Case "HROFile"
    If Not IsNull(MyRS.HROFile) Then
    RegistrationData = MyRS.HROFile
    Else
    RegistrationData = "error"
    End If

    Case "AboutYN"
    RegistrationData = MyRS.ABOUTYN

    Case "ActivityName"
    RegistrationData = MyRS.ActivityName

    Case Else
    RegistrationData = "error"

    End Select

    end_registrationdata:
    Exit Function

    error_registrationdata:
    MsgBox "There was an error retrieving data from the Registration Table. Please ensure all data is filled in correctly."
    RegistrationData = "x"
    Resume end_registrationdata:

    End Function

  13. #13
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68
    Originally posted by dgerler
    I fixed the declaration but I'm still getting the type mismatch.

    Here's the full function in case something stands out:

    Function RegistrationData(Reqinfo As String) As String
    On Error GoTo error_registrationdata:


    Dim Mydb As DAO.Database
    Dim MyRS As Recordset
    If you intend to use DAO, you should also declare:

    Dim MyRS As DAO.Recordset

    because otherwise MyRS is considered an ADO Recordset, which is a different object type from a DAO Recordset.

    You should add a DAO. prefix for all objects that must be explicitly declared as DAO objects in order to avoid confusion with ADO objects with identical names. Although they have same names, they are objects of completely different types.

  14. #14
    Join Date
    Oct 2003
    Posts
    15
    Originally posted by mashinovodja
    If you intend to use DAO, you should also declare:

    Dim MyRS As DAO.Recordset

    because otherwise MyRS is considered an ADO Recordset, which is a different object type from a DAO Recordset.

    You should add a DAO. prefix for all objects that must be explicitly declared as DAO objects in order to avoid confusion with ADO objects with identical names. Although they have same names, they are objects of completely different types.
    Okay.. I changed all refernces to Database and Recordset to include the "DAO." I am still getting the type mismatch and it's calling out the same line.

Posting Permissions

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