Importing table from Postgres to MS Access and losing records

Question

I have a postgres table containing nearly 700,000 records, I import that table into MS access (via an ODBC data source) and end up with only 250,000 records.

I start with an empty MS access database (520 kbytes). Select (external data)/(New data source)/(from other sources)/(ODBC database)/(Import the source data)/(Machine data source) I pick my ODBC postgres database, and select the table I want, wait for 30 seconds, then I get a message box saying all objects have been successfully imported followed by being asked if I want to save the import steps.

There are no error messages, but the number of rows in my MS access version of the table is around 250,000.

Other info...

• I'm using MS office 365 version 1710
• I'm using postgres 9.5.7
• I'm using the PostgrSQL ANSI ODBC driver (not sure which version)
• There are no signs of any error messages (or warnings).
• After the import the Access database is still only 375 Mbytes, well short of the 2 Gbyte limit.
• I've checked the 'ODBC data sources' app to check how the postgres ODBC link is configured, there's no obvious problem with it.
• The final message that MS access gives me after the import includes 'all objects imported without errors'
• There is no obvious difference between the records that are getting through and those that aren't.

Why am I losing records, and what can I do to cure it?

Thanks

Answer 1

If you attempt to "slurp" all records from the database at one time, the ODBC driver will stop fetching at some point and just return what it has without warning. It's annoying. As far as I know this has nothing to do with the 32-bit limit.

The way to solve this is to not fetch all records at once, but use the declare/fetch option on the driver. You can do this permanently on the ODBC settings by going to your ODBC properties, selecting "Datasource" and then on "Page 2" checking the "Use Declare/Fetch" and setting your cache (# of rows) size. I recommend a number somewhere between 5,000 and 50,000. Each batch represents a hit to the database, so you want it to be reasonably large to begin with.

From all practical purposes, the use of declare/fetch will be totally transparent to your application. You won't even notice. You will on the database admin side, but if your fetch size is sufficiently large, it won't be an issue.

You can also do one-time edits to your connection string from your particular query. You would add the following to make this work:

UseDeclareFetch=1;Fetch=25000;