relink oracle linked tables in code

[raj]

Hi,

I need to relink the linked tables during the application startup depending
up on the users selection of oracle database.

Please advice how to do this in code.

 

[Stefan Hoffmann]

hi Raj,

I need to relink the linked tables during the application startup depending
up on the users selection of oracle database.

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)


mfG
--> stefan <--

 

[BeWyched]

Hi Raj

Try looking at the ADO ConnectionString property (search in Help to find).
The Visual Basic Example link gives many options for connection strings which
should give you what you need.

Cheers.

BW

 

[raj]

Hi Stefan,

Thanks for your reply.

I tried your suggestion, but i am getting

"There are several tables with that name. Please specify owner in the format
'owner.table'. (Error 3298)"

But my oracle db contains only 1 table with that name.

 

[Stefan Hoffmann]

hi Raj,

I tried your suggestion, but i am getting

"There are several tables with that name. Please specify owner in the format
'owner.table'. (Error 3298)"

But my oracle db contains only 1 table with that name.

Post your code.


mfG
--> stefan <--

 

[John Spencer]

I ***suspect*** (guessing here) that Oracle can have tables with the same
name in a database that are distinguished by the owner name. Something akin
to the way Access can have a control named "cboxGetStuff" on multiple forms.
As I recall MS SQL server can have multiple tables with the same name that
must be distinguished by owner name and database name in certain contexts.

Beyond that tid bit, I have no advice to offer since I have not worked with
Oracle.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[raj]

Hi Stefan,

Here is my code

Sub test()
With CurrentDb
.TableDefs.Append .CreateTableDef("TESTBRANCH", 0, "DB_BRANCH",
"ODBC;DSN=CAS;DBQ=CAS1.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")
End With
End Sub

In oralce i have synonums for all tables, does it causing any problems?

 

[Stefan Hoffmann]

hi Raj,

Sub test()
With CurrentDb
.TableDefs.Append .CreateTableDef("TESTBRANCH", 0, "DB_BRANCH",
"ODBC;DSN=CAS;DBQ=CAS1.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")
End With
End Sub

In oralce i have synonums for all tables, does it causing any problems?

Maybe, synonyms can be schema bound

CREATE [PUBLIC] SYNONYM [schema.]synonym
FOR [schema.]object [@dblink]

so you need to specify this schema.


mfG
--> stefan <--

 

[raj]

Hi Stefan,

Thanks again for your reply.

Yes, its b'cos of synonyms. I created a new table with out synonym and
tried, the code works.

Regarding schema bound synonym, all the existing tables have public synonym.

I just read "You cannot specify a schema for the synonym if you have
specified PUBLIC".

Once again thanks for your help.

hi Raj,

Sub test()
With CurrentDb
.TableDefs.Append .CreateTableDef("TESTBRANCH", 0, "DB_BRANCH",
"ODBC;DSN=CAS;DBQ=CAS1.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")
End With
End Sub

In oralce i have synonums for all tables, does it causing any problems?

Maybe, synonyms can be schema bound

CREATE [PUBLIC] SYNONYM [schema.]synonym
FOR [schema.]object [@dblink]

so you need to specify this schema.


mfG
--> stefan <--

 

[raj]

Hi Stefan,

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

With CurrentDb
.TableDefs.Append .CreateTableDef("TESTBRANCH", 0, "DB_BRANCH",
"ODBC;DSN=CAS;DBQ=" & myDBQ &
";DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")
End With

But when I quit and restart the application, the application by default
connects to the previous dbq, though mydbq dynamically changes depending
upon the user's choice.

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?

hi Raj,

Sub test()
With CurrentDb
.TableDefs.Append .CreateTableDef("TESTBRANCH", 0, "DB_BRANCH",
"ODBC;DSN=CAS;DBQ=CAS1.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")
End With
End Sub

In oralce i have synonums for all tables, does it causing any problems?

Maybe, synonyms can be schema bound

CREATE [PUBLIC] SYNONYM [schema.]synonym
FOR [schema.]object [@dblink]

so you need to specify this schema.


mfG
--> stefan <--