Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Dim Database (2000)

    I have found numerous references in books and help files to setting up a DB variable " Dim db As Database" but when I try and do that it is not an option I am offered. Does this mean you have to define the type "DataBase" first and if so how do you do it?

    Thanks

    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dim Database (2000)

    Database is an object in the DAO (Data Access Objects) object library. DAO was the default way of accessing data objects such as recordsets in Access 97 and before.
    In Access 2000 and up, the default data model is ADO (ActiveX Data Objects). DAO is still available, but you have to set a reference before you can use it. To do so:
    <UL><LI>Activate the Visual Basic Editor (Alt+F11)
    <LI>Select Tools/References....
    <LI>Locate Microsoft DAO 3.6 Object Library.
    <LI>Check its box and click OK.[/list]Since DAO and ADO have a few objects with the same name, such as Recordset and Field, it is advisable to distinguish between the two by prefixing DAO object with DAO. and ADO objects by ADODB. For example:

    Dim rst As DAO.Recordset
    Dim cn As ADODB,Connection

    When you type DAO., a list of available objects will be shown by IntelliSense. Same for ADODB.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Dim Database (2000)

    Hans

    Thanks once more, I can now Dim databases. What I am trying to do is update a number of records with a year number, I tried an update query but that did not work as I had to create the record first (if that makes sense) it is two tables one for years the other various peoples data. So I thought I would ask for the year then open a form count the records and cycle through each one inserting the year. Maybe I am going about this the wrong way, what do you think.

    Thanks

    Peter

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Dim Database (2000)

    Are you trying to update a year in a table, if so an UPDATE query will work.
    I don't understand your problem. Is it just one table you are trying to update, and all records in that table?

    Pat

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Dim Database (2000)

    Pat

    No , I am not trying to change a year in a table I need to create a group of new records for each year. My primary table is linked to to a year table. All the year table has is , YearID (autonumber) PersonalID(number) "link to primary and Year (number) . The primary table also has a field that indicates if a record is part of a contributing group. So I need to pull up all the records in the primaty table where the contibution field is true and then add a record for each primary record for the year in question.

    thanks

    Peter

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dim Database (2000)

    What you need is an append query, not an update query. Update queries are for modifying existing records, append queries are for inserting new records.

    Create a selection query based on your primary table that returns all PersonalID's for which Contribution is True. Add a column Year:[Enter Year].
    To test if it's OK up to here, switch to datasheet view. You'll be prompted for a year, then you should see the records that are to be inserted in the year table.
    Switch back to design view, and change the query to an Append query in the Query Menu.
    Specify the year table as the one to append to.
    Specify (if necessary) that PersonalID is to be added to PersonalID and Year to Year.
    Then execute the query by clicking the Run button or selecting Query | Run.

    The SQL for the query would look more or less like this:

    INSERT INTO tblYear ( PersonalID, Year )
    SELECT tblPrimary.PersonalID, [Enter Year] As Year
    FROM tblPrimary
    WHERE (tblPrimary.Contribution = True);

    Of course, your table and field names will probably be different.

    If you would like to use a form to let the user enter the year, replace the parameter [Enter Year] by [Forms]![frmYearEntry]![txtYear] where frmYearEntry is the name of the form and txtYear is the name of the text box.

Posting Permissions

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