Solved Possible to use a huge Firebase db?

[Etxezarreta]

Hello all,
One client asks me if we could help them operate some Firebase databases:
they are huge:
I have tried tiding them up to Access or Powerpivot, but it chokes before ending. I guess the queries would be very slow, or even wouldn't ever return results.
Some of you people have some feed-back about that kind of process?
Many thanks!

 

[NauticalGent]

I am guessing that Firebase is an App builder like PowerApps and you are using .dbf files to store the data?

Edit: I found this. We would probably need a better understanding of what "tidying up" entails. If you are cleaning up the data, then it would probably be better to export from Firebase and then import into Access. Once there, clean up the data accordingly and them reverse the process to import it into Firebase.

Again, some more information is needed, at this point I am speculating.

 

[CJ_London]

unable to read the image - quality too low (at least for me). Not sure what you mean by 'operate' or 'tidying up'. Implies a DBA role rather than developer role

In terms of connecting, suggest use the firebase ODBC driver and use passthrough queries (which use Firebase syntax) . Better to do as much processing on the server and not access as possible. Certainly you don't want to return whole tables, just the data required so pass as much criteria. I don't suggest using linked tables.

Alternative is to use stored procedures if they exist in Firebase

 

[Etxezarreta]

I am guessing that Firebase is an App builder like PowerApps and you are using .dbf files to store the data?

Exactly

 

[CJ_London]

looks like I've misunderstood the requirement

 

[Etxezarreta]

Sorry for the word operate: I meant, just using the data from that DBase with Access or Powerpivot.
Many thanks.

 

[NauticalGent]

I have tried tiding them up to Access or Powerpivot, but it chokes before ending.

To be clear, you have a .dbf file that you are simply trying to import into Access?

If so, is it failing silently, does Access quit or do you get some sort of error message?

 

[Etxezarreta]

Access quits actually

 

[NauticalGent]

Ok, thanks. Is it possible to upload the file? We would be able to see if we have the same issue.

If not, the only thing I suggest would be to see if the data could be exported to a different format like excel.

 

[Minty]

My understanding is that firebase is a backend relational database, similar to many others.
I've seen it used in a couple of applications (Most notably a mail app as the storage medium.)

 

[Uncle Gizmo]

I had a look at firebase a few years ago and it is a noSQL database. I would imagine this would make it quite difficult to integrate the data with an MS SQL database. There was a previous thread on this awhile back where Jack provided and excellent looking link:-

How to connect Access to Firebase Realtime Db ?

I have a procedure created with Access 2013 which must read / write on a Firebase database Is there any example of code that I can use as a basis for development?

www.access-programmers.co.uk

I also found this website where it appears that you can link firebase to VB6, visual basic 6 is very similar to VBA (Visual Basic for Applications) so you might get some useful information from this site:-

Firebase Examples for Visual Basic 6.0

I'm somewhat surprised that anyone has linked vb6 to firebase because VB6 is very old now! Mind you, the same could be said for MS Access

 

[Etxezarreta]

Here is the link to the table:
https://www.insee.fr/fr/statistiques/fichier/5395740/RP2018_indreg_dbf.zip

I create a linked table, and then when I try the query below:

SELECT FD_INDREG_2018.DEPT, Sum(FD_INDREG_2018.IPONDI) AS SommeDeIPONDI
FROM FD_INDREG_2018
GROUP BY FD_INDREG_2018.DEPT;

this message appears

 

[Etxezarreta]

Whereas I said it quit, I managed to connect actually. It is when I try to sum up values that it chokes

 

[NauticalGent]

Wow...that file is MASSIVE...3.5 gb! Access has a 2 gb limit which is why (I would wager) you are unable to do anything with it.

 

[jdraw]

I notice that the OP, amorosik , who had the initial request for Access--Firebase has been active on this forum recently.
He may have some insight on the subject.

 

[Etxezarreta]

The data also come as CSV. We will try to import into a MySql, I keep you updated

 

[The_Doc_Man]

Access quits actually

If Access quits on you, there might be an error event in the system event logs.

Click the Windows icon at the far left of the bottom line task bar. In the search box, type EVTLOG and look into the System event logs. They are sorted by date/time with most recent events on top. If you know the time of such a "quit" event then you should be able to find it easily. You want to see an event related to MSACCESS.EXE at that time, though a .DLL file might also be involved as primary with Access as a secondary process. There will be a bunch of stuff that nobody but Microsoft understands, but there will also be a status or event code and the names of involved modules. Some of that information might help to figure out what killed Access.

 

[Etxezarreta]

Hi Doc,

I will try to figure this out, many thanks

 

[Pat Hartman]

Is the file ONE table or multiple tables? Do you have a way of exporting the data to a .csv file from FireBase? That could be a simpler way to transfer the data.

However, if the file is only a single table, your are not going to be able to load it into Access so you would have to load it into SQL Server or some other real RDBMS and then you can use Access to query it if you want.

 

[Etxezarreta]

Is the file ONE table or multiple tables? Do you have a way of exporting the data to a .csv file from FireBase? That could be a simpler way to transfer the data.

However, if the file is only a single table, your are not going to be able to load it into Access so you would have to load it into SQL Server or some other real RDBMS and then you can use Access to query it if you

We manage to deal with these files: we upload them into a MySql in a remote server, then we used pass-through queries from an Access, the queries are then used by Excel through Powerquery.
Thanks for your help you all.