Pull query results from Access to Excel

First off, thanks to everyone that's helped me the past week. I've gone from knowing nothing about VBA and thumbing my eyes all day to being able to work consistently on long projects and observe improving results.

However, I'm at a stand-still again, as my current task calls for a way to communicate between Access and Excel. I know where the data I want in my Excel workbook is, and there's a query in place to pull it into a table. What I'm trying to do is write a macro that will open that particular table in Access, run the query, select the table, open a particular worksheet in my workbook, and then paste the data in.

This isn't hard to do by hand, besides finding the right database among a mess of others. But I want someone who isn't me to be able to get ahold of the same data by running the macro. It'll make less work for me in the long run.

Anything would be helpful.

EDIT: Found this on another website...

Please Login or Register  to view this content.

Changing LPath, I can open the right database, but there's always a security warning that throws off the automation. Is there a way around that? I figure this isn't a bad start, I just need a way to, from Excel, run the appropriate query and copy/paste the result.

EDIT 2: I'm noticing things in the help file about a Xlodbc.xla add-in, but it says not to use it. However, some of the methods in that add-in look like they're what I want to use, like the SQLOpen, SQLExecQuery, and SQLRetrieve functions. What's the reasoning behind the help file saying not to use them (use MS ActiveX Data Objects, ADOs, it says)? Would it be a bad idea to look into this Xlodbc.xla add-in and see if I can use it?

yellephant,

This is probably not the ultimate solution to your problem, but I would suggest using the macro recorder as a first step. It will probably have a bunch of stuff that is not needed, but it may give you a place to start by defining a few things for you. I use this a great deal as a starting point to get a few data elements I am needing and then run with it from there.

I just tried this with an Access database from which I pull data and it definitely gave me one big harry line of information. I can see things within that line that would be needed within a subroutine.

If the the database is password protected (the one I am using is), the subroutine does stop and ask for the password, but then continues on.

Again, I realize this is not the answer to your question, but it may give you a place to start.

Assuming you've added a reference to the Microsoft DAO Object Library, this code will open the query called "My_Query" in your database and copy the contents into the active worksheet

Please Login or Register  to view this content.

Col

Colofnature: I see how your code plays out, and I think the biggest thing that was holding me back was not knowing what commands were available (things like CopyFromRecordset). However, when I run your code, it errors out because of the Database data type. Does this have to do with the reference to the DAO library you mentioned? If so, how should I go about creating that reference?

I know that the Access helpfile includes examples using that datatype, but the VBA editor doesn't seem to like it at all.

EDIT: I found this command through the helpfile, and added it to my previous code.

Please Login or Register  to view this content.

This opens the database and runs the query I want. I just don't know how to copy it back.

If the error is type not defined then it's definitely the reference. To add it, in the VB editor, go to the Tools menu and select References, then in the listbox scroll down to Microsoft DAO x.x Object Library, where x.x is the version number - there might be several, just pick the highest. Check the box next to it, hit OK and you should be sorted.


Col

While it's probably possible to export the data using your method, off the top of my head I don't know how. Besides, the DAO method is more elegant: simpler, runs quicker and doesn't involve opening another app...

Hmm. Can't replicate that error, so I'm at a loss as to how to get round it. If you want you could email me the spreadsheet and database, or a link to them, to <my excel forum user name at gmail dot com>, but due to the firewall around the office network I won't be able to look at it until I get home this evening - maybe another 2 or 3 hours. I'd be happy to see what I can do if you can wait.

If not, you could go back to your original method and look at the possibilities of "oApp.DoCmd.TransferSpreadsheet". I've never used it myself, but it should allow you to save the results of your query to an Excel file which you can then open in your macro.


Col

yellephant,

I apologize! I apparently did not understand exactly what you are doing. My interaction with an Access database is done through Data > Import External Data > New Database Query. That is how I was able to use the recorder because all of it was being done through Excel.

I am going to bow out of this discussion because there are obviously folks that interact between Access and Excel more than I do and can be a lot more help.

Apparently not: http://www.access-programmers.co.uk/...1&postcount=10

Looks like the logic in the Access module will need to be built into your Excel VBA code somehow. What does the Access function do?

Yep, either a) create a new query (probably the easiest way if you can alter the database); or b) define your query using SQL. Then do whatever manipulation to the results your Access function would have done. For example

Please Login or Register  to view this content.

The two methods of accessing the contents of a field within a record in a recordset called rst are:

rst!Field_Name - using the bang as a separator and using the name of the field as it appears in the database
rst.fields(index).value - using the index (0 to fields.count - 1, or the field name as above)

Of course you'll need to fiddle around with the bit within the Do...Loop to get it to do whatever you need it to but this ought to get you started.

Col