Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Changing data source on ODBC query

Hi,
Can anyone tell me if it is possible to change the datasource on an ODBC
query set-up within excel?
Can this be done with code?
If so can anyone suggest some code?

Thanks.


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 663
Default Changing data source on ODBC query

Hi,

Follow these instruction:

I can't create a new data source in Microsoft Query.
-----------------------------------------------------------

Check the server address and logon information Before you set up a data
source, make sure you know the address where the database is located on your
network and have the necessary permissions to connect to the database and log
on. See the administrator of your database for a logon name, password
(password: A sequence of characters needed to access computer systems, files,
and Internet services. Strong passwords combine uppercase and lowercase
letters, numbers, and symbols.), or any other permissions required, and to
make sure the access you've been granted is working properly.

Check your driver First, make sure you have the right ODBC driver (Open
Database Connectivity (ODBC) driver: A program file used to connect to a
particular database. Each database program, such as Access or dBASE, or
database management system, such as SQL Server, requires a different driver.)
or data source driver (data source driver: A program file used to connect to
a specific database. Each database program or management system requires a
different driver.) for your data source (data source: A stored set of
"source" information used to connect to a database. A data source can include
the name and location of the database server, the name of the database
driver, and information that the database needs when you log on.). ODBC
drivers and data source drivers allow you to connect to new databases as they
become available. However, you must make sure correct driver is installed for
the type of database you're using.

Make sure the driver works with Excel In addition to the drivers
provided with Microsoft Office, you can use ODBC and data source drivers
provided by third-party manufacturers. Before you try to use a third-party
driver, make sure the manufacturer has tested the driver with Microsoft
Excel. For some databases, the driver supplied with the database software may
be the best choice. Contact the administrator of your database to find out
what's available and what works best at your site.

Make sure the driver is properly installed
-------------------------------------------------

To display the list of available drivers, point to Import External Data on
the Data menu, and then click New Database Query.
Double-click New Data Source on the Databases or OLAP Cubes tab.
Type a name in step 1 of the Create New Data Source dialog box, and then
click the list in step 2. If you don't see the driver you need, you should
check to make sure the ODBC driver or data source driver is installed
properly.
Make sure you supplied all of the configuration information After you've
installed the driver and selected it in step 2 of the Create New Data Source
dialog box, make sure you provide all of the necessary information in step 3
of the dialog box. For information about a Microsoft driver, click Connect,
and then click Help in the setup dialog box for the driver. For third-party
drivers, see the Help system or the documentation for the driver.

If you are setting up a data source with an ODBC driver or data source
driver provided by Microsoft, click the name of your driver for information
about the settings you should make in step 3 of the Create New Data Source
dialog box.

Creating data sources
-------------------------

The data source I want isn't listed in the Select Data Source dialog box.

If you can't find your data source (data source: A stored set of "source"
information used to connect to a database. A data source can include the name
and location of the database server, the name of the database driver, and
information that the database needs when you log on.), click New Source in
the Select Data Source dialog box, and then click Other/Advanced under What
kind of data source do you want to connect to in the Data Connection Wizard.
If you are still unable to find your data source, check with your system
administrator or the vendor that provides the database you want to access.

Challa Prabhu

"Aussie CPA" wrote:

Hi,
Can anyone tell me if it is possible to change the datasource on an ODBC
query set-up within excel?
Can this be done with code?
If so can anyone suggest some code?

Thanks.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Changing data source on ODBC query

I just found a workaround for this.

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 =

Move the cursor to the end of the string and press enter. This will store
the updated connection string back into the worksheet. Try your query now.

Repeat for any other embedded queries in your worksheet.



"Aussie CPA" wrote:

Hi,
Can anyone tell me if it is possible to change the datasource on an ODBC
query set-up within excel?
Can this be done with code?
If so can anyone suggest some code?

Thanks.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
change the ODBC data source of a pivot table JohnH Excel Discussion (Misc queries) 0 August 16th 06 07:10 PM
changing query source [email protected] Excel Worksheet Functions 2 March 21st 06 09:55 PM
Changing Pivot Table Source from ODBC to .odc. njdavies Excel Discussion (Misc queries) 0 February 6th 06 10:29 PM
change odbc data source jenn Excel Worksheet Functions 0 January 20th 06 01:12 AM
Winn98SE, Excel2000: ODBC query opens the source workbook Arvi Laanemets Excel Discussion (Misc queries) 9 March 10th 05 07:32 AM


All times are GMT +1. The time now is 02:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"