Updating View Programmatically

J

Josh Asbury

Hi all -

I am having a problem with modifying views/saved queries in Access 2000. I
have written a Java servlet that updates my customers Access 2000 databases
by connecting and updating their DBs by issuing update and insert statements
against a comma-separated-value file. No problems.

I now need to add a column to a table and have modified my servlet to alter
the "Home" table in my database. Again, no problem. However, there is a
saved query in my database that I need to update to include this new column.
After I updated my Jet engine to 4.0, I am able to create and drop views
(alter view evidently doesn't exist).

The problem that I am running into is that the newly created view is
different than what I am expecting.

The SQL of the view *should* look like:
SELECT HOME.ID, HOME.CREATED_ON, HOME.CREATED_BY, HOME.LAST_UPDATED_ON,
HOME.LAST_UPDATED_BY, HOME.WAREHOUSE_ID, HOME.NAME, HOME.ADDRESS1,
HOME.ADDRESS2, HOME.CITY, HOME.STATE, HOME.ZIP, HOME.PHONE, HOME.FAX,
HOME.EMAIL, HOME.LOGO_FILE, HOME.COUNTRY, WARE.CODE AS WAREHOUSE_CODE,
WARE.NAME AS WAREHOUSE_NAME, WARE.LOCATION AS WAREHOUSE_LOCATION
FROM HOME, R_WAREHOUSE AS WARE
WHERE HOME.WAREHOUSE_ID = WARE.ID;

but is being created like this:
SELECT HOME.ID AS Expr1, HOME.CREATED_ON AS Expr2, HOME.CREATED_BY AS Expr3,
HOME.LAST_UPDATED_ON AS Expr4, HOME.LAST_UPDATED_BY AS Expr5,
HOME.WAREHOUSE_ID AS Expr6, HOME.NAME AS Expr7, HOME.ADDRESS1 AS Expr8,
HOME.ADDRESS2 AS Expr9, HOME.CITY AS Expr10, HOME.STATE AS Expr11, HOME.ZIP
AS Expr12, HOME.PHONE AS Expr13, HOME.FAX AS Expr14, HOME.EMAIL AS Expr15,
HOME.LOGO_FILE AS Expr16, HOME.DISPLAY_FULL_BIO AS Expr17,
HOME.DISPLAY_LIFE_REVIEW AS Expr18, HOME.COUNTRY AS Expr19, WARE.CODE AS
WAREHOUSE_CODE, WARE.NAME AS WAREHOUSE_NAME, WARE.LOCATION AS
WAREHOUSE_LOCATION;

When I attempt to run the query from within Access, it asks me for a
parameter for each field in the view. When I open it in design view, each
field is listed with ExprN: in front of the column name (eg. Expr1: Home.ID,
Expr2: Home.Created_On, etc).

Does anyone have any idea what I might be doing wrong here? My view create
statement is below.

Thanks for any guidance!!

Josh

CREATE VIEW HOME_V AS SELECT HOME.ID, HOME.CREATED_ON, HOME.CREATED_BY,
HOME.LAST_UPDATED_ON, HOME.LAST_UPDATED_BY, HOME.WAREHOUSE_ID, HOME.NAME,
HOME.ADDRESS1, HOME.ADDRESS2, HOME.CITY, HOME.STATE, HOME.ZIP, HOME.PHONE,
HOME.FAX, HOME.EMAIL, HOME.LOGO_FILE, HOME.DISPLAY_FULL_BIO,
HOME.DISPLAY_LIFE_REVIEW, HOME.COUNTRY, WARE.CODE AS WAREHOUSE_CODE,
WARE.NAME AS WAREHOUSE_NAME, WARE.LOCATION AS WAREHOUSE_LOCATION
FROM HOME, R_WAREHOUSE AS WARE
WHERE (((HOME.WAREHOUSE_ID)=[WARE].[ID]));
 
J

Josh Asbury

All -

I apologize for the previous post. I have resolved the issue. Turns out
that ODBC didn't appreciate the carriage returns that I was using in my
external SQL file. When I removed them, the saved query was created with no
problems.

Thanks!
Josh

Josh Asbury said:
Hi all -

I am having a problem with modifying views/saved queries in Access 2000. I
have written a Java servlet that updates my customers Access 2000 databases
by connecting and updating their DBs by issuing update and insert statements
against a comma-separated-value file. No problems.

I now need to add a column to a table and have modified my servlet to alter
the "Home" table in my database. Again, no problem. However, there is a
saved query in my database that I need to update to include this new column.
After I updated my Jet engine to 4.0, I am able to create and drop views
(alter view evidently doesn't exist).

The problem that I am running into is that the newly created view is
different than what I am expecting.

The SQL of the view *should* look like:
SELECT HOME.ID, HOME.CREATED_ON, HOME.CREATED_BY, HOME.LAST_UPDATED_ON,
HOME.LAST_UPDATED_BY, HOME.WAREHOUSE_ID, HOME.NAME, HOME.ADDRESS1,
HOME.ADDRESS2, HOME.CITY, HOME.STATE, HOME.ZIP, HOME.PHONE, HOME.FAX,
HOME.EMAIL, HOME.LOGO_FILE, HOME.COUNTRY, WARE.CODE AS WAREHOUSE_CODE,
WARE.NAME AS WAREHOUSE_NAME, WARE.LOCATION AS WAREHOUSE_LOCATION
FROM HOME, R_WAREHOUSE AS WARE
WHERE HOME.WAREHOUSE_ID = WARE.ID;

but is being created like this:
SELECT HOME.ID AS Expr1, HOME.CREATED_ON AS Expr2, HOME.CREATED_BY AS Expr3,
HOME.LAST_UPDATED_ON AS Expr4, HOME.LAST_UPDATED_BY AS Expr5,
HOME.WAREHOUSE_ID AS Expr6, HOME.NAME AS Expr7, HOME.ADDRESS1 AS Expr8,
HOME.ADDRESS2 AS Expr9, HOME.CITY AS Expr10, HOME.STATE AS Expr11, HOME.ZIP
AS Expr12, HOME.PHONE AS Expr13, HOME.FAX AS Expr14, HOME.EMAIL AS Expr15,
HOME.LOGO_FILE AS Expr16, HOME.DISPLAY_FULL_BIO AS Expr17,
HOME.DISPLAY_LIFE_REVIEW AS Expr18, HOME.COUNTRY AS Expr19, WARE.CODE AS
WAREHOUSE_CODE, WARE.NAME AS WAREHOUSE_NAME, WARE.LOCATION AS
WAREHOUSE_LOCATION;

When I attempt to run the query from within Access, it asks me for a
parameter for each field in the view. When I open it in design view, each
field is listed with ExprN: in front of the column name (eg. Expr1: Home.ID,
Expr2: Home.Created_On, etc).

Does anyone have any idea what I might be doing wrong here? My view create
statement is below.

Thanks for any guidance!!

Josh

CREATE VIEW HOME_V AS SELECT HOME.ID, HOME.CREATED_ON, HOME.CREATED_BY,
HOME.LAST_UPDATED_ON, HOME.LAST_UPDATED_BY, HOME.WAREHOUSE_ID, HOME.NAME,
HOME.ADDRESS1, HOME.ADDRESS2, HOME.CITY, HOME.STATE, HOME.ZIP, HOME.PHONE,
HOME.FAX, HOME.EMAIL, HOME.LOGO_FILE, HOME.DISPLAY_FULL_BIO,
HOME.DISPLAY_LIFE_REVIEW, HOME.COUNTRY, WARE.CODE AS WAREHOUSE_CODE,
WARE.NAME AS WAREHOUSE_NAME, WARE.LOCATION AS WAREHOUSE_LOCATION
FROM HOME, R_WAREHOUSE AS WARE
WHERE (((HOME.WAREHOUSE_ID)=[WARE].[ID]));
Click to expand...
 
Top