Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Alter Table Statement In Vba

I have the following SQL statement:

StrSQL = "Alter Table TblOne FieldOne number"

I am changing FieldOne type to be number but would like to force the format to "percent" and the decimal places to "2". I would appreciate some help.....would it go something like this?

StrSQL = "Alter Table TblOne FieldOne number percent decimal(2)"?


View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Syntax Error On ALTER TABLE Query Statement
I am working in VB6 with and Access.mdb database.

I get a syntax error on the Alter Table query statement below. I have tried to change it but I cannot seem to beat the problem.

Using the .Open statement I have done selects/insert/drops/deletes and updates so that does not seem to be the problem.

Can you help me?

CODEdbImportDB.Open "dbImportDB"
rsNewCampaign.Open "ALTER TABLE " & Trim(strCampName) & " RENAME TO " & strHistTblName, dbImportDB, adOpenDynamic, dLockOptimistic

SQL Alter Table (add Columns In Middle Of Table Via Script)
Lets say u have a typical SQL alter table statement like:

ALTER TABLE dbo.tbl_beneficiary_profile
unemp_upto6mon_less25_val int NULL,
unemp_upto6mon_over25_val int NULL ,
sec_public_adm_def_val int NULL ,
sec_non_specific_val int NULL

Basic stuff - but what if u need to update the database in script and - unfortunately, u also need the fields to appear "not at the end of the table" - as per the default when adding fields.

any ideas, i'm thinking surely one does not have to
.1. Create temp table
.2. backup data into temp
.3. Drop main table
.4. Recreate main from scratch
.5. Bring back in data from temp
.6. drop temp table

Hell of a long way for a shortcut



Alter Table
Anyone know the ALTER TABLE field type for HYPERLINK?

Alter Table Help

First let me explain what I am doing. I have a ledger program that I have made that has a list of Rental properties, and a list of Code Items. What I am trying to do is when the user wants to add a new property or a code item, I need to take the new names and have the db save the names in the LabelHouse and LabelCodeItems Table of the db (this works fine), and also the the record data be the same as the table name(my problem). When I am trying to add the record data to the new field I get an error. Here is the error and some code. ERROR "Ado could not find the object in the collection corresponding to the name or ordinal reference request by the app"

If PropA.Text > "" Then
Check1.Value = vbChecked
sql$ = "ALTER TABLE LabelHouse ADD " & PropA.Text & " varchar(50)"
db.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & App.Path & "" & sDB & "; PASSWORD=********"
Set rs = db.Execute(sql$)

'Below line erros if rs.Fields is (PropA.Text) or (0)
rs.Fields(PropA.Text).Value = PropA.Text
End If

Alter Table Help
I have a alter table query that inserts a field into a table for me.

I run the query with VB Code. How can I put something in the code to tell the database to look in the table and see if that field exists or not before it runs the query. If it does exist then don't run the query and if does not then run the query?

DoCmd.OpenQuery "AlterACE", acViewNormal

Alter Table
I would like to add in a new column to a table. The codes is as following "ALTER TABLE Customer ADD COLUMN DateAdded Date", conn

I get an error at the ADD COLUMN area.

Please advise

Alter Table Via SQL ???
I have a problem... I need to manipulate my Access database tables via SQL, so far not having much luck. Here is my problem:

Table A: Primary Key ID, field type is Integer
Table B: Foreign Key ID, field type is Integer

So, there is a one to many relationship here. I need to ALTER the field type of both fields in both tables from Integer to Long. I can do it manually in Access, but having plenty of trouble trying to do it with SQL.

I am under the assumption that I must first remove the relationship, and then make the field type change... but still no luck yet getting it to work. I have tried DROP CONSTRAINT, but it requires a constraint "name", which I have no clue what that name is.

Any ideas?
Thanks a lot...

Alter Table
I have a text field in an access database that don't accept the empty sting. How do I change this field so it do this thrue SQL and not by open ACCESS and do this manual?

And I want to do it whit ADO and not DAO, maybe ADOX if ADO isn't possible...

Alter Table

I'm fairly new to programming with SQL Server database and don't know where to start. Here is my situation. I have a SQL server database and I want to add two new fields to an existing table. Each of the field will have values of either 0 or 1. I want to have a script or procedure to do this? Where would I do this? In the Query Analyzer?

Thanks in advance!

Alter Table
OK, Muddy is obviously having a bad SQL day

How come this makes a memo field: ?

conn.Execute "ALTER TABLE " & strTable & " ADD Temp text"

and so does this:

conn.Execute "ALTER TABLE " & strTable & " ADD Temp text 100"

How do I make a Text field with length of 100 ???

Alter Table
When you're creating a new column on a table you must specify the field type. There are: TEXT, CURRENCY, etc. BUT how do i say BOOLEAN?
I just wrote BOOLEAN but i got an error.

the sentence is like this:

ALTER TABLE tablename ADD COLUMN columnName Type

Alter Table
I can create a blank field doing

strSQL1 = "ALTER TABLE " & strbatch & " ADD COLUMN donotdisplay Integer;"

DB.Execute strSQL1


but want a yes/no table,

tried putting yes/no where interger is now and also yesno, does anyone have alist of have to create a 'type' of column

Alter Table Via VB
G'day all!

This MAY not be the proper forum....I'm attempting to pass a command from our server to a VB app running on a client's machine.  I'm attempting to change the length of a field from 3 to 4 (in his Access 2002 db), without changing any of the other properties of the field or table.  I keep getting syntax errors with this command:

CODEalter table TableName alter column FieldName char(4)

Alter Table
Hi friends,

I know this may sound like a silly query here, but, i need to alter a table in access.

I am adding a new column ex: planids, of type: Memo. I need to make the allowzerolength property = yes. All this needs to be done by one alter table command.

So far i am able to do the following only


How do i accomodate the allowzerolength property in the above command? this is in Access 2000.


Alter Table From Vb Reg
I want to add columns to table at runtime.

I am using Alter table to add new column

i want to know how to check whether that particular column is exists in the table before adding the column

Pls Help me

Using Alter Table
hi vb gurus

I am trying to use the "Alter Table" to add a column to an existing table created in ms access.when ever i attempt to do this i get an error saying that :
"microsoft jet could not lock the 'table name' as it is in use by another person or process"
i'm unable to understand this

is there any other way to add a column at runtime to a table????

please help!!!!!

have a nice day

Alter Table Using Yes/No Field
could someone help me with my problem in vb/asp...

im wondering why my codes below doesnt work the way i wanted to.i want to create a new column in my table Trip_Info with a Yes/No field,however when i clicked the button(cmdAlter) im getting a new column with the right datatype in the design view but the problem is when i view/run the table...the new column(chkSample) doesnt have a checkbox in it instead i get a 0(zero) default in the chkSample field.

im using an Access database...
Private Sub cmdAlter_Click()
myDB = App.Path
If Right(myDB, 1) <> "" Then myDB = myDB & ""
myDB = myDB & "Win_A_Trip.mdb"
Set Con = New ADODB.Connection
Con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & myDB & ";Persist Security Info=False"

mySQL = "ALTER TABLE Trip_Info ADD COLUMN chkSample YesNo"

Con.Execute mySQL

your reply would be very much appreciated...thanks

alright, I've figured out how to add fields to a table with the ALTER TABLE SQL command, but I've run into a bit of a problem.

I want to add a field called Med(4) to the table, but whenever I execute the commend:

("ALTER TABLE Information ADD Med(4) text")
nothing happens.

Now, I thought the problem might be that the table name wasn't in ' ', so I added them to the query:

("ALTER TABLE Information ADD 'Med(4)' text")
this inserted a field called 'Med(4)'

How can I insert a field titled with just Med(4)?

And if you're wondering, I'm naming the fields this way so I can use a do...loop... statement to write data to fields and save any updates.

Thanks for your help

Problem With Alter Table
Ok, this is probably a really simple problem and I'm just not seeing something. I'd like to alter one of my data tables and add a column to it of type boolean. Thing is, in Access 2000, boolean is called "yes/no". Now I know the syntax to alter a table is

ALTER TABLE tablename ADD columnname columntype


ALTER TABLE tablename ADD COLUMN columnname columntype

When I try either and add "boolean" or "yes/no" in the columntype it gives me a syntax error. What am I doing wrong?

Alter Table Error
"Incorrect syntax near the word column"

For i = 0 To UBound(hArray)
sql = "ALTER TABLE Excel ADD column(i) varchar(30) "
Next i

Alter Table Method
I have create this querydef to add a table:

Set qdf.SQL = "ALTER TABLE Employees ADD COLUMN Department Text"

I don't want to add the column as the very last column. I thought that the correct syntax was something like

Set qdf.SQL = "ALTER TABLE Employees ADD COLUMN Department Text BEFORE Birthdate"

to add the column "Department" before the column "Birthdate" but that doesn't work. can someone remind me of the correct syntax? I know it is possible I just can't remember or find in my SQL books.



Alter Table Expresion In VB
i need a help on vb for altering table(vmbs) it is working by giving continues string if i gave space while entering into textbox then it is not taking

//examp:main.text="jayaram palavalasa" it will not create colum please help

Set rsaddress = dbaddress.Execute("ALTER TABLE vmbs " _
& "ADD " & main.Text & " text;")

//advance thanks

Alter Table Error
Hi All,
I am using a Access Table; in this i have createad a query
as 'test2' name , now this is created in database in remote place i am doing updates and i want set in an application , so when the user executes teh updates automatically takes place.
But when i execute this statemnt ast conn1.execute
it gives me an error like the following.
'[Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement.'

Alter view test2
SELECT A.contractid,Sum(B.chqamt) AS sum123, B.cleardt,1 as A FROM Contract AS A ,ChequeDetails AS B Where A.ContractID = B.ContractID and B.Status = True GROUP BY A.contractid,B.cleardt Order by B.cleardt UNION SELECT A.contractid,Sum(A.Cashamt+A.ccamt) AS Cashamt, A.contractDt, 2 FROM Contract AS A GROUP BY A.contractid,A.contractDt
John Philip

I'm writing a script that will automatically check a table to make sure it has all the fields, and add any missing ones. I'm getting a syntax error on this line:

Conn.Execute "ALTER TABLE Versions ADD Major integer(3)"

I was also wondering if there was a list anywhere of all the data types for Access. I haven't been able to find anything on specific Date/Time field types, or how to create a memo field.


I'm trying to create a new column in an access 2000 database via ADO and it works fine but when I just can't seem to find the right syntax to set the default value to 0.
I've looked evrywhere and tried lots of syntaxes but they all give me a syntax error in alter statement. Could somebody tell me the exact syntax with real table name in an example so that I can finally set the default value?

Either you can set it thru the first alter statement (the one that ceates the column) or you have to execute a second qry to specify this criteria.

If you also know a way to set the allow zero length value to yes, I would gladly mak use of it.

Any help will be VERY appreciated.

I tried to add a field in a database table but getting an syntax error.

db.EXECUTE "ALTER TABLE tblCustomer COLUMN Company c(50) NULL"

Any help?

Alter Table Question
I tried to use the following statement to change a table.  I want to delete a field in the table but it gives me an error.

Alter [test_table] DROP test

"test" is the field name I want to drop.  What am I doing wrong?



Alter Table In MS Access Through VB6.0
Hello Friends
 I am getting problem regarding altering and droping the table field in MS Access. For that i used the command

        sql = "Alter Table Temp DROP COLUMN " & FieldName
        DBcon.Execute (sql)

     and to alter field type

        sql = "Alter Table Temp ADD COLUMN " & FieldName & " " & ChangedFieldType
        DBcon.Execute (sql)

      But if the field name is of like no or like any keyword etc . it will show syntax error. Can anybody know solution for this?


Syntax Alter Table In DB2
Hi.. I want to ask to You.
How syntac ALTER TABLE in DB2 (AS 400 Server)
My syntax is :


That is correct but I want to create alter table again

 I GET ERROR “SQL SYSTEM ERROR” , What's wrong ?


Edited by - triary on 4/23/2004 9:06:39 PM

Alter DBF Table Strucrure Using ADO!!!!!!!!!!!!!

I'm creating a VB 6.0 application that needs to alter the strucrure of a DBF table by adding a new column. I can connect to the DBF file to select records from the table as well as performing inserts and deletes. However, when I try to execute an "alter table" statement I receive and error stating that this "operation is not supported on tables containing data". If I delete all the records from the DBF file then I can perform the "alter table" statement. Is this a documented bug between ADO and the DBF file format?

Does anyone have some insight/code on how a column can be added to a DBF file containing data? I've read that DAO may be a better way to add the column, but again, I've run into the same problems (i.e. can connect, select, insert, delete but not alter) as with ADO.

How We Can Alter The Table In Foxpro Using ADO?Please
  can anybody tell me how to alter table SQL command runs with ADO in .dbf database.
i am using Microsoft.Jet.OLEDB.4.0 provider.but it does not allow me to add a column and delete a column in a foxpro database.please help me.
thanx in advanced

How We Can Alter A Table In Foxpro In VB Using ADO
  can anybody tell me how to alter table SQL command runs with ADO in .dbf database.
i am using Microsoft.Jet.OLEDB.4.0 provider.but it does not allow me to add a column and delete a column in a foxpro database.please help me.
thanx in advanced

Hi guys & gels,

I know this isn't really a VB question (though I was using the Data Environment when I first encountered it) but I thought I'd ask it anyway ... cos I'm like that.

Here's some SQL, cut and pasted from MSDN (what a great, accurate source of information that is):-

ALTER TABLE customer ADD COLUMN fax c(20) NULL

The only thing I changed from the MSDN example was the table name. This was pasted into a SQL Server 7.0 stored procedure. The error I get is "Syntax error near the keyword 'COLUMN' ".

Any Ideas as to why this error is occurring and how I can get my column added to my table?



Nothing is foolproof to a sufficiently talented fool

Alter Table Problems
I'm having a problem in changing the lenght of a field in a table on an access database.

' Data Base Name: Multi
' Table Name : Empresas
' Field Name : Empresa
' alter tablename change field field type(count)

dbsMulti.Execute "ALTER Table Empresas " _
& "Alter Empresa Char(60);"

I've talked to too many people and read too many books and so far no good result. I've got only errors.

Can you help me please?

Julio Borges
Rio de Janeiro - Brazil

Syntax -Alter Table
HI guys, I hope somebody has a suggestion to my problem

I need to alter a table(which I haven't done before) for my app. that is written in Visual Basic 6 using Access database.

I know the "formula" for altering the table,don't require that.

This is the code for creating the table

nErr = myObj.Modify(Handle, "CREATE TABLE LabelsPrinted ( CustomerID INT, PrintedWhen DATETIME, " & _
"DataRecorderCode VARCHAR(30), NumberBoxes INT, FirstBoxNbr VARCHAR(100), RecorderReturnedWhen DATETIME, " & _
"CONSTRAINT FK_CustomerID_001 FOREIGN KEY (CustomerID) REFERENCES CustomerDetails (CustomerID))")
If myObj.ReportError(Handle, nErr, "attempting to update the database. (v" & CStr(lVersion) & ")", App.title) Then
Exit Function
End If

What I am not sure here is what is the way I need to alter this table, how or where do I use the altering formula/syntax to acctually do this, I can seem to write it properly but I am not getting any errors nor is anything getting created.


Alter Table Drop Constraint
Was able to remove an index (not the primary key) from a field using:

sAlter = "ALTER TABLE tablename DROP CONSTRAINT fieldname Index "
cn.Execute sAlter

This works the first time. However, if run again, bomb.

How to check if a field has already been altered?

How To Alter Table In Access 2000
I have tried this before but no would I change a field definition in Visual Basic vode?

ALTER TABLE Default Setting
Dear all,

I'm trying to execute a sql statement in vba to create a numeric field in an existing table in a MS Access 2000 database. I want the field to be created with a default value setting. I'm using this statement:

"alter table tblGenericViewer ADD intS_A short NULL DEFAULT((1))"

I scavenged the "..DEFAULT((1))" syntax from similar code for SQLServer, but I know it doesn't work with an Access table. I've found plenty of selfhelp pages on the web describing the proper use of ALTER TABLE for MS Access, but I can't find an example that shows the syntax for a DEFAULT value for a numeric field.

As always, your help is greatly appreciated. Thanx!

Alter Access Table At Run Time...
how do i alter an access database table at run time?

Alter Table Problem In MS Access - VB
Hello Friends
 I am getting problem regarding altering and droping the table field in MS Access. For that i used the command

        sql = "Alter Table Temp DROP COLUMN " & FieldName
        DBcon.Execute (sql)

     and to alter field type

        sql = "Alter Table Temp ADD COLUMN " & FieldName & " " & ChangedFieldType
        DBcon.Execute (sql)

      But if the field name is of like no or like any keyword etc . it will show syntax error. Can anybody know solution for this?


ALTER TABLE Command Problem In Access
I've created a table in a MS Access database and am trying to alter it to add an autoincrement column through ADO.


The above statement created the column but the seed and increment values are 1 (the default values). It doesn't seem to pick up the seed and increment values in the SQL statement (unless I'm doing this wrong).

Thanks in advance for any help here

Alter Table In SQL With Cascade Update/Delete
I've read a couple questions on the Alter Table command already, but could not figure out how to create a constraint that also provides Cascade Update/Delete abilities as well. I am trying to do this in Access XP, which may be the problem, but I have no choice, since I believe it can be done in SQL Server 2000 from what I have read. Here's the code I am currently trying to use:


If you take out the ON UPDATE CASCADE part, it will create the relationship no problem. If anyone can figure out how to get it to Cascade Update and Delete, I would really appreciate it!


How To Create Memo Field Using ALTER TABLE?
what would i use for the data type argument?

thanks for any help!


in my code, i create new columns in a table with "ALTER TABLE ADD COLUMN" statements through ADO.

I use a MSACESS database.

But now, i also need to determine when i create new columns that the columns may contain empty values (null), and -for now-i don't see a way to do this through SQL-ADO.

Does anyone know a way how to achieve this wthout using ADOX?

I would be grateful if anyone could help me or point me to the right direction.



His Royal Dudenss

Edited by - His_Royal_Dudeness on 2/8/2006 8:26:02 AM

I have a statement that I need to get to work on both Access Databases and SQL Server.

For access, I use:
ALTER TABLE [Folder] ADD MyField longText NULL

In SQL Server, I use:
ALTER TABLE [Folder] ADD MyField longText NULL

How come SQL Server sets the field to allow-nulls, whilst the access version, with no error, create the field but does not set it to allow-nulls.

I have also tried the ALTER TABLE MODIFY and although I do not get an error from Access (pasting the statement directly into the query window) the field is modified but not the allow-nulls.

Am I missing something? Thanks!

Alter Table Decimal Query On Access 2000
I try to execute this query: "ALTER TABLE T_HASAR
ALTER COLUMN MHASAR_TUTARI DECIMAL(14,2)" on Access 2000 database.

but the query fails and gives a message "Syntax error in ALTER TABLE statement".

What is wrong with the query? could anyone help me?

Sql Statement To Get The Last Record In A Table
is there any sql statement to select the last record in a table?

i'm making an ado database. i've just wrote a function that adds a user to a database. i then want to display the data that has been added. obviously this will be the last record in the table.


Kudos to y'all!!! Quick help here. I was trying to create an MS Access table using SQL statement. Is it possible to define the format in dates and numbers? Kinda like the way in MS Access UI, when you select the the date/time or any numeric data type, you can select the format. Is it possible? Can anyone please share an example?

UPDATE Table With SQL Statement In VB
Set gcnnData = New ADODB.Connection
Set grstData = New ADODB.Recordset

intValue = 17
strValue = "LH"
gvntSelect = "UPDATE tblOrders SET [fTypeO] = " & intValue & " WHERE [fUser] = " & strValue & ";"
grstData.Open (gvntSelect), gcnnData, adOpenDynamic, adLockOptimistic

I am trying to update a table: tblOrders
using criteria that finds any fUser fields
that has the initials: LH
then update the fTypeO field to the number: 17

I keep getting a Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters
and it highlights the following:

grstData.Open (gvtnSelect), gcnnData, adOpenDynamic, adLockOptimistic

I took the above UPDATE select statement directly
out of Microsoft Access' SQL statement and not sure
what modifications I need for it to work.

Please help.


Copyright 2005-08, All rights reserved