ADO, Access 2000, ALTER TABLE

Mikael Stalvik

Delphi Developer

Tue, 16 Mar 2004 01:09:59 GMT

ADO, Access 2000, ALTER TABLE

Hi.

I have been trying to send the following SQL query to my Access Database:

'ALTER TABLE tblTest ADD COLUMN test text(48)'.
(Using an ADOCommand component)

This doesn't work, I only get an error message "Syntax error in field
definition".

The strange think is that the query works when I run it from inside Access
2000.

I have also tried to use the the following code (using an ADODataSet
instead):
DBERS.Recordset.Fields.Append('test', adVarChar, 48, adFldIsNullable +
adFldMayBeNull);

This gives me an errormessage "Operation is not allowed in this contex".

Is it impossible to add fields to a table using Delphi??????????????

Thanks in advance,
Mikael Stalvik

Brian Bushay Team

Delphi Developer

Tue, 16 Mar 2004 10:44:22 GMT

Re:ADO, Access 2000, ALTER TABLE

Quote

>I have been trying to send the following SQL query to my Access Database:

>'ALTER TABLE tblTest ADD COLUMN test text(48)'.
>(Using an ADOCommand component)

>This doesn't work, I only get an error message "Syntax error in field
>definition".

This works for me when I try it. Of course I did strip the quotes of it.
What version of ADO are you using? I use 2.6
Access is using DAO instead of ADO so it isn't a good test of what works in
ADO.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Luis E Cang

Delphi Developer

Tue, 16 Mar 2004 07:05:17 GMT

Re:ADO, Access 2000, ALTER TABLE

Quote

Mikael Stalvik <mikael.stal...@telia.com> wrote in message

news:3bb35d4d_1@dnews...

Quote

> Hi.

> I have been trying to send the following SQL query to my Access Database:

> 'ALTER TABLE tblTest ADD COLUMN test text(48)'.
> (Using an ADOCommand component)

try this one:
'ALTER TABLE tblTest ADD test text(48)'.

Regards
Eduardo

Quote

> This doesn't work, I only get an error message "Syntax error in field
> definition".

> The strange think is that the query works when I run it from inside Access
> 2000.

> I have also tried to use the the following code (using an ADODataSet
> instead):
> DBERS.Recordset.Fields.Append('test', adVarChar, 48, adFldIsNullable +
> adFldMayBeNull);

> This gives me an errormessage "Operation is not allowed in this contex".

> Is it impossible to add fields to a table using Delphi??????????????

> Thanks in advance,
> Mikael Stalvik

Mikael Stalvi

Delphi Developer

Tue, 16 Mar 2004 23:37:47 GMT

Re:ADO, Access 2000, ALTER TABLE

Thanks to both of you!
I have now made it with an correct ALTER TABLE SQL Query.

But, can I from Delphi add fields, using the Recordset? (AS ADO claims)

Regards,
Mikael Stalvik

"Luis E Canga" <lca...@satlink.com> wrote in message
news:3bb45e6a_2@dnews...

Quote

> Mikael Stalvik <mikael.stal...@telia.com> wrote in message
> news:3bb35d4d_1@dnews...
> > Hi.

> > I have been trying to send the following SQL query to my Access
Database:

> > 'ALTER TABLE tblTest ADD COLUMN test text(48)'.
> > (Using an ADOCommand component)

> try this one:
> 'ALTER TABLE tblTest ADD test text(48)'.

> Regards
> Eduardo

> > This doesn't work, I only get an error message "Syntax error in field
> > definition".

> > The strange think is that the query works when I run it from inside
Access
> > 2000.

> > I have also tried to use the the following code (using an ADODataSet
> > instead):
> > DBERS.Recordset.Fields.Append('test', adVarChar, 48, adFldIsNullable +
> > adFldMayBeNull);

> > This gives me an errormessage "Operation is not allowed in this contex".

> > Is it impossible to add fields to a table using Delphi??????????????

> > Thanks in advance,
> > Mikael Stalvik

Brian Bushay Team

Delphi Developer

Wed, 17 Mar 2004 11:21:37 GMT

Re:ADO, Access 2000, ALTER TABLE

Quote

>But, can I from Delphi add fields, using the Recordset? (AS ADO claims)

No you can't add fields with Delphi and ADO. As far as I know you can't do it
with ADO although you can do it through ADOX. Do you have a reference to the
"(AS ADO claims)"
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Vassil Nazaro

Delphi Developer

Wed, 17 Mar 2004 12:00:44 GMT

Re:ADO, Access 2000, ALTER TABLE

Quote

> Do you have a reference to the "(AS ADO claims)"

He has probably seen a reference to the ADO's ability to create
in-memory Recordsets with the Connection property set to nothing.

--
Vassil Nazarov
http://web.orbitel.bg/vassil/

Mikael Stalvi

Delphi Developer

Wed, 17 Mar 2004 16:21:44 GMT

Re:ADO, Access 2000, ALTER TABLE

Yep, you are so right Vassil.
I read the doc i found much more in detail, and
that refered to in-memory record sets.

Ok, but what if I do it with ADOX?
Is it very complicated/unneccesary?

Regards and thanks,
Mikael Stalvik

Quote

"Vassil Nazarov" <vas...@mail.orbitel.bg> wrote in message

news:3bb54658_1@dnews...

Quote

> > Do you have a reference to the "(AS ADO claims)"

> He has probably seen a reference to the ADO's ability to create
> in-memory Recordsets with the Connection property set to nothing.

> --
> Vassil Nazarov
> http://web.orbitel.bg/vassil/

Sunil Furtad

Delphi Developer

Wed, 17 Mar 2004 18:39:54 GMT

Re:ADO, Access 2000, ALTER TABLE

Works on my Access 97 database.

Sunil Furtado
New Mangalore Port Trust

Quote

"Mikael Stalvik" <mikael.stal...@telia.com> wrote:

>'ALTER TABLE tblTest ADD COLUMN test text(48)'.
>(Using an ADOCommand component)

>This doesn't work, I only get an error message "Syntax error in field
>definition".

Brian Bushay Team

Delphi Developer

Thu, 18 Mar 2004 10:00:38 GMT

Re:ADO, Access 2000, ALTER TABLE

Quote

>Ok, but what if I do it with ADOX?
>Is it very complicated/unneccesary?

In my opinion ADOX is more complicated than using SQL to do an alter table. And
most of ADOX only works with Access so its not very portable.
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

DRS

Delphi Developer

Fri, 19 Mar 2004 22:17:51 GMT

Re:ADO, Access 2000, ALTER TABLE

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:hdtcrt0rdfnj0bfngbbp2if1s2ss09sutd@4ax.com...
|
| >Ok, but what if I do it with ADOX?
| >Is it very complicated/unneccesary?
|
| In my opinion ADOX is more complicated than using SQL to do an alter
table. And
| most of ADOX only works with Access so its not very portable.

That's an OLEDB issue, not an ADOX issue.

Quidquid latine dictum sit, altum viditur.
#319

Other Threads

1. ADO, Access 2000, Alter Table and AllowZeroLength

2. ADO Refresh with Ado Table and ADO Query on a network application with SQL Server 2000

3. Alter MS Access Table with ADO

4. ALTER TABLE/ALTER COLUMN problem with MS Access 2K

5. Access 2000/ADO - combining like tables from 2 dbs

6. Select Max(field) from table with ADO and Access 2000

7. Record locking with ADO Express, ADO 2.5 and Access 2000 .mdb files

8. Upgrade from Access 2000 to SQL Server 2000 - table values are padded with spaces

9. Lock Access table prior to ALTER TABLE

10. Converting DBF Tables to Access tables Inside Access Database with ADO and ADOX