• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3000
  • Last Modified:

Oracle Reserved Word for Column Name in ODBC Linked Access DB

I have an application that uses an Access MDB database for its datasource and references a table with a column named 'Date'.  This column must be named 'Date', which is a reserved word in Oracle.  Is there anyway I can link in a table and change the Column name to 'Date' at the Access level, but so it still points to a column on the oracle table that's named slightly different?  Thanks.
0
CalDude
Asked:
CalDude
  • 3
  • 2
  • 2
1 Solution
 
TextReportCommented:
You are still ok to have the field named DATE but you need to reference it as TableName.[Date]. The best option is to create a query in Access that aliases the field

MyDate: TableName.[Date]

Cheers, Andrew
0
 
CalDudeAuthor Commented:
Unfortunately, the application differentiates between tables and queries, it must be seen as a table in access.
0
 
TextReportCommented:
"the application differentiates between tables and queries" I take it you are refering to your application rather than Access.

In Oracle create a view that aliases the DATE field and ensure there is an index on the view, if you cant do this in Oracle you can create one in Access after you have attached, you can then refer to the view as if it were a table and if you have the unique index the view will be updateable in access.
Cheers, Andrew
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CalDudeAuthor Commented:
I cannot create a view in oracle with a column named 'DATE', it's a reserved word for views too.  As far as a view in access, I thought that was the same thing as a query...  Yes, I was referring to my application, which will not look at a query when it wants a table.
0
 
Jinesh KamdarCommented:
>> I have an application that uses an Access MDB database for its datasource and references a table with a column named 'Date'.

I'm a bit confused here. Your problem is that u need a column named DATE in Access or in Oracle ?
0
 
TextReportCommented:
Sorry I thought DATE was in the Oracle Table but from what you are saying that is the name in the Access table that you want to move to Oracle.

The restrictions you have put in your application by not allowing the use of a query in place of the table means that there is not a way to work around the issue and you need to either allow the interchangable use of Tables and Queries in Access or rename the field from DATE.

Sorry to be the bearer of such bad news.

Cheers, Andrew
0
 
Jinesh KamdarCommented:
In Oracle, u can still create a table with a column named DATE by wrapping it within double (") quotes, but i would NOT recommend u to do it since it can cause a lot of complications later on and would like to suggest a work-around if u can share further details.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now