Avatar of pwken
pwken asked on

Excel 2007 External Data Connection problem

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

Microsoft Excel

Avatar of undefined
Last Comment
pwken

8/22/2022 - Mon
Jan Karel Pieterse

It would help if you could tell us what part of the code errors and what the error message is.
Ken Butters

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.
ASKER
pwken

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
pwken

I added the the MSRDO20 "Microsoft Remote Data Object 2.0" to the reference list. It still does not work with Excel 2007?
Ken Butters

Can you add a stop, trace through the code and verify that it is being executed?
ASKER
pwken

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'
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Ken Butters

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ken Butters

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.
ASKER
pwken

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.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
pwken

Problem solved.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
pwken

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.