Runtime error 3155 inserting to linked SQL tables from Access

I have suddenly started getting a runtime error 3155 when attempting to
insert new rows into a linked SQL (2000 on Windows 2003 server) database
table from Access (2002 under XP).
A new bit of code in the Access database attempts to insert a database login
record into table Logins, as follows
Set oNet = CreateObject("Wscript.Network")
If Err.Number <> 0 Then
MsgBox "Unable to create Wscript.WshNetwork object"
Wscript.Quit Err.Number
End If
sqlstr = "Insert into Logins (username, workstation, intime) values ( '" &
oNet.UserName & "','" & oNet.computername & "', now())"
DoCmd.RunSQL sqlstr
Oddly the code works perfectly if it is run using the Run sub button from a
vb window in Access, but it produces the error above if it is run from
another function called from the autoexec macro.
ThanksHave 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:
Set oNet ...
BTW, if you want to continue the code after the error (which you are trying
to check with the If statement), you need to use the statement:
On Error Resume Next
before the Set statement.
In addition, in the Access IDE, use the Menu Tools / Options / General tab /
Error trapping pane to see the error-trapping options (you need "Break on
Unhandled Errors" option)
If the code works in Access IDE but doesn't work when you call it via
AutoExec / function, I suspect that it is a timing issue when Access start
... I would try a few DoEvents statement to allow the OS completes some
pending jobs like:
DoEvents
DoEvents
On Error Resume Next
'{Your code here}
HTH
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:C90D9F78-3421-407D-9906-60E7CC101383@.microsoft.com...
>I have suddenly started getting a runtime error 3155 when attempting to
> insert new rows into a linked SQL (2000 on Windows 2003 server) database
> table from Access (2002 under XP).
> A new bit of code in the Access database attempts to insert a database
> login
> record into table Logins, as follows
> Set oNet = CreateObject("Wscript.Network")
> If Err.Number <> 0 Then
> MsgBox "Unable to create Wscript.WshNetwork object"
> Wscript.Quit Err.Number
> End If
> sqlstr = "Insert into Logins (username, workstation, intime) values ( '" &
> oNet.UserName & "','" & oNet.computername & "', now())"
> DoCmd.RunSQL sqlstr
> Oddly the code works perfectly if it is run using the Run sub button from
> a
> vb window in Access, but it produces the error above if it is run from
> another function called from the autoexec macro.
> Thanks|||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 inser
t
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 t
he
> error is in the statement:
>|||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...
> 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 permissi
on 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
>|||I am using SQL authentication. The login/ password is not currently stored i
n
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 linke
d
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...
>
>|||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...
>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.
>|||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 t
o
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 databas
e
> 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...
>
>|||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...
> 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.
>|||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...
>
>|||You're welcome ... Gald it finally worked for you ...
Van T. Dinh
MVP (Access)
"Alistair Taylor" <AlistairTaylor@.discussions.microsoft.com> wrote in
message news:46AE9D61-A29C-46CC-A44D-7C60E700C076@.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
>