Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Alter Table



Permlink Replies: 17 - Last Post: Feb 22, 2016 1:43 PM Last Post By: Harvey Arkawy Threads: [ Previous | Next ]
Harvey Arkawy

Posts: 15
Registered: 12/30/99
Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 18, 2016 2:40 PM
I have software in use by the general population. The current software uses ADO and an Access Database.
I am trying to update the software but in order to do it, I need to be able to add three fields to table 7 of 10 tables during run time.
When they download and install the upgrade, the first time that it is run the program will make the adjustments.

So far I have...

Before I make the alteration, I test the current table to count the quantity of fields. If the qty = 25, I need to add the three fields.

All three fields are 'Yes/No' for checkboxes. Their names are: 'Use_GMK', 'Both' and 'Default_Ball' and in that order.

Procedure Fix_The_DB;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then
Begin
Help Here // This is where the code will add the three fields.
End;
End;
End;

If have spent the last three days trying everything that I can think of to do this to no avail.
Could someone assist me by adding code to 'Help Here'?

Jeffrey Ottery

Posts: 42
Registered: 11/27/05
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 18, 2016 4:22 PM   in response to: Harvey Arkawy in response to: Harvey Arkawy
Harvey Arkawy wrote:
I have software in use by the general population. The current software uses ADO and an Access Database.
I am trying to update the software but in order to do it, I need to be able to add three fields to table 7 of 10 tables during run time.
When they download and install the upgrade, the first time that it is run the program will make the adjustments.

So far I have...

Before I make the alteration, I test the current table to count the quantity of fields. If the qty = 25, I need to add the three fields.

All three fields are 'Yes/No' for checkboxes. Their names are: 'Use_GMK', 'Both' and 'Default_Ball' and in that order.

Procedure Fix_The_DB;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then
Begin
Help Here // This is where the code will add the three fields.
End;
End;
End;

If have spent the last three days trying everything that I can think of to do this to no avail.
Could someone assist me by adding code to 'Help Here'?


With standard SQL:
ADOQuery1.SQL.Add('ALTER TABLE MKSystem ADD COLUMN Use_GMK boolean ');
Harvey Arkawy

Posts: 15
Registered: 12/30/99
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 18, 2016 4:40 PM   in response to: Jeffrey Ottery in response to: Jeffrey Ottery
Jeffrey Ottery wrote:
Harvey Arkawy wrote:
I have software in use by the general population. The current software uses ADO and an Access Database.
I am trying to update the software but in order to do it, I need to be able to add three fields to table 7 of 10 tables during run time.
When they download and install the upgrade, the first time that it is run the program will make the adjustments.

So far I have...

Before I make the alteration, I test the current table to count the quantity of fields. If the qty = 25, I need to add the three fields.

All three fields are 'Yes/No' for checkboxes. Their names are: 'Use_GMK', 'Both' and 'Default_Ball' and in that order.

Procedure Fix_The_DB;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then
Begin
Help Here // This is where the code will add the three fields.
End;
End;
End;

If have spent the last three days trying everything that I can think of to do this to no avail.
Could someone assist me by adding code to 'Help Here'?


With standard SQL:
ADOQuery1.SQL.Add('ALTER TABLE MKSystem ADD COLUMN Use_GMK boolean ');

Thank you, Jeff. I'll try that later as I'm just a little burned out.
If I can get this to function properly, I'll keep you informed.
Harvey Arkawy

Posts: 15
Registered: 12/30/99
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 19, 2016 2:00 PM   in response to: Jeffrey Ottery in response to: Jeffrey Ottery
Jeffrey Ottery wrote:
Harvey Arkawy wrote:
I have software in use by the general population. The current software uses ADO and an Access Database.
I am trying to update the software but in order to do it, I need to be able to add three fields to table 7 of 10 tables during run time.
When they download and install the upgrade, the first time that it is run the program will make the adjustments.

So far I have...

Before I make the alteration, I test the current table to count the quantity of fields. If the qty = 25, I need to add the three fields.

All three fields are 'Yes/No' for checkboxes. Their names are: 'Use_GMK', 'Both' and 'Default_Ball' and in that order.

Procedure Fix_The_DB;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then
Begin
Help Here // This is where the code will add the three fields.
End;
End;
End;

If have spent the last three days trying everything that I can think of to do this to no avail.
Could someone assist me by adding code to 'Help Here'?


With standard SQL:
ADOQuery1.SQL.Add('ALTER TABLE MKSystem ADD COLUMN Use_GMK boolean ');

Jeff,

I added your code to mine.
I have a break below the "With MKS_Table do" at the ADOQuery1.SQL.Clear; I then step through the code. When I F8 at the ADOQuery1.Open just after your code, I get the following error message...
"Project FasterMasterIIPro.exe raised exception class EOleException with message 'Syntax error in field definition'. Process stopped. Use Step or Run to continue." Any suggestions? Below is my procedure.

Procedure Fix_MKS_Table;
Var A : Integer;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin
With MKS_Table do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('ALTER TABLE MKSystem ADD COLUMN Use_GMK boolean');
ADOQuery1.Open;
MKS_Table.edit;
ADOQuery1.post;
// Add the other two fields
// Reset the table and go through each record
// Set the new fields to False;
// If a particular record meets a specific criteria,
// then set one of the new fields to true;
End;
End;
End;
End;
Jeffrey Ottery

Posts: 42
Registered: 11/27/05
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 19, 2016 6:56 PM   in response to: Harvey Arkawy in response to: Harvey Arkawy
Harvey Arkawy wrote:
Jeffrey Ottery wrote:
Harvey Arkawy wrote:
I have software in use by the general population. The current software uses ADO and an Access Database.
I am trying to update the software but in order to do it, I need to be able to add three fields to table 7 of 10 tables during run time.
When they download and install the upgrade, the first time that it is run the program will make the adjustments.

So far I have...

Before I make the alteration, I test the current table to count the quantity of fields. If the qty = 25, I need to add the three fields.

All three fields are 'Yes/No' for checkboxes. Their names are: 'Use_GMK', 'Both' and 'Default_Ball' and in that order.

Procedure Fix_The_DB;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then
Begin
Help Here // This is where the code will add the three fields.
End;
End;
End;

If have spent the last three days trying everything that I can think of to do this to no avail.
Could someone assist me by adding code to 'Help Here'?


With standard SQL:
ADOQuery1.SQL.Add('ALTER TABLE MKSystem ADD COLUMN Use_GMK boolean ');

Jeff,

I added your code to mine.
I have a break below the "With MKS_Table do" at the ADOQuery1.SQL.Clear; I then step through the code. When I F8 at the ADOQuery1.Open just after your code, I get the following error message...
"Project FasterMasterIIPro.exe raised exception class EOleException with message 'Syntax error in field definition'. Process stopped. Use Step or Run to continue." Any suggestions? Below is my procedure.

Procedure Fix_MKS_Table;
Var A : Integer;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin
With MKS_Table do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('ALTER TABLE MKSystem ADD COLUMN Use_GMK boolean');
ADOQuery1.Open;
MKS_Table.edit;
ADOQuery1.post;
// Add the other two fields
// Reset the table and go through each record
// Set the new fields to False;
// If a particular record meets a specific criteria,
// then set one of the new fields to true;
End;
End;
End;
End;

I assume that it is failing on this line?

ADOQuery1.SQL.Add('ALTER TABLE MKSystem ADD COLUMN Use_GMK boolean');

Maybe try: ADOQuery1.SQL.Add('ALTER TABLE "MKSystem" ADD COLUMN "Use_GMK" boolean'); - In other words enclose the Table Name and Field Name in quotations.

Also are you sure boolean is a valid field type in ADO. I'm using postgreSQL so I have no experience with ADO.
Harvey Arkawy

Posts: 15
Registered: 12/30/99
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 19, 2016 7:24 PM   in response to: Jeffrey Ottery in response to: Jeffrey Ottery
One or more fields in one or more tables within the database have Yes/No (Boolean) fields and the ADOQuery routines adequately address their boolean state.

With that said....
This is what I have been up to....

Procedure Fix_MKS_Table;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin //Your suggestion
ADOQuery1.SQL.Add('ALTER TABLE "MKSystem" ADD COLUMN "Use_GMK" boolean');
ADOQuery1.post;
end;
End;
End;

Error on ADOQuery1.Post;

Project FasterMasterIIPro.exe raised exception class EDatabaseError with message 'ADOQuery1:Cannot perform this operation on a closed dataset'. Process stopped. Use Step or Run to continue.

The Open statement already exists. So I changed the code to include the Edit statement.

Procedure Fix_MKS_Table;
Var A : Integer;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin
ADOQuery1.SQL.Add('ALTER TABLE "MKSystem" ADD COLUMN "Use_GMK" boolean');

ADOQuery1.Edit;
ADOQuery1.post;
end;
End;
End;


Error on ADOQuery1.Edit;

Project FasterMasterIIPro.exe raised exception class EDatabaseError with message 'ADOQuery1:Dataset not in edit or insert mode'. Process stopped. Use Step or Run to continue.

But it does have the edit statement
Changed the code to reopen the table.

Procedure Fix_MKS_Table;
Var A : Integer;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin
ADOQuery1.SQL.Add('ALTER TABLE "MKSystem" ADD COLUMN "Use_GMK" boolean');
ADOQuery1.Open;
ADOQuery1.Edit;
ADOQuery1.post;
end;
End;
End;

Error on ADOQuery1.Open;

Project FasterMasterIIPro.exe raised exception class EOleException with message 'Syntax error in FROM clause'. Process stopped. Use Step or Run to continue.

I am wondering if the problem lies with the fact that I'm trying to modify a database table while inside the IDE.

Harvey Arkawy

Posts: 15
Registered: 12/30/99
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 19, 2016 7:39 PM   in response to: Harvey Arkawy in response to: Harvey Arkawy
After each compilation, I exited out of the IDE and run the exe file directly.
I tried all of the previous attempts and I even added
Var Use_GMK : TField;

And tried using
If ADOQuery1.Fieldcount = 25 then
Begin
MKS_Table.Fields.Add(Use_GMK);
MKS_Table.Edit;
MKS_Table.Post;
End;

Nothing seems to function.
Jim Fleming

Posts: 113
Registered: 2/12/00
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 20, 2016 11:12 AM   in response to: Harvey Arkawy in response to: Harvey Arkawy
For the ALTER TABLE command, use a TADOCommand object, not a TADOQuery.

--
JF
Harvey Arkawy

Posts: 15
Registered: 12/30/99
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 20, 2016 11:19 AM   in response to: Jim Fleming in response to: Jim Fleming
Jim Fleming wrote:
For the ALTER TABLE command, use a TADOCommand object, not a TADOQuery.

--
JF

Thank you. I'll try your suggestion today.
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 20, 2016 12:45 AM   in response to: Harvey Arkawy in response to: Harvey Arkawy
Harvey Arkawy wrote:

One or more fields in one or more tables within the database have
Yes/No (Boolean) fields and the ADOQuery routines adequately address
their boolean state.

With that said....
This is what I have been up to....

Procedure Fix_MKS_Table;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin //Your suggestion
ADOQuery1.SQL.Add('ALTER TABLE "MKSystem" ADD COLUMN
"Use_GMK" boolean'); ADOQuery1.post;
end;
End;
End;

Error on ADOQuery1.Post;

Project FasterMasterIIPro.exe raised exception class EDatabaseError
with message 'ADOQuery1:Cannot perform this operation on a closed
dataset'. Process stopped. Use Step or Run to continue.

The Open statement already exists. So I changed the code to include
the Edit statement.

Procedure Fix_MKS_Table;
Var A : Integer;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin
ADOQuery1.SQL.Add('ALTER TABLE "MKSystem" ADD COLUMN
"Use_GMK" boolean');

ADOQuery1.Edit;
ADOQuery1.post;
end;
End;
End;


Error on ADOQuery1.Edit;

Project FasterMasterIIPro.exe raised exception class EDatabaseError
with message 'ADOQuery1:Dataset not in edit or insert mode'. Process
stopped. Use Step or Run to continue.

But it does have the edit statement
Changed the code to reopen the table.

Procedure Fix_MKS_Table;
Var A : Integer;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin
ADOQuery1.SQL.Add('ALTER TABLE "MKSystem" ADD COLUMN
"Use_GMK" boolean'); ADOQuery1.Open;
ADOQuery1.Edit;
ADOQuery1.post;
end;
End;
End;

Error on ADOQuery1.Open;

Project FasterMasterIIPro.exe raised exception class EOleException
with message 'Syntax error in FROM clause'. Process stopped. Use
Step or Run to continue.

I am wondering if the problem lies with the fact that I'm trying to
modify a database table while inside the IDE.


I have no experience with ADO but in other TQuery descendent you would
execute the Alter table statement using ExecSQL.
Harvey Arkawy

Posts: 15
Registered: 12/30/99
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 20, 2016 11:20 AM   in response to: Lajos Juhasz in response to: Lajos Juhasz
Lajos Juhasz wrote:
Harvey Arkawy wrote:

One or more fields in one or more tables within the database have
Yes/No (Boolean) fields and the ADOQuery routines adequately address
their boolean state.

With that said....
This is what I have been up to....

Procedure Fix_MKS_Table;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin //Your suggestion
ADOQuery1.SQL.Add('ALTER TABLE "MKSystem" ADD COLUMN
"Use_GMK" boolean'); ADOQuery1.post;
end;
End;
End;

Error on ADOQuery1.Post;

Project FasterMasterIIPro.exe raised exception class EDatabaseError
with message 'ADOQuery1:Cannot perform this operation on a closed
dataset'. Process stopped. Use Step or Run to continue.

The Open statement already exists. So I changed the code to include
the Edit statement.

Procedure Fix_MKS_Table;
Var A : Integer;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin
ADOQuery1.SQL.Add('ALTER TABLE "MKSystem" ADD COLUMN
"Use_GMK" boolean');

ADOQuery1.Edit;
ADOQuery1.post;
end;
End;
End;


Error on ADOQuery1.Edit;

Project FasterMasterIIPro.exe raised exception class EDatabaseError
with message 'ADOQuery1:Dataset not in edit or insert mode'. Process
stopped. Use Step or Run to continue.

But it does have the edit statement
Changed the code to reopen the table.

Procedure Fix_MKS_Table;
Var A : Integer;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin
ADOQuery1.SQL.Add('ALTER TABLE "MKSystem" ADD COLUMN
"Use_GMK" boolean'); ADOQuery1.Open;
ADOQuery1.Edit;
ADOQuery1.post;
end;
End;
End;

Error on ADOQuery1.Open;

Project FasterMasterIIPro.exe raised exception class EOleException
with message 'Syntax error in FROM clause'. Process stopped. Use
Step or Run to continue.

I am wondering if the problem lies with the fact that I'm trying to
modify a database table while inside the IDE.


I have no experience with ADO but in other TQuery descendent you would
execute the Alter table statement using ExecSQL.

Thank you. I'll also try your suggestion today.
Steven Mitchell

Posts: 16
Registered: 5/8/97
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 20, 2016 12:49 PM   in response to: Harvey Arkawy in response to: Harvey Arkawy
Harvey Arkawy wrote:

Jeffrey Ottery wrote:
Harvey Arkawy wrote:
I have software in use by the general population. The current
software uses ADO and an Access Database. I am trying to update
the software but in order to do it, I need to be able to add
three fields to table 7 of 10 tables during run time. When they
download and install the upgrade, the first time that it is run
the program will make the adjustments. So far I have...

Before I make the alteration, I test the current table to count
the quantity of fields. If the qty = 25, I need to add the three
fields.

All three fields are 'Yes/No' for checkboxes. Their names are:
'Use_GMK', 'Both' and 'Default_Ball' and in that order.

Procedure Fix_The_DB;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then
Begin
Help Here // This is where the code will add the three
fields. End;
End;
End;

If have spent the last three days trying everything that I can
think of to do this to no avail. Could someone assist me by
adding code to 'Help Here'?


With standard SQL:
ADOQuery1.SQL.Add('ALTER TABLE MKSystem ADD COLUMN Use_GMK
boolean ');

Jeff,

I added your code to mine.
I have a break below the "With MKS_Table do" at the
ADOQuery1.SQL.Clear; I then step through the code. When I F8 at the
ADOQuery1.Open just after your code, I get the following error
message... "Project FasterMasterIIPro.exe raised exception class
EOleException with message 'Syntax error in field definition'.
Process stopped. Use Step or Run to continue." Any suggestions?
Below is my procedure.

Procedure Fix_MKS_Table;
Var A : Integer;
Begin
With Trax do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from MKSystem');
ADOQuery1.Open;
If ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
Begin
With MKS_Table do
Begin
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('ALTER TABLE MKSystem ADD COLUMN
Use_GMK boolean'); ADOQuery1.Open;
MKS_Table.edit;
ADOQuery1.post;
// Add the other two fields
// Reset the table and go through each record
// Set the new fields to False;
// If a particular record meets a specific criteria,
// then set one of the new fields to true;
End;
End;
End;
End;

Harvey,

While I have no experience with Access, but more with SQL Server
databases, I would suggest the following approach. The syntax of the
SQL statements might need to be adjusted for Access. Keep in mind that
some databases need exclusive access to the tables to make structural
changes but I do not know whether Access does. In most cases it is
safer to make these changes when no other users are accessing the
database.

procedure Fix_MKS_Table;
begin
ADOQuery1.Close;
ADOQuery1.SQL.Text := 'Select * from MKSystem';
ADOQuery1.Open;
if ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
begin
ADOQuery1.Close;
ADOQuery1.SQL.Text := 'ALTER TABLE [MKSystem] ADD COLUMN [Use_GMK]
boolean';
ADOQuery1.ExecSQL;
// Add the other fields by repeating the previous two lines

// Update the new fields in each record as required
end
else
ADOQuery1.Close;
end;
end;

HTH.

Cheers,
Steve
Harvey Arkawy

Posts: 15
Registered: 12/30/99
Re: Alter Table
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 20, 2016 1:57 PM   in response to: Steven Mitchell in response to: Steven Mitchell
Thank you all for your suggestions.
The last three were very interesting. Unfortunately they didn't work.
As I stepped through the code.

procedure Fix_MKS_Table;
begin
With Trax do //this is the form that all off the ADO components have been added
Begin
ADOQuery1.Close;
ADOQuery1.SQL.Text := 'Select * from MKSystem';
ADOQuery1.Open;
if ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
begin
ADOQuery1.Close;
ADOQuery1.SQL.Text := 'ALTER TABLE [MKSystem] ADD COLUMN [Use_GMK] boolean';
ADOQuery1.ExecSQL;
// Add the other fields by repeating the previous two lines

// Update the new fields in each record as required
end
else
ADOQuery1.Close;
end;
end;

When I stepped through the ADOQuery1.ExecSQL line, I received the following error message...
Project FasterMasterIIPro.exe raised exception class EOleException with message 'Syntax error in field definition'. Process stopped. Use Step or Run to continue.

I removed the square brackets and tried again. Same error message.
I changed the boolean to Yes/No (as listed within the database) and same error message.

It appears the ADO within Delphi 6 doesn't like the command ('ALTER TABLE [MKSystem] ADD COLUMN [Use_GMK] boolean'; ) even though it is listed as such in all of the Delphi 6 books that I have.

Edited by: Harvey Arkawy on Feb 20, 2016 2:00 PM
Steven Mitchell

Posts: 16
Registered: 5/8/97
Re: Alter Table [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 21, 2016 1:13 PM   in response to: Harvey Arkawy in response to: Harvey Arkawy
Harvey Arkawy wrote:

Thank you all for your suggestions.
The last three were very interesting. Unfortunately they didn't work.
As I stepped through the code.

procedure Fix_MKS_Table;
begin
With Trax do //this is the form that all off the ADO components have
been added Begin
ADOQuery1.Close;
ADOQuery1.SQL.Text := 'Select * from MKSystem';
ADOQuery1.Open;
if ADOQuery1.Fieldcount = 25 then // Fix the MKS_Table
begin
ADOQuery1.Close;
ADOQuery1.SQL.Text := 'ALTER TABLE [MKSystem] ADD COLUMN [Use_GMK]
boolean'; ADOQuery1.ExecSQL;
// Add the other fields by repeating the previous two lines

// Update the new fields in each record as required
end
else
ADOQuery1.Close;
end;
end;

When I stepped through the ADOQuery1.ExecSQL line, I received the
following error message... Project FasterMasterIIPro.exe raised
exception class EOleException with message 'Syntax error in field
definition'. Process stopped. Use Step or Run to continue.

I removed the square brackets and tried again. Same error message.
I changed the boolean to Yes/No (as listed within the database) and
same error message.

It appears the ADO within Delphi 6 doesn't like the command ('ALTER
TABLE [MKSystem] ADD COLUMN [Use_GMK] boolean'; ) even though it is
listed as such in all of the Delphi 6 books that I have.

Edited by: Harvey Arkawy on Feb 20, 2016 2:00 PM

Apparently the correct syntax for the command in Access is:

'ALTER TABLE MKSystem ADD COLUMN Use_GMK BIT DEFAULT 0 NOT NULL'

Go to the following URL for more info:
http://stackoverflow.com/questions/13605663/how-to-add-a-boolean-column-to-ms-access-via-sql-in-vb-net

Cheers,
Steve
Harvey Arkawy

Posts: 15
Registered: 12/30/99
Re: Alter Table [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 21, 2016 2:10 PM   in response to: Steven Mitchell in response to: Steven Mitchell

Apparently the correct syntax for the command in Access is:

'ALTER TABLE MKSystem ADD COLUMN Use_GMK BIT DEFAULT 0 NOT NULL'

Go to the following URL for more info:
http://stackoverflow.com/questions/13605663/how-to-add-a-boolean-column-to-ms-access-via-sql-in-vb-net

Cheers,
Steve

YES!!!!! YES!!!!!! THANK YOU!!!!! THANK YOU!!!!!

Steven....

I knew there had to be a genius out there!
Harvey Arkawy

Posts: 15
Registered: 12/30/99
Re: Alter Table [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 21, 2016 6:20 PM   in response to: Harvey Arkawy in response to: Harvey Arkawy
Harvey Arkawy wrote:

Apparently the correct syntax for the command in Access is:

'ALTER TABLE MKSystem ADD COLUMN Use_GMK BIT DEFAULT 0 NOT NULL'

Go to the following URL for more info:
http://stackoverflow.com/questions/13605663/how-to-add-a-boolean-column-to-ms-access-via-sql-in-vb-net

Cheers,
Steve

YES!!!!! YES!!!!!! THANK YOU!!!!! THANK YOU!!!!!

Steven....

I knew there had to be a genius out there!

OK, Still one minor problem.
The code adds the proper field(s) to the table, but your code leaves the "Format" under Field Properties blank. I need the Format to be Yes/No.

A copy of some of the current data...
Rotating is Yes/No. StartingChar on some is the 'A'. The zeros should be No.
Rotating StartingChar Use_GMK
No 0
No 0
No 0
Yes A 0
Yes A 0
Yes A 0
Yes A 0

If tried adding Field Yes/No. Syntax error
Properties Field Yes/No Syntax error
Format Yes/No. Syntax error
Field Format Yes/No.... Syntax error
Properties Format Yes/No Syntax error

If moved the items around and I'm still stuck.

What do I need to do to modify the code to set the Field Properties to Yes/No which will show a check box (either a No or a Yes).
Steven Mitchell

Posts: 16
Registered: 5/8/97
Re: Alter Table [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2016 1:22 PM   in response to: Harvey Arkawy in response to: Harvey Arkawy
Harvey Arkawy wrote:

Harvey Arkawy wrote:

Apparently the correct syntax for the command in Access is:

'ALTER TABLE MKSystem ADD COLUMN Use_GMK BIT DEFAULT 0 NOT NULL'

Go to the following URL for more info:
http://stackoverflow.com/questions/13605663/how-to-add-a-boolean-column-to-ms-access-via-sql-in-vb-net

Cheers,
Steve

YES!!!!! YES!!!!!! THANK YOU!!!!! THANK YOU!!!!!

Steven....

I knew there had to be a genius out there!

OK, Still one minor problem.
The code adds the proper field(s) to the table, but your code leaves
the "Format" under Field Properties blank. I need the Format to be
Yes/No.

A copy of some of the current data...
Rotating is Yes/No. StartingChar on some is the 'A'. The zeros
should be No. Rotating StartingChar Use_GMK
No 0
No 0
No 0
Yes A 0
Yes A 0
Yes A 0
Yes A 0

If tried adding Field Yes/No. Syntax error
Properties Field Yes/No Syntax error
Format Yes/No. Syntax error
Field Format Yes/No.... Syntax error
Properties Format Yes/No Syntax error

If moved the items around and I'm still stuck.

What do I need to do to modify the code to set the Field Properties
to Yes/No which will show a check box (either a No or a Yes).

Perhaps you could try the following syntax instead:

'ALTER TABLE MKSystem ADD COLUMN Use_GMK YESNO'

If that doesn't make any difference then you need someone with specific
knowledge of Access to help.

Cheers,
Steve
Harvey Arkawy

Posts: 15
Registered: 12/30/99
Re: Alter Table [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2016 1:43 PM   in response to: Steven Mitchell in response to: Steven Mitchell
Steven Mitchell wrote:


Perhaps you could try the following syntax instead:

'ALTER TABLE MKSystem ADD COLUMN Use_GMK YESNO'

If that doesn't make any difference then you need someone with specific
knowledge of Access to help.

Cheers,
Steve

Thanks Steve but..... No Cigar!
Using that code produces the same results as 'Alter table MKSystem Add Column Use_GMK Bit DEFAULT 0 NOT NULL';
The Format is blank and not Yes/No.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02