Runtime error 3155 inserting to linked SQL tables from Access

Sorry, I should have mentioned at the outset that the really odd thing about
this is that the error is:
"ODBC--insert on a linked table 'Logins' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT permission denied on
object 'Logins', database 'data', owner 'dbo'.
(#229)"
Needless to say, it occurs at the statement
DoCmd.RunSQL sqlstr
That's why it's odd that it works OK when run directly from the code window,
but not when it is called from the autoexec macro. It is attempting to insert
just a single record.
Thanks
Alistair
"Van T. Dinh" wrote:

> Have you identified which line of code produced the error?
> Error 3155 is "Application-defined or object-defined error" so I suspect the
> error is in the statement:
>
I am using SQL authentication. The login/ password is not currently stored in
the dsn: the tables are either linked manually before opening the database,
or using a button which runs a macro from within the database. The odd thing
is that you could open the database with autoexec, and get the Insert error
message. If you then opened it again without autoexec, kept the tables linked
as they were, then ran the code manually it worked.
"Van T. Dinh" wrote:

> Are you using Windows Authentication or SQL Server Authentication?
> If the later, do you store the LoginID / Password in the ODBC DSN?
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:6E8412D8-15BE-47C0-83D0-07878DEC7921@.microsoft.com...
>
>
|||Thanks for your suggestions.
I added this code just before the code which attempts to Insert to the
database. The passthrough query works perfectly, but the Insert still fails.
A manual Insert attempted subsequently does work, however. I have also
discovered that if I re-link the table into the database, I am then unable to
Insert to it even manually, until I have closed and re-opened the database.
"Van T. Dinh" wrote:

> I does sound that when you execute the insert in the AutoExec, the database
> has not establish a connection to the SQL Server BE.
> Perhaps, you should run code to create a dummy recordset just to establish
> the connection to the BE before your insert action.
> Stefan Hoffman posted this procedure in another newsgroup
>
> ****
> Create a passthrough query, call it "qryLogin". Use a simple SELECT
> statement like
> SELECT * FROM dbo.Table WHERE 0=1
> ..
> This code should do the login:
> Dim db As DAO.Database
> Set db = CurrentDb
> db.QueryDefs.Item("qryLogin").Connect = _
> db.TableDefs.Item("linkedTable").Connect & _
> ";UID=username;PWD=password"
> db.OpenRecordset("qryLogin")
> ****
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:08E0025C-A6C9-4780-84F3-DDB878288B4F@.microsoft.com...
>
>
|||Refreshing the link immediately before the insert statements does seem to
work consistently, thanks. While the root cause of the insert problem is
still unknown, I am very grateful to have a working solution now.
Thanks
Alistair
"Van T. Dinh" wrote:

> I suspect that the Table Logins is still not linked correctly when the
> insert code is executed.
> Try using the RefreshLink method (check Access VB Help) on this Table and
> then 1 or 2 DoEvents statements beore your DoCmd.RunSQL.
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
> message news:6F567ABE-4307-4F49-A3D4-56CE7A0A6729@.microsoft.com...
>
>