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

    Changing field attributes in code (Access XP)

    I have been changing a database for a client and have made many changes to the tables. What I would like to do is to make these changes to the original database's tables in VBA.
    Can I change a certain field in a table from a text field to a long field in VBA?
    Can I delete indexes using VBA?
    Can I add fields to a table using VBA?
    Can I delete fields from a table using VBA?

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

    Re: Changing field attributes in code (Access XP)

    You can use SQL, DAO or ADOX <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    If you have a reference to the DAO library, type CreateIndex in the Immediate window and press F1. Read the subject, then view some of the examples, they will show how to create and delete indexes. Look at See Also, and look up ALTER TABLE; this offers a way of modifying a table using DDL SQL.

    Also type CreateField in the Immediate window and press F1.

    You can also search for CreateField and CreateIndex in this forum.

    Some random examples: <post#=274678>post 274678</post#>, <post#=322312>post 322312</post#>, <post#=246831>post 246831</post#>.

    An alternative is ADOX. You must set a reference to Microsoft ADO Ext. 2.7 for DDL and Security for this. <!profile=MarkD>MarkD<!/profile> knows much more about that, see for example <post#=305390>post 305390</post#>.

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

    Re: Changing field attributes in code (Access XP)

    Thanks for that Hans, I hope Mark puts his 2 cents worth in.

    I tried an INSERT INTO query the other day and it didn't work, the SQL was basically:
    INSERT INTO tablename (a, b, c, d, e)
    VALUES ('a', 12, 123, 'd', 'e')
    I put in alternate code to add the record to the tablename table using VBA code AddNew etc.

    I am at odds as to why this does not work.

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

    Re: Changing field attributes in code (Access XP)

    The SQL looks ok by itself. What was the error message you got?

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

    Re: Changing field attributes in code (Access XP)

    There was no error message, I even put the code into a query and ran the query and it still didn't insert the record.

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

    Re: Changing field attributes in code (Access XP)

    Strange. Have you tested if it works in a fresh database? Perhaps the one you tried it on has other problems.

    (If you have table or field names with spaces or unusual characters, you must put square brackets [ ] around them, but omitting them would doubtlessly cause an error message, as would using data of the wrong type. So I don't think that could cause the problem.)

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

    Re: Changing field attributes in code (Access XP)

    It now works. What I had noticed yesterday was that Access was playing up in that when I was marking text it was going a bit crazy. It was around that time when I could not do the INSERT INTO statement.
    I exitted out of the database and it fixed the marking of text, I have encountered this before, but wrt the INSERT INTO it never occurred to me to test it again as I had changed it to the AddNew etc by that time.

    Oh well, alls well that ends well. Thanks for your time again Hans.

Posting Permissions

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