How to Create an Attachment Field in a Table

[ADezii]

Does anyone know how to 'pro-grammatically' create an Attachment Field in an Access Table using DDL? The following Code does NOT add the [Attachments] Field to the [Students] Table. All works well up to that point.

Dim strSQL As String
 
strSQL = "CREATE TABLE [Students] ([ID] COUNTER,[Last Name] VARCHAR(50), " & _
         "[First Name] VARCHAR(50), [E-mail Address] VARCHAR(50), [Student ID] VARCHAR(20), " & _
         "[Level] VARCHAR(30), [Room] VARCHAR(20), [Date of Birth] DATETIME, [ID Number] VARCHAR(255), " & _
         "[Home Phone] VARCHAR(25), [Mobile Phone] VARCHAR(25), [Address] VARCHAR(255), " & _
         "[City] VARCHAR(50), [State/Province] VARCHAR(50), [ZIP/Postal Code] VARCHAR(15), " & _
         "[Country/Region] VARCHAR(50), [Web Page] MEMO, [Notes] MEMO, [Attachments] ATTACHMENT);"
 
CurrentDb.Execute strSQL, dbFailOnError
RefreshDatabaseWindow

[NeoPa]

Hi ADezii.

Just a couple of questions before we can really start in earnest :

1. What SQL string does your VBA code produce?
2. What error number & message do you get when you run your code? I think we can assume it errors on line #10 as posted.

You can be assured of our best efforts for you my friend.

[twinnyfo]

ADezii,

I am not sure if I am using the correct language, but from what I understand, the Attachment "Field" is not technically a "Field", nor is the Attachment "Data Type" truly a "Data Type" in the traditional sense of the word--at least in how "we" consider data types. The closest I can get is that it is, itself, an "Object" that is referred to by the Table/Field.

I remember running across some sort of discussion about this a while back, which gave me this understanding, but I can't find the reference, not even on MS Docs.

Because of this, the Attachment "Field/Data Type" is outside the scope of SQL, which prevents us from creating, modifying, updating or otherwise manipulating an Attachment Field through SQL. It is possible to access (attach, delete, etc.) Attachment fields through VBA, however.

In my opinion, the Attachment field can be a great thing and it can be a bad thing, too. Because of their complexity, I tend to avoid them, but they can certainly be useful when trying to transport files between users, using only a DB interface.

Not sure if this hepps or not.

[twinnyfo]

Also, if you go to the MS Online Reference, you will see a list of Data Types available for a Field. NOT listed is that of dbAttachment, which is available in the MS Access Object Browser. The DataTypeEnum for dbAttachment is 101, which is beyond the normal range of the other Data Types listed (1-23). There are also several "Complex" data types listed, which are also not listed in MS Docs. I've also run across an "Embedded Macro" data type, which doesn't show up anywhere. Below is a complete list of known (to me) data types:

Val   Data Type
1     dbBoolean
2     dbByte
3     dbInteger
4     dbLong
5     dbCurrency
6     dbSingle
7     dbDouble
8     dbDate
9     dbBinary
10    dbText
11    dbLongBinary
12    dbMemo
15    dbGUID
16    dbBigInt
17    dbVarBinary
18    dbChar
19    dbNumeric
20    dbDecimal
21    dbFloat
22    dbTime
23    dbTimeStamp
101   dbAttachment
102   dbComplexByte
103   dbComplexInteger
104   dbComplexLong
105   dbComplexSingle
106   dbComplexDouble
107   dbComplexGUID
108   dbComplexDecimal
109   dbComplexText
8209  EmMacro

This may be an indicator that, although these data types exist, they may not be available through all means of manipulation--such as SQL.

Again, not sure this hepps to address your current issue, other than to say that what you are trying to do cannot be done.

[ADezii]

NeoPa, always a pleasure to see a familiar face, not literally of course, and I hope you are doing well and staying safe. First let me state that I have a strong suspicion that it cannot be done as twinnyfo indicates in his Reply, but I won't give up in searching for a way to accomplish this in Code. Simply stated, I guess I just can't accept the fact that there is no way to pro-grammatically create an Attachment Field. I am actually assisting someone with a Project who is a lot smarter than I, and he got stuck on this very issue. Simpy stated, he is creating a Utility that:

1. Will allow you to select any Access Database.
2. List all Non-System Tables within that Database to a ListBox.
3. Allow you to select None, One, or Several Tables from that ListBox.
4. The DDL Statements required to re-create that/those Table(s), along with the actual Data as an Option, will be displayed in a TextBox adjacent to the ListBox.
5. The listings will be comprehensive in that Indexes, Relationships, LookUp Fields, etc. will also be displayed.
6. The concept in a nutshell is: Rather that downloading a Database, list the SQL Statements and other relevant information required to recreate the Tables contained within, a lot safer Option.
7. I hope that I have explained everything adequately.

P.S. - Thanks NeoPa and twinnyfo! If there were any two individuals who could point me in the right direction, it would be you guys! I'm sure I don't have to tell you, twinnyfo, what excellent company you are in with in NeoPa. The two of you compliment each other. Take care, stay safe, and I will check back periodically for any updates.

[twinnyfo]

Well, then, you can always pass along the VBA to perform these tasks as text:

Option Compare Database
Option Explicit
 
Public Sub CreateNewTable()
On Error GoTo EH
    Dim db  As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
 
    Set db = CurrentDb()
    Set tdf = db.CreateTableDef("tblTest")
    With tdf
        Set fld = .CreateField("tblID", dbLong)
        .Fields.Append fld
        .Fields.Refresh
        Set fld = .CreateField("Field1", dbText, 30)
        .Fields.Append fld
        .Fields.Refresh
        Set fld = .CreateField("Field2", dbAttachment)
        .Fields.Append fld
        .Fields.Refresh
    End With
 
    With db.TableDefs
        .Append tdf
        .Refresh
    End With
 
    Set tdf = Nothing
    Set db = Nothing
 
Exit Sub
 
EH:
    MsgBox Err.Number & " - " & Err.Description
End Sub

This does work. It's just a bit more complicated than the simplicity of SQL. However, the premise is the same. It does allow the addition of Attachment fields (see above).

Of course, you could essentially build an entire DB using just VBA: create tables, queries, forms, reports, relationships, modules. Just pass it along as a text file and all is well....

[ADezii]

you can always pass along the VBA to perform these tasks as text:

I could not agree with you more, but remember this is not my Project. I'm not sure if he would be willing to rewrite his Code to accept this new approach.

[twinnyfo]

Totally agree! Right now, if he wants to include Attachment Fields, it may be his only option. Or, he could just write code for the few tables that do have Attachment fields in VBA and the others stick with SQL.

[ADezii]

Unless I am missing something, it appears as though an Attachment Field cannot be created via VBA also (see Code reference).

Dim MyDB As dao.Database
Dim tdfNew As dao.TableDef
Dim fldNew As dao.Field
 
CurrentDb.TableDefs.Delete "Demo"
 
Set MyDB = CurrentDb
 
Set tdfNew = MyDB.CreateTableDef("Demo")
 
With tdfNew
  Set fldNew = .CreateField("ID", dbLong)           'Works
               .Fields.Append fldNew
               .Fields.Refresh
  Set fldNew = .CreateField("FName", dbText, 50)    'Works
               .Fields.Append fldNew
               .Fields.Refresh
  Set fldNew = .CreateField("LName", dbText, 50)    'Works
               .Fields.Append fldNew
               .Fields.Refresh
  Set fldNew = .CreateField("MI", dbText, 1)        'Works
               .Fields.Append fldNew
               .Fields.Refresh
  '********* The assignments to fld do NOT work *********
  'Set fldNew = .CreateField("My_Files", dbAttachment)
               '.Fields.Append fldNew
               '.Fields.Refresh
  'Set fldNew = .CreateField("My_Files", 101)
               '.Fields.Append fldNew
               '.Fields.Refresh
  '******************************************************
End With
 
MyDB.TableDefs.Append tdfNew
MyDB.TableDefs.Refresh
 
RefreshDatabaseWindow

[twinnyfo]

Very strange! It works fine on my system. What version of MS Access? I'm using Pro Plus 2016.

I wonder if it is a missing reference? Here is a list of my references:

[ADezii]

I'm running Access in Microsoft 365 (32-bit). I'll check the References, but I really don't think that has anything to do with it. The dbAttachment Type is simply not recognized. Thanks, will get back to you.

[twinnyfo]

Yeah - grasping at straws. Like I say, My demo worked perfectly.

[twinnyfo]

And I just tried on my home MS 365 64-bit and all works fine on a new, blank db.

This becomes curiouser and curiouser!

[ADezii]

Still no cigar, very strange. dbAttachment is not even listed as an option in the DataTypeEnum Enumeration.

[twinnyfo]

This would be so much easier over the phone.

Dumb question: are you able to create a table the old fashioned way with an Attachment field?

[ADezii]

OK, mystery solved and it was due to my own stupidity. Whenever I work or, or assist with a Project, I always work with the lowest common denominator if possible. I am referring to working with an *.mdb Database unless the situation warrants an Upgrade. My Test DB was an *.mdb, and I am sure that I need to say no more. I thank you for your help and I do apologize for wasting some of your precious time!

[NeoPa]

Twinny.

Please do me a quick favour so I can proceed with my explorations. This is ADezii we're helping here so deserves (& gets) the 5 star treatment ;-)

Ctrl-G (Immediate Pane).
Type out "dbAttachment".
Shift-F2 (Go to definition).
Report on the structure of the reference from the library down to the item itself via any Types or whatever.

Separately, if it's a value within an ENum, include the value it represents.

[NeoPa]

Just to confirm, I've seen ADezii's latest post which led me to look using an AccDB database. Now I have the info I requested so please don't worry about that now.

I also realise you had already posted the value of dbAttachment in an earlier post. I'd seen it but not recognised the important value was included.

@ADezii.
Can we assume from that post that you are now able to reproduce the table using the VBA suggested by Twinny in post #6?

[twinnyfo]

And, a further question, "Will SQL now create a Table with an Attachment field?" Now THAT would be a bit ironical.....

:-o

[NeoPa]

I was planning to ask that question but felt, as you'd already given an unequivocal answer to it, that I ought not to ;-)

[ADezii]

Can we assume from that post that you are now able to reproduce the table using the VBA suggested by Twinny in post #6?

Yes, and thanks to you and twinnyfo for the 5-start treatment! Still cannot create it using DDL. I even tried DROPPING the Attachment Column and re-creating it with ADD COLUMN, but no cigar. Thanks again guys!

[twinnyfo]

Any time, my friend! I remember that some of my earliest learning on this forum came at your instruction, ADezii! So, if I was able to offer some hepp, I’m glad I could.

Always a pleasure to interact with all y’all!