DelphiFeeds.com

  • Dashboard
  • Popular Stories
  • Trending Stories
  • Feeds
  • Login
Trending now

Learn A Powerful Python Concept: Modules And Packages With A Delphi Windows GUI App

The IFDEF Problem!

Quickly Build Natural Language Processing Capable GUI Apps With Delphi And Python NLTK

Freebie Friday: capture controls or forms

Easily Learn How To Use Python List Comprehensions In A Delphi Windows GUI App

Powerful Native Open Source Hard Disk, Folder and Storage Analyzer Built In Delphi

Incredible Database Development And Visualization Tool Is Built In Delphi

User Defined Functions with InterBase

Learn How To Use C++ Extending Sizeof For Windows Development

Learn How Easy It Is To Apply Updates With TFDQuery.OnUpdateRecord In Delphi

Changing Excel query connection strings

1
Sean Sean 11 years ago in excel 0
We use Excel and database queries extensively for reporting purposes. It's quick and easy to set-up, and provides reports that our clients can manipulate.

However I have recently run into a rather painful excel quirk with ODBC connections: Excel stores the database connection string internally. Even if you change the ODBC connection on the computer, excel still uses the original connection from when the query was created.

This bit us when moving the reports to a different machine. Despite having the same ODBC connection set up, excel wouldn't refresh the query and gave the error "[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied."

Aside
A similar problem can happen if you update the database from sql server 2000 to sql server 2005 or 2008. In that case you may get the error "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON." In that case, you need to alter the connection string to change "APP=Microsoft® Query" to something else. Apparently MS hard coded a check for "Microsoft® Query" in sql server which then runs DBCC TRACEON for no apparent reason. I suggest "APP=WTFWYT"


Changing one query
(from here)
"Open the worksheet and place the cursor on a cell within the cell range of
the query. Press Alt-F11 to open the VBEditor. Press Ctrl-G to open the
Intermediate window. Type the command: ? ActiveCell.QueryTable.Connection.
The embedded connection string will be echoed back to the screen. Put double
quotes around the string and update the connection information with the new
server info. Move the cursor to the beginning of the connection string and
insert the following in front of the string:
ActiveCell.QueryTable.Connection =
"

Changing multiple queries in a spreadsheet
(modified from here)
You need to create the following macro (change connection string to suit, see above) and run it in each spreadsheet requiring change. (See the steps below). Once the macro has run successfully, delete it before saving the spreadsheet.

Sub ChangeConnections()
Dim sh As Worksheet, qy As QueryTable
For Each ws In ActiveWorkbook.Sheets
For Each qy In ws.QueryTables
qy.Connection = "..."
On Error Resume Next
qy.Refresh
If Err.Number <> 0 Then MsgBox "Problem refreshing QueryTable: " & Err.Description
Next qy
Next ws
End Sub

Getting the new connection string
The easiest way to find the new connections string is to create a new query and do the ?ActiveCell.QueryTable.Connection trick.

Trending Stories

  • Learn A Powerful Python Concept: Modules And Packages With A...

  • The IFDEF Problem!

  • Quickly Build Natural Language Processing Capable GUI Apps With Delphi...

Embarcadero GetIt

  • Brook Framework

    Microframework which helps to develop web Pascal applications.

  • Trial - TMS Scripter

    Add the ultimate flexibility and power into your apps with native Pascal or Basic scripting and […]

  • Trial - TMS VCL Chart

    DB-aware and non DB-aware feature-rich charting components for business, statistical, financial […]

  • Trial - TMS VCL Cloud Pack

    TMS VCL Cloud Pack is a Delphi and C++Builder component library to seamlessly use all major cloud […]

  • Trial - TMS VCL UI Pack

    Create modern-looking & feature-rich Windows applications faster with well over 600 components […]

  • Learn Delphi Programming
  • Learn C++
  • Embarcadero Blogs
  • BeginEnd.net
  • Python GUI
  • Firebird News
  • Torry’s Delphi Pages
Copyright DelphiFeeds.com 2021. All Rights Reserved
Embarcadero
Login Register

Login

Lost Password

Register

Lost Password