Solved

Excel 2007 External Data Connection problem

Posted on 2010-11-10
12
1,663 Views
Last Modified: 2012-05-10
I have an Excel workbook that contains 3 connections to an Oracle Database. The code I used to open the connection is apparently not compatible with Office 2007. I have spent many hours trying to decipher what it is that I need to do to make this work for both Office 2007 and Office 2003. Can someone please take a look at the code I have included and give me some examples of replacement code.
Dim wrkODBC As Workspace

Dim conORAP039 As Connection



Function Some_Function()



SetConnectionObject



'Run GetVehicleModels_Connection query "VEHICLE_MODEL" on 'Lists' worksheet



Sheets("Lists").Range("A1").QueryTable.Refresh BackgroundQuery:=False



End Function



Private Sub SetConnectionObject()

   

    ' Create ODBCDirect Workspace object and open Connection objects.

            

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "xxxxxx", "xxxxxx", dbUseODBC)



Workspaces.Append wrkODBC



    ' Note: The DSNs referenced below must be configured to

    '       use Microsoft Windows NT Authentication Mode to

    '       authorize user access to the Microsoft SQL Server.

    

Set conORAP039 = wrkODBC.OpenConnection("ORAP039", dbDriverNoPrompt, True, _

                     "ODBC;DATABASE=ORAP039;UID=xxxxxx;PWD=xxxxxx;DSN=ORAP039")

  

End Sub

Open in new window

0
Comment
Question by:pwken
  • 7
  • 4
12 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 34102572
It would help if you could tell us what part of the code errors and what the error message is.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 34102609
This is not an issue of compatibility.   The Workspace object is handled by an external DLL.  --  MSRDO20.DLL ---

Check to see if the file exists in your windows/system32 directory... and if so, make sure it is included in the references in your VBA project.
0
 

Author Comment

by:pwken
ID: 34102742
Sorry about that. I do not get any errors.  It just does not work. When I execute the code using Excel 2003 the connection opens and my table is refreshed. When I execute the code using Excel 2007 nothing happens. It appears to ignore the OpenConnectionOjbect part of the code.

I have three (3) data connections that I use in sequence. When the user enters a value into a cell that triggers the OpenConnectionObject part of the code then refreshes the necessary table (table1). The user then has additional options based on the results from the query. These options call the QueryTable.refresh method for there respective tables.

What I have found is that if I do the first refresh manually by right clicking in the table (table1) and selecting refresh, then the VBA code works for all the following QueryTable refreshes. I assume that this is because manually refreshing the first table is opening the connection allowing the other refreshes to work correctly.
0
 

Author Comment

by:pwken
ID: 34102902
I added the the MSRDO20 "Microsoft Remote Data Object 2.0" to the reference list. It still does not work with Excel 2007?
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 34103742
Can you add a stop, trace through the code and verify that it is being executed?
0
 

Author Comment

by:pwken
ID: 34105171
It appears though 2007 does not support the WorkSpace object. I did manage to get it to return:

Run time error '3633', can not load DLL '?????L'
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 19

Assisted Solution

by:Ken Butters
Ken Butters earned 250 total points
ID: 34106649
Are you working with Excel 2003 / Excel 2007 on the same machine?    If so... are you saying it works in 2003 and not in 2007?

The thing is... it is not excel that "supports" it or doesn't support it.   In either case, whether you are using 2003 or 2007, and you have the appropriate reference included in your project, then the code to create the WorkSpace Object will be handled by the appropriate DLL ... NOT by Excel itself.  

Think of the line:
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "xxxxxx", "xxxxxx", dbUseODBC)

As a call to that External DLL.

So keeping that in mind... I would guess that the machine where you are having problems... either doesn't have the correct DLL in the System directory.... or else the appropriate Reference is not selected for your project.

Try taking a look in your 2003 vba Project reference list... and make sure that you have all of the exact same references checked/selected in your 2007 project.   If so... the code should work the  same in both... because both 2003/2007 would be calling the exact same DLL to perform this function.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 34106789
I was wondering if maybe the MSRDO20.DLL could not be found on your system?

Can you try to register it using REGSVR32

If that fails / gives you an error message, then maybe you ahve a dependend DLL missing as well.
0
 

Author Comment

by:pwken
ID: 34111602
Thanks for the help so far.

I successfully ran REGSVR32 using MSRDO20.DLL with no problems.

The workbook in question has all the necessary references. It runs just fine when I run it on a machine that has Office 2003. When I run the exact same workbook on a different machine that has Office 2007 installed it fails to open the connection. No errors just does not even attempt to open the connection.

Since the refrences are contained in the project which is contained in the workbook and I am using the same workbook, then I would not expect a problem with refrences unless they did not exist on the machine.
0
 

Accepted Solution

by:
pwken earned 0 total points
ID: 34111625
I finally got it to work.  Not sure if this is the most elegant way to do this but it works.

I removed all references to the WorkSpace object and the Connection object.

I now use the following code to open the connection the run the query.

'Open connection GetVehicleModels_Connection on 'Lists' worksheet
    With Sheets("Lists").QueryTables(1)
        .Connection = "ODBC;DATABASE=ORAP039;UID=xxxx;PWD=xxxx;DSN=ORAP039"
        .MaintainConnection = False
    End With
   
 'Run "VEHICLE_MODEL" query using GetVehicleModels_Connection on 'Lists' worksheet
    Sheets("Lists").Range("A1").QueryTable.Refresh BackgroundQuery:=False
0
 

Author Comment

by:pwken
ID: 34111696
Problem solved.
0
 

Author Closing Comment

by:pwken
ID: 34143332
Thanks for all the help. Even though I discovered a fix myself, the information you provided has given me better insight into the workings of DLL's which I will need for future enhancements to this same project. I give you 1/2 the points for this valuable information.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now