Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Custom access query from inside exce?

Posted on 2011-09-16
9
Medium Priority
?
319 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

718 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