Import and manipulate Large Files in Access, compact and repair

[crozfader]

Hello all.

I have been trying to do something all day and I have partially succeeded at this point. Let me give you some background.

I have 26 xlsx files that are exported from an ERP system. Each file is about 60 megs and have around 500,000 lines of data.

What I'm trying to do, is import everything into access. I don't need all 26*500,000 lines, I actually need about 300,000 (in one single table)... All the rest will be filtered. I thus need to create a union query at some point.

Because the max size of a DB in Access is 2 GB. I can't just import everything using code, then run a query to filter everything and keep the 300k lines.

What I've been is the following:
- Write a VBA macro that imports all files in a folder into Access as separate tables
- After a file is imported, create a temporary query that filters the 500k lines just imported. Delete the query and the large table just imported. Keep just the query results (placed into a permanent using a select INTO table query).


This works very well, I'm sure it's not optimal, but it works. There's only one big problem... I can't do that for all 26 files because at one point the Access file will reach a 2 GB limit eventhough you will never have more than 500k lines at any point in time.

What is happening is that the manipulations are increasing the size of the DB and a Compact and Repair is required to reduce the size of the DB.

Example. If I import 8 out of 26 files, my DB size is 450 megs. When I run compact and repair the file size goes down to 50...

Unfortunately, from what I've read, there is no way to run a compact and repair from within the database... But there is apparently a way to do it on a closed Database.

So, I was thinking, please give me your opinion, to have two databases... One empty database that contains only VB code, and one database that contains the actual data. I don't even know if this is possible, I'm not Access expert.


What this would do is:
- Import the files in a closed database, execute queries in that same database. Delete the query and the original large tables-
- After about importing 8 tables; run a compact and repair which reduce the size of the file significantly
- Continue import etc. another 8 files, and continue.

Is this doable? How would I modify the code below to make it work on another database (where i can just specify the path of the DB that should contain the data)?

Thank you all in advance.

 

[crozfader]

CODE BELOW

Public Sub ImportFolder()

Dim InputDir, ImportFile As String, tblName As String
Dim InputMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
InputDir = InputBox(InputMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.xlsx")
'MsgBox ImportFile


counter = 1

Do While Len(ImportFile) > 0

tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))

'MsgBox InputDir

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tblName, InputDir & "\" & ImportFile, True

Dim strsql As String
strsql = "SELECT *, MID(SOLD_DATE_RANGE, 15, 10) AS MONTHS, iif(EXT_PRICE < 0, 0, EXT_PRICE) AS [New Inv Cost], price * qty_sold as [Sales Cost] INTO " & tblName & "_" & counter & " FROM " & tblName & " WHERE (QTY_SOLD <> 0 and QTY_ONHAND <> 0)"

CreateQuery strsql, tblName & "_q" & counter
CurrentDb.QueryDefs.Delete tblName & "_q" & counter

DoCmd.DeleteObject acTable, tblName
counter = counter + 1

ImportFile = Dir
Loop


End Sub
Public Function CreateQuery(strsql As String, strQryName As String)
On Error GoTo Err_CreateQuery

Dim db As DAO.Database

Dim qdef As DAO.QueryDef

Dim strTblName As String

Dim strFldName As String

'Dim strQryName As String

Dim strSQLLog As String

strTblName = "[MyTableName]"

strFldName = "[MyFieldName]"
'

'Create Temporary Query Name with Date & Time to prevent possible duplication

'strQryName = "qryTemp_" & Year(Date) & Month(Date) & Day(Date) & Hour(Now) & Minute(Now) & Second(Now)

Set db = CurrentDb

Set qdef = db.CreateQueryDef(strQryName)

qdef.sql = strsql
' SELECT CASE CAN BE ADDED HERE TO ALLOW FLEXIBILITY TO OUTPUT METHOD CONTROLLED BY USER INPUT FORM (EG DATASHEET, REPORT, EXPORT etc)

DoCmd.SetWarnings False

DoCmd.OpenQuery strQryName, acViewNormal, acReadOnly

'CurrentDb.QueryDefs.Delete (strQryName)

DoCmd.SetWarnings True

Exit_CreateQuery:

qdef.Close

db.Close

Set qdef = Nothing

Set db = Nothing

Exit Function
Err_CreateQuery:

MsgBox Err.Description

Resume Exit_CreateQuery
End Function

 

[SydneyGeek]

If you want to increase capacity you probably have 2 main options.
1. Import each file into a separate database. Use another front end database to do the filtering, pulling the processed data into the front end. The capacity is 2GB per database.
2. Use SQL Server Express as the data store, then manipulate it from the Access front end. The latest service pack SSE2008 has a capacity of 10GB.

Denis

 

[crozfader]

Thanks for your answer.

I'm ok with both alternatives, but wouldn't know how to modify my code to make it connect to an external database instead of the current one (whether the external DB is SQL Server or not).

I like the SQL server option as I think it would be a better practice, but don't know if my code could work with it.

Can you help me adapting the code?

 

[crozfader]

Thanks for your answer.

I'm ok with both alternatives, but wouldn't know how to modify my code to make it connect to an external database instead of the current one (whether the external DB is SQL Server or not).

I like the SQL server option as I think it would be a better practice, but don't know if my code could work with it.

Can you help me adapting the code?

 

[SydneyGeek]

The Access option is probably simpler to implement.

In the front end (no tables) database containing the code, link to each of the databases in turn.
File | Get external data | Link (in 2003 or earlier)
Data | Extenal Data | From Acess (in 2007 and 2010).

Those links will look and behave like normal tables to the code; you should be able to run the code without modification.

For SQL Server there's a bit more to it. One option is to create an ODBC connection to the database; the tables will then link through and behave in much the same way as the Access option. Except, you will most likely need to provide the user name and password in the code.

Denis

 

[crozfader]

Ignore my last post forgot to click the "link table" option