Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Add field to backend table (2002)

    Hi,

    I need to add field [sales_rep] to a backend table in database [SSIS split tables_be.mdb]. The table name is [tblVisitReport] and the format is text. The backend table resides on N:SSISSSIS Split Tables.

    I've searched some of the old stuff on the forum and have tried looking at stuff help but honestly its not only Greek to me, but I don't know where to begin. Can someone point me in the right direction? I'd do this manually but the database lies on multiple off site computers and I figure its about time I learn how to do this via code.

    So, with that said, where do I start this new learning experience?

    Thanks,
    Leesha

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

    Re: Add field to backend table (2002)

    The ALTER TABLE may be what you are after, however I cannot find it in the help, surprise, surprise.

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

    Re: Add field to backend table (2002)

    From Access:
    Help > Microsoft Access Help > Contents tab > Microsoft Jet SQL Reference > Microsoft Jet SQL Reference (yes, twice) > DDL (Data Definition Language) > ALTER TABLE

    From the Visual Basic Editor:
    Type any Access-specific term in a module or in the Immediate window, for example DoCmd, then Press F1.
    Activate the Contents tab, then proceed as above.

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

    Re: Add field to backend table (2002)

    Thanks for the guided tour Hans.

    I have used this command in the past, and was easily able to find in Access 97 help, but Access 2000 and beyond, ????????????

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

    Re: Add field to backend table (2002)

    I agree that you can only find things in the online help if you know what you're looking for, and often also where to look.

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add field to backend table (2002)

    Thanks to both of you! I'll hunt around. The hardest thing I find about Access is that it doesn't use the terminology that I lookup things in. I would never have thought to look under alter tables. I looked under everything you can think of. I did find some code in a book totally by accident, not by the index! God only knows if it will work but at least now I have leads as to where to start.

    I'm sure to be back!

    Leesha

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add field to backend table (2002)

    Hi,

    I tried the following code which I got from "Beginnin Access 2002" by Suusman and Blackburn. The problem is that it blows up and I'm not sure why as I copied it from their code and inserted my table information:

    Dim db As Database
    Dim tbl As tabledef
    Dim fld As Field

    'Start by openning the database
    Set db = CurrentDb()

    'Retrieve thetabledef object

    Set tbl = db.tableDefs("tblVisitReport")


    'Create a field. Set its properties. Add it to the tabledef
    Set fld = tbl.createfield("TestCode", dbtext)

    fld.Size = 50
    fld.Required = False

    tbl.Fields.Append fld

    'Indicates creation was successful

    MsgBox "The database was successfully modified."


    End Sub


    I put it on the click function of a cmd on a form. Since I copied the code (and I think I understand what its saying) I'm not sure what isn't defined.

    Also, while I'm at it, eventually I want the code to actually do this for a table that is stored in a different database not .....set db = currentDb() and I'm not sure how to write that. But, I can tackle that after I figure out what is wrong with this code.

    Thanks,
    Leesha

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

    Re: Add field to backend table (2002)

    This code uses DAO, so you must set a reference to the Microsoft DAO 3.6 Object Library in Tools | References... (in the Visual Basic Editor).

    If you want to operate on another database than the current one, replace

    Set db = CurrentDb

    with

    Set db = OpenDatabase("C:TestBackend.mdb", True)

    You must of course substitute the correct path and filename. The argument True means that you open the database exclusively.

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add field to backend table (2002)

    Hi Hans,

    First thanks for the explanation and help on defining how to do this in a different database. It helps me to understand the why of what I'm doing so I'm not simply copying other people's code and can't replicate or adjust it down the line.

    I set the DAO 3.6 but still get an error, although now in a different spot. The section highlighted is

    fld.size = 5 and the error is "method or data member not found". I took out the line of code to see if that helped and then got the same error on the code fld.required = true

    Leesha

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

    Re: Add field to backend table (2002)

    This is because of an ambigouos declaration. Field can be a DAO object or an ADO object. An ADO field does not have a Size property. I always prefix all DAO and ADODB data types explicitly, to avoid confusion:

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field

    (It's not strictly necessary for Database and TableDef, but it makes the declarations and their relationships clearer)

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add field to backend table (2002)

    That is perfect!! I am so tickled!

    Thanks Hans,
    Leesha

Posting Permissions

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