Create field error

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

Hi,
I am using DAO to create a field called BEVerNo in a linked table called
UserOptions.
The field is created without any problems.
I then run an SQL statement to set the value to 1. First time through the
code
does not work, second time it does. The createtablefield code is generic and
I cannot set the value
of BEVerNo at the time the field is created.

I have tried DoEvents prior to the SQL but it does not make any difference.

The SQL is:

DoCmd.RunSQL "UPDATE UserOptions SET UserOptions.BEVerNo = 1 WHERE
((UserOptions.UserOptionsID)=1);"

Any suggestions?

Max
 
It would probably be more useful to see how you're adding the field than the
SQL that's failing...

Also, what error message do you get when you run the SQL?
 
I do not get an error message. It runs through the code, but the table is
not updated. The code to create the table field is:

Called by:
CreateTblField "C:\LOGBOOK\LOGDAT97.MDB", "UserOptions", "BEVerNo",
dbInteger, 20

Function CreateTblField(strDBName As String, strTblName As String,
strFieldName As String, _
strFieldType As String, strFieldSize As Integer)

Dim dbs As Database, tdf As TableDef
Dim fldInitial As Field

Set dbs = DBEngine.OpenDatabase(strDBName)
Set tdf = dbs.TableDefs(strTblName)
Set fldInitial = tdf.CreateField(strFieldName, strFieldType,
strFieldSize)
tdf.Fields.Append fldInitial
tdf.Fields.Refresh

Max
 
It could be latency.

From your Update query, I'm assuming that UserOptions is a linked table.

You may want to put in a CurrentDb.TableDefs("UserOptions").RefreshLink
after you add the field.
 
This was my thought exactly.
UserOptions is a linked query.
I put in the line you suggested after I added the field but before I ran the
SQL and the problem persists.

Max
 
Try adding

dbs.Close

in your CreateTblField function. Maybe that'll flush the buffer.

If that doesn't work, while it seems unnecessarily drastic, perhaps you need
to delete the linked table and readd it.

If that still doesn't work, then it must be a case of a time delay before
external database is ready. Try putting a pause in your code before you run
the SQL.
 
Hi have tried all these things.
In addition I put in a message box for a signigicant delay before running
the SQL.
I have repaired and compacted the BE.
I wrote code to use DAO to update the field instead of SQL- which works from
the Debug window,
but not if run within five minutes of adding the field to the table.

This is really puzzling.

Max
 
Back
Top