MS ACCESS binary field and make table query

The MS ACCESS binary field is basically a BLOB field if you familiar with DB2 or DEC terminology. It is simply a field that can hold a chunk of data of some kind of format. It can hold video, audio, text ... etc. Or you can think of it as un-typed field.

When using MS ACCESS (till ver. 2007) through its normal user interface to create a table, ACCESS do not offer users the option of creating fields of type 'binary' even though it does offer types like OLE object that allows users to store picture, video, ... etc. Binary fields, however, can be create via making table queries which I discovered by accident.

For more information about the BLOB and MS ACCESS binary field please see:

Wikipedia on BLOB
MS ACCESS binary equivalence in other DB (MS article).
Binaries in MS SQL server (MS article)
How binary may be used in VB/ACCESS (generation-nt.com)

You can create a binary field by using [Yr(yyyy)=] in a make table query, MS ACCESS will create a binary field for it. It seems that the binary type is the default for make table query unless the ACCESS can derived the type from other information -for example, the type of the data source. In general, the SELECT statement in the make table query will provide these information. So the SELECT Fld1, Fld2 ... FROM SrcTbl INTO TblCreated ... will create fields based on the type of FldN in the SrcTbl. It is when you use the syntax: [Yr(yyyy)=]  in a create table query that the ACCESS will run into troubles on determine the correct field type. You can overcome this by providing additional information in the form of the type of a function. For example, with CLng([Yr(yyyy)=]), ACCESS will make the field a type of long-integer.

Once the field is created, it seems to me, the field will take any kind of value you inserted into it even though the data may not show in the way you expected. For example, if you insert a integer to the binary field, it may look like you inserted an ASCII code and shown as a character.

I do, however, have trouble to understand the failure of some of my queries that create a binary field. One of my queries is a complex query that invoke several other queries as its source. The query contains prompting syntax (i.e. []) for various purposes. Some of them are used to filter the data source and one of them is used to create the binary field. That query successfully create the table with binary field when all records are filtered out. However, when valid records were to inserted into the created table via that same query, error was reported that there are type conversion errors and the binary field was inserted with NULL. The value to be inserted was the value assigned to the prompting syntax in the form of integer. This is what bring me to the investigation of the ACCESS binary field originally. Unfortunately, as mentioned earlier, binary fields seem to be able to take in any type of data except in my particular cases (There are actually two very similar queries that failed).

Life goes on, so I will leave this mystery for someone else to solve.

RODBC sqlSave() autonumber auto-increase problem

This is a quick note on R.

The RODBC package for R-project is a popular add-on to access ODBC database. The sqlSave() function is a common method used to save a data.frame to an ODBC table. For an ODBC table with autonumber or auto-increase fields, this function reports errors. Turning on the verbose option of that function, it is observed that sqlSave() constructed a SQL statement that ALWAYS intended to insert values to all table fields. With this SQL statement, user are forced to assign a value for the auto-increase field, which, by its own definition, should not be assigned a value from external source.

For some odd database implementation, you might be able to assign a null value to the auto-increase field and got it to work. But, as far as I know, the MS ACCESS would not take that.

I was trying to find the source code for the RODBC package but was unsuccessful. I do found that if I wrote a function so that it write the data.frame to database using the sqlQuery() function, this can be overcome. However, this may take longer to process the data. The idea solution is to modify the sqlSave() function so that it is more flexible.

I also run into the problem with sqlSave() when dealing with two MS ACCESS memo fields. Again, this can be solved with the sqlQuery() function.

MySql MyOdbc MS ACCESS server has gone away error

While using MS ACCESS to connect to MySQL database through MyOdbc, I constantly got the following error: 'server has gone away error #2006'.

After searching the MySql site with no success, I finally find the solution at: SiteGround Forum posted by hwj.

Basically, when create DSN in Windows using ODBC configuration tool, you need to enable the option auto-reconnect immediately before you click OK to exit. According to hwj, if you did not do it while you creating the DSN, you will have to start over.

This works for me!

Hopefully, with this link, it will promote that article in Google search.