Solved

Custom access query from inside exce?

Posted on 2011-09-16
9
315 Views
Last Modified: 2012-05-12
Hello EE!

I'm trying to do a macro, where on click a query is sendt to a access database to retrieve certain fields of data. For example:

A1: RAWDATA
B1: Database

A2: Names
A3: Age


Running the macro would then automaticly run the query:
SELECT RAWDATA.Names AS PersonNames, RAWDATA.Age as PersonAge                                                                                                                                                      
FROM RAWDATA;

The reason for this is that the database and the fields change names constantly. And it would be a great help if you could simply type in the name of the database, the table and the two column names and the data was automaticly imported

Is having custom queries in excel even possible?

I hope some of you wizards out there have a suggestion! Thanks in advance!
-Thomas
0
Comment
Question by:ThomasFoege
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36548073
For example:

Sub GetAccessData()
   ' Sample demonstrating how to return a recordset from an Access db
   ' requires a reference to the Microsoft ActiveX Data Objects Library.
   
   Dim cnn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset
   Dim strPathToDB As String, strFormula As String, i As Long
   Dim wks As Worksheet
   Dim lngNextNum As Long, lngRow As Long, lngCol As Long
   Dim varData
   Dim strTable as String, strField1 as string, strField2 as string
   
   ' output to activesheet
   Set wks = ActiveSheet
   
   ' Path to database
   strPathToDB = wks.Range("B1").Value
   strTable = wks.Range("A1").Value
   strField1 = wks.Range("A2").Value
   strField2 = wks.Range("A3").Value
   Set cnn = New ADODB.Connection
   ' open connection to database
   With cnn
      .CursorLocation = adUseServer
      .ConnectionTimeout = 500
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
      .CommandTimeout = 500
   End With
   ' SQL query string - change to suit
   strQuery = "SELECT [" & strtable & "].[" & strfield1 & "] AS PersonNames, [" & strtable & "].[" & strfield2 & "] as PersonAge FROM "[" & strTable & "];"
   ' create new recordset
   Set rst = New ADODB.Recordset

   ' open recordset using query string and connection
   With rst
      .CursorLocation = adUseClient
      .Open strQuery, cnn, adOpenStatic, adLockPessimistic, adCmdText
      rst.Filter = "id = 1"
      ' check for records returned
      If Not .EOF Then
         'Populate field names
         For i = 1 To .Fields.Count
            wks.Cells(5, i) = .Fields(i - 1).Name
         Next i
         ' Copy data to A6 onwards
         wks.Cells(6, "A").CopyFromRecordset rst
      End If
      .Close
   End With

   ' clean up
   Set rst = Nothing
   cnn.Close
   Set cnn = Nothing
End Sub

Open in new window

0
 

Author Comment

by:ThomasFoege
ID: 36548102
Hey! Thanks for the reply

I had to change
strQuery = "SELECT [" & strTable & "].[" & strField1 & "] AS PersonNames, [" & strTable & "].[" & strField2 & "] as PersonAge FROM [" & strTable & "];"

Open in new window


My

A1 is now RAWDATA
B1 is Database.accdb
A2 is Names
A3 is Ages

When i try running that script, i get a System Error &H80004005

Did i do something wrong?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36548116
For 2007 or later you need to change the provider to:
"Microsoft.ACE.OLEDB.12.0"
instead of Jet, and you need to include a database path.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ThomasFoege
ID: 36548153
All right!

So i changed

.Provider = "Microsoft.ACE.OLEDB.12.0"

Open in new window


And i changed B1 to "C:\Users\Thomas\Desktop\Excel\Database.accdb"

When i run the macro, i get an error box without any description


Did i miss something?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36548196
If you step through it (using f8), which line causes the error?
0
 

Author Comment

by:ThomasFoege
ID: 36548203
Smart! I dindt know you could do that

I get to where line
      rst.Filter = "id = 1"

Open in new window

is highlighted. I press F8 again and it fails
Dont know if thats this line then      
If Not .EOF Then

Open in new window


It gives Runetime error 3265 Application-defined or object-defined error
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 36548209
Oh - delete that line. It's left over from the original version.
0
 

Author Comment

by:ThomasFoege
ID: 36548222
It works! Brilliant!

Thanks again for amazing help!
0
 

Author Closing Comment

by:ThomasFoege
ID: 36548228
Genius soloution as always!
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question