[R] exporting tables from an access database using parallel foreach

John McKown john.archie.mckown at gmail.com
Sun Nov 22 02:38:06 CET 2015


On Sat, Nov 21, 2015 at 11:55 AM, Vivek Sutradhara <viveksutra at gmail.com>
wrote:

> Hi John and Jeff,
> Thanks a lot for your help. I agree that row numbers are not a standard
> feature in SQL. What I am looking for is some kind of a hack. After all,
> the sqlFetch command is able to return a specific number of rows. And the
> sqlFetchMore command is able to take up the baton from that row onwards to
> futher return rows corresponding to the max parameter.
>
> I wonder if it is possible to straight away hop to a certain row number
> (without going through sqlfetch and sqlFetchMore and without loading any
> data into memory) and then return the contents corresponding to a certain
> number of rows. The question is : is there a "catch" for accessing a row
> location, and what could be the "hook" for that? I am interested in the the
> recent updated rows to a table after a certain date. Is it possible to
> identify them in a quick way? Running sql queries on such large tables
> appears to take too long a time.
>
>  I understand that there is no provision to do this by available methods.
> But, is it possible to get under the hood and find some hack?
>

​Now you're talking about the internals of Microsoft Access. And you're
_way_ beyond my knowledge. Is there such knowledge? I sure there is. But,
unfortunately, once you get into that depth, you can get into real trouble
when (not if) MS decides to change the internals out from under you without
any warning at all. If you are really needing this, try looking the the
"MDB Tools" software at either https://github.com/brianb/mdbtools or
http://mdbtools.sourceforge.net/​ I don't think this does exactly what you
want, but it may give you the information you need to read the MDB file
yourself directly in R code. <shudder/>

​What you would really want is something like the ROWID in SQLite. That is
a "system" maintained column in every table in SQLite. It is a 64-bit
unique number. Basically, it starts at 1 and increments every time you add
a new row.

What would be "best", IMO, would be if you could alter your Access database
to have a "serial" column which would be your "row number" You could then
get "directly" there by using a SELECT similar to:

SELECT * FROM table WHERE serial BETWEEN (first-row,last-row)



>
> Jeff, I will take your suggestion and try my luck at the R-sig-db mailing
> list.
> Thanks,
> Vivek
>
>
>
-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

	[[alternative HTML version deleted]]



More information about the R-help mailing list