Dropping creating an external odbc link in vba

[mozgheib]

Hello,

I have an external odbc link from vba to oracle 7.
The problem I am having when running my aplication
the link is not refreshing properly. So I decided to
drop and recreate the external link every time I load
the application.

I know how to drop create a tabledef but not an external
link.

Could you kindly post the code for dropping and recreating
an external link in VBA.


Thanks.

 

[PHV]

Take a look here:

http://www.peterssoftware.com/tlk.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mozgheib]

Kindly see the code I extracted. It drops a link,
re-creates it and tries to refresh. But it does not.
The form that I have linked to the table opens the table in readonly. Please see code below.


Dim dbsTemp As DAO.Database
Dim tdfLinked As DAO.TableDef


Set dbsTemp = CurrentDb()

dbsTemp.TableDefs.Delete ("ORION_PS_STAGING_DATA")

Set tdfLinked = dbsTemp.CreateTableDef ("ORION_PS_STAGING_DATA")


tdfLinked.Connect = "odbc;dsn = oriontest; database=scd"
'tdfLinked.SourceTableName = "ORION.PS_STAGING_DATA"
dbsTemp.TableDefs.Append tdfLinked

tdfLinked.RefreshLink

Set tdfLinked = Nothing
Set dbsTemp = Nothing

 

[PHV]

Has this linked table a (eventually pseudo) Primary Key ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mozgheib]

Yes it has.

 

[PHV]

You may consider to check the permissions on the Oracle's side for the user/password defined in the oriontest DSN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mozgheib]

The account I am using is working fine when I manually link the table.

 

[mozgheib]

As a matter of fact all my links are opening readonly
despite the fact the mdb is not readonly even if I
dropped them and relinked them manually?

This is weird what might be the problem.

 

[mozgheib]

any hints.