If you can't find your data source, 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.
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, 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 or data source driver for your data source. 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.
My data source has an asterisk next to it.
The data source is from a version of Microsoft Query earlier than Query 97.
Data sources created in versions of Query earlier than Query 97 have a different format from data sources in later versions of the product. These data sources and queries can still be used with later versions of the product, but queries that are created by using these data sources cannot be shared with other users. Versions earlier than Query 97 store data source information as part of your Microsoft Windows operating system, and that information is available only on your system.
Identify data sources created with earlier versions of Query
- On the Data menu, point to Import External Data, and then click New Database Query.
- Click Options in the Choose Data Source dialog box, and then select the Include registry DSNs in list of available databases check box.
All data sources that were created by using a version earlier than Query 97 appear in the Choose Data Source dialog box with asterisks next to their names.
Delete old data sources After you identify data sources created with earlier versions of Query, on the Databases tab in the Choose Data Source dialog box, click the data source you want to remove, and then click Delete.
Create shared data sources If you want to share queries or report templates that use data from the external databases that are specified in your non-shareable data sources, create new data sources for these databases. Use the new data sources to create the queries, query files, and report templates.
Importing data
A message indicates that the path to my database is not valid.
Check for a mapped network drive If your database is on a shared network directory, when you set up the data source and selected the database file, the path to the database may have been recorded in the data source with the mapped drive letter in use by your system at that time. For example, if your database is named Inventory.mdb, and you had drive G mapped to the shared network directory where this database is stored, your data source might record this location as G:\public\Inventory.mdb. When you try to use this data source, or you or other users try to run queries created with this data source, the ODBC driver displays a message that the path is not valid if drive G is not mapped to the same shared network directory.
Use an alternative to drive mapping If you are using the Microsoft Access driver or the Microsoft Excel driver, you can correct this problem by creating a new data source. When you specify the location of the database file, don't select the mapped drive for the shared network directory. Instead, type the UNC address of the shared network directory, and then locate the database file. For example, if your database file is stored on a server named Shared, you could type \\Shared\public and then select the file Inventory.mdb.
Map the same network drive letter before using the data source For other ODBC drivers, before you use a data source or run a query created with that data source, make sure the same drive is mapped to the shared network directory where the database is located as was mapped when the data source was created.
Sorting and formatting are incorrect after I refresh an external data range.
Formatting changes in Query won't affect Excel Formatting that you apply in Query affects the view of the result set only in Query. When you return the result set to Microsoft Excel, formatting changes you made while in Query
Preserve Excel formatting when you refresh Each time you refresh an external data range Excel replaces the existing data with new data and also removes any Excel outlining and subtotals. You can preserve formatting, but not row sorting or outlining, for an external data range by clicking Data Range Properties
on the External Data
toolbar and making sure that the Preserve cell formatting check box is selected under Data formatting and layout.
To preserve sorting, copy the data Each time you refresh an external data range, Excel automatically removes any sorting you applied. If you want to sort or format data from an external data range and keep all sorting and formatting, copy the data, and then use the Paste Special command and select the Values option to paste the data onto another sheet in the workbook. Then format the data the way you want. The sorting and formatting will be preserved; however, you won't be able to refresh the data because the underlying query associated with the external data range was not copied.
Record a macro to restore sorting and formatting If you want to be able to refresh an external data range and keep your sorting and formatting, try recording a macro for formatting the data in your external data range and then running the macro after you refresh the data. On the Tools menu, point to Macro, and then click Record New Macro. Specify the options you want, and click OK. Format the external data range the way you want, and then click the Stop Macro button on the Stop Recording toolbar. Run the macro after you refresh the data.
-
Set the security level to Medium or Low.
-
On the Tools menu, click Options.
-
Click the Security tab.
-
Under Macro Security, click Macro Security.
-
Click the Security Level tab, and then select the security level you want to use.
-
-
Open the workbook that contains the macro.
-
On the Tools menu, point to Macro, and then click Macros.
-
In the Macro name box, enter the name of the macro you want to run.
-
Do one of the following:
Run a macro in a Microsoft Excel workbook
-
Click Run.
If you want to interrupt, press ESC.
Run a macro from a Microsoft Visual Basic module
-
Click Edit.
-
Click Run Sub/UserForm
.
If you want to run a different macro while you are in the Visual Basic Editor, click Macros on the Tools menu. In the Macro name box, enter the name of the macro you want to run, and then click Run.
-
A range of blank cells is selected when I return data to Microsoft Excel.
Check your ODBC driver You may not be using a compatible ODBC driver. If you're using an ODBC driver from an earlier version of Microsoft Excel or Query, you must install the most recent version of the driver to import data.
Check your system's free memory Your computer might not have enough memory available to import the data. To check available memory in Windows 2000, switch to the Windows desktop. Right-click the My Computer icon, click Properties, and then click the Advanced tab. Click Performance Options, and then click Change to see the percentage of available memory. To free some memory, try closing unnecessary documents and applications.
Check whether Excel is ignoring other programs The Ignore other applications check box may be selected in Excel. This option may prevent other programs, including Microsoft Query, from establishing a dynamic data exchange (DDE) connection to Excel. On the Tools menu, click Options, and then click the General tab. Under Settings, make sure that the Ignore other applications check box is cleared. Then run the query again.
I run out of disk space when I try to import data.
Determine how much space is needed When you create a query, the query is temporarily placed on your hard disk. As a general rule, you should have a minimum of 3 to 5 MB of available disk space to create the temporary query file. If your query is large, you will need more free disk space. If enough disk space is not available, the query will take longer to retrieve data or the query may quit running.
Check for available disk space To check available hard disk space in Windows 2000, switch to the Windows desktop, double-click the My Computer icon, and then click the disk you want to check. On the File menu, click Properties. To free some space on your hard disk, try emptying the Recycle Bin, backing up unneeded files and then removing them from your hard disk, or removing Windows components that you don't use. For more information about freeing hard disk space, see Microsoft Windows Help.
Strategies you can try when disk space is limited If you have only a limited amount of space available on your hard disk, try the following:
- Simplify your query Make sure you include only those tables and fields that are needed for your query. If your query contains unnecessary tables or fields, delete them from the query to reduce the size of the temporary query file.
- Use criteria to reduce the size of the result set Use criteria to retrieve only specific records rather than retrieving all the records from a database. For more information, see Microsoft Query Help.
- Set a limit on the number of records returned by the query Limit the number of records the query returns. In Query, click Options on the Edit menu, select the Limit number of records returned to check box under User settings, and enter the maximum number of records to return in the Records box.
A macro I use to import data doesn't work.
Make sure Query is installed If you're using a Microsoft Visual Basic macro, make sure ActiveX Data Objects for Visual Basic are installed when you install Query.
Check your data source and driver Make sure that you have the correct ODBC driver or data source driver for the data source that is used by the macro.
Install and load the Microsoft ODBC Function add-in program On the Tools menu, click Add-Ins, and then select the ODBC Add-in check box. If ODBC Add-in does not appear in the box, the add-in was made unavailable when you installed Microsoft Excel or Microsoft Office, and you need to install the ODBC add-in program.
The columns are in the wrong order after I change my query.
You can preserve either Excel or Query column order To keep the columns where you moved them in Microsoft Excel after you refresh or change a query, click a cell in the external data range, and then click Data Range Properties
on the External Data
toolbar. Under Data formatting and layout, select the Preserve column sort/filter/layout check box. To change the column order in Query and have the changes reflected in the external data range, clear the check box.
Effects of changing column names in Query If you select the Preserve column sort/filter/layout check box and then change a column name in Query, when you return the data to Excel, the renamed column becomes the rightmost column in the external data range. You can prevent this from happening by clearing the check box before you change the query, or you can move the column to the position you want in Excel.