self Aware ODBC...?

[Me]

I have a duly annoying ODBC problem:
I make a new, blank Access 2k database, and add a single ODBC link to
MS SQL Server table, using a DSN login which has full access rights to the data.
If I remain in the same session, everything works. If I exit the MDB and
return to it,
the table is suddenly not allowing record addition! (I try to add a
record in datasheet mode)- up jumps: SQL Error #229, INSERT permission
denied, on object XXX, database XXX, Owner DBO.

this happens every time, unless I open a new MDB. Both in Access 97
and 2000.

I have done some ODBC VBA tinkering, and found that if I set the
..connect string of that link's Tabledef object to "", and do a
tdf.RefreshLink
it works suddenly [how come it connects, if it has no connection
string anymore? only access Knows...]
still this only works until the database closes.

[short 2 minute boxing round with access goes here, knockout in 10
lines of code to Access of course]

Actually, now even that doesn't work anymore. (perhaps Access doesn't
like it when I post things about it).
what have I done to deserve this?

Thanks

HH.

[Brian]

"Me" <he****@lycos.com> wrote in message
news:2d**************************@posting.google.c om...

I have a duly annoying ODBC problem:
I make a new, blank Access 2k database, and add a single ODBC link to
MS SQL Server table, using a DSN login which has full access rights to the data. If I remain in the same session, everything works. If I exit the MDB and
return to it,
the table is suddenly not allowing record addition! (I try to add a
record in datasheet mode)- up jumps: SQL Error #229, INSERT permission
denied, on object XXX, database XXX, Owner DBO.

this happens every time, unless I open a new MDB. Both in Access 97
and 2000.

I have done some ODBC VBA tinkering, and found that if I set the
.connect string of that link's Tabledef object to "", and do a
tdf.RefreshLink
it works suddenly [how come it connects, if it has no connection
string anymore? only access Knows...]
still this only works until the database closes.

[short 2 minute boxing round with access goes here, knockout in 10
lines of code to Access of course]

Actually, now even that doesn't work anymore. (perhaps Access doesn't
like it when I post things about it).
what have I done to deserve this?

Thanks

HH.

Maybe you have SQL Server authentication in Mixed mode, and you didn't tick
the "Save Password" box when you created the link. Thus, when you first
create the link, Access uses the user name/password that you entered to
authenticate against SQL Server, but later, Access having forgotten the user
name/password, SQL Server uses your Windows login to authenticate.
Different SQL Server user, different permissions. Just a thought.