Getting File Size

[andrewsmith]

I have an Access DB with a whole load of file imports coded in VBA. The files are a mixture of CSV and Excel. The system was originally coded in access but we are in the process of migrating to SQL server due to file size issues. However, the imports are still in Access and they write the data to SQL Server. Whilst importing the database is expanding and reaching the 2GB size limit. This then cuases a corruption and we have to restore from a backup.

To prevent this I have added a piece of code that checks if the DB is greater in size than 1.5GB before importing the next file. This seems to update as I break in the code but left to its own devices does nothing.

I have tried 2 techniques.

Dim mdb As File
    Set mdb = fso.GetFile(CurrentProject.Path & "\" & CurrentProject.Name)
    
    'loop through files in folder
    For Each f In fls
        
        If mdb.Size / 1000000 > 1500 Then
            MsgBox "File size greater than 1.5GB,  Please process imported data into the SQL Server and compact database", vbCritical, "Database Expansion Error"
            Exit For
        Else
            'file import code

This was flakey even when breaking out at the if statement.So I tried:

If FileLen(CurrentProject.Path & "\" & CurrentProject.Name)  / 1000000 > 1500 Then

Neither work as the code runs and the database expands away to over 2GB and corrupts.

Any help greatfully appreciated.

 

[Beetle]

Why not take the (.mdb) tables that the files are being imported into and place them in a separate .mdb file and link to them. Import a batch of files, and after they are written to SQL Server backup UP that .mdb file, delete the data in those tables, then C&R. Rinse, repeat.

 

[andrewsmith]

I'd still need to know when to call the compact function. It looks like the file size doesn't refresh properly while the code is active

 

[Beetle]

Actually, my thought was to backup, delete the data and compact after every import once the data was migrated to SQL Server. In this case it wouldn't matter what the file size is as long as each import is less than 2g.

 

[andrewsmith]

I have tried to implement a compact every 10 imports but it is telling me the linked file is opened. How does one go about closing a linked file?