Solved

Creating a VBA connection string from an ODBC System DSN entry

Posted on 2011-09-08
10
2,334 Views
Last Modified: 2012-05-12
Our DBA created the necessary ODBC entry for our database using the System DSN page.

The Connection Test passes and the one thing we needed to do was uncheck the Enable Query Timeout checkbox, which is checked by default.

Could someone please show me how to create a connection string that I can use in Excel 2007 to connect to the Oracle database with which this ODBC connection is linked?

It's an Oracle database. I am running Windows 7, and Excel 2007. The spreadsheet control is an Active X control.

In case its relevant, our DBA configured SQL Tools with that same connection and I have the following data available for each of the following fields:

UserName
Password
Bypass tnsnames.ora (checkbox is checked)
Host
TCP Port
SID
Use Service instead of SID (checkbox is checked)

Thanks!
0
Comment
Question by:newbieweb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 

Author Comment

by:newbieweb
ID: 36502801
Is this close?

Microsoft ODBC for OracleType:    ODBC Driver
Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;

Is this a token "{Microsoft ODBC for Oracle}" ?

Or do I include this literally?

What do I use for Server?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 36502852
http://www.connectionstrings.com/oracle

>>What do I use for Server?

The Database alias set up in your tnsnames.ora file.  Your DBA should be able to assist you with this.
0
 

Author Comment

by:newbieweb
ID: 36502952
Thanks, I already had that link but found it confusing for the ODBC connection. My DBA gave me a sample connection string, but I am confused about how the variable "Driver" relates to Connection string?

He gave me something like this:

'Driver={Oracle in OraClient11g_home1};Data Source=OurDomain.engineering.dom;User ID=user1;Password=pw1;

How do I relate this to MyDatabase? As defined in VBA as:
Set MyDatabase = New ADODB.Connection
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:newbieweb
ID: 36502963
The Data Source Name is already defined in the ODBC connection, and it succeeds in connecting to the DB. Should I be using that?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 36502994
>>how the variable "Driver" relates to Connection string?

The driver specifies Who's ODBC software you are using.  In the first example you are using Micrososts.

In your latest, Oracle's.

>>and it succeeds in connecting to the DB. Should I be using that?

If you are connecting why shouldn't you use it?

>>As defined in VBA as:

My VBA is pretty rusty but you put it all together in the Open for the recordset.

There are a LOT of examples out there if you look around.

0
 

Author Comment

by:newbieweb
ID: 36503049
Your last post confuses the heck out of me.

> If you are connecting why shouldn't you use it?

I am connecting via the Test Connection in ODBC, so it seems I should use that, but this:

{Oracle in OraClient11g_home1} seems generic.

This is the Driver but in the ODBC Aministrator, there is an actual name to this entry. Should I use that instead?

> There are a LOT of examples out there if you look around.

I know, but that's part of the problem. So many, but my needs are pretty simple: connecting from Excel 2007 to an existing ODBC connection using VBA.

I need to know how to relate Driver (defined above) with the Open comand. Do you have any idea?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 36503080
>>{Oracle in OraClient11g_home1} seems generic.

That is just the way ODBC names the drivers.

>>there is an actual name to this entry. Should I use that instead?

I think I did steer you wrong before.  I mixed up ODBC and Native Access providers like ODP.Net and OleDb.

The 'name' is the DSN and if you want to use ODBC, that name should be used in the Server entry.  The driver should stay the same.
0
 

Author Comment

by:newbieweb
ID: 36503233
I do not need to use ODBC. I've been told it would be easier if there was no ODBC config needed on the machine where this would be deployed.

So this is fine:
{Oracle in OraClient11g_home1}

So this is right, but I do not know what to do with it:

'Driver={Oracle in OraClient11g_home1};Data Source=OurDomain.engineering.dom;User ID=user1;Password=pw1;

regarding my vaiable:
Set MyDatabase = New ADODB.Connection

Any idea?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36503350
>>easier if there was no ODBC config

You will need to install and configure an Oracle Client anyway.

There are examples out there using a DSNless connection.

>>So this is fine: {Oracle in OraClient11g_home1}

If you want to use the Oracle driver, yes.

>>but I do not know what to do with it:

check out the code example in:
Problem with querying from Excel 2007 macro to Oracle.

http://www.astahost.com/info.php/Vba-Oracle_t10766.html

It uses the Microsoft driver but it should be the same for any ODBC driver.
 
0
 

Author Closing Comment

by:newbieweb
ID: 36503597
Thanks for the help.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question