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.
Showing posts with label ODBC. Show all posts
Showing posts with label ODBC. Show all posts
Monday, February 6, 2012
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.
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.
Monday, August 1, 2011
MS Word mail merge using MS Access LIKE operator
Mail merge refer to the process of using/linking data from an external source to fill out an otherwise static document. An example can be a formal letter that send to all board members. The content of the letter is the same (static), but the title/address-block and greeting can be pulled from a database so that letters are with proper greeting and title of the addressed board member.
For most part, Microsoft's (MS) word processing (Word) and databbase (Access) works well with each other. However, under one situation, the normal way of linking MS Word and MS Access failed to work correctly. This article is to report a work around for that situation.
In general, database can hold a lot of information and it is not uncommon that a mail merge incident only need a small part of those information stored in the database. For example, the database may hold the contact information for all employee, while the letter is addressed only to board members. In database, queries are used to filtered out unwanted information and MS Word can use queries to obtained the needed information. For most of queries, MS Word worked well with MS Access.
The Problem:
When a query uses the LIKE operator/keyword, the normal way of linking MS Word and Access could fail. The Word will return results that would be produced with the '=' operator instead of the 'LIKE' operator. This problem had been reported dated back to 2005 and still exist today (Aug. 2011) in Word 2007.
The work around:
To work around the problem, we can have Word linked to Access through an ODBC (Open Database Connectivity), which defines a standard protocol to be implemented by middle-layer software to allow applications (e.g. Word) to connect to various database.
To proceed, in Windows environment, you proceed by defining a DSN (Data Source Name) through the ODBC manager ( -Start -Control Panel -Administrative Tools -Data Sources) with 'Driver do Microsoft Access' as the middle-layer software and the Access database file (.mdb) as the database file.
Once the DSN is in place, you can now use it as the mail merge data source. In Word, when prompt to select the data source, click the 'New Source...' button, Select ODBC DSN and click Next. Select the DSN you created, click Next, select the query using the LIKE keyword, click Next and you are prompt to save these settings in a data connection file to be used later. Click 'Finish'.
Now you can highlight the connection file and click open. The query should work correctly.
For most part, Microsoft's (MS) word processing (Word) and databbase (Access) works well with each other. However, under one situation, the normal way of linking MS Word and MS Access failed to work correctly. This article is to report a work around for that situation.
In general, database can hold a lot of information and it is not uncommon that a mail merge incident only need a small part of those information stored in the database. For example, the database may hold the contact information for all employee, while the letter is addressed only to board members. In database, queries are used to filtered out unwanted information and MS Word can use queries to obtained the needed information. For most of queries, MS Word worked well with MS Access.
The Problem:
When a query uses the LIKE operator/keyword, the normal way of linking MS Word and Access could fail. The Word will return results that would be produced with the '=' operator instead of the 'LIKE' operator. This problem had been reported dated back to 2005 and still exist today (Aug. 2011) in Word 2007.
The work around:
To work around the problem, we can have Word linked to Access through an ODBC (Open Database Connectivity), which defines a standard protocol to be implemented by middle-layer software to allow applications (e.g. Word) to connect to various database.
To proceed, in Windows environment, you proceed by defining a DSN (Data Source Name) through the ODBC manager ( -Start -Control Panel -Administrative Tools -Data Sources) with 'Driver do Microsoft Access' as the middle-layer software and the Access database file (.mdb) as the database file.
Once the DSN is in place, you can now use it as the mail merge data source. In Word, when prompt to select the data source, click the 'New Source...' button, Select ODBC DSN and click Next. Select the DSN you created, click Next, select the query using the LIKE keyword, click Next and you are prompt to save these settings in a data connection file to be used later. Click 'Finish'.
Now you can highlight the connection file and click open. The query should work correctly.
Subscribe to:
Posts (Atom)