A2007 Attachment Field Type problem

[Peter Hibbs]

I am using Access 2007 (in the new .accdb mode) and I am trying to add
the new Attachment field type to a table in the back-end file using
VBA code in the front-end.

Unfortunately there is very little information on the Internet about
this subject so I am having to guess on how the code should work. I am
using the code from Allen Browne's site as a starter. The problem is
that I'm not entirely sure of the syntax for the CreateField function,
I'm guessing that the second parameter is 'dbAttachment' and the last
parameter is irrelevant (I have set it to 1).

This is the code in the FE that I am using :-

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim vPathname As String

vPathname = "C:\Access Test\Database_be.accdb"
Set db = OpenDatabase(vPathname) Set tdf =
db.TableDefs("tbl_BE_Table")
tdf.Fields.Append tdf.CreateField("NameOfField", dbAttachment, 1)
Set tdf = Nothing
Set db = Nothing
RefreshDatabaseWindow

The weird thing is that the code appears to work, no errors, and the
new field is appended to the table correctly but when I try to open
the table from the front end I get this error message :-
'The search key was not found in any record.'

The same thing happens if I try to open the table in Design mode (from
the FE), after the usual 'you cannot modify this table,etc' message I
get the same error message. However, if I then open the BE file and
open the table, the new field is there and I can do all the usual
things with it with no problems. If I use the same code for other
types of fields, dbText, dbMemo, etc, it works correctly so it appears
to be a problem with the dbAttachment type fields..

If I execute the same code to add an Attachment field to a table in
the FE it works fine. The field is appended to the table and I can
open the table as normal. This code looks like this :-

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb()
Set tdf = db.TableDefs("tbl_FE_Table")
tdf.Fields.Append tdf.CreateField("NameOfField", dbAttachment, 1)
Set tdf = Nothing
Set db = Nothing
RefreshDatabaseWindow

Is this a bug in A2007 or am I missing something. Can anyone reproduce
the problem, does anyone know how to fix it.

Peter Hibbs.

 

[Allen Browne]

Hi Peter

I was not able to reproduce the problem you describe. I do have SP1 for
Office 2007.
Here's what worked for me:

Function AddAttachField()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset
Const strcFile = "C:\Data\db1.accdb"

Set db = OpenDatabase(strcFile)
Set tdf = db.TableDefs("Table1")
Debug.Print tdf.Fields.Count
tdf.Fields.Append tdf.CreateField("AjbAttach", dbAttachment)
Debug.Print tdf.Fields.Count
Set tdf = Nothing
db.Close
Set db = Nothing

Set db = OpenDatabase(strcFile)
Set rs = db.OpenRecordset("SELECT Table1.* FROM Table1;")
Do While Not rs.EOF
Debug.Print rs!ID
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function

In db1.accdb, Table1 already exists. It has just one field named ID (a Long
Integer), and 4 rows. The Fields.Count is incremented when the field is
added. The OpenRecordset works, and loops through the values without
problem.

 

[Peter Hibbs]

Allen,

Tried your code and it did exactly the same. Checked for SP1 and that
is already installed. So I'm thinking it might be something wrong with
the BE. I opened the BE and deleted the table that I have been using
to test this code and created another one. Went back to the FE,
relinked the table and tried the code again. Lo and behold, it worked
OK, (well sort of!).

The original tables in the BE were actually imported from an A2000 mdb
file so I'm wondering if that caused the problem, or maybe there was
some corruption in the table anyway. I will test it further later.

Now to explain the 'sort of' comment. When I run the code in the FE to
add an ATTACHMENT type field to my new table in the BE, it works fine.
If I open the table for editing from the FE I can see all the fields,
including the Attachment field, and I can edit them as normal.

However, if I open the table in Design mode from the FE (and dismiss
the usual warning message) I can see all the fields in the table
EXCEPT the Attachment field that I have just added. The field is there
(because if I add another field of a different type using the same
code, say Text, that field shows on the next line down) but the Field
Name, Data Type and Description fields are all blank for that one
field. Even if I click on the field, the Properties window at the
bottom of the form show blank. Did you, by any chance, happen to try
that when you were testing your code or have you any ideas why that
should happen. I don't think it is a major problem since it is
unlikely that the users will need to open a linked table in Design
mode but it could be very confusing for the developer.

I have tried C&R on FE and BE but this makes no difference. I have
also opened the BE, added an Attachment type field to the new table
manually and when I return to the FE I cannot see that field either in
Design mode. So I assume it is not a code problem, perhaps another
bug. It would be helpful if you could check this so that I can
ascertain whether it is something weird on my system or Access 2007
itself.

TIA

Peter.

 

[Allen Browne]

Didn't test all that Peter, but a workaournd might be to drop the attached
table, and link it again?

 

[Peter Hibbs]

Hi Allen,

You were quite right, relinking the table fixed the problem. It would
seem that there is a bug in A2007, i.e. if an Attachment type field is
added to a table in a back-end file, it is not visible from the
front-end until the links are refreshed. Another one to add to the
list!

I have implemented your 'work around' in code by adding the field to
the table and then refreshing the link to the back end. The code looks
something like this (simplified a bit for this post) and it works
fine. The field gets added and it shows up correctly when displayed in
Design mode from the FE.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim dbLocal As DAO.Database

Set tdf = db.TableDefs("tblClients")
tdf.Fields.Append tdf.CreateField("MyField", dbAttachment)

Set dbLocal = CurrentDb()
For Each tdf In dbLocal.TableDefs
If tdf.Name = "tblClients" Then
tdf.Connect = ";DATABASE=" & vPathname
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

Just one last question and I will leave you in peace. As you can see,
to refresh the link, I am iterating through ALL the tables and
refreshing the link when it finds the table (tblClients in this
example) that holds the new field. While this works OK I'm wondering
if there is any easy way to refresh the specified table without
scanning all the tables in the BE. I think it would be a bit more
efficient and tidier. I have found plenty of examples of scanning all
the tables but can't work out how to do just one. The vPathname
variable holds the pathname to the BE.

Thanks anyway for all your help on this.

Peter.

 

[Allen Browne]

Interesting. Attachments are a kind of multi-valued field (MVF), so the
'link' to the back end would involve also linking the super-hidden junction
table that A2007 creates to house the 'many' fields. A curious possibility
is that the existing link doesn't include the super-hidden junction table
info until you drop and re-link it. If that were so, you could reproduce
this bug by adding any kind of MVF field to a linked table, not just the
attachment.

Perhaps someone who has some spare time wants to investigate that
hypothisis. I'm in the middle of a dev. job at present, so won't be able to
for several weeks.

I don't have boilerplate code for scanning the back end for new non-system
tables that need linking. Someone else might post that.