Remove MS-Query(ODBC) QueryTable Definition and Replace with an OLEDB Query (to MS-Access)


In Excel 2007, I have a MS-Query QueryTable (Excel to Excel) in a worksheet that I would like to replace with an OLEDB Query (Excel to Access).  When I delete the reference to the MS-Query QueryTable (via the Ribbon or VBA) the External Data ribbon is still greyed out and will not allow a new query to be added in it's place without completely removing the Excel 2007 Table.

To Delete the Query I am using either...
Ribbon:    Table Tools / Design / External Data Table / Unlink
VBA:    Selection.ListObject.QueryTable.Delete

The existing Excel 2007 Table based on the query has over 2,000 references to it from other worksheets in the workbook (Index/Match lookups, etc...).  Is there a way to make the change to OLEDB without removing the existing Excel Table and breaking the formulas that reference it?

Thanks,
Jerry
LVL 16
Jerry PaladinoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkpieterseCommented:
Hi Jerry,

I would do it like this:
- Create a backup copy of your file.
- Build a fresh querytable with the proper things in place
- Use a little bit of VBA to change the existing QT to the settings you need. Suppose the existing QT is on sheet Sheet1 and the new one on Sheet2:

Sub ChangeQT()
    With Sheet1.ListObjects(1).QueryTable
        Sheet2.ListObjects(1).QueryTable.Connection = .Connection
        Sheet2.ListObjects(1).QueryTable.CommandText = .CommandText
    End With
End Sub

Regards,
Jan Karel
0
jkpieterseCommented:
hmm, I see I messed up, the code should read:

Sub ChangeQT()
    With Sheet2.ListObjects(1).QueryTable
        Sheet1.ListObjects(1).QueryTable.Connection = .Connection
        Sheet1.ListObjects(1).QueryTable.CommandText = .CommandText
    End With
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jerry PaladinoAuthor Commented:
Jan,
Yes - Thank you.  I was able to make the change with your suggestion.  I had to modify the VBA syntax slightly so I have included it here for anyone who finds this solution from an EE Search.  I also added SourceDataFile and CommandType properties which I needed to complete the change for my specific instance.
Thank you,
Jerry

Sub ChangeQT()
    With Sheets("Sheet2").ListObjects(1).QueryTable  'New OLEDB QueryTable
        'Sheet1 is the MS-Query(ODBC) QueryTable
        Sheets("Sheet1").ListObjects(1).QueryTable.Connection = .Connection
        Sheets("Sheet1").ListObjects(1).QueryTable.CommandText = .CommandText
        Sheets("Sheet1").ListObjects(1).QueryTable.SourceDataFile = .SourceDataFile
        Sheets("Sheet1").ListObjects(1).QueryTable.CommandType = xlCmdTable
    End With
End Sub

Open in new window

0
Jerry PaladinoAuthor Commented:
Jan - Thanks for your help.  Much appreciated.

Jerry
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.