db File size from SAP

[jayhawker]

Howdy,

I am currently using Office 2016 and have an XL file that is 10 mb. When I import that file to Access the db is 40 mb. Any tips for reducing the size? Using a xlb extension gets me to 3 mb but I'd really like to use Access. I've tried importing and even used ADO and sent the file line by line using vba. Any suggestions would be appreciated.

Thank you.

 

[MarkK]

Have you run a compact and repair on the database? Also, you can set an Access database to automatically compact and repair on close.

But also, why is an Access Database file size of 40MB a problem?

 

[jayhawker]

Hi Mark K - thanks for the reply! I have compacted and repaired (several times). 40mb isn't a problem yet butbi have more tables to pull in and want to keep the size from getting out if hand. I don't understand why the file sizes from SAP are so large. Thanks again

 

[MarkK]

What's SAP?

I don't think 40MB is that big, but keep us posted as you import more, and let's see if it does cause a problem. I understand there's a 2GB size limit to an Access file, so that's 40MB x 2000. You've got some room still.

And keep compacting the file periodically too.

 

[Minty]

SAP = small little simple bit of software used by a couple of little companies to rule the world.

http://www.sap.com/index.html
http://www.sap.com/corporate/en.html

 

[CJ_London]

40MB x 2000

Wouldn't that be 500? but still plenty of room.

Access may be larger because of indexing which will not be in te SAP file. You many also be storing numbers as text which take up more space - a long number is 4 bytes, if stored as text it will be 10 bytes plus 1 for each character.

Also as I recall from importing from files exported from SAP there are often a multitude of miscellaneous rows and spaces that will increase Access file size - I've seen columns with data of 3 characters followed by 47 spaces for example.

 

[MarkK]

Doh! Yeah, 500. And thanks for the SAP info.