Linking to Oracle

[lcox400w]

I have a split database we have been using for about a year. I want to add a
new feature to the application that will query a table in our oracle DB to
retreive some information to reduce duplicate data entry. I know how to link
to the oracle database manually but I'm not sure how to proceed with doing
this in VBA.

None of my client machines have the ODBC Connection on them. So my first
questions are, do I create the link manually on the front end of the app and
then install the ODBC on each client machine? Or is there a better/easier
method of doing this?

My second question is, I'm not sure how to use VBA to open the database
table I want so I can then run the query. Since the database is not part of
my tables, I have never opened a second database through VBA before if
someone can help point me in the right direction with a sample?

thanks in advance.

 

[lcox400w]

Thanks for the info. The oracle client is on each machine already, I will
look into automating the ODBC installation and create the link on the front
end.

Do you have a sample code of how to create the odbc connection and
incorporate the password so the users dont have to do that?

 

[lcox400w]

Hi,

This assumes that all of the computers have the same version of Oracle
and that it was installed in the same way. Start up a command prompt on your
machine and enter "regedt32". Expand the tree so you can see the
"HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI" key. Right-click on the "ODBC
Data Sources" key and choose Export. Give it a name and only export the
selected branch. Repeat for the actual DSN name you have configured on your
computer. So if you have named it "Oracle_ODBC", you will find a key with
that name. Close out of the registry editor and the command prompt window.
You will now have two files. Right-click on the first file and choose Edit.
Remove all but the registry editor version, the key name and the entry for
your Oracle DSN. Right-click on the second file and copy all but the
registry version line from it into the first file. Close and save. Now,
provide the first file to the other users. All they have to do is double-
click on the file and click OK twice. They may need to have some kind of
administrative permissions to do this.

Clifford Bass

Thanks, that should help a lot to get me started.