VBA compile error object required

Question

I have inherited a vba project and have been recently getting my feet wet with vba development. I have enountered an error Compile Error Object Required when running a portion of code that I have recently made edits to. Since it's a compile error, I cant see at runtime exactly which line is throwing the error. The only code I have modified is the ElseIf block in the following code... all help is appreciated.

            If tableType = 1 Then 'Resident/Local
                Set db = CurrentDb
                db.Execute "ALTER TABLE [" & TableName & "] ALTER COLUMN [" & KeyColumn & "] COUNTER(1,1)"
            ElseIf tableType = 4 Then
                Set conn = New ADODB.Connection
                Dim connectionString As String
                Set connectionString = DLookup("[Connect]", "[MSysObjects]", "[Name] = '" & TableName & "'")
                conn.connectionString = connectionString
                conn.Open
                Dim cmd As ABODB.Command
                Set cmd = New ADODB.Command
                cmd.ActiveConnection = conn
                cmd.CommandText = "ALTER TABLE [" & TableName & "] ALTER COLUMN [" & KeyColumn & "] COUNTER(1,1)"
                cmd.Execute
                conn.Close
            Else 'Linked Table
                Set wsp = DBEngine.Workspaces(0)
                Set db = wsp.OpenDatabase(DLookup("[Database]", "[MSysObjects]", "[Name] = '" & TableName & "'"))
                db.Execute "ALTER TABLE [" & TableName & "] ALTER COLUMN [" & KeyColumn & "] COUNTER(1,1)"  'reset the autonumber column value
            End If

Comintern · Accepted Answer · 2016-11-29 14:52:28Z

3

Remove the Set from this line:

Set connectionString = DLookup("[Connect]", "[MSysObjects]", "[Name] = '" & TableName & "'")

Set is only used for assigning object references, and connectionString is a String.

For future reference, the VBE will highlight the line with the compile error if you use Debug->Compile from the menu.

answered Nov 29, 2016 at 14:52

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CatyG · Accepted Answer · 2016-11-29 14:52:06Z

-1

Normally if I was writing and if statement o would have it like this

If tableType = 1 Then 'Resident/Local
            Set db = CurrentDb
            db.Execute "ALTER TABLE [" & TableName & "] ALTER COLUMN [" & KeyColumn & "] COUNTER(1,1)"
        Else
If tableType = 4 Then

give that a bash. May just bee the formatting error

answered Nov 29, 2016 at 14:52

CatyG

11 bronze badge

1 Comment

David Zemens Over a year ago

There is no "formatting" (syntax?) error in OP's code other than incorrect usage of the Set keyword to assign a string. Also, using nested If/Else/If`` statements instead of an ElseIf` makes for unnecessarily complicated/nested If blocks and hampers readability and maintainability (spaghetti code)

Collectives™ on Stack Overflow

VBA compile error object required

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related