MS Query and Access

Topic

New Reply

We use MS Query to get information from our systems. We then download it to Excel2000. The problem is that there are too many records for Excel (more then 64k). How can I run MS Query from Access and have the output go directly to Access?

Thanks

Reply | Quote

Replies

This depends on how you’re currently receiving the information from your system. Access is very powerful with its importing and linking features. It will handle everything from TXT files to dBASE and beyond. You may want to find out more about the format of the data coming from your system currently. Then check into the import features of Access – you may be able to bypass using MS Query.

Also, if you’re getting your data from an ODBC data source, you can create a linked table in Access. Then you can run a Make-Table query to actually store the data in your Access database.

HTH

Reply | Quote

Without more specific information (what houses your data? AS/400, SQL Server, Oracle, several hyper-intelligent monkeys), it’s hard to say much intelligently, so here’s the unintelligent bits.

MS Access uses the Jet data engine rather than MS Query to pull data from a source. The trick will be to make Access aware of the data source (probably by linking to it using the wizards in Access), then running a query against the data source. Post back more specifics, and I’m sure someone can help.

Regards!

Reply | Quote

We use Oracle8 ODBC Driver Connect to get the info from MS Query. How would I do this with Access?

Reply | Quote

Since you were already importing the data into Excel via ODBC, I assume you have the ODBC source information already configured on the PC you’re using. In which case, it should be a breeze to import or link to Access.

Open the database you want to import or link the data to (or create a new blank database). File | Get External Data | Import… (or Link Tables…).
Choose ODBC Databases as your file type. The “Select Data Source” dialog will open, giving you the opportunity to select your Data source from the list.

If you decide to link to the data source, you’ll probably want to create a Make-Table query from the linked table in order to have the data stored in Access. Otherwise, you can just import the data directly from the already established ODBC source.

Good luck!!

Reply | Quote

I was able to import the table but I want to just run a query off of this table. I do not want the entire table.

Reply | Quote

Because you have Excel2000, I’m going to assume you have the same version of Access, and because you were able to import the Oracle table, I’m going to assume that you’ve got the ODBC drivers figured out.

So, in the tables collection of your Access database, click on the ‘New’ button, choose to ‘Link’ a table. From the ‘Files of Type:’ drop-box, choose ‘ODBC Databases ()’, and find your Oracle table that you were able to import. Now that you have the table linked, you should be able to run queries against it as though it were a local table physically located in your Access db. The great part about linking a table vs. importing, is that each time you run a query on the linked table, you get the most up-to-data data out of the table.

Reply | Quote

I agree with Shane’s post – regarding using a Linked table vs. importing the table. The linked table will ultimately be more efficient, especially since you intend to filter out some of the data with a query. Once you’ve linked the table it will function exactly as a normal table, except that the data will always be accurate and up-to-date due to the live connection with your source database. If you stay with the imported table you’ll have to import the data each time you want to run your query.

As for making the query:
Once the table is linked (or imported) simply switch to the Query part of the Access Database Window and create a new query. You may want to start with the Query Wizard depending on the complexity of the query you need and your comfortability level with designing queries.

Post back if you need further help with the query.

Reply | Quote