I've gone through the help files and read various forums for similar applications, so I think this is possible.....but can't seem to get this to work.
I have an access db query in Office '03 that is constantly being populated with new data.
What I want to do:
From time to time I want to search this query and read the data that was added since the last time I searched the query. Then, copy the new data and put it in excel to analyze. I don't want to copy the entire query every time I read (have seen a couple examples for this), just the new entries.
I've been able to open access file and query I want to search with vba, but am stumped after that. Not sure if I need to use DAO or ADO af this point or not. Here's the code so far (it's come from a variety of sources):
Dim LPath As String
Dim LCategoryID As Long
'Path to Access database
LPath = "C:\Documents and Settings\bjones\Desktop\mdb\SmartTrac.mdb"
'Open Access and make visible
Set oapp = CreateObject("Access.Application")
oapp.Visible = True
'Open Access database as defined by LPath variable
'Open query called smartview_qry filtering by CategoryID
I'm continuing to work on this, but any help is appreciated. Particularly if what I'm trying to do is even possible. Thanks in advance.
Yes, it's doable -- but you will need to use ADO or DAO (and there is no particular need to open Access first).
This tutorial gives you most of what you need. You will need to change:
1. The SQL -- to get just the changed records. (eg, records with an ID >= the highest ID in Excel)
2. The CopyFromRecordset statement -- adjust the starting cell from A2 to the first available cell in column A.
3. Don't clear out everything from the worksheet first.
Thank you. Your tutorial was the key!