binary data type

[Guest]

What is it?

Why would one use it?

Thanks for any insight.

 

[Graham R Seach]

Dave,

Access doesn't have a datatype called Binary, but it does have one called
OLE Object. It is used for storing Binary Large OBjects (BLOB), which is
just a fancy word for binary files, such as pictures, program files (such as
msaccess.exe), and so on.

I use the OLE Object datatype to store small icon files that I want to
distribute with my applications. I unpack these files from the database to
disk, so I know they will always be available, no matter where the database
is installed.

In Access 2003 and earlier, the OLE Object datatype is fraught with
problems, not the least of which is the potential for corruption.
Experienced developers tend not to use OLE Object fields too much. Access
2007 has a better datatype, called Attachment.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

One would think Access doesn't have a datatype called binary but I'm looking
at a table with a number of fields whose datatype says "Binary."

So, somehow, it was put there, despite it not appearing on any menu or
reference material.

 

[Guest]

Point of clarification: according to this MSFT site, Access doesn't use
binary data types but recognizes them when used from other db applications:
http://msdn.microsoft.com/library/d.../html/acdatComparisonDataTypes_HV05186571.asp

So, the question really is: what is a binary data type, not how is it used
in Access?

I know that a base-2 number system is a binary number system but that
doesn't really tell me much. Anyone ever see this in Access before?

 

[Allen Browne]

Graham, I'm interested in this too.

You think the Attachment is a better data type than the Hyperlink? Even
though it's a complex data type?

And while the table design interface does not let you create a field of type
Binary, it seems like you can create a Binary type field by using:
- BINARY (size) in a DDL query,
- dbBinary in DAO code, or
- adVarBinary in ADOX code.
But I have no experience with this data type, and don't know when I would
should it.

 

[Guest]

The table I'm looking at appears to have been created via a make table query,
which query's SQL is the following:

SELECT tblELR.ID, tblELR.[Effective Date], "N/A" AS [Asset Number],
tblELR.Description, tblELR.Vendor, "N/A" AS [Manufacturer Name], [Amount
DR]-[Account CR] AS Cost, Null AS CurrentUnits, Null AS [Deprn Amount], Null
AS [Deprn Reserve], Null AS Nbv, Null AS [Life In Months], "N/A" AS [Location
Key], "N/A" AS [Location Description], "One-time expense" AS [Major
Category], "N/A" AS [Minor Category], "N/A" AS [Serial Number], " N/A" AS
[Tag Number], Null AS [Ytd Deprn], " One-time service" AS [Asset Type], "N/A"
AS [Book Type Code], tblELR.Corp, tblELR.Expense, tblELR.[Manager 1],
tblELR.[Manager 2], tblELR.Account, "N/A" AS [Deprn Expense Account], "N/A"
AS [Deprn Reserve Account], tblELR.Entity, "N/A" AS [Tax Location Code],
"N/A" AS [Accounting Key], "N/A" AS [Asset Key], "N/A" AS [Project Number],
tblELR.[Effective Date] AS [Creation date], tblELR.[Invoice Date],
tblELR.Invoice, Null AS PONumber, "N/A" AS [Payable Batch Name], "ELR" AS
[Feeder System Name], tblELR.[Amount DR], tblELR.[Account CR], 2 AS
ExpenseType, 0 AS MPRLineID INTO [ELR Records to Add]
FROM tblELR;

Nothing in that suggests to me a reason for this Binary data type to appear.

 

[Allen Browne]

You would expect the fields in the new table to be the same as those in
tblELR.

Which field is the Binary?
And what type was it in tblELR?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The table I'm looking at appears to have been created via a make table
query,
which query's SQL is the following:

SELECT tblELR.ID,
tblELR.[Effective Date],
"N/A" AS [Asset Number],
tblELR.Description,
tblELR.Vendor,
"N/A" AS [Manufacturer Name],
[Amount DR]-[Account CR] AS Cost,
Null AS CurrentUnits,
Null AS [Deprn Amount],
Null AS [Deprn Reserve],
Null AS Nbv,
Null AS [Life In Months],
"N/A" AS [Location Key],
"N/A" AS [Location Description],
"One-time expense" AS [Major Category],
"N/A" AS [Minor Category],
"N/A" AS [Serial Number],
" N/A" AS [Tag Number],
Null AS [Ytd Deprn],
" One-time service" AS [Asset Type],
"N/A" AS [Book Type Code],
tblELR.Corp,
tblELR.Expense,
tblELR.[Manager 1],
tblELR.[Manager 2],
tblELR.Account,
"N/A" AS [Deprn Expense Account],
"N/A" AS [Deprn Reserve Account],
tblELR.Entity,
"N/A" AS [Tax Location Code],
"N/A" AS [Accounting Key],
"N/A" AS [Asset Key],
"N/A" AS [Project Number],
tblELR.[Effective Date] AS [Creation date],
tblELR.[Invoice Date],
tblELR.Invoice, Null AS PONumber,
"N/A" AS [Payable Batch Name],
"ELR" AS [Feeder System Name],
tblELR.[Amount DR], tblELR.[Account CR],
2 AS ExpenseType,
0 AS MPRLineID
INTO [ELR Records to Add]
FROM tblELR;

 

[Graham R Seach]

Hi Allen,

<<...Attachment is a better data type than the Hyperlink>>
Sorry mate, but I don't follow your question. Attachment and Hyperlink are
nothing alike.

If you meant Attachment vs OLE, then yes, if what we're told about it's
compatibility and stability is true, then I do prefer Attachment.

I stand corrected with the Binary datatype. I didn't know Jet supported it.
I just added a Binary field to an existing table via DDL, and entered some
character data. Given that I could enter *character* data, I have no idea
what it could actually be storing the data as. Like you (and I guess Dave),
I have no idea what I'd use it for.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Guest]

In the new table, the following fields show data type binary:

CurrentUnits
Deprn Amount
Deprn Reser
NB
Life In Months
Ytd Deprn
PONumber

Now that I look at the SQL, it appears that these fields are newly made
fields, which didn't appear in the original table from which this new table
is created. The SQL for Current Units, for example, reads Null AS Current
Units

Could that Null statement be forcing the binary data type? Is this a bug?

 

[Allen Browne]

Graham, thanks for clarifying that attachments are always embedded.

I am guessing the Binary data type is a byte array.

Dave, yes. Access does treat an unknown field type as dbBinary by default.
You can demonstrate that by creating a query with the field:
CurrentUnits: Null
Save it as Query1.
Then in the Immediate Window (Ctrl+G):
? CurrentDb.QueryDefs("Query1").Fields("CurrentUnits").Type
The result will be 9, which is dbBinary.

You can fool Access into treating it as a different data type with:
CurrentUnits: IIf(True, Null, 0)
The True is always true, so the value is always Null, but the alternative is
enough to give Access a clue about the intended data type.

The other alternative is to create the table with the fields defined as you
want. Then use an Append query to populate it instead of the Make Table.

 

[Guest]

Wow Allen, thanks.

The Indian Ocean must make you smart.

 

[Graham R Seach]

Hi Allen,

I can't guarantee that Attachments are "always" embedded, but I will do some
research to find out.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------