DBF file larger than 2 GB

[Access9001]

I have a DBF file larger than 2 GB -- Access seems to have a limit, here. What is the typical workaround?

 

[SOS]

Well, you won't be able to import it into an Access file (Access has the same 2Gb limitation and that includes system objects too, so you don't get a full 2Gb). You may want to use SQL Server Express which will handle 4 Gb of data.

 

[Access9001]

Can SQL Server Express interface with Access? What would be the difficulties involved, if so?

Would it work with VBA, etc?

 

[SOS]

Yes, SQL Server Express can work with Access just like SQL Server can. You can set up linked tables (either by using ODBC or an ODBCless connection) and then it is just about like working with any linked Access backend.

 

[Mr. B]

Access9001,

While I agree with SOS that you would be better served to move the backend file to SQL Server or the Express version, just for the record, on work around that I here others talk about using is to split the backend into multiple files. If you split up the storage files to two files you how have a 4 Gb limit. If you split it into three files, you would then have a total of 6 Gb limit.

Just some thoughts for you to consider.

 

[The_Doc_Man]

Mr. B's point is correct. Then the only trick is to be sure that you keep related things together. At some point you are going to take a humongous performance hit with Access and big files, but SQL Server, MySQL, and other products of similar design will have a much higher limit before their performance goes to hell in a handbasket.

Not to mention that if your files are shared, your network people will hate your living guts for putting that much of a load on their network. Whereas with an SQL backend, your files don't touch the net, only the resulting recordsets do.