Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Data from Access Query to Excel Sheet | Excel Programming | |||
Importing Access Query in Excel 2007 | Excel Programming | |||
Importing access query | Excel Discussion (Misc queries) | |||
Importing Access Query | Excel Discussion (Misc queries) | |||
Importing Complicated Access Query into Excel | Excel Programming |