Run-time error '3049' when qdf.Execute dbFailOnError in Access

Question

I am trying to commit new data into the various databases and when I keep committing data after a while, it shows me this error:

enter image description here

The commit statment looks like this:

sql "INSERT INTO  Bond Values("","HK0000122334","CNH",8447.5357732363,8447.5357732400,0.0000000037,109913,"01Jun15")".

The database reaches 2.09Gb as well. My code looks this:

Sub commit(dbName As String, tableName As String, commitString As String, reportDate As String)
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim sSQL As String
    Dim qdf As QueryDef

    sDb = dbName & ".accdb"
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(sDb)
    sqlStatementList = Split(commitString, ";")

    For Each sqlStatement In sqlStatementList
        sqlStatement = Replace(sqlStatement, ")" & vbLf, reportDate)

        If InStr(tableName, "EIS") <> 0 Then
            sqlStatement = Replace(sqlStatement, "EIS", tableName)
        End If

        sSQL = sqlStatement
        Set qdf = db.CreateQueryDef("", sSQL)
        qdf.Execute dbFailOnError
    Next sqlStatement
End Sub

What I have tried so far:

1)

Set qdf = Nothing 
Set db = Nothing

This did not help. Still the same issue.

2) Tried to delete that particular database and proceeded with committing to the rest of databases but still had the same issue.

Need some guidance on solving this.

It looks like the dbName parameter passed to the function is `NULL` — cha, Jun 19 '15 at 04:13
it is not so. I am passing a dbName. I have checked the sql statement as well. — lakshmen, Jun 19 '15 at 04:17
Bond is the table name and Values refers to the values to be inserted into the database — lakshmen, Jun 19 '15 at 04:41

score 1 · Answer 1 · answered Jun 19 '15 at 04:47

1

The maximum size of an Access database is 2GB (Link is for 2010, but 2013 appears to be the same). So yes your insert will fail when the database gets that large. Your options are to break the data into another database file or switch to SQL Server or some other database type.

answered Jun 19 '15 at 04:47

shf301

31,086
2
52
86

1

or compress&repair, might be only 100mb large afterwards ;) – luk2302 Jun 19 '15 at 08:59
How to compress&repair? can explain? – lakshmen Jun 19 '15 at 09:24
Actually, luk2302 may have meant Compact & Repair (available under Database Tools on Ribbon) or with [VBA](http://stackoverflow.com/questions/1460129/ms-access-how-to-compact-current-database-in-vba): `Application.compactRepair sourecFile, destinationFile`. You can include this after each insert. Alternatively, break your large file into multiple Access files, either as linked tables, ODBC or DAO connected. – Parfait Jun 19 '15 at 21:17

Run-time error '3049' when qdf.Execute dbFailOnError in Access

1 Answers1