Accessing MS Access Databases from SQL Server

I recently installed WinTV 8.5, and after scanning TV channels, I noticed that editing channel numbers was incredibly cumbersome.

According to the FAQ, it would involve a lot of scrolling, and 4 clicks – *per channel*. I also noticed a couple other peculiarities in the UI, such as the EPG viewer, which made me research where the program’s data is stored.

Actually it’s all in the FAQ: it’s an MS Access database called hcwChanDB_5.mdb residing in a directory set during installation.

To save your channel database go to:

C:\Users\Public\WinTV\Channel Database (Windows 7 and Vista)

C:\Documents and Settings\All Users\Shared Documents\WinTV\Channel Database (Windows XP)

Copy the  hcwChanDB_5 file to a place  for safe keeping. To replace saved Channels just copy HcwChanDB_5 back to Channel Database Dir.

When I double-click the .mdb file, a nice reminder shows up that I only have a Test edition of MS Access, and not the fully activated Professional edition. Essentially this means that I can view the data, but cannot change it.

Time to start up SSMS, link the Access database and browse the data from SSMS. … which turned out to be not so easy.

Linked Server to MS Access via ODBC System DSN

WinTV creates an ODBC System DSN called HCW_mdb, and you can easily create a linked server in SSMS referencing this DSN. Running

exec sp_catalogs 'hcw'

on the linked server returns the path names of the MS Access databases, but using them as catalog names in a SELECT * FROM statement only raises an error message

OLE DB provider “MSDASQL” for linked server “hcw” returned message “[Microsoft][ODBC Microsoft Access Driver] Syntaxfehler (fehlender Operator) in Abfrageausdruck ‘`Tbl1002`.`id` `Col1005`’.”.
Msg 7321, Level 16, State 2, Line 4
An error occurred while preparing the query “SELECT `Tbl1002`.`id` `Col1005`,`Tbl1002`.`service_id` `Col1006`,`Tbl1002`.`preferred_name` `Col1007`,`Tbl1002`.`preferred_number` `Col1008`,`Tbl1002`.`description` `Col1009`,`Tbl1002`.`audio_lang` `Col1010`,`Tbl1002`.`subtitle_lang` `Col1011`,`Tbl1002`.`audio_mode` `Col1012`,`Tbl1002`.`irblaster_data` `Col1013`,`Tbl1002`.`channel_type` `Col1014`,`Tbl1002`.`language` `Col1015`,`Tbl1002`.`genre` `Col1016`,`Tbl1002`.`duplicate_channel_id` `Col1017`,`Tbl1002`.`duplicate_priority` `Col1018`,`Tbl1002`.`blocked_flag` `Col1019`,`Tbl1002`.`last_used` `Col1020`,`Tbl1002`.`epg_source` `Col1021`,`Tbl1002`.`preferred_minor` `Col1003` FROM `D:\data\WinTV\Channel Database\hcwChanDB_5`.`hcwChannels` `Tbl1002`” for execution against OLE DB provider “MSDASQL” for linked server “hcw”.

However, you can query the database using OPENQUERY()

select * from openquery(hcw, 'select * from hcwChannels')

where hcwChannels is one of the tables in the MS Access database.

Linked Server to MS Access via OLEDB provider

I kept searching, and fortunately found a forum post on social.MSDN in which the last comment provided a hint to another workable solution:

Note: The same issue is present using Sql Server 2012 RC0, and still forcing the use of the deprecated MSDASQL provider (ms ole db provider for odbc drivers) as a workaround.

Update: besides using msdasql, it works, in some cases, to do the following before creating the linked server:
right-click and select “properties” for “Microsoft.ACE.OLEDB.12.0” under Server objects, Linked servers, Providers in SSMS.  Check “nested queries” and “allow inprocess.”   Then create the linked server for access accdb using the ACE.OLEDB.12.0 provider, with the access filename under data source.

So I checked the OLEDB provider settings and changed them accordingly. Then I created a linked server using Microsoft.ACE.OLEDB.12.0 referencing the hcwChanDB_5.mdb database.

While I could not enumerate the catalogs of the new linked server, the database could now be queried using standard SELECT statements such as

select * from hcwole...hcwChannels

I could now create a SYNONYM for each table inside the MS Access database.

See my WinTV repository on GitHub for the SQL files.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.