problem with refreshing oracle table (linked in access)

[TjS]

I have four linked tables within my access db; i need to change the connection string; of course i can refresh by the menu (linked table manager) and choose the right odbc-connection, but i want to be able to do this for the four tables at once by a button.

At this moment i have made a local table in which i have three fields,

1st field: tablename (name of the table)
2nd field: connection string at the moment (datafilepath)
3rd field: connection string to be (PathToBe)

In following code i first pick the first table (for testing purposes)
to change the connectivity.

I debug this by in the immediate window calling the function (also for testing purposes); however i get an error by letting this function run (3011 syaing "The microsoft Jet Database engine could not find the object '<<TABLENAME>>'. Make sure the object exists and that you spell its name and the path name correctly", with of course the buttons END and DEBUG.

I think one of the problems is that the databasename (for example db_tst) is mentioned in the actual tablename.

Example:
real tablename is db_tst.tblName
linked name: tblName

My question? How to cope with it, i can't seem to get it work, the connection string isn't changing.....

Thanks for your answers/reactions!

Function a() 'voor de test

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim rst As DAO.Recordset
Dim tbl As DAO.TableDef

Set db = CurrentDb
Set rst = db.OpenRecordset("tblLinkedTables")
Set tbl = db.TableDefs(rst!TableName)

tbl.RefreshLink

End Function

 

[TjS]

part of problem fixed (i think)

Somehow, linking the tables again (manually) and saving the password in the string (checkbox, save password) fixed my problem of not being able to change the property of the connection string manually, however the code does not still seem te work(?)

 

[user-hostile]

I have four linked tables within my access db; i need to change the connection string; of course i can refresh by the menu (linked table manager) and choose the right odbc-connection, but i want to be able to do this for the four tables at once by a button.

At this moment i have made a local table in which i have three fields,

1st field: tablename (name of the table)
2nd field: connection string at the moment (datafilepath)
3rd field: connection string to be (PathToBe)

In following code i first pick the first table (for testing purposes)
to change the connectivity.

I debug this by in the immediate window calling the function (also for testing purposes); however i get an error by letting this function run (3011 syaing "The microsoft Jet Database engine could not find the object '<<TABLENAME>>'. Make sure the object exists and that you spell its name and the path name correctly", with of course the buttons END and DEBUG.

I think one of the problems is that the databasename (for example db_tst) is mentioned in the actual tablename.

Example:
real tablename is db_tst.tblName
linked name: tblName

My question? How to cope with it, i can't seem to get it work, the connection string isn't changing.....

Thanks for your answers/reactions!

Function a() 'voor de test

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim rst As DAO.Recordset
Dim tbl As DAO.TableDef

Set db = CurrentDb
Set rst = db.OpenRecordset("tblLinkedTables")
Set tbl = db.TableDefs(rst!TableName)

tbl.RefreshLink

End Function

After your set rst statement, you need to set the cursor:

rst.moveLast
rst.MoveFirst

Then, rst!Tablename will actually contain a value.

As for the connection string, I know at some point you need to set this property:

tbl.Connect

<Connect> is a string value: your connection string. So I would try something like:

Set rst = db.OpenRecordset("tblLinkedTables")
rst.MoveLast
rst.MoveFirst 'you're now at the first record in rst
while not rst.eof
  Set tbl = db.TableDefs(rst!TableName)
  tbl.Connect = rst!PathToBe
  tbl.RefreshLink
  rst.moveNext
wend

Let us know if this works any better.


u-h

 

[TjS]

Thanks for the solutions; all the code works fine, except for the fact that for some reason i cannot modify the linked odbc table properties....
Do i have to allow the db somehow to manage the linked table property?.

 

[user-hostile]

Thanks for the solutions; all the code works fine, except for the fact that for some reason i cannot modify the linked odbc table properties....
Do i have to allow the db somehow to manage the linked table property?.

Yes, I finally had a chance to test it myself, and you're right: this code won't make the change an Oracle table's links; it does work with Access linked tables.

I tried using another property of the Oracle tbl object: something like "SourceTableName" (tbl.SourceTableName) and changing that, but Access wouldn't allow that to change once it's set.

Sorry; I've had just a tiny bit of Oracle backend table experience in Access. Perhaps an Oracle/ODBC expert can clear this up for you. Good luck.


u-h