No Data Type Attachment

[tjcreekdoc]

All,

I am building my first Access database.

To help me build the tables, I import the data fields from an Excel spreadsheet. I need some of the imported data types to be Attachments. However, when I import the Excel spreadsheet, I do not have the option of Attachment. So to complete the import, I select Short Text. Once the import is complete, I open the table using Design View. I then select the Row and change the Data Type to Attachment for each Field Name I need to change. This is tedious and I can EASILY miss a few. Is there a better way?

tjcreekdoc

 

[Ranman256]

WhAt could possibly be in a excel file that has to go into an attachment field?

 

[The_Doc_Man]

If you are talking about somehow having associated a file with the spreadsheet (including Object Linking and Embedding), there are some issues to consider.

First, Access has limited physical storage capacity. By that I mean that the total of everything you put in one Access .MDB or .ACCDB file cannot exceed 2 Gb. The FASTEST and MOST RELIABLE way to fill up a limited file space is with files that have structural overhead (as is usually the case with any file other than Notepad format or its equivalent). Therefore, you need to know ahead of time how many different attachments you will need to store. One way you MIGHT do this is that you can define an OLE data element in a record.

However, there is another way to consider this task. You COULD have the attachment as a separate file in a folder related to your database location (e.g. a child folder). You could then store, in a text field, the name of the file. If the path name is not incredibly long, you might even be able to store the whole file spec (drive, path, name, type). Access itself won't do anything with that attachment, I'm sure. You have it for attaching to something else, right? So having the file spec ready for use when you need to attach it might be enough. And, if there is duplication such that the same attachment will be used for more than one "whatever" then by having the file spec instead of the full object means less duplicated data.

 

[tjcreekdoc]

The_Doc_Man,

Thank you for the quick reply!

First, I did not know there is a size limit on an Access Database. I need to ensure I don’t come close to this limit! I will look into your suggestion!


When a HDD is wiped clean or verified, I need to track a few things: the serial number, the date it was wiped/verified, the serial number of the HDD, and the signature of the person whom wiped it/verified it was wiped. I HAD planned to store their DocuSign signature in the database.


So, I just want to be sure..., when I select External Data>Excel>File Name of the Excel spreadsheet & Import the source data into a new table in the current database>Ok> the worksheet I want to import>next>First Row Contains Column Headings>Next>I select the field I want to have a data type as Attachment, I do not see Attachment as an option.

I have select one of the following:

Yes/No
Byte
Integer
Long Integer
Currency
Single
Double
Date with time
Short Text
Long Text
Hyperlink


So, after I select one of the above listed Data Types, I have to go back to the table, open it in Design View and change the Data Type to Attachment. There is no other way to import and Excel spreadsheet into Access and make a Data Type as Attachment?

Thank you,

Tjcreekdoc

I have attached a screen shot of the Data Type options I have when I import.

 

[The_Doc_Man]

There IS no such data type that you name in Access OR, for that matter, in Excel. To be sure, I tried to find that data type in my Ac2010 but it ain't there.

"Attachment" is an Outlook Concept (primarily), and Excel claims (but in so doing, lies) that IT has such things as data types. Excel actually has multiple data FORMATS, but individual Excel cells are nearly "faceless" with respect to data typing. You can easily interchange the formats without changing cell contents.

Access has OLE and hyperlink data types that let you embed (OLE) or externally reference (hyperlink) a file to be interpreted as a self-contained unit. Note that a hyperlink doesn't always have to reference a web document.

So either you can embed this signature as an OLE file in a database (and if it is a picture, the graphics content will make it a large file) OR you can keep the information in an external file with some arbitrary name on it (even just a letter followed by a number string) and then associate the file name to the appropriate record. If you keep the file in question external to the database, the only thing that counts against the size limit is the text of the referenced file's specification. The file itself, being external to the database, doesn't really count.

You have to decide which way you want to go before proceeding too much farther. It will make a big difference in how you have to proceed. Having said that, I will add that the rest of what you are trying to achieve seems perfectly possible. You just need to make a design decision up front so that you won't have headaches later if you need to retrofit to something else.

 

[The_Doc_Man]

I'm going to retract a part of my statement. I did manage to find an "attachment" data type but it seemed to be Access Web-related. It is not, however, available to me in my home copy of Access. Since I am now retired, I no longer have a terminal available to me with a newer version of Access so am limited to what's on my home systems.

My comments, however, appear to be correct in regard to handling attachments. They would still be space eaters.

 

[isladogs]

I'm going to retract a part of my statement. I did manage to find an "attachment" data type but it seemed to be Access Web-related. It is not, however, available to me in my home copy of Access. Since I am now retired, I no longer have a terminal available to me with a newer version of Access so am limited to what's on my home systems.

My comments, however, appear to be correct in regard to handling attachments. They would still be space eaters.

Hi Doc

The attachment datatype was introduced in Access 2007.
It applies to both desktop and web databases.

However I never use it due to both space issues and its lack of portability to other programs.
In fact its on my wish list of features that should be deprecated along with multi valued fields and embedded macros

 

[The_Doc_Man]

Thanks, Colin, for supporting my contention that it is a space-eater.

For some reason, my version of Ac2010 doesn't list "attachment" as a data type. Or else I'm asking the wrong question. (Wouldn't be the first time.)

 

[isladogs]

Thanks, Colin, for supporting my contention that it is a space-eater.

For some reason, my version of Ac2010 doesn't list "attachment" as a data type. Or else I'm asking the wrong question. (Wouldn't be the first time.)

Back when A2007 first came out, I briefly tried the Attachment datatype & sure enough the datafile size rapidly spiralled out of control.
Partly because of that & also deciding to upsize to SQL Server, it was a one off and short lived experiment

This is the list of datatypes I have in Access 2010:

Looking at that list, I'd also like to see both calculated fields & lookup fields in tables consigned to the dustbin of history together with split forms & navigation forms.

I think I'll start a thread on this idea ...