Solved

Custom access query from inside exce?

Posted on 2011-09-16
9
311 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

830 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