Dynamically adding a field to a table from a form event

[Julia Boswell]

Hi all,

I'm using a form after update event to run a make table query, which works
fine. However then I need the code to append a field to the new table. I'm
using ADO in Access 2003. Here's the code:

Dim db As ADODB.Connection
Dim rsTemp As New ADODB.Recordset
'open the new temporary table and add a new field
Set db = CurrentProject.Connection
rsTemp.Open "tblTempDespbyIL", db, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rsTemp.Fields.Append "Despatch", adBoolean, , adFldUpdatable

When it comes to the last line of the code I get a run-time error 3219
"Operation is not allowed in this context".

Anyone any ideas?

Thanks

Julia

 

[Julia Boswell]

All,

After extensive reading I've realised that the append does not work if the
table is opened, so I removed the rsTemp.Open "tblTempDespbyIL", db,
adOpenKeyset, adLockOptimistic, adCmdTableDirect line of the code and the
error message stops. However although the code runs, the field is not
appended, so I'm still not there!

Any clues?

Tks

Julia

 

[Van T. Dinh]

If you use DAO, you can access the TableDef Object to add a Field into the
Table.

Alternatively, you can use a DLL Query using the "ALTER TABLE ..." SQL

Not sure about ADO as I only use ADO if the database engine is not JET (and
that's fairly rare for me).

However, it is rather strange that you need to modify the Table structure
using code. It sounds like you need code to change the Table regularly.
Normally, the Table Structure should be "permanent" once the database is "in
production" since other Access Objects depend on the stable Table structure.

Check Access / Access VB on special terms I mentioned above.

 

[Julia Boswell]

Thanks, I'll try DAO. I've found another couple of postings in various
places with people saying that append doesn't seem to work using ADO.

This is a temporary table based on a query that needs creating for a
specific purpose, it is time dependent and needs to change regularly and it
doesn't link to anything else. It gets created for a purpose, used, then
deleted. Unfortunately I have to append the extra field using code as it
doesn't exist in any other tables, therefore the query doesn't create it.
There is method to my madness!

Thanks

Julia