Solved

Script to poll database 3/3

Posted on 2014-02-08
9
342 Views
Last Modified: 2014-02-11
This is part 3 of a 3-part question; the others are located at
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28360144.html
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28360145.html

In the attached database, which is data retrieved from an ODBC-connected database to which I have read-only access, I can import the data shown in tblOrig. There are hundreds of IDNumbers; this is filtered data for one of them. Essentially, the table from which I am drawing this information lists every IDNumber, and revisions to it are shown in the Revision field, so the unique identifier of each record in the table is IDNumber & Revision.

I don't have to look at this data every single day, but what I would like to do is build something that goes and looks at the ODBC database and pulls the revisions done since the last time I added them. If it's Thursday, that probably means yesterday, but if it's Tuesday, it might have been yesterday, but it could have also been last Friday. I only need the new ones added to my current table.

Part 1 of this question has to do with automatically updating records that I import from the ODBC database. Part 2 of this quesiton has to do with updating thousands of existing records.

My Access coding skills are... rusty... so any assistance would be greatly appreciated.

Thanks,

ep
efptest01.mdb
0
Comment
Question by:ericpete
  • 4
  • 3
  • 2
9 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39845080
You might not need this at all.
See my suggestion in 2/3.

/gustav
0
 
LVL 15

Author Comment

by:ericpete
ID: 39845583
Gustav,

I don't think I was clear.

I pull the data into my Access database through ODBC (essentially, I've built a front end for it), but I can't edit the data in the original database.

The EditDate field is in the ODBC database. What I do now is pull all the data down and manipulate it for my purposes (functionality that doesn't exist in the ODBC database). What I want to do is pull down ONLY those new records (revisions) since the last time I pulled the data down.

If that's not possible, I can always do a CreateTable query that pulls all the data every time I need it, but that seems like overkill.

Does that make sense?

ep
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39845617
Is the edit date always going to be greater than the prior time you imported the data? As in the creators/editors of the data don't go back and edit the prior stuff? Or put in yesterdays date?
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 485 total points
ID: 39845640
My thought is that you don't need to neither import, append, nor update anything, just pull the records with the query that generates the EndDate dynamically.

Of course, you can have table to store the date for the last view, then use that to filter on the EditDate so you will not pull the full table. When done, save the current data as the last viewed date.

/gustav
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 15

Author Comment

by:ericpete
ID: 39845716
Jim,

Good question; I've asked, but haven't heard back. For the time being, let's assume that since it's a proprietary system and that it's locked down from people tinkering with the data that the Start (nee Edit) Date is the actual date of the revision.

Gustav,

So you're suggesting simply introducing a variable into the query that pulls the data down, and not bothering with automating it? That's certainly doable.

ep
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39845843
If you had to compare for revised dates then you get into a RBAR (Row-By-Agonizing Row)  situation.

But it becomes simple if the last start date in the tblOrig is the qualifier for looking for new stuff.
The query
INSERT INTO tblOrig (IDNumber, Revision, LocationString, EditDate)
SELECT IDNumber, Revision, LocationString, EditDate
FROM odbcTbl
WHERE odbcTbl.EditDate > (SELECT Max(tblOrig.EditDate) FROM tblOrig);

Open in new window


The only risk in this is if you run it in the middle of the day and stuff is added later the same day you may miss it.  One that I do is have function tied to an "autoexec" from that opens on DB startup. If the time is between say 7A and 8A the form runs the functions/queries assigned in the VBA code. If it is outside that time then the form just shuts down and no one notices it. Then I have a scheduled task to open the DB at 7:30 A every day and it runs the stuff for me.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39845912
> So you're suggesting simply introducing a variable into the query that
> pulls the data down, and not bothering with automating it?

Yes.

/gustav
0
 
LVL 15

Author Comment

by:ericpete
ID: 39850978
Finally got a response; the revisions (and the revision date) are posted immediately, so when the ODBC database gets polled, any activity is logged.

So it's just a matter of creating a query that searches the data for any records created after the last time. That should be easy enough.

Thanks for your help!

ep
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39851575
You are welcome!

/gustav
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

867 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

21 Experts available now in Live!

Get 1:1 Help Now