Access Modify

[kramer31]

Hi. At my company, we have an internal application for updating access
databases. It allows the user to select an access database to update
and a text which contains a series of SQL statements to be executed on
the database.

I'd like to write add to that an SQL statement to change a field from
type int to type double.

Now the statement:

ALTER TABLE tblName MODIFY fieldname double

doesn't work in Access.

ALTER TABLE tblName ALTER COLUMN fieldname double

does work if executed directly in Access, but not when executed by this
application.

Can anyone explain this? I believe that the application uses JET for
database connectivity.

I've read that somehow I can accomplish this by using the hidden table
tableDef or something like that. Can someone explain please?

Thanks in advance.

 

[Van T. Dinh]

--
HTH
Van T. Dinh
MVP (Access)

 

[Van T. Dinh]

Your "internal application" may expect a particular flavour of SQL and then
it translate / convert the input SQL text to SQL statements of a different
flavour depending on the target database???

Suggest you look in the existing SQL text (presumably saved somewhere in
your system) to see how the ALTER statement should be constructed when the
target database is a JET database.

I think you referred to the system Tables (which are hidden) ... I think it
is simply too risky to change the data in the system Tables directly ... so
I never tried ...

 

[kramer31]

Your "internal application" may expect a particular flavour of SQL and then
it translate / convert the input SQL text to SQL statements of a different
flavour depending on the target database???

No. It just executes the query. I know the guy who wrote it and it
doesn't do anything like that.

Suggest you look in the existing SQL text (presumably saved somewhere in
your system) to see how the ALTER statement should be constructed when the
target database is a JET database.

What existing SQL text are you talking about?

 

[Van T. Dinh]

1. It sounds to me that the "internal application" solves a non-problem???

Wouldn't it be easier to open the database and modify the Table, either
through the GUI or the SQL?

2. Surely, users must have used the "internal application" previously and
saved the SQL Strings that have been proven to work as the "Knowledge Base"
for the "internal application"?

3. Any Help file / documentation on the "internal application"?

4. Ask the author of the "internal application" why the "ALTER COLUMN ..."
syntax (which is proven through the Access interface) doesn't work if run
via the "internal application"?

My wild guess (without any idea how the "internal application" was written):
The "internal application" was probably written for ealier version of JET
database format (e.g. JET 3.5 / A97) or uses earlier versions of
References, e.g. DAO 3.51 which , IIRC, cannot recognise JET 4 database
format / "ALTER TABLE ... ALTER COLUMN ..." syntax.

(Note: MODIFY is not a valid JET SQL syntax element, AFAIK.)