Desperately seeking HELP! 1

[StihlRunning]

In desperate need of help with Excel VBA ADO access to Oracle tables. I have to deliver this Excel sheet to multiple people and I want the Excel file to be populated with the most recent data from a Oracle DB every time they open it. I don't want them to have to configure a DSN or TSN and it will get passed around and forwarded, etc. I do have a background in Oracle databases and Oracle Applications ERP. I have written VBA code in the past but only with ODBC. I am wanting to deliver spreadsheets that my co-workers can only worry about opening the spreadsheet and everything else is auto for them. So, my dilemma is in my actual call to the database server.

My call:
conn.ConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=" & strHost & ")(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"

The con.open gives me this error message:
Run-time error '-2147467259(80004005)';
[Microsoft][ODBC Driver Manager]Data source name not found and no default driver specified
I found this code but realize it is for EZ Connect(I have no idea what EZ Connect is) but it looks like what I am looking for:

'OracleConnectionStringBuilder sb = new OracleConnectionStringBuilder();
'sb.DataSource = "MyOracle.MyCompany.com:1521/MySid"; // EZ Connect -- no TNS Names!
'sb.UserID = "xxxx";
'sb.Password = "xxxxxxxx";
'
'OracleConnection conn = new OracleConnection(sb.ToString());
'conn.Open();

I am running on a 64-it operating system and believe my problem is with my drivers. The Microsoft ODBC driver is found in my 32-bit ODBC manager but not my 64-bit ODBC manager. How do I get the Microsoft ODBC into my 64-bit manager? But isn't the whole purpose is to not have to install drivers on machines. Our users do not have any oracle clients or drivers installed on their machines. Currently they have everything(data) pushed to them but I would rather let them get the data when they want it in real-time.

I apologize for the extra-long post but I have been at this all week and have exhausted all my resources. I can't be the only smuck trying to accomplish this simple task!!! HELP is greatly appreciated. And OH, I have until Monday to deliver! ! !

 

[Andrzejek]

My guess here is, if "Our users do not have any oracle clients or drivers installed on their machines" then how do you want to connect your Excel to Oracle?

You may want to have a simple program written and install on a Server with all necessary/needed ‘pieces’ installed and have your users run it whenever they want the data from Oracle. This program will create an Excel file, connect to Oracle, grab any data needed, dump it into Excel, and show it to the user.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Assuming that you have access to a shared server on which you will have an Excel workbook accessible by your customers, and which YOU will periodically run queries to your Oracle database to refresh the data.

Each of your users will have MS Query, for instance, to access the tables in your workbook, that will Refresh on Open when they open their individual workbooks.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[StihlRunning]

I can ping the database server just fine from any location(user pc). So if I can ping it, shouldn't I be able to connect to it via VBA ADO? Thank you for your help!

 

[SkipVought]

Post your code, not just snippets.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

...also

DO you use Option Explicit in each Module?
DO you have a Reference set to Microsoft ActiveX Data Objects in your VBA Project?

This entire discussion ought to be in Forum707.

Oh, and BWT, welcome to Tek-Tips.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

There is a website dedicated to connection strings.

combo

 

[strongm]

>if I can ping it, shouldn't I be able to connect to it via VBA ADO?
Afraid not. All that tells you is that the host server exists and is up and running and responding to certain ICMP queries (oh, and that any firewalls on the route are not blocking ICMP). It tells you nothing about whether Oracle is running on it, much less whether you can connect to it. For that you need something like a port test utility. The Orac le client ships with one, but you can also test with Powershell's test-netconnection command. Something like:

[tt]test-netconnection -computername <targetserver> -port 1521[/tt]

Port 1521 is the default port use by Oracle for unencrypted traffic (which is why it appears in the EZConnect string you illustrate in your original post. Note that EZConnect has to be installed and working on the Oracle server for you to be able to use it from a client application. Three's a FAQ aboput it here

In addition, for Excel to be able to connect to Oracle via ADO (whether using ODBC or Ole Db) then, as Andy says above, you'll need oracle clients or drivers to be installed as well.

 

[StihlRunning]

Thanks again for all the help, I truly appreciate it!

my code:
Option Explicit

Sub connectDB()
Dim conn As ADODB.Connection
Dim rs As ADODB.RecordSet
Dim strHost, strDatabase, pwd, strUser, strPassword As String
Set conn = New ADODB.Connection
strHost = "46!#!dbs"
strDatabase = "n!$c"
strUser = "XXXXX"
strPassword = "xxxxxxx"
conn.ConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=" & strHost & ")(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"
conn.Open
Set rs = New ADODB.RecordSet
rs.Open "Employees", conn, adOpenKeyset, adLockBatchOptimistic
ActiveCell.CopyFromRecordset rs
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub


I am trying to just simply connect t our database server and pull back a small amount of data. I get the error at the "conn.open" line. Again, I apologize for the confusion on my part, but if the Microsoft ODBC for Oracle is only showing in the 32-bit odbc driver tab and not in the 64-bit odbc driver tab shouldn't that be a problem? Is there another route that I should try instead of "Driver={Microsoft ODBC for Oracle}; ". Should I try the OLE DB providers according to the Oracle Connection strings site?

Thanks again for all the help!!

 

[strongm]

Unless you have specifically optred to install a 64bit version of Office, then you are working in 32bit, no matter what the bitness of the OS, so you'll need the 32bit ODBC driver (and preferably the Oracle version rather than the Microsoft version)

However, as both Andrzejek anbd myself have suggested, you need the Oracle client installed to give the ODBC driver something to talk through (it does not talk directly to the Oracle server). You can get some ifno about whether this by using the 32bit ODBC manager to try and add a User DSN using the Oracle ODBC driver. I suspect that you'll get an error ...

 

[Andrzejek]

... and even if you will be able to successfully connect Excel to Oracle from your computer, that does not mean other users who will open your Excel file on their machines will be able to do the same.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[StihlRunning]

Thank you very much for the replies, I appreciate the help!

My PC works very well getting the data the the spreadsheet needs because I have installed the client on my machine and have the ODBC drivers installed as well. I was hoping to publish the spreadsheets on our local intranet and let the customers pull the data as needed instead of waiting for a report to run (let them pull data instead of the data being pushed). I was hoping not to have to each computer to install clients. So, my machine works well with my setup but is there anyway to publish the spreadsheets out to the customers without any touching of their computers? I have seen some articles about tsn-less dsn-less connections but I drug down into the mud with 100 different ways of accomplishing my goal. Again, I appreciate the help and any suggestion is appreciated!

 

[Andrzejek]

You can use Skip's suggestion from 8 Oct 21 17:40 - that will definitely work.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

>tsn-less dsn-less

Sadly neither of these mean you can do away with installing the Oracle client. Just means you do not have to configure tnsnames.ora on the client.

As to a viable solution, as Andy says - see Skip's post of 8 Oct 21 17:40