Solved

Custom access query from inside exce?

Posted on 2011-09-16
9
316 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

628 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