relinking oracle tables through code

[raj]

Hi,

I am using the following code to relink the oracle tables during my
application startup

With CurrentDb
TableDefs.Append _
.CreateTableDef(ADestinationName, 0, _
ASourceName, CONNECTION_ODBC)
End With

Where

- ADestinationName is the name of the linked table in Acces
- ASourceName is the qualified (schema.name) of the table or view in Oracle.
- CONNECTION_ODBC is the connection string (see connectionstrings.com)

But when I quit and restart the application, the application by default
connects to the previous dbq/dns.

I every time need to click Tools > Database Utilities > Compact and Repair
Database to take the news DBQ or connection string.

Is there any way i could clear the cache or close the connection properly?

 

[Alex Dybenko]

hi,
have a look here:
http://www.mvps.org/access/tables/tbl0010.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[raj]

Hi Alex,

Thanks for you link.

I have tried this first. But i am getting the message

"The User DSN for Oracle Tables were not found. Please check ODBC32 under
Control Panel."

I have Oracle user DSN, but still not sure why this message is getting
prompted.

Pls help.

 

[Alex Dybenko]

Hi,
try the following - open database, then manually link table from oracle db,
open it in design and in table properties look at connection - it will show
you the right one, which will also work in this proc

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com