Adding ROW function in VBA that can get data from Access

Skinny1434

New Member
Joined
Feb 15, 2012
Messages
24
Hello all, sorry if this topic has been discussed before, this is my first post on the forum. I am very novice with VBA, and currently I am stuck on a certain problem. I have a ton of data, that is currently stored in Access, it used to be stored in Excel but it made the workbook run very slow. I have a table in access that has many different values for the same customer name. i.e.

Customer A 2000
Customer A 5000
Customer A 1000

Before I switched the data over to Access, I used a INDEX>ROW function that gave me all the values related to Customer A. What I am wondering now, is if there is a way to do the same with an access table. I currently have a connection to the Access table, and I am using a DBVlookup, however, I need to be able to return more than one value. Any help would be great!!
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
Welcome to the Forum,

I would suggest that you set up a query in Access that will return the data you want and then add a DoCmd.TransferSpreadsheet which will then place the data into a spreadsheet for you.
 

Skinny1434

New Member
Joined
Feb 15, 2012
Messages
24
Thanks for the Reply Trevor, that would be the easy solution, unfortunatley, I have a template built into Excel, which has a series of drop downs, I want the user to be able to open up excel, click in the drop down, bring up a customer and then have all their accounts show up. I dont want to have to have a user go into Access and run a query. Therfore I was thinking of just having the VBA do all the work. Do you follow?
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
Yes I follow no problems,

The following example I use to select a department from a drop down in Excel it then goes to a database uses a query and brings back all the relevant data. So a standard query has to be made, but the users only use the excel workbook. In the screen below D3 is a validation list which they select from.

Sample of spreadsheet.

Excel Workbook
ABCDEFGHIJ
1Run Access Query into Excel
2
3Which DepartmentShipping
4Count
5
6TitleFirstnameLastnameJobTitleBirthDateGenderOfficeDepartmentEmailSalary
7MissCordeliaThreiplandShipping Assistant22 Oct 1954FNew YorkShippingcordelia.threipland@fontstuff.com25000
8MissMichelleHeumanStock Controller21 May 1956FIndianapolisShippingmichelle.heuman@fontstuff.com26000
9MsSarahHislopRegional Shipping Manager18 Oct 1970FLondonShippingsarah.hislop@fontstuff.com26000
10MrRichardBrierleyShipping Assistant07 Mar 1973MLondonShippingrichard.brierley@fontstuff.com25000
11MissKateButlandShipping Assistant25 Mar 1976FBrusselsShippingkate.butland@fontstuff.com25000
12MrsSamanthaHillShipping Assistant23 Mar 1968FCardiffShippingsamantha.hill@fontstuff.com25000
13MissRebeccaJacksonRegional Shipping Manager12 Dec 1971FManchesterShippingrebecca.jackson@fontstuff.com25000
14MrJamesGledhillRegional Shipping Manager27 Feb 1949MParisShippingjames.gledhill@fontstuff.com25000
15DrAnnetteRawlenceSecretary24 Sep 1953FBerkeleyShippingannette.rawlence@fontstuff.com26000
16MissNatashaHiromShipping Assistant13 Apr 1963FSeattleShippingnatasha.hirom@fontstuff.com26000
Sheet1
#VALUE!
Sample Code in the workbook

Sub RunParameterQuery()

'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase("M:\Access Files\Test ME Today.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("qryGoToExcel") 'Query name in the database

'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter Department]") = Range("D3").Value 'From parameter field in access

End With

'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 5: Clear previous contents
Sheets("Sheet1").Select
ActiveSheet.Range("A6:K10000").ClearContents

'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A7").CopyFromRecordset MyRecordset

'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i
Cells.EntireColumn.AutoFit

MsgBox "Query has been successful", vbInformation, "Sample"

End Sub
 

Skinny1434

New Member
Joined
Feb 15, 2012
Messages
24
Thanks Trevor, I am going to try this out, I will let you know how I make out.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
Pleased to read you have a solution you can work with,

Thanks for the feedback. ;)
 

Skinny1434

New Member
Joined
Feb 15, 2012
Messages
24
Question, is there a way to make copies of this for other tabs, and make it possible to select different customers for each tab? The way it is set up now doesnt allow for that to happen. Thanks.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
Set the data to use a Pivot Table, you can then use the Page Option to split the data down to individual sheets and they will have there own Pivot table.
 

Skinny1434

New Member
Joined
Feb 15, 2012
Messages
24
Not to sound like a noob, I know what a pivot table is but I have never used one, how would I go about doing that?
 

Forum statistics

Threads
1,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top