Trouble with tables link from Access to SQL

[Tom Collins]

First, let me apologize for cross posting, but I wasn't sure exactly where
the problem is.

In a brand new access database I'm trying to link to some tables in an SQL
database that I own. If I do it via a DSN, it works fine. If I do it via a
DSNless connection, it's read only.

I believe I'm using the same method I successfully used before. I have tried
both a named user and a trusted connection (that's active directory, isn't
it?), both with the same results. I think I have my SQL setup for mixed
verification (I had to change the registry entry of LoginMode from 1 to 0).

This is the code I use to attach (with both versions of my connect string):
strConnectString = "ODBC;DRIVER={sql
server};SERVER=rumpunch;DATABASE=streetnames;UID=comdev;PWD=comdev

strConnectString = "ODBC;DRIVER={sql
server};SERVER=rumpunch;DATABASE=streetnames;Trusted_Connection=Yes"

CurrentDb.TableDefs.Delete "Streets"
Set tdf = CurrentDb.CreateTableDef("Streets", dbAttachSavePWD, "Streets",
strConnectString)
CurrentDb.TableDefs.Append tdf


I am able to link to a different DB on the same server successfully (using a
trusted connection), so I'm thinking it's a security issue. I'm listed as
the owner of both DB's, so I don't see what the problem would be.

Anyone have any ideas? Thanks.

Tom Collins

 

[Douglas J. Steele]

Since you're using Trusted Connection, I don't believe it's appropriate to
use the dbAttachSavePWD attribute.

 

[Tom Collins]

Actually, the problem was that I didn't have a unique index setup yet on the
SQL tables. This is required for updating.
Thanks anyway.

 

[Douglas J. Steele]

I thought you'd said you could join using a DSN and everything was fine.
That's why I didn't even suggest the unique index issue!