[R] porting an access database to sqlite

[ravi]

Hi,All my data is presently locked in a Microsoft access database. This has huge data in a number of large tables. Using RODBC and connecting to it takes too long a time, sometimes making the system to hang up. 

To make things more manageable, I have tried to transfer the data to manageable .RData or .csv files. But I am not able to do this with some of the larger files. I am currently stuck in the one of the preliminary steps. I am not able to find the number of rows in a table. If I know this, I can transfer chunks of the tables to a sqlite database.
I am able to connect to connect to the access data base with :
library(RODBC)
con<-odbcConnect("TestDB")d1<-sqlFetch(ch,"table1",max=1e5,as.is=TRUE)d2<-sqlFetchMore(ch,max=1e5,as.is=TRUE)
d3<-rbind(d1,d2)I wanted to develop this into a loop to get a concatenated data frame, which I wanted to save either as a binary file, or transfer it to a sqlite data base. I would like to have some help on the simplest route to follow. But first, I will proceed in describing my immediate problem. In some of the tables, I find that the sqlFetchMore returns a value of -1L, meaning that the end has been reached. In RODBC, I find no command for getting the row count in a table. I have found this in DBI. But I have not been able to figure out how I should specify the connection to an acccess database (con in the following).

while(!dbHasCompleted(con)){   print(dbGetRowCount(con))}
I would appreciate help on the following points :1. How can I get the row count (and size) of a table in an access data base? With RODBC, DBI or any other way.2. I have found that saving the tables as .RData files reduces the file size and and reduces the reading time. Is there some way of appending to an already saved data frame with this method?3. I have come across alternative ways of saving data - using writeBin and packages like saves, rhdf5 etc. Would they be useful alternatives?
4. Is there an advantage in combining binary files and databases like sqlite? Or, are files already saved in a binary format in databases like sqlite?5. What is the simplest method of porting from the access to the sqlite database? With RSQlite and RODBC, can I have connections to the access and sqlite databases open at the same time? Or, should I close one and then open the other? It would help if I can get a detailed bit if code for doing this in a simple way.

I would appreciate all help that I can get.Thanks,Ravi





	[[alternative HTML version deleted]]