Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change the ODBC data source of a pivot table | Excel Discussion (Misc queries) | |||
changing query source | Excel Worksheet Functions | |||
Changing Pivot Table Source from ODBC to .odc. | Excel Discussion (Misc queries) | |||
change odbc data source | Excel Worksheet Functions | |||
Winn98SE, Excel2000: ODBC query opens the source workbook | Excel Discussion (Misc queries) |