I have connected to various data sources in the past using ODBC, including Access, SQL Server, and DB2 databases. For the most part using ODBC is easy, since all you really need is the proper connection string and authentication. Once you have those, then you can easily run queries against the data source. I recently ran into an issue with inserting records into an Excel worksheet.
I had created the Excel worksheet and inserted the column headers, which I will call fields in this post. Once I had created the Excel workbook with the one worksheet, I attempted to insert a record into the sheet using vbscript and ODBC. When I executed the Insert SQL query, I continuously received an error. After doing some research, I solved my problem and I’ll explain what the issue was and how I fixed it in this post.
Syntax Error When Inserting Records
I haven’t really inserted records into an Excel sheet using ODBC before, so it was a new experience for me. The good news is that it isn’t much different than inserting records into a database. Once you have the proper connection string, the SQL queries are the same as with databases.
The problem was when I executed an insert SQL statement against the worksheet. The following ODBC error occurred:
I modifed the connection string, and the SQL query, but with no luck. I continued to get the above error. I decided to do some research.
The Worksheet and Code
I created an Excel workbook with one sheet called User. This sheet had the following columns:
- ID
- Password
- FirstName
- LastName
The Excel file was saved on the root of my C drive during my testing. So the full path of the file was C:\Users.xls.
The vbscript code for both the connection string and SQL query looked like:
Dim strQuery
strQuery = “INSERT INTO [User$] (ID, Password, FirstName, LastName) VALUES (2, ‘Password1’, ‘John’, ‘Smith’)”
While the above code looks fine, it produced an error when using ODBC and ADO to insert the record into the excel worksheet. I had created a procedure that executed the query, which worked against various other data sources, so I knew the problem was related to one of the two lines shown above. I decided to do some research online to find a solution.
The Solution
I simply entered the error message into Google and came up with several results. One of the results provided the answer: reserved keywords.
There are several keywords that you shouldn’t use for field names. If you do use them, it is recommended that you enclose the field names in square brackets “[]” within the query. After looking at my list of fields, I noticed that one name is probably a keyword. The field name Password is probably reserved.
I changed the field name Password to UserPassword and then reran the vbscript code. The code ran without any problems and the record was inserted into the Excel worksheet.
If you receive the same error message in the future when inserting a record into any type of data source, check to make sure you aren’t using any keywords as a field name. If you are, you should either rename the field or enclose the name in square brackets.