|
|
You're in the
sql server odbc
group:Runtime error 3155 inserting to linked SQL tables from Access
sql server odbc:
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.
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: 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... [quoted text, click to view] >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 insert just a single record. Thanks Alistair [quoted text, click to view] "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: >
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... [quoted text, click to view] > 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 >
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... [quoted text, click to view] >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. >
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. [quoted text, click to view] "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... > > 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 > > > >
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. [quoted text, click to view] "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... > >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. > > > >
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... [quoted text, click to view] > 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 [quoted text, click to view] "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... > > 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. > > > >
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... [quoted text, click to view] > 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 >
I know this thread is closed and ended with a solution, but I am also having this problem and was wondering how to code the RefreshLink method? I did - Set db = CurrentDb() Set tdfLinked = db.CreateTableDef("ProductsTable") tdfLinked.Connect = "ODBC;DATABASE=Inquiry;DSN=Inquiry" tdfLinked.SourceTableName = "Products" tdfLinked.RefreshLink Set rs = db.OpenRecordset("Products", DB_OPEN_DYNASET, dbSeeChanges) With rs .AddNew !BearingNomenclature = UCase(NewData) .Update .Close End With Set rs = Nothing db.Close Set db = Nothing but on the RefreshLink method I get invalid operation. I copied the Example from online Help in VBA and tried to modify it to what I want to do, obviously not successfully. Thank you for any help you can provide.
RefreshLink only works on *existing* Linked Table, not on a new Linked Table
you want to establish. Check Access VB Help on the CreateTableDef method. There is sample code in the Help topic to do exactly what you need. BTW, threads are never closed ... but there are disadvantages in posting to a finished thread. Firstly, potential responsdents don't "watch" threads that have clearly been resolved or some other respondents have replied with seemingly correct suggestions. Secondly, most respondents don't look at old threads ... and the follow-up questions in old threads are attached with old threads so they don't see them. Thus, unless the thread still have recent replies posted, says 48 hours, posting to an old thread reduces the potential number of viewers / respondents. Usually, it is better to post your question in a new thread but you can refer to the old thread in your post by way of background information leading to your question. -- HTH Van T. Dinh MVP (Access) [quoted text, click to view] "Marlene Deyo" <marlenedeyo@hotmail.com> wrote in message news:OpOKUMABHHA.4808@TK2MSFTNGP03.phx.gbl... > > > I know this thread is closed and ended with a solution, but I am also > having this problem and was wondering how to code the RefreshLink > method? I did - > Set db = CurrentDb() > Set tdfLinked = db.CreateTableDef("ProductsTable") > tdfLinked.Connect = "ODBC;DATABASE=Inquiry;DSN=Inquiry" > tdfLinked.SourceTableName = "Products" > tdfLinked.RefreshLink > Set rs = db.OpenRecordset("Products", DB_OPEN_DYNASET, > dbSeeChanges) > With rs > .AddNew > !BearingNomenclature = UCase(NewData) > .Update > .Close > End With > Set rs = Nothing > db.Close > Set db = Nothing > but on the RefreshLink method I get invalid operation. I copied the > Example from online Help in VBA and tried to modify it to what I want to > do, obviously not successfully. > > Thank you for any help you can provide. > > *** Sent via Developersdex http://www.developersdex.com ***
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |