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

 

[Norie]

Well you can transfer all the data from the recordset with one line of code.

Worksheets("Data").Range("A1").CopyFromRecordSet Recordset

Exactly what data you will get depends on what criteria, eg dates, area..., you use in the query.

You could also do other things with the query, really depends what you want.

By the way you mentioned you 'run' a report, what exactly does that involve?

If you have a report set up in Access that displays all the data you want to import to Excel then you could simply import/export the report's record source.

 

[Sharpefiction]

When you say the 'recordset' do you mean the query within the database? For example:
Query name = west area. & within west area we refer to table 1, table 2, table 3 & so on?

If so then this would work as there are queries already set up. I just need to be able to refer to it in my excel coding. Using the coding above only gets me so far before it comes up with an error relating to permissions of the data I'm trying to read.

'Running a report' = I have a working query in the database called E&S All Data (amongst others) which I manually go into & change the to/from dates depending on what we're after, via the 'design mode'. I then select view & it generates a report listing all colleagues, their employee number, activites, time, quantity & codes relevant for between the two dates specified. I then use office links, Analyze with excel to transfer the data to excel where I then use it for further reports/manipulation.

 

[Sharpefiction]

.

 

[SydneyGeek]

SQL interprets dates as US format, regardless of regional settings. I typically convert dates to Long Integer before feeding them to SQL; then there is no ambiguity.
Something like --

sSQL = "SELECT * FROM qryMyQuery WHERE SomeDate BETWEEN " & CLng([StartDate]) & " AND " & CLng([EndDate])

Denis

 

[Norie]

Denis

I'm not sure that's quite right.

I think I've only really had problems when using hardcoded dates with #s around them.

Sometimes if you use / as a delimiter the dates do seem to get changed, so I just write out the month, eg #21 Oct 2010#

 

[SydneyGeek]

It happens to me often enough that I convert to the serial number by default. I guess it particularly bit me when I was working with a US client but I have talked to a number of Australian developers who encounter the same problems.
So... it's become my standard approach because it works for me.

Denis