MSAccess doesn't see sql2005 dataset

Question

I have a sql2005 setup on a windows7 machine. I am able to see the dataset in SSMS, but am unable to create a connection string that will see the dataset from MSAccess. I am able to connect to the MASTER dataset, but the object dataset is unknown to access.

I am using Windows authentication where the local user is a sysadmin to the object dataset.

Any ideas?

Answer 1

First off, for use from Access, it's easiest to set up a DSN. When you do that, you indicate the default database. In the native client (which I use with SQL 2008 R2), it's on the third page of options for setting up the DSN.

Once you have your linked tables set up with the DSN, you can then convert them to DSN-less connections using Doug Steele's handy code.

Answer 2

It turns out that I was able to create a dsn in linking the tables to Access. That worked find. But when it came to writing adodb code, it failed every time. Apparently, the SP! from Windows 7 clobbered the adodb object , and there is no 'fix' for it. There is some literature on the web for this, and itthe problem has been encountered before, but as of this writing, so satisfactory solution has been provided.