Importing from Oracle (dba_tab_columns)

[RJN]

Running Access 2000.

Tried using a link table however it gives me an error saying that I must specify owner because several tables with that name exist. I hit ok and the window closes, I never receive a chance to specify owner (sys?). Is there a way around this or another way to create a table that is constantly accurate with my Oracle table?

Thanks

 

[Moniker]

You need to explicitly use the full table path. For example, if you have a lot of DBs and each DB has a table named "Employees", then you'd have to fully qualify it. The same thing happens with fields. If you have two tables, "Employees" and "EmpDetail" for example, and both have a field named "EmployeeID", then you'd have to qualify it, like this:

EmpDetail.EmployeeID

-or-

Employees.EmployeeID

Without the full qualification, the DB doesn't know which field you mean and will return an "ambiguous name" error.

 

[RJN]

Without the full qualification, the DB doesn't know which field you mean and will return an "ambiguous name" error.

Yea, the problem is the link table wizard doesn't let me type in the qualification. I select the table from a list (no textboxes at this point) and hit next, gives me the error (textbox is behind the error, cant click/type) and hit OK. The wizard quits.

 

[boblarson]

Does the table have a primary key defined? SQL Server will let you link without one but you can't update data from Access to SQL Server unless there's a primary key in the SQL table.

I don't know Oracle, but you might check that because maybe it does with the ODBC driver for Oracle.

 

[RJN]

Does the table have a primary key defined? SQL Server will let you link without one but you can't update data from Access to SQL Server unless there's a primary key in the SQL table.

It does have a PK and I am not updating, only selecting.

Maybe walking you through my steps would help us solve this issue.

1. Tables -> New -> Link Table
2. Files of type -> ODBC Database
3. New -> Microsoft ODBC for Oracle/Oracle ODBC Driver (Have tried both)
4. Link Tables -> DBA_TAB_COLUMNS (see screenshot 1)
5. Error message appears (see screenshot 2)

There is no opportunity to qualify my table. Only solution I can possibly think of is using a SQL-Passthrough (relatively unfamiliar with this however) for this. Does anyone have any ideas?

 

[RJN]

bump

Still stumped on this. Thanks