Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 29th 17, 12:04 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2017
Posts: 3
Default VBA - Importing Access Query into Excel using ADO

I am trying to import an Access query into Excel using VBA, but with a parameter. For example, the Access query I am trying to import asks the user for a valuation year, such as 2017.

I have already found some code, using Microsoft ActiveX Data Objects (ADODB). My understanding is that this is the technique that MS is currently supporting for doing imports, even though are other ways, such as DAO (Data Access Objects)

The ADO code below works to import queries that have no parameters. It uses 6 variables that have been set up in the rest of the code, but this subroutine does most of the actual importing work. I would like to add a prompt and a parameter field on the sheet in Excel where I am importing the parameter query.

Regarding the code below where there are no parameters, I believe I need to add something to the query string to use the prompt and parameter, and some additional code to use the parameter.

Has anyone done this before, and perhaps can point me in the right direction?

VBA subroutine to import Access query with No Parameters:
Private Sub PullQueryDataNoParam(SrcPathfile, SrcQry, TgtTab, Col2Start, TgtCol, Row2Start)
'Purpose: Pull query results from Access into Excel, using ActiveX Data Objects interface
Dim strDb, strQry As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim i As Integer
Dim Cell4Title, Cell2Start As String

Cell4Title = Col2Start + CStr(Row2Start) 'the first row pasted is the column headings
Cell2Start = Col2Start & CStr(Row2Start + 1) 'the query results are pasted in the next row

Sheets(TgtTab).Select 'select tab in Excel WB for results to be imported
strDb = SrcPathfile 'path and filename of Access DB
strQry = "SELECT * FROM " & SrcQry 'code for select query

Set cn = New ADODB.Connection 'create a new ADO connection to Access DB
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDb & ";"

Set rs = New ADODB.Recordset

'open the query in Access
With rs
Set .ActiveConnection = cn
.Open strQry
End With

'copy field names in Access as a title row in Excel (bold font), then copy the query results in the next row
With Sheets(TgtTab)
For i = TgtCol To rs.Fields.Count + TgtCol - 1
.Cells(Row2Start, i).Value = rs.Fields(i - TgtCol).Name 'fields is a 0 based collection
Next i
.Range(Cell4Title).Resize(ColumnSize:=rs.Fields.Co unt).Font.Bold = True 'title row is bolded and resized to query
.Range(Cell2Start).CopyFromRecordset rs
End With

rs.Close 'close ADODB.RecordSet
cn.Close 'close ADODB.Connection

End Sub 'PullQueryData(a,b,c,d,e,f)

  #2   Report Post  
Old November 29th 17, 09:59 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default VBA - Importing Access Query into Excel using ADO

You need to add a WHERE clause to your query so it pulls only the desired
records for the specified valuation year. Rather than write this for you, I
think it better that you review this database programming primer first so you
have a better idea of how ADODB works as well as how to work with it.

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Old December 5th 17, 12:33 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2017
Posts: 3
Default VBA - Importing Access Query into Excel using ADO

On Wednesday, November 29, 2017 at 2:59:10 AM UTC-6, GS wrote:
You need to add a WHERE clause to your query so it pulls only the desired
records for the specified valuation year. Rather than write this for you, I
think it better that you review this database programming primer first so you
have a better idea of how ADODB works as well as how to work with it.

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Thanks for the zip file, Garry. Looks like I have a lot of research to do before I dive in!
  #4   Report Post  
Old December 5th 17, 09:44 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default VBA - Importing Access Query into Excel using ADO

On Wednesday, November 29, 2017 at 2:59:10 AM UTC-6, GS wrote:
You need to add a WHERE clause to your query so it pulls only the desired
records for the specified valuation year. Rather than write this for you, I
think it better that you review this database programming primer first so
you have a better idea of how ADODB works as well as how to work with it.

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Thanks for the zip file, Garry. Looks like I have a lot of research to do
before I dive in!


Best wishes...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #5   Report Post  
Old December 5th 17, 10:36 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2017
Posts: 3
Default VBA - Importing Access Query into Excel using ADO

On Tuesday, December 5, 2017 at 2:44:58 AM UTC-6, GS wrote:
On Wednesday, November 29, 2017 at 2:59:10 AM UTC-6, GS wrote:
You need to add a WHERE clause to your query so it pulls only the desired
records for the specified valuation year. Rather than write this for you, I
think it better that you review this database programming primer first so
you have a better idea of how ADODB works as well as how to work with it.

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Thanks for the zip file, Garry. Looks like I have a lot of research to do
before I dive in!


Best wishes...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Now that I have all this helpful information, I went back to Excel and Access to build the macro. The first thing I found out was that I can't find where the parameter is input in Access. The Access database is several years old, and the person who built it is no longer here.

I looked at the query I was trying to import into Excel, and could find no "Current Year" field, which is the parameter prompt. When I run the query in Access, the first thing that happens is a pop-up message box that shows this:

Enter Parameter Value ?
Current Year

I then enter 2017 and the query runs ok. However, I get exactly the same query results if I put in 2014 for the current year, which leads me to believe that the parameter has no effect, and it may be some kind of bug in Access.

I looked at the query in design view in Access, and I could find no field with the [Current Year] prompt. I also looked in the queries that feed this query, and could find no [Current Year] prompt.

Does anybody have any suggestions about where to go from here?


  #6   Report Post  
Old December 5th 17, 10:59 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default VBA - Importing Access Query into Excel using ADO

Fieldnames are not necessarily 'column' names! The fieldname might very well be
"CurrentYear" or simply just "Year" in your WHERE clause. Hard to say without
examining how the data table is constructed in the mdb file. Open it in Access
and study its structure to see how the fields are defined...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


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
Importing Data from Access Query to Excel Sheet Memphis Excel Programming 4 March 18th 09 07:24 PM
Importing Access Query in Excel 2007 Tamara Excel Programming 0 April 14th 08 07:00 PM
Importing access query dany Excel Discussion (Misc queries) 3 March 23rd 07 12:55 PM
Importing Access Query help Excel Discussion (Misc queries) 3 August 24th 06 06:16 PM
Importing Complicated Access Query into Excel Mike Roberto Excel Programming 3 August 30th 04 04:56 PM


All times are GMT +1. The time now is 09:11 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017