Oracle custom connect string

[nvrwrkn]

Morning!



I need to create an app that contains link tables to a networked Oracle database. Currently, every machine in the enterprise uses TNS to find the database, the TNSNAMES file located in a common mounted network drive that every machine can see. Every machine has the necessary Oracle drivers installed to make use of the TNS file to find it's databases. Problem is, this form of connection isn't available when creating linked tables, we're forced to create a system or machine connection that then becomes unique to that machine only since it doesn't exist on every other machine. We don't have access to admin rights on the machines we want to run the app on so we can't go around creating new connections on every machine.

Question is, how can we create link tables using a custom connection string in a module? Rather than looking for a connection string from a machine stored file we need to be able to use a module connect string to allow us to get to the linked tables. This allows us to run the app on any machine with access on it and not have to try and create a machine or system connect string.

If link tables can't be created this way, is there a way to create the tables upon opening up the app using the custom connect string in a module run at app start?

This wan't a problem until admin rights changes rolled out. Without access to the outside Oracle tables using an embedded string rather than rely on a machine/system DNS that won't exist on target machines, the app kinda dies.

Any help at all is greatly appreciated

[rpeare]

So you're saying that when you open up administrative tools your ODBC connection to your oracle tables is not visible? it's contained in another application that the machine itself can not see or display? Check the USER and SYSTEM DSN's to see if it's there if it is you can readily connect to it through MS access and I can help with that, if it's not there I've never seen or heard of the odbc driver not being available when a software product was installed.

[nvrwrkn]

Correct,kinda. The connection isn't ODBC but rather the result of the installation of Oracle TNSAdmin drivers installed from Oracle client installation software. That client install sets up the proprietary Oracle driver to use TNSAdmin and its registry settings to find the TNSNames file, parse it and find the location of the necessary database. The inherent MS database DNS driver never comes into play. All machines are set up with rudimentary Oracle Forms applications that point to forms on a remote directory and invoke Oracle 6.0 binaries on the client machine to execute Oracle Forms/Runtimes.

No ODBC drivers are used therefore no ODBC connections are made into the MS repository of connection strings. In an Access app I can create an ODBC connection and save it to my machine and it works, but that doesn't help on every other machine that doesn't have the same MS ODBC connection saved in its repository.

so I basically need to start an app, create a login page that uses user/pass and passes it to a custom connection string that then connects to the remote oracle database and then recreate the link tables every time the app is started.

[rpeare]

Wow that is a unique problem, I've never tried to connect to a database that didn't have an ODBC driver available in the administrative tools. I have no way to test this but does this link help:

http://forums.eukhost.com/f15/how-co.../#.UGrwHK6QhQU

It seems to allow you to tell it which driver you want to use which applies to your situation although it's strictly relating to connecting to an access database there's theoretically no reason it wouldn't work for other database types if you know the driver name to insert in the connection string.

here's another link that has more of an explanation

http://www.cuyamaca.net/WebDevFAQs/a...ng/default.htm

[nvrwrkn]

well the Microsoft ODBC drivers are likely installed when access itself is installed, the problem lies in that a specific connection isn't defined in the DSN list that points to the database. In my instance, creating a DSN that points to the oracle database works because TNS administration has been installed on every machine for other apps to use, so when creating the DSN entry telling it I want to connect to my oracle database, TNS administration translates the database name I type in using the TNSNAMES file located on the network drive so the connection string can find it. TNSNAMES contains the proper "world" name and SID of the database I connect to as well as tells the driver which port the dbase is running on and it's IP address on the network. That's done so if they change servers, they change the IP in the TNSNAMES file and I can still find the dbase by just typing in the name. That's the prob in a nutshell.

But, as for your links... it's interesting. Although they use an access database definition, I wonder if there's a specific name for an ODBC to Oracle connection? In my case though, I can't define the location of the database using windows local or network locations because that happens through TNS. The database has it's own server and there's no windows network location for the database. There is an IP that I could use but that would likely die in short order as maintenance changes could change the IP I used in the string and then it's broken. I have to define the "name" of the database and let TNS find it and pass that info into the connection string.

Odd, it's a half and half problem. Need both an Access and Oracle expert.....

Thanks for those links though, it gives me something to work on and think about.

[nvrwrkn]

how about using an Office Data Connection? Just made one in excel. Contains necessary connection parameters....just how to use it in a connection string inside access......?


skimming through the odc file I see the following:

<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=MSDAORA.1;User ID=*******;Data Source=MYORADB</odc:ConnectionString>
<odc:CommandType>Table</odc:CommandType>
</odc:Connection>
</odc:OfficeDataConnection>

[nvrwrkn]

ok, made a connection to the database and made a prompt for the user to enter their creds. Login successful, but how to create link tables from VBA?

[rpeare]

this is an example taken from

http://stackoverflow.com/questions/3...s-in-ms-access

Function LinkTables()
    Dim DB As Database, tDef As TableDef
    Set DB = CurrentDb
    Set tDef = DB.CreateTableDef("YourAccessLinkedTableNameHere")
    tDef.Connect = "ODBC;Driver={SQL Server};Server=srvname;Database=dbname;UID=sqluserid;PWD=sqlpwd"
    tDef.SourceTableName = "dbo.YourSourceTableNameHere"
    DB.TableDefs.Append tDef
End Function

[nvrwrkn]

eureka! I think..... does that last append actually populate the access table or just create a link table? There's waaaaay too much data in the oracle table to suck it all down into an access table.

[rpeare]

The code is going to create a table in access with all the data in the table you link from your oracle database. As long as you don't have more than 255 columns in your oracle table access should be able to handle quite a bit, I use it regularly to handle a database with around 750,000 records with 140 fields per records.

[nvrwrkn]

hmmm....if this is the only option then I guess I have no choice. Main reason I prefer a link table is that I have do to this for 3 tables, one is a geocode table for a large metropolitan city. The personnel table I can download every time the database is opened since it's only around 12K employees, but as for the geocode table you can see why I'd rather have a tuned oracle datawarehouse doing the heavy lifting of returing one recordset rather than the entire city geodata every time someone opens the app. Not to mention we use fairly antiquated workstations here and it just becomes that much slower to sift it locally.

So no way to create a linked table without appending all the source table's data then?

[rpeare]

Have you got code that actually lets you view the geocode data within Access? can you show the code? The code we've been talking about should create a linked table not import the data and re-establishing a link when you open a database is very quick (I've actually been working today on an issue linking dynamically to a very large text file and the linking is very fast, accessing the data, however, is slower).

[nvrwrkn]

off for a couple days, sorry for the delay. That plus I currently have other parts of the app I have to get done the table issue fell by the wayside for a few days. At the moment the original linked table is still being used as I let access create it for me for initial use. I can connect to the database now although I haven't tried to pull data yet, TNS appears to be coming up and connecting though. Since I rarely work in access or VBA that Tabledefs.Append line threw me but I've since read up on it and see it's just adding the table to the tabledefs in the dbase.

I'll try and get something done this way today sometime and get back to you. I greatly appreciate your time with the code!

[nvrwrkn]

ok, getting the old "Could not find installable ISAM" error, although I'm not linking files but rather tables.

This may have something to do with the Oracle connection and tables?

[nvrwrkn]

trying to do this a different way. I figure I can import the data into a local table for speed with a make table query. I have the connection string in a function that I call now, so what I'm doing is a simple call to the function to open the connection then try running the make table query to create the table. Of course it's not working as it wants to look for the table locally.

str = "SELECT MV_OWNER.GEO_CODES_MV.* INTO GeoData FROM MV_OWNER.GEO_CODES_MV" is the quick test.

This error because I'm not defining the above table to be located at the connection I just made I'm thinking?

Guess I need another way to do this then.