Using VBA to add Fields (columns) to a table

jstpierre · #1 (**permalink**) 02-13-12, 05:39

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!

healdem · #2 (**permalink**) 02-13-12, 05:46

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

jstpierre · #3 (**permalink**) 02-13-12, 05:57

The variable being in this case...?? One more hint please...

healdem · #4 (**permalink**) 02-13-12, 05:58

the variable you have built your SQL in...
in this case "mySQL"

jstpierre · #5 (**permalink**) 02-13-12, 05:58

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. ;-)

healdem · #6 (**permalink**) 02-13-12, 06:02

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.

jstpierre · #7 (**permalink**) 02-13-12, 06:25

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

healdem · #8 (**permalink**) 02-13-12, 06:50

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.

Sinndho · #9 (**permalink**) 02-13-12, 06:50

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")

jstpierre · #10 (**permalink**) 02-13-12, 07:06

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....

jstpierre · #11 (**permalink**) 02-13-12, 07:08

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

jstpierre · #12 (**permalink**) 02-13-12, 07:14

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