Table from SAP into Access database - enormous number of records

[jaryszek]

Hi People,

I have about 6 millions records which i want to use in my Access database as vlookup table.

So if user will choose the employee's ID (not unique) from combobox there subform should be shown with few filtered records based on choosen ID.

So for example ID: 1001 will show
Company1 , number of company, department
Company2 , number of company, department
Company3 , number of company, department

and next user would write new records for each company.

It seems to be good approach.


Problem is within size of this history table which will be growing every day (new employees are added to SAP).

What do you think Access will handle it or here i should think about MSSQL server ?

Best Wishes,
Jacek Antek

[ranman256]

for 6 million, you may go with SQLserver. I crammed 2 million in access, but it was at the limit.
or
you could try breaking up the table into 26 different .mdbs based on last name letter.

[jaryszek]

thank you ranman256.

I am courious with your second solution.

So i have 26 small databases.
how in my front-end should i concatenate them ? (union them)?
Using simple union query ?

Additionally problem is that each of these database should be update every day (appending new records).
So i have to also run a macro inserting into queries... for 26 databases...

Best Wishes,
Jacek Antek

[John_G]

how in my front-end should i concatenate them ? (union them)?

If you need to union them, then you're no further ahead. Behind the scenes in MS Access, a query is treated as a temporary table, so if all the data would not fit into one table without exceeding the file size, then it won't fit into a query either (even though it is temporary).

With the amount of data you have, you should be looking at SQL Server for the data, and MS Access for the front end.

[jaryszek]

Thank you John_G.

, so if all the data would not fit into one table without exceeding the file size, then it won't fit into a query either (even though it is temporary).

sorry, i do not exactly know what you mean, could you please explain it more?

You are saying that creating union query will exceed the database size?

Jacek

[orange]

jacek,

6 million is a lot of records.
What are these records?

[aytee111]

If you normalize the structure (employee table, company table, etc.), this may help reduce the amount of space needed. Also, make sure each field is exactly the right size, not left at the default of 50.

[John_G]

You are saying that creating union query will exceed the database size?

Yes. The results of a query are treated in much the same way as a temporary table, so the union query result could exceed the file size limit. Remember that it would not just be the query result taking up space in the Access file, but all your other objects too - especially other tables.

[jaryszek]

Thank you aytee111 for you help!

orange,
These are all employees from SAP with their history of companies where they worked.
(is this sentence correct ? :P)

best wishes
Jacek

[jaryszek]

Thank you John_G!

Wow i thought that queries are counted on the fly and theirs weight is very small and futhermore--> they don't have influance for database sizing...

Best Wishes,
Jacek Antek