Solved

Custom access query from inside exce?

Posted on 2011-09-16
9
304 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access MDB/PDF 21 32
How to calcualate lateness in Access 2010 11 29
Name Rotation 11 30
Insert rows based on cell value vba 3 12
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now