CODEWITHSUNDEEP
vbams-accessnull
GregH
GregH

Reputation: 5457

VBA invalid use of null

I have inherited a VBA project and am trying to debug an error I am receiving: Invalid use of Null. After some time I have located where the error is occurring but am yet to find the specific culprit and/or a solution for the issue. See the following snippit (line throwing the exception is annotated with comment):

Dim db As Database, wsp As Workspace
    Dim retVal As Variant
    Dim tableType As Long

    'Check to see if the table name exists in the zLinked_Tables table; if it does, that means its a SQL table
    If DCount("*", "[zLinked_Tables]", "[LocalName] = '" & TableName & "' AND [ObjectType] = 'Table'") > 0 Then
        'SQL Table
        retVal = ExecuteSPT("TRUNCATE TABLE [" & TableName & "]", 0) 'truncate the table via passthrough query on server
        If KeyColumn <> "" Then
            retVal = ExecuteSPT("DBCC CHECKIDENT([" & TableName & "],RESEED,1)", 0)  'reset the identity column value via passthrough query on server
        End If
    Else
        'MS Access Table
        tableType = DLookup("[Type]", "[MSysObjects]", "[Name] = '" & TableName & "'")
        DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE * FROM [" & TableName & "]" 'delete all records from table
        If KeyColumn <> "" Then
            DoCmd.SetWarnings True
            If tableType = 1 Then 'Resident/Local
                Set db = CurrentDb
                db.Execute "ALTER TABLE [" & TableName & "] ALTER COLUMN [" & KeyColumn & "] COUNTER(1,1)"
            Else 'Linked Table
                Set wsp = DBEngine.Workspaces(0)
                Set db = wsp.OpenDatabase(DLookup("[Database]", "[MSysObjects]", "[Name] = '" & TableName & "'")) 'ERROR THROWN ON THIS LINE
                db.Execute "ALTER TABLE [" & TableName & "] ALTER COLUMN [" & KeyColumn & "] COUNTER(1,1)"  'reset the autonumber column value
            End If
            DoCmd.SetWarnings False
            Set db = Nothing
        End If
    End If
    Exit Function

Note that I have inspected the TableName variable and it is not null and it is a valid table name

Upvotes: 2

Views: 499

Answers (2)

Brad
Brad

Reputation: 12245

There are several things happening on this line. In addition to checking TableName for null you should also check Is wsp null? does Set wsp = DBEngine.Workspaces(0) fail for whatever reason?

Is the dlookup failing? DLookup("[Database]", "[MSysObjects]", "[Name] = '" & TableName & "'"). If that is null that means you are passing null to wsp.OpenDatabase() which gives the error you see:

enter image description here

Upvotes: 2

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

Your DLOOKUP returns a row that has no value (NULL) in the 'Database' field in MSYSObjects. Type the following into the immediate window and executs. You need to add code to allow for that possibility.

I assume you are trying to get the full path?

?(DLookup("[Database]", "[MSysObjects]", "[Name] = '" & "Table1" & "'"))

Upvotes: 2

Related Questions