Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Unanswered: Using VBA to add Fields (columns) to a table

    Hello All!

    I am using Access 2007, VBA, Using ADO and following along from the manuals which seem to be on target. Have moved to using ADO in the VB Scripting. All seemed well until I decided to try and implement adding additional columns to an existing table. The manual (Dummies) advised to set up the connection which I believe I did as I alias'd con1 As ADODB.Connection and followed the remaining steps as laid out in the manual. Obviously I am new to VB and could use a pointer.

    Case is as follows:

    I have a test db named Handlers2
    I have two tables that I created using VB. (Picture Happy Dance Here!!!)
    I want to add some fields to tblEmployees. (Currently only has ID field)
    I am running into syntax errors. Mostly it seems to bomb on the DoCmd****nSQL mySQL
    I KNOW I have a syntax error going on. Can you review the statement for me please and give me a suggestion as to what I am doing wrong when you have a minute. (I know the simple answer is to go into design view on the table and add the fields)
    Code:
    Option Compare Database
    Sub UptdateEmployeetbl()
    Dim con1 As ADODB.Connection
    Set con1 = CurrentProject.Connection
    Dim myRecordSet As New ADODB.Recordset
    myRecordSet.ActiveConnection = con1
    myRecordSet.CursorType = adOpenDynamic
    myRecordSet.LockType = adLockOptimistic
    Dim mySQL As String
    mySQL = mySQL & " (UPDATE tblEmployees "
    mySQL = mySQL & " [FirstName] text (20),"
    mySQL = mySQL & " [LastName]  text (25),"
    mySQL = mySQL & " [StreetAddress] text (50),"
    mySQL = mySQL & " [LocationCity] text (25),"
    mySQL = mySQL & " [State] text (15),"
    mySQL = mySQL & " [County]  text (25)"
    mySQL = mySQL & " [Country] text (15),"
    mySQL = mySQL & " [PostalCode] text (20))"
    
    DoCmd****nSQL mySQL
    End Sub
    What happens is that the code fails and the DoCmd is highlighted in Yellow.
    Can someone give me a pointer as to where I have gone wrong?

    Thanks in advance!
    Fighting Terror One Query @ a Time

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    check your punctuation, looks to me like you are missing a comma after county
    Code:
    mySQL = mySQL & " [County]  text (25),"
    don't know if you need the opening and closing brackets
    you don't need the square brackets if the column name doesn't have spaces in it.

    debugging these sort of issues can be a pain as the coimpiler may not give a very helpfull message

    what I always do, is as you have is assign the SQL to a variable
    then displayed that variable either in a msgbox or set a breakpoint and examine the value of the variable
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    The variable being in this case...?? One more hint please...
    Fighting Terror One Query @ a Time

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the variable you have built your SQL in...
    in this case "mySQL"
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Brackets were removed btw. The square brackets that is around each new desired column. Are you referring to the " " as well?
    And I would rather be riding my Yamaha Roadliner vs sitting in the desert avoid catching the occasional rocket or morter. ;-)
    Last edited by jstpierre; 02-13-12 at 07:02.
    Fighting Terror One Query @ a Time

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry just looked in more detail at your SQL

    I think you may need to use ALTER in place of update.
    ALTER TABLE Statement (Microsoft Access SQL)*[Access 2007 Developer Reference]

    although SQL is the language used to talk to most (relational) databases it comes in two flavours
    DML (Data manipulation) ie processing data using using statements such as select, inert, delete)
    DDL (Data definition) ie processign the physical db design using stateemtns such as drop, create, alter and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Thanks for the pointer to the article. But no soap....I tried adding just one field and it keeps failing. Your kindness is appreciated. Jim
    Code:
    Option Compare Database
    Sub UptdateEmployeetbl()
    Dim dbs As Database
    Set dbs = OpenDatabase(Handlers2.accdb)
    dbs.Execute "ALTER TABLE tblEmployees ADD COLUMN FirstName TEXT (25)"
    Error Message is this: Compile Error: method or data member not found.

    As you can see, I pulled my entire prior statements out of the module window except the SubtdateEmployeetble()
    I did this based on the article example. It returns another error where it does not like the .accdb in the naming convention
    Last edited by jstpierre; 02-13-12 at 07:38.
    Fighting Terror One Query @ a Time

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im suspicious of
    Code:
    Set dbs = OpenDatabase(Handlers2.accdb)
    I woulkd have expected the name of the db to be quoted
    eg:-
    Code:
    Set dbs = OpenDatabase("Handlers2.accdb")
    or
    Code:
    Set dbs = OpenDatabase('Handlers2.accdb')
    access can use either string delimiter

    OK there's a couple of things that may be casuign problems here
    either the table doesn't exist so an alter table woudl fail
    the column already exists so an add column would fail

    note you can only use DDL in an Access db, NOT if you are connecting to an exteranl db type such as SQL server, MySQL and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It would go better if you tried:
    Code:
    Set dbs = OpenDatabase("Handlers2.accdb")
    And you probably should use the full path instead of simply using the file name:
    Code:
    Set dbs = OpenDatabase("C:\Users\Sinndho\Documents\Access\AccessHandlers2.accdb")
    Have a nice day!

  10. #10
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Thanks Sinndho,

    I am now getting the error " The database has been placed in a state by user 'Admin' on machine ********(***represents machine name) that prevents it from being opened or locked.

    I was trying the code originally posted using ADO and could get no where. So Healdem suggested this approach based on an MSDN article (that is very useful by the way)...sigh....
    Fighting Terror One Query @ a Time

  11. #11
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Here is what I did
    Code:
    Option Compare Database
    Sub UptdateEmployeetbl()
    Dim dbs As Database
    Set dbs = OpenDatabase("D:\Documents and Settings\mynamehere\My Documents\Handlers2.accdb")
    dbs.Execute "ALTER TABLE tblEmployees ADD COLUMN FirstName TEXT (25)"
    End Sub
    Fighting Terror One Query @ a Time

  12. #12
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    OF COURSE IF I HAD REMEMBERED TO CLOSE THE TABLE...........All would be good! Thanks to both of you!! It worked.

    One other question, if I wanted to add additional fields would I do the following:
    Code:
    Option Compare Database
    Sub UptdateEmployeetbl()
    Dim dbs As Database
    Set dbs = OpenDatabase("D:\Documents and Settings\mynamehere\My Documents\Handlers2.accdb")
    dbs.Execute "ALTER TABLE tblEmployees ADD COLUMN LastName TEXT (25), ADD COLUMN FavoriteColor TEXT (10), ADD COLUMN City TEXT (15)"
    Thanks...so the question is can i just separate by using a comma , for the additional fields?
    End Sub
    Fighting Terror One Query @ a Time

  13. #13
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Geniuses both of you, thanks again!
    Fighting Terror One Query @ a Time

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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