By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,069 Members | 1,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,069 IT Pros & Developers. It's quick & easy.

How to Create an Attachment Field in a Table

ADezii
Expert 5K+
P: 8,692
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.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "CREATE TABLE [Students] ([ID] COUNTER,[Last Name] VARCHAR(50), " & _
  4.          "[First Name] VARCHAR(50), [E-mail Address] VARCHAR(50), [Student ID] VARCHAR(20), " & _
  5.          "[Level] VARCHAR(30), [Room] VARCHAR(20), [Date of Birth] DATETIME, [ID Number] VARCHAR(255), " & _
  6.          "[Home Phone] VARCHAR(25), [Mobile Phone] VARCHAR(25), [Address] VARCHAR(255), " & _
  7.          "[City] VARCHAR(50), [State/Province] VARCHAR(50), [ZIP/Postal Code] VARCHAR(15), " & _
  8.          "[Country/Region] VARCHAR(50), [Web Page] MEMO, [Notes] MEMO, [Attachments] ATTACHMENT);"
  9.  
  10. CurrentDb.Execute strSQL, dbFailOnError
  11. RefreshDatabaseWindow
Jun 3 '20 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. Val   Data Type
  2. 1     dbBoolean
  3. 2     dbByte
  4. 3     dbInteger
  5. 4     dbLong
  6. 5     dbCurrency
  7. 6     dbSingle
  8. 7     dbDouble
  9. 8     dbDate
  10. 9     dbBinary
  11. 10    dbText
  12. 11    dbLongBinary
  13. 12    dbMemo
  14. 15    dbGUID
  15. 16    dbBigInt
  16. 17    dbVarBinary
  17. 18    dbChar
  18. 19    dbNumeric
  19. 20    dbDecimal
  20. 21    dbFloat
  21. 22    dbTime
  22. 23    dbTimeStamp
  23. 101   dbAttachment
  24. 102   dbComplexByte
  25. 103   dbComplexInteger
  26. 104   dbComplexLong
  27. 105   dbComplexSingle
  28. 106   dbComplexDouble
  29. 107   dbComplexGUID
  30. 108   dbComplexDecimal
  31. 109   dbComplexText
  32. 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.

Share this Question
Share on Google+
21 Replies


NeoPa
Expert Mod 15k+
P: 31,761
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.
Jun 4 '20 #2

twinnyfo
Expert Mod 2.5K+
P: 3,482
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.
Jun 4 '20 #3

twinnyfo
Expert Mod 2.5K+
P: 3,482
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:

Expand|Select|Wrap|Line Numbers
  1. Val   Data Type
  2. 1     dbBoolean
  3. 2     dbByte
  4. 3     dbInteger
  5. 4     dbLong
  6. 5     dbCurrency
  7. 6     dbSingle
  8. 7     dbDouble
  9. 8     dbDate
  10. 9     dbBinary
  11. 10    dbText
  12. 11    dbLongBinary
  13. 12    dbMemo
  14. 15    dbGUID
  15. 16    dbBigInt
  16. 17    dbVarBinary
  17. 18    dbChar
  18. 19    dbNumeric
  19. 20    dbDecimal
  20. 21    dbFloat
  21. 22    dbTime
  22. 23    dbTimeStamp
  23. 101   dbAttachment
  24. 102   dbComplexByte
  25. 103   dbComplexInteger
  26. 104   dbComplexLong
  27. 105   dbComplexSingle
  28. 106   dbComplexDouble
  29. 107   dbComplexGUID
  30. 108   dbComplexDecimal
  31. 109   dbComplexText
  32. 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.
Jun 4 '20 #4

ADezii
Expert 5K+
P: 8,692
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.
Jun 4 '20 #5

twinnyfo
Expert Mod 2.5K+
P: 3,482
Well, then, you can always pass along the VBA to perform these tasks as text:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub CreateNewTable()
  5. On Error GoTo EH
  6.     Dim db  As DAO.Database
  7.     Dim tdf As DAO.TableDef
  8.     Dim fld As DAO.Field
  9.  
  10.     Set db = CurrentDb()
  11.     Set tdf = db.CreateTableDef("tblTest")
  12.     With tdf
  13.         Set fld = .CreateField("tblID", dbLong)
  14.         .Fields.Append fld
  15.         .Fields.Refresh
  16.         Set fld = .CreateField("Field1", dbText, 30)
  17.         .Fields.Append fld
  18.         .Fields.Refresh
  19.         Set fld = .CreateField("Field2", dbAttachment)
  20.         .Fields.Append fld
  21.         .Fields.Refresh
  22.     End With
  23.  
  24.     With db.TableDefs
  25.         .Append tdf
  26.         .Refresh
  27.     End With
  28.  
  29.     Set tdf = Nothing
  30.     Set db = Nothing
  31.  
  32. Exit Sub
  33.  
  34. EH:
  35.     MsgBox Err.Number & " - " & Err.Description
  36. 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....
Jun 4 '20 #6

ADezii
Expert 5K+
P: 8,692
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.
Jun 4 '20 #7

twinnyfo
Expert Mod 2.5K+
P: 3,482
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.
Jun 4 '20 #8

ADezii
Expert 5K+
P: 8,692
Unless I am missing something, it appears as though an Attachment Field cannot be created via VBA also (see Code reference).
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As dao.Database
  2. Dim tdfNew As dao.TableDef
  3. Dim fldNew As dao.Field
  4.  
  5. CurrentDb.TableDefs.Delete "Demo"
  6.  
  7. Set MyDB = CurrentDb
  8.  
  9. Set tdfNew = MyDB.CreateTableDef("Demo")
  10.  
  11. With tdfNew
  12.   Set fldNew = .CreateField("ID", dbLong)           'Works
  13.                .Fields.Append fldNew
  14.                .Fields.Refresh
  15.   Set fldNew = .CreateField("FName", dbText, 50)    'Works
  16.                .Fields.Append fldNew
  17.                .Fields.Refresh
  18.   Set fldNew = .CreateField("LName", dbText, 50)    'Works
  19.                .Fields.Append fldNew
  20.                .Fields.Refresh
  21.   Set fldNew = .CreateField("MI", dbText, 1)        'Works
  22.                .Fields.Append fldNew
  23.                .Fields.Refresh
  24.   '********* The assignments to fld do NOT work *********
  25.   'Set fldNew = .CreateField("My_Files", dbAttachment)
  26.                '.Fields.Append fldNew
  27.                '.Fields.Refresh
  28.   'Set fldNew = .CreateField("My_Files", 101)
  29.                '.Fields.Append fldNew
  30.                '.Fields.Refresh
  31.   '******************************************************
  32. End With
  33.  
  34. MyDB.TableDefs.Append tdfNew
  35. MyDB.TableDefs.Refresh
  36.  
  37. RefreshDatabaseWindow
Jun 4 '20 #9

twinnyfo
Expert Mod 2.5K+
P: 3,482
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:

Attached Images
File Type: png References.png (18.0 KB, 55 views)
Jun 4 '20 #10

ADezii
Expert 5K+
P: 8,692
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.
Jun 4 '20 #11

twinnyfo
Expert Mod 2.5K+
P: 3,482
Yeah - grasping at straws. Like I say, My demo worked perfectly.
Jun 4 '20 #12

twinnyfo
Expert Mod 2.5K+
P: 3,482
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!
Jun 4 '20 #13

ADezii
Expert 5K+
P: 8,692
Still no cigar, very strange. dbAttachment is not even listed as an option in the DataTypeEnum Enumeration.
Jun 4 '20 #14

twinnyfo
Expert Mod 2.5K+
P: 3,482
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?
Jun 4 '20 #15

ADezii
Expert 5K+
P: 8,692
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!
Jun 4 '20 #16

NeoPa
Expert Mod 15k+
P: 31,761
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.
Jun 4 '20 #17

NeoPa
Expert Mod 15k+
P: 31,761
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?
Jun 4 '20 #18

twinnyfo
Expert Mod 2.5K+
P: 3,482
And, a further question, "Will SQL now create a Table with an Attachment field?" Now THAT would be a bit ironical.....

:-o
Jun 4 '20 #19

NeoPa
Expert Mod 15k+
P: 31,761
I was planning to ask that question but felt, as you'd already given an unequivocal answer to it, that I ought not to ;-)
Jun 4 '20 #20

ADezii
Expert 5K+
P: 8,692
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!
Jun 4 '20 #21

twinnyfo
Expert Mod 2.5K+
P: 3,482
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, Im glad I could.

Always a pleasure to interact with all yall!
Jun 4 '20 #22

Post your reply

Sign in to post your reply or Sign up for a free account.