Excel VBA to get data from Access DataBase

[Sharpefiction]

Hi all,

I've been browsing this site and using suggestions/tips for ages and until now, I've always found the answer I needed..

I have a huge Database I want to get data from just by using VBA coding in Excel. I know how (or at least have some coding from a course I attended) to get the data from a table, however I need to specify that I only want data from X date to Y date. For example, get all data from 01 Aug 2010 to 31 Aug 2010.

Unfortunately it's this part I'm unsure of. I don't really deal with Access too much, so I'm a bit of newb when it comes to stuff like that. I'm sure it's not much extra coding I need, but I'm hoping someone out there can help me.. Please?

The coding I have to get all data is:

Option Explicit
Option Compare Text
Dim theSales As New Collection
Dim aSale As Sale

Sub GetData()

Const ConnString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\Northwind For Excel.mdb;Persist Security Info=False"

Const SQL As String = "SELECT * FROM qryOrdersForExcel"
' * means all info in field

Dim Con1 As ADODB.Connection
' sets a variable for connection
Dim RecordSet As ADODB.RecordSet
' sets which table/query in database will be referencing

Set Con1 = New ADODB.Connection
Con1.ConnectionString = ConnString
Con1.Open
Set RecordSet = New ADODB.RecordSet
Call RecordSet.Open(SQL, Con1)

With RecordSet
If .BOF And .EOF Then
MsgBox "There are no records"
Exit Sub
End If
' checks to see if the end of the record & beginning of the record are the same
.MoveFirst
Call ClearSalesCollection

Do While Not .EOF
Set aSale = New Sale
aSale.EmployeeName = .Fields("LastName") ' sets name of field in brackets
aSale.CustomerName = .Fields("CompanyName")
aSale.ProductName = .Fields("ProductName")
aSale.DateOfSale = .Fields("OrderDate")
aSale.PricePerUnit = .Fields("UnitPrice")
aSale.OrderQuantity = .Fields("Quantity")
aSale.OrderNumber = CStr(.Fields("OrderLine"))

Call theSales.Add(aSale, aSale.OrderNumber)
.MoveNext
Loop
End With

Con1.Close
' closes the connection to the database

End Sub

Thanks in advance

 

[Gary McMaster]

I believe you need a "Where" clause in your SQL statement

Something like this:

Const SQL As String = "SELECT * FROM qryOrdersForExcel WHERE (whatevertable.datefield>'1/1/2010' And whatevertable.datefield<'7/1/2010')"

I always find the connection string and SQL statement to be the hardest part.

What I usually try first is turn on the macro recorder and then create a query of the target database using the Excel "query wizard". The code that the macro recorder generates is usually very close to the same thing ADO wants. Sometimes it may not want parenthesis. Sometimes it wants double quotes around the dates instead of a single apostrophe ... things like that but it should give you a great head start.

Hope this helps.

Gary

 

[Sharpefiction]

great stuff, thanks Gary. I'll give it a try & let you know how I get on

Re: connection string. I was shown a very useful 'shortcut' I'll try & dig out.

The person who shared it with me said it makes life much easier - though I've not tried the other way to know the difference!

 

[Ruddles]

Something like this maybe:-

SELECT * FROM qryOrdersForExcel WHERE [[COLOR=red]datefield[/COLOR]] Between #1/8/2010# And #31/8/2010#;

(You need to insert your field name where I've coded datefield.)

 

[Gary McMaster]

You might be able to just use the "Query wizard" to get what you want with no code at all.

Excel and MS Query support "Parameter Queries" where you can tell MS Query to take (for example) Start date from A1 and End date from B1 and return all data between those dates to Excel. Of course you can also have it pop up a dialog box and key the dates if you wish. No code required.

If you search this forum for "Parameter Query" you should find some good stuff. Also Google "Excel Parameter Query".

Gary

 

[Gary McMaster]

Here's a link in case you want to consider a parameter query. You should be able to find many others.

http://www.dailydoseofexcel.com/archives/2004/12/13/

Gary

 

[Sharpefiction]

Thanks Ruddles. I'll try that way too..

Thanks again Gary. I'll look into that option as well

I've found the notes I have for 'easy' connection string. Steps as below:

1. Open notepad
2. Save it as anyname.udl on the desktop (though it could be to your local drive)
3. Close notepad
4. Open anyname.udl
This should open a small application window - Data Link Properties
5. Select the Database type 'Microsoft Jet 4.0 OLE DB Provider' on the Provider tab
6. On connection tab, navigate to the Database in section 1 & enter username/password in 2 (where applicable)
7. Click the Test Connection button on the Connection tab
8. When all is ok, click OK
9. Navigate to your anyname.udl file and right click, open with Notepad
10. In there you should have your full connection string needed. For my example above this was: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\Northwind For Excel.mdb;Persist Security Info=False

You may already have known this anyway. Just thought I'd share though as it might be useful

 

[Sharpefiction]

I've tried your suggestion Ruddles and it seems to work. I say seems as I'm having some issues with the database.

It runs fine until the last line:

Sub GetData()

Const ConnString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\Northwind For Excel.mdb;Persist Security Info=False"

Const SQL As String = "SELECT * FROM qryOrdersForExcel"
' * means all info in field

Dim Con1 As ADODB.Connection
' sets a variable for connection
Dim RecordSet As ADODB.RecordSet
' sets which table/query in database will be referencing

Set Con1 = New ADODB.Connection
Con1.ConnectionString = ConnString
Con1.Open
Set RecordSet = New ADODB.RecordSet
Call RecordSet.Open(SQL, Con1)

When it gets to the line I've hightlighted in red, it says that for one of the tables within the query (& I presume it will be for all of them) that I dont have permission to read the data.

When I go into the database itself I'm not asked for login details & can amend/update data as I wish. Any ideas why it is saying I dont have permission? I've tried adding in 'Admin' as the username & leaving the password blank, but to no avail. Any suggestions would be great

 

[Norie]

What data are you actually trying to get from the database?

You seem to be using some sort of collection to store the data and you are populating the collection from the recordset.

So why not just use the recordset itself, it's got all the data?

Or is the collection required elsewhere or doing something with the data.

 

[Sharpefiction]

The code I'm using is slightly different to the above - as in the query name & the database name, but everything else is the same.

The database I'm trying to access contains colleague information - name, employee number, activity names + time & quantity, date of record, area they work.

I'm trying to set up an application that goes into the database & can get all of the data for an area, between certain dates. I'll then use that for further calculations & information. At the moment we run a report from the database & then export it to excel & then use it. I'm sure Excel can cut the process down with some VBA coding & just go straight to the database & grab the information - it's that part I need help with.

The code I'm using is just what I've been shown. If there's an easier way, please do let me know & I'll try it. As mentioned above, I'm a bit a newb when it comes to databases & interacting with them from Excel.