DLookup vs. Table Def (1 Viewer)

[Chrisopia]

I was looking for a way to update tables remotely, one issue I had was I had to invent a new column in a table. Which was straight forwards, using the "ALTER" SQL command.

Obviously, I only want this to run if the column didn't exist already.

I searched for "How to tell if column exists in table" and was greeted with a whole new system of TableDef, which looked messy and I wasn't prepared to learn:

Dim db As Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim strName As String
    Set db = CurrentDb
    Set tbl = db.TableDefs(tableName)
    For Each fld In tbl.Fields
        If fld.Name = fieldName Then
            FieldExists = True
            Exit For
        End If
    Next

Which does work - but looks messy.

I had a think and thought of a work around:

Dim Bacs As String

On Error GoTo NoField
Bacs = DLookup("BacsInfo", "tblBranch", "ID = 1") 'Looks for the first line in the new column

NoField:
    If Err.Number = 2471 Then 'Error in finding the field
    Err.Clear
    'Run Code
    End If

It runs a DLookup, and if there is nothing, it throws error 2471 (which says I've spelt it wrong or it can't find it)

What is the main difference? why could none of the forums think of my solution? - I feel like I've done something wrong??
I'm sure if I spent time on it, I could turn it into a generic function...

 

[plog]

I had a think and thought of a work around:

This whole thing sounds like a workaround. What exactly are you trying to do? 'update tables remotely' isn't that descriptive. Give an example if possible.

 

[pr2-eugin]

I am unable to understand your problem, error 2471 is thrown, so now what?

 

[Chrisopia]

I am confused why the forums were saying to run TableDef, but I just placed an error around a DLookup.
I think my way is easier and more straight forwards, or is there something I am overlooking?

Updating the system for Marketing returns, currently the system doesn't have a column for that, so I'm adding it. Also the invoices now need Bank details for online banking, so I need to add that too.

 

[DemonDNF]

The only difference I can think of is performance. My tables will holds hundreds of records. How many will yours hold, millions?

Robert

 

[gemma-the-husky]

what you cannot do is directly amend a linked table

you have to open the linked database directly, and then you can amend the tables, either with DDE statements (I think that's what you are using), or by DAO code, which is easy to do.