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...
>
>
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Friday, March 30, 2012
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
>
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
>
Runtime error 3146
hello!
I am currently using SQL 2000 and Access 97. My desktop
used to be Win NT. Recently i migrate to Win XP and I
got this error while SELECTING, UPDATING and INSERTING
record.
Full error is Runtime '3146'.ODBC call failed.
The documentation for this error is quite limited, anyone
can help? I need solutions? Does converting the Access
97 to Access 2002 help?
Thanks and Regards.The error is just generic. My first guess would be an MDAC
issue if by migrate you updated your desktop from NT to XP.
You can always reapply SQL Server SP 3a to make sure your
MDAC is up to date. You could also run the component checker
tool to check your MDAC installation. You can download the
tool, as well as MDAC versions from MDAC downloads at:
http://msdn.microsoft.com/data/mdac/default.aspx
-Sue
On Wed, 7 Jul 2004 01:23:52 -0700, "sam"
<anonymous@.discussions.microsoft.com> wrote:
>hello!
>I am currently using SQL 2000 and Access 97. My desktop
>used to be Win NT. Recently i migrate to Win XP and I
>got this error while SELECTING, UPDATING and INSERTING
>record.
>Full error is Runtime '3146'.ODBC call failed.
>The documentation for this error is quite limited, anyone
>can help? I need solutions? Does converting the Access
>97 to Access 2002 help?
>Thanks and Regards.|||hi!
I discover that when i do the same operation on SQL
Server 2000 Professional Edition, it works!
The error occurs when I did the operation in SQL Server
2000 Standard Edition.
Seems like this is the root cause, but cannot confirm.
What do you think?
By the way, how do i convert Standard Edition to
Professional Edition?
Thanks!
>--Original Message--
>The error is just generic. My first guess would be an
MDAC
>issue if by migrate you updated your desktop from NT to
XP.
>You can always reapply SQL Server SP 3a to make sure your
>MDAC is up to date. You could also run the component
checker
>tool to check your MDAC installation. You can download
the
>tool, as well as MDAC versions from MDAC downloads at:
>http://msdn.microsoft.com/data/mdac/default.aspx
>-Sue
>On Wed, 7 Jul 2004 01:23:52 -0700, "sam"
><anonymous@.discussions.microsoft.com> wrote:
>
anyone[vbcol=seagreen]
Access[vbcol=seagreen]
>.
>sql
I am currently using SQL 2000 and Access 97. My desktop
used to be Win NT. Recently i migrate to Win XP and I
got this error while SELECTING, UPDATING and INSERTING
record.
Full error is Runtime '3146'.ODBC call failed.
The documentation for this error is quite limited, anyone
can help? I need solutions? Does converting the Access
97 to Access 2002 help?
Thanks and Regards.The error is just generic. My first guess would be an MDAC
issue if by migrate you updated your desktop from NT to XP.
You can always reapply SQL Server SP 3a to make sure your
MDAC is up to date. You could also run the component checker
tool to check your MDAC installation. You can download the
tool, as well as MDAC versions from MDAC downloads at:
http://msdn.microsoft.com/data/mdac/default.aspx
-Sue
On Wed, 7 Jul 2004 01:23:52 -0700, "sam"
<anonymous@.discussions.microsoft.com> wrote:
>hello!
>I am currently using SQL 2000 and Access 97. My desktop
>used to be Win NT. Recently i migrate to Win XP and I
>got this error while SELECTING, UPDATING and INSERTING
>record.
>Full error is Runtime '3146'.ODBC call failed.
>The documentation for this error is quite limited, anyone
>can help? I need solutions? Does converting the Access
>97 to Access 2002 help?
>Thanks and Regards.|||hi!
I discover that when i do the same operation on SQL
Server 2000 Professional Edition, it works!
The error occurs when I did the operation in SQL Server
2000 Standard Edition.
Seems like this is the root cause, but cannot confirm.
What do you think?
By the way, how do i convert Standard Edition to
Professional Edition?
Thanks!
>--Original Message--
>The error is just generic. My first guess would be an
MDAC
>issue if by migrate you updated your desktop from NT to
XP.
>You can always reapply SQL Server SP 3a to make sure your
>MDAC is up to date. You could also run the component
checker
>tool to check your MDAC installation. You can download
the
>tool, as well as MDAC versions from MDAC downloads at:
>http://msdn.microsoft.com/data/mdac/default.aspx
>-Sue
>On Wed, 7 Jul 2004 01:23:52 -0700, "sam"
><anonymous@.discussions.microsoft.com> wrote:
>
anyone[vbcol=seagreen]
Access[vbcol=seagreen]
>.
>sql
Runtime error 3146
hello!
I am currently using SQL 2000 and Access 97. My desktop
used to be Win NT. Recently i migrate to Win XP and I
got this error while SELECTING, UPDATING and INSERTING
record.
Full error is Runtime '3146'.ODBC call failed.
The documentation for this error is quite limited, anyone
can help? I need solutions? Does converting the Access
97 to Access 2002 help?
Thanks and Regards.
The error is just generic. My first guess would be an MDAC
issue if by migrate you updated your desktop from NT to XP.
You can always reapply SQL Server SP 3a to make sure your
MDAC is up to date. You could also run the component checker
tool to check your MDAC installation. You can download the
tool, as well as MDAC versions from MDAC downloads at:
http://msdn.microsoft.com/data/mdac/default.aspx
-Sue
On Wed, 7 Jul 2004 01:23:52 -0700, "sam"
<anonymous@.discussions.microsoft.com> wrote:
>hello!
>I am currently using SQL 2000 and Access 97. My desktop
>used to be Win NT. Recently i migrate to Win XP and I
>got this error while SELECTING, UPDATING and INSERTING
>record.
>Full error is Runtime '3146'.ODBC call failed.
>The documentation for this error is quite limited, anyone
>can help? I need solutions? Does converting the Access
>97 to Access 2002 help?
>Thanks and Regards.
|||hi!
I discover that when i do the same operation on SQL
Server 2000 Professional Edition, it works!
The error occurs when I did the operation in SQL Server
2000 Standard Edition.
Seems like this is the root cause, but cannot confirm.
What do you think?
By the way, how do i convert Standard Edition to
Professional Edition?
Thanks!
>--Original Message--
>The error is just generic. My first guess would be an
MDAC
>issue if by migrate you updated your desktop from NT to
XP.
>You can always reapply SQL Server SP 3a to make sure your
>MDAC is up to date. You could also run the component
checker
>tool to check your MDAC installation. You can download
the[vbcol=seagreen]
>tool, as well as MDAC versions from MDAC downloads at:
>http://msdn.microsoft.com/data/mdac/default.aspx
>-Sue
>On Wed, 7 Jul 2004 01:23:52 -0700, "sam"
><anonymous@.discussions.microsoft.com> wrote:
anyone[vbcol=seagreen]
Access
>.
>
I am currently using SQL 2000 and Access 97. My desktop
used to be Win NT. Recently i migrate to Win XP and I
got this error while SELECTING, UPDATING and INSERTING
record.
Full error is Runtime '3146'.ODBC call failed.
The documentation for this error is quite limited, anyone
can help? I need solutions? Does converting the Access
97 to Access 2002 help?
Thanks and Regards.
The error is just generic. My first guess would be an MDAC
issue if by migrate you updated your desktop from NT to XP.
You can always reapply SQL Server SP 3a to make sure your
MDAC is up to date. You could also run the component checker
tool to check your MDAC installation. You can download the
tool, as well as MDAC versions from MDAC downloads at:
http://msdn.microsoft.com/data/mdac/default.aspx
-Sue
On Wed, 7 Jul 2004 01:23:52 -0700, "sam"
<anonymous@.discussions.microsoft.com> wrote:
>hello!
>I am currently using SQL 2000 and Access 97. My desktop
>used to be Win NT. Recently i migrate to Win XP and I
>got this error while SELECTING, UPDATING and INSERTING
>record.
>Full error is Runtime '3146'.ODBC call failed.
>The documentation for this error is quite limited, anyone
>can help? I need solutions? Does converting the Access
>97 to Access 2002 help?
>Thanks and Regards.
|||hi!
I discover that when i do the same operation on SQL
Server 2000 Professional Edition, it works!
The error occurs when I did the operation in SQL Server
2000 Standard Edition.
Seems like this is the root cause, but cannot confirm.
What do you think?
By the way, how do i convert Standard Edition to
Professional Edition?
Thanks!
>--Original Message--
>The error is just generic. My first guess would be an
MDAC
>issue if by migrate you updated your desktop from NT to
XP.
>You can always reapply SQL Server SP 3a to make sure your
>MDAC is up to date. You could also run the component
checker
>tool to check your MDAC installation. You can download
the[vbcol=seagreen]
>tool, as well as MDAC versions from MDAC downloads at:
>http://msdn.microsoft.com/data/mdac/default.aspx
>-Sue
>On Wed, 7 Jul 2004 01:23:52 -0700, "sam"
><anonymous@.discussions.microsoft.com> wrote:
anyone[vbcol=seagreen]
Access
>.
>
run-time changeable queries
I need to extract and store a value from a table (or from a MS Access file with OpenDataSource) which is not always the same and it is therefore stored in the @.openfile variable. Something like this:
...
declare @.standardselect nvarchar(4000)
declare @.value int
select @.standardSelect='select top 1 @.value=val from ' + @.openfile
exec (@.standardSelect)
...
...
declare @.standardselect nvarchar(4000)
declare @.value int
select @.standardSelect='select top 1 @.value=val from ' + @.openfile
exec (@.standardSelect)
...
It obviously doesn't work because the variable @.value is not declared within the sql string.
However, since @.openfile is always different, I need to pass it through a string and the only way I know is within a variable. If I declare @.value inside the @.standardselect it is not accessible to the rest of the procedure, which is not acceptable for me.
Any suggestions?I have solved it using a temporary table, rather than a variable, where to store the value val. But if you have a better idea...|||
You can use EXEC statement
@.sSQL = 'select...from ' + @.tablename + ' where ...'
Exec @.sSQL
However this is a bad practice.
|||Andranik Khachatryan, I guess you haven't read the code in my first message?|||Andranik Khachatryan wrote:
You can use EXEC statement
@.sSQL = 'select...from ' + @.tablename + ' where ...'
Exec @.sSQLHowever this is a bad practice.
Oops sorry :)
My bad, I am a bit careless today.
|||You could use sp_executesql with output parameters to do this.
Declare @.StandardSelect nvarchar(4000)
Declare @.Value Int
Select @.StandardSelect = 'Select top 1 @.Value=Id From ' + @.OpenFile
exec sp_executesql @.StandardSelect, N'@.Value int output', @.value OUTPUT
Select @.value
|||You don't really need dynamic SQL for doing this. You can do below instead:
declare @.source varchar(30)
-- ... initialize based on whether you want to query table or Access
set @.source =
declare @.value int
set @.value = (
select top 1 val from (
select val from your_table where @.source = 'table'
union all
select val from opendatasource(...) where @.source = 'access'
) as t
order by ...
)
RunSQL method with tables linked to SQL2005 Express
In ACCESS 2003 :
1. The DoCmd.RunSQL method works fine with tables linked (ODBC) to SQL 2000
on a first machine. On this machine, SQL2005 is not installed.
2. On a second machine, with SQL2000 and SQL2005Express installed,
DoCmd.RunSQL fails with tables linked to SQL2000 or SQL2005Express. In both
cases, I get error '3024' 'File C:\Documents and Settings\ ... \dbo.mdb not
found'.
I checked SQL2005 configuration with the SQL2005 configuration tools, and it
seems ok.
Apart from the RunSQL method, the linked tables can be accessed normally.
Any idea ? Thanks in advance.
Hi,
My understanding of your issue is that:
Your Acess application failed with tables linked to SQL Server 2000 or SQL
2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
\dbo.mdb not
found'.
If I have misunderstood, please let me know.
I performed a test but unfortunately I didn't reproduce your issue. My test
was based on the following steps:
1. Create an Access database in Acess 2003
2. Create a SQL Server 2000 database to the File DSN
4. Add linked tables from the ODBC File DSN
5. Create a Form in Acess 2003
6. Add code in the click event:
Dim strSQL As String
strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
where ProductID=" & Me.comProductId
DoCmd.RunSQL strSQL
MsgBox "OK"
7. Save the database
Firstly, I opened the database file and triggered the click event of the
Form on my local computer with SQL Server 2000 and SQL Server 2005 without
any problem, then I copied the .mdb file to another machine with SQL Server
2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
From your error message, it seems that your dbo.mdb file cannot be found.
Please check that.
Also, could you please tell me more detailed on your scenario? It is better
that you could describe your whole process so that I could better
understand your issue and probably reproduce your issue according to your
steps.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles,
Thanks for your testing and fast reply.
Your scenario is quite similar to mine and I can't see why results are
different.
In fact, it looks like if access to the tables concerned is denied. So I am
suspecting SQL 2005 security. Before going further in tests, I will have a
closer look at SQL 2005 schemas, which I am new in as they did not exist in
SQL 2000.
When I have completed this, I will let you know more.
Thanks to you.
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My test
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Server
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is better
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Charles,
Finally, I got it. The issue is this :
1. When, in ACCESS, you create a link to a SQL table which hasn't got a
primary key, a dialog box appears in second, asking for a field unique
identifier. If you do not select a field, the link is however created and you
can access normally to the table from ACCESS. But the Docmd.RunSql method
does not work. What is disturbing in that case is that the table link is
created however.
2. On another hand, if you select a unique identifier while the source table
hasn't got any primary key, it leads to incoherent behaviour such as tables
appearing different in ACCESS from their real content in SQL.
3. My conclusion is : If you have source tables without primary key (which
of course can happen), you have to choose between A/ Not using Docmd.RunSql
(or CurrentDb.Execute) or B/ Having dangerous behaviour in your app.
I would say it sounds more or less buggy.
I think you will understand my pain when I say it is a real waste that
Microsoft is abandonning ADP platform. With ADPs you can directly manipulate
SQL objects in ACCESS, such as tables, views, procs, ... (and what is more:
most often without writing any code). And this trouble could not happen.
I would be very glad to know your feeling about all this.
Once more, many thanks for your help.
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My test
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Server
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is better
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi Jason,
Thanks for your detailed explanation on this issue which make me totally
understand your issue.
Yes, I reproduced your issue according to your steps. It is indeed an by
design issue.
Please refer to:
276035PRB: ADOX Creates Read Only Linked Tables to Access Databases
http://support.microsoft.com/default...b;EN-US;276035
If you are very concerned on this issue, I recommend that you give
Microsoft feedback which will be routed to the related team via the link:
http://www.microsoft.com/office/comm...pe=suggestion&
lang=en&cr=US&cat=en-us-office&pt=3a4e9862-cdce-4bdc-8664-91038e3eb1e9
Also, ADP is still available in Access 2007
http://technet2.microsoft.com/Office...1c-446a-8ff2-2
21769a58ba51033.mspx?mfr=true
For now, you may assign the primary key in your Access database file when
you add the linked table due to no better alternative resolutions on this
issue.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles,
Thanks for your reply. Just one comment about your suggestion of assigning a
field unique identifier to a link, while source table hasn't got a primary
key.
1. Let us consider a SQL table with no primary key :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Mary
2. In ACCESS (.mdb), create an odbc link to this table, selecting empl_Name
as unique identifier. The link is created without any warning.
3. Then, you can have a look at your table in ACCESS. You will see :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Bob
A bit confusing ...
|||Hi Jason,
Thanks for your detailed response.
In this case, you can choose the two columns empl_Name and empl_FirstName
as the unique record identifier.
Please feel free to let me know if you have any other questions or
concerns.
Have a great day!
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
|||Charles,
Thanks very much for your interest in my issue.
My context is the following :
1. I have been developing for long with ADPs, doing the "hard job" in SQL
Server, and using ACCESS as front end. I considered this way of doing as
highly productive. My field could be described as the construction of
analytical relational databases fed with data coming from transactional
systems (finance, supply chain, ... ).
2. Unfortunately, Microsoft is leaving ADP platform. As a matter of fact,
you will see ADPs in ACCESS 2007, but just for the purpose of compatibility.
From now, ADPs don't work with SQL 2005 as far as building purpose is
concerned.
3. So, I am going back to ODBC and ADO with ACCESS .mdb. And problems come.
The one I met with DoCmd.RunSql (or CurrentDb.execute) is one amongst many.
For instance, links refreshing is quite an issue. If you have to frequently
create/kill tables in SQL Server, links management gets complicated.
Thanks for your suggestion, about my example, of selecting two fields as
unique identifier. I confess I didn't think of it. But the problem is
elsewhere : when you receive, from transactional systems, data in tables with
no primary key (and this frequently happens), it would not be reasonable to
add a unique identifier when creating odbc link, as you may never be sure
about the existence of duplicates.
With ADPs, everything is more simple : you work with the database "as it
is", without interfering with risky adds. Roughly speaking, "what you see is
what you get". And of course the direct manipulation of SQL objects in
ACCESS, with no code writing, allows fast developments.
4. As a conclusion, I would say it is possible to find a solution, more or
less satisfying, to this RunSQL problem (In fact, the definite solution will
be to avoid this method). But the real fact is that there is galaxy between
ADPs and ODBC/ADO (in ACCESS world of course) ...
What is rather strange is that Microsoft has, for years, presented ADPs as
THE solution for client/server ACCESS apps.
Once again, thanks very much for your help and interest in these questions.
"Charles Wang[MSFT]" wrote:
> Hi,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Have a great day!
> Charles Wang
> Microsoft Online Community Support
>
|||Hi Jason,
Appreciate your detailed response. I do understand your concerns. Just one
point that you mentioned I cannot understand clearly.
You said, "From now, ADPs don't work with SQL 2005 as far as building
purpose is concerned.". Did you mean that ADPs cannot work with SQL 2005
from now on? As far as I know, both Access 2003 and Access 2007 can work
well with SQL 2005 Express, Standard and Enterprise edtions.
Did you encounter some issue when you use ADP to work with SQL 2005? If so,
please post the issue here and I can assist you on it.
I performed a test with ADP and managed to update a SQL Server 2005 Express
table with primary key. Also, I managed to execute DoCmd.RunSQL on a no
primary key table in ADP:
I created a table in SQL 2005 Express:
CREATE TABLE [dbo].[Employee](
[First_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Last_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO Employee VALUES('F1','L1','F1 L1')
INSERT INTO Employee VALUES('F2','L2','F2 L2')
INSERT INTO Employee VALUES('F1','L1','F1 L1')
Then in ADP, I managed to run the query:
DoCmd.RunSQL "Update Employee SET Description='FX LX' WHERE First_Name =
'F1'"
For a table with no primary key, I think it is a by design limitation in
Access database (.mdb) and I have submited a feedback for this.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
1. The DoCmd.RunSQL method works fine with tables linked (ODBC) to SQL 2000
on a first machine. On this machine, SQL2005 is not installed.
2. On a second machine, with SQL2000 and SQL2005Express installed,
DoCmd.RunSQL fails with tables linked to SQL2000 or SQL2005Express. In both
cases, I get error '3024' 'File C:\Documents and Settings\ ... \dbo.mdb not
found'.
I checked SQL2005 configuration with the SQL2005 configuration tools, and it
seems ok.
Apart from the RunSQL method, the linked tables can be accessed normally.
Any idea ? Thanks in advance.
Hi,
My understanding of your issue is that:
Your Acess application failed with tables linked to SQL Server 2000 or SQL
2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
\dbo.mdb not
found'.
If I have misunderstood, please let me know.
I performed a test but unfortunately I didn't reproduce your issue. My test
was based on the following steps:
1. Create an Access database in Acess 2003
2. Create a SQL Server 2000 database to the File DSN
4. Add linked tables from the ODBC File DSN
5. Create a Form in Acess 2003
6. Add code in the click event:
Dim strSQL As String
strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
where ProductID=" & Me.comProductId
DoCmd.RunSQL strSQL
MsgBox "OK"
7. Save the database
Firstly, I opened the database file and triggered the click event of the
Form on my local computer with SQL Server 2000 and SQL Server 2005 without
any problem, then I copied the .mdb file to another machine with SQL Server
2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
From your error message, it seems that your dbo.mdb file cannot be found.
Please check that.
Also, could you please tell me more detailed on your scenario? It is better
that you could describe your whole process so that I could better
understand your issue and probably reproduce your issue according to your
steps.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles,
Thanks for your testing and fast reply.
Your scenario is quite similar to mine and I can't see why results are
different.
In fact, it looks like if access to the tables concerned is denied. So I am
suspecting SQL 2005 security. Before going further in tests, I will have a
closer look at SQL 2005 schemas, which I am new in as they did not exist in
SQL 2000.
When I have completed this, I will let you know more.
Thanks to you.
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My test
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Server
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is better
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Charles,
Finally, I got it. The issue is this :
1. When, in ACCESS, you create a link to a SQL table which hasn't got a
primary key, a dialog box appears in second, asking for a field unique
identifier. If you do not select a field, the link is however created and you
can access normally to the table from ACCESS. But the Docmd.RunSql method
does not work. What is disturbing in that case is that the table link is
created however.
2. On another hand, if you select a unique identifier while the source table
hasn't got any primary key, it leads to incoherent behaviour such as tables
appearing different in ACCESS from their real content in SQL.
3. My conclusion is : If you have source tables without primary key (which
of course can happen), you have to choose between A/ Not using Docmd.RunSql
(or CurrentDb.Execute) or B/ Having dangerous behaviour in your app.
I would say it sounds more or less buggy.
I think you will understand my pain when I say it is a real waste that
Microsoft is abandonning ADP platform. With ADPs you can directly manipulate
SQL objects in ACCESS, such as tables, views, procs, ... (and what is more:
most often without writing any code). And this trouble could not happen.
I would be very glad to know your feeling about all this.
Once more, many thanks for your help.
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My test
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Server
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is better
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi Jason,
Thanks for your detailed explanation on this issue which make me totally
understand your issue.
Yes, I reproduced your issue according to your steps. It is indeed an by
design issue.
Please refer to:
276035PRB: ADOX Creates Read Only Linked Tables to Access Databases
http://support.microsoft.com/default...b;EN-US;276035
If you are very concerned on this issue, I recommend that you give
Microsoft feedback which will be routed to the related team via the link:
http://www.microsoft.com/office/comm...pe=suggestion&
lang=en&cr=US&cat=en-us-office&pt=3a4e9862-cdce-4bdc-8664-91038e3eb1e9
Also, ADP is still available in Access 2007
http://technet2.microsoft.com/Office...1c-446a-8ff2-2
21769a58ba51033.mspx?mfr=true
For now, you may assign the primary key in your Access database file when
you add the linked table due to no better alternative resolutions on this
issue.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles,
Thanks for your reply. Just one comment about your suggestion of assigning a
field unique identifier to a link, while source table hasn't got a primary
key.
1. Let us consider a SQL table with no primary key :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Mary
2. In ACCESS (.mdb), create an odbc link to this table, selecting empl_Name
as unique identifier. The link is created without any warning.
3. Then, you can have a look at your table in ACCESS. You will see :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Bob
A bit confusing ...
|||Hi Jason,
Thanks for your detailed response.
In this case, you can choose the two columns empl_Name and empl_FirstName
as the unique record identifier.
Please feel free to let me know if you have any other questions or
concerns.
Have a great day!
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
|||Charles,
Thanks very much for your interest in my issue.
My context is the following :
1. I have been developing for long with ADPs, doing the "hard job" in SQL
Server, and using ACCESS as front end. I considered this way of doing as
highly productive. My field could be described as the construction of
analytical relational databases fed with data coming from transactional
systems (finance, supply chain, ... ).
2. Unfortunately, Microsoft is leaving ADP platform. As a matter of fact,
you will see ADPs in ACCESS 2007, but just for the purpose of compatibility.
From now, ADPs don't work with SQL 2005 as far as building purpose is
concerned.
3. So, I am going back to ODBC and ADO with ACCESS .mdb. And problems come.
The one I met with DoCmd.RunSql (or CurrentDb.execute) is one amongst many.
For instance, links refreshing is quite an issue. If you have to frequently
create/kill tables in SQL Server, links management gets complicated.
Thanks for your suggestion, about my example, of selecting two fields as
unique identifier. I confess I didn't think of it. But the problem is
elsewhere : when you receive, from transactional systems, data in tables with
no primary key (and this frequently happens), it would not be reasonable to
add a unique identifier when creating odbc link, as you may never be sure
about the existence of duplicates.
With ADPs, everything is more simple : you work with the database "as it
is", without interfering with risky adds. Roughly speaking, "what you see is
what you get". And of course the direct manipulation of SQL objects in
ACCESS, with no code writing, allows fast developments.
4. As a conclusion, I would say it is possible to find a solution, more or
less satisfying, to this RunSQL problem (In fact, the definite solution will
be to avoid this method). But the real fact is that there is galaxy between
ADPs and ODBC/ADO (in ACCESS world of course) ...
What is rather strange is that Microsoft has, for years, presented ADPs as
THE solution for client/server ACCESS apps.
Once again, thanks very much for your help and interest in these questions.
"Charles Wang[MSFT]" wrote:
> Hi,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Have a great day!
> Charles Wang
> Microsoft Online Community Support
>
|||Hi Jason,
Appreciate your detailed response. I do understand your concerns. Just one
point that you mentioned I cannot understand clearly.
You said, "From now, ADPs don't work with SQL 2005 as far as building
purpose is concerned.". Did you mean that ADPs cannot work with SQL 2005
from now on? As far as I know, both Access 2003 and Access 2007 can work
well with SQL 2005 Express, Standard and Enterprise edtions.
Did you encounter some issue when you use ADP to work with SQL 2005? If so,
please post the issue here and I can assist you on it.
I performed a test with ADP and managed to update a SQL Server 2005 Express
table with primary key. Also, I managed to execute DoCmd.RunSQL on a no
primary key table in ADP:
I created a table in SQL 2005 Express:
CREATE TABLE [dbo].[Employee](
[First_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Last_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO Employee VALUES('F1','L1','F1 L1')
INSERT INTO Employee VALUES('F2','L2','F2 L2')
INSERT INTO Employee VALUES('F1','L1','F1 L1')
Then in ADP, I managed to run the query:
DoCmd.RunSQL "Update Employee SET Description='FX LX' WHERE First_Name =
'F1'"
For a table with no primary key, I think it is a by design limitation in
Access database (.mdb) and I have submited a feedback for this.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
RunSQL method with tables linked to SQL2005 Express
In ACCESS 2003 :
1. The DoCmd.RunSQL method works fine with tables linked (ODBC) to SQL 2000
on a first machine. On this machine, SQL2005 is not installed.
2. On a second machine, with SQL2000 and SQL2005Express installed,
DoCmd.RunSQL fails with tables linked to SQL2000 or SQL2005Express. In both
cases, I get error '3024' 'File C:\Documents and Settings\ ... \dbo.mdb not
found'.
I checked SQL2005 configuration with the SQL2005 configuration tools, and it
seems ok.
Apart from the RunSQL method, the linked tables can be accessed normally.
Any idea ? Thanks in advance.Hi,
My understanding of your issue is that:
Your Acess application failed with tables linked to SQL Server 2000 or SQL
2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
\dbo.mdb not
found'.
If I have misunderstood, please let me know.
I performed a test but unfortunately I didn't reproduce your issue. My test
was based on the following steps:
1. Create an Access database in Acess 2003
2. Create a SQL Server 2000 database to the File DSN
4. Add linked tables from the ODBC File DSN
5. Create a Form in Acess 2003
6. Add code in the click event:
Dim strSQL As String
strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
where ProductID=" & Me.comProductId
DoCmd.RunSQL strSQL
MsgBox "OK"
7. Save the database
Firstly, I opened the database file and triggered the click event of the
Form on my local computer with SQL Server 2000 and SQL Server 2005 without
any problem, then I copied the .mdb file to another machine with SQL Server
2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
From your error message, it seems that your dbo.mdb file cannot be found.
Please check that.
Also, could you please tell me more detailed on your scenario? It is better
that you could describe your whole process so that I could better
understand your issue and probably reproduce your issue according to your
steps.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
Thanks for your testing and fast reply.
Your scenario is quite similar to mine and I can't see why results are
different.
In fact, it looks like if access to the tables concerned is denied. So I am
suspecting SQL 2005 security. Before going further in tests, I will have a
closer look at SQL 2005 schemas, which I am new in as they did not exist in
SQL 2000.
When I have completed this, I will let you know more.
Thanks to you.
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My tes
t
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "
'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Serve
r
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is bette
r
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Charles,
Finally, I got it. The issue is this :
1. When, in ACCESS, you create a link to a SQL table which hasn't got a
primary key, a dialog box appears in second, asking for a field unique
identifier. If you do not select a field, the link is however created and yo
u
can access normally to the table from ACCESS. But the Docmd.RunSql method
does not work. What is disturbing in that case is that the table link is
created however.
2. On another hand, if you select a unique identifier while the source table
hasn't got any primary key, it leads to incoherent behaviour such as tables
appearing different in ACCESS from their real content in SQL.
3. My conclusion is : If you have source tables without primary key (which
of course can happen), you have to choose between A/ Not using Docmd.RunSql
(or CurrentDb.Execute) or B/ Having dangerous behaviour in your app.
I would say it sounds more or less buggy.
I think you will understand my pain when I say it is a real waste that
Microsoft is abandonning ADP platform. With ADPs you can directly manipulate
SQL objects in ACCESS, such as tables, views, procs, ... (and what is more:
most often without writing any code). And this trouble could not happen.
I would be very glad to know your feeling about all this.
Once more, many thanks for your help.
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My tes
t
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "
'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Serve
r
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is bette
r
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi Jason,
Thanks for your detailed explanation on this issue which make me totally
understand your issue.
Yes, I reproduced your issue according to your steps. It is indeed an by
design issue.
Please refer to:
276035 PRB: ADOX Creates Read Only Linked Tables to Access Databases
http://support.microsoft.com/defaul...kb;EN-US;276035
If you are very concerned on this issue, I recommend that you give
Microsoft feedback which will be routed to the related team via the link:
http://www.microsoft.com/office/com...ype=suggestion&
lang=en&cr=US&cat=en-us-office&pt=3a4e9862-cdce-4bdc-8664-91038e3eb1e9
Also, ADP is still available in Access 2007
http://technet2.microsoft.com/Offic...a1c-446a-8ff2-2
21769a58ba51033.mspx?mfr=true
For now, you may assign the primary key in your Access database file when
you add the linked table due to no better alternative resolutions on this
issue.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
Thanks for your reply. Just one comment about your suggestion of assigning a
field unique identifier to a link, while source table hasn't got a primary
key.
1. Let us consider a SQL table with no primary key :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Mary
2. In ACCESS (.mdb), create an odbc link to this table, selecting empl_Name
as unique identifier. The link is created without any warning.
3. Then, you can have a look at your table in ACCESS. You will see :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Bob
A bit confusing ...|||Hi Jason,
Thanks for your detailed response.
In this case, you can choose the two columns empl_Name and empl_FirstName
as the unique record identifier.
Please feel free to let me know if you have any other questions or
concerns.
Have a great day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support|||Charles,
Thanks very much for your interest in my issue.
My context is the following :
1. I have been developing for long with ADPs, doing the "hard job" in SQL
Server, and using ACCESS as front end. I considered this way of doing as
highly productive. My field could be described as the construction of
analytical relational databases fed with data coming from transactional
systems (finance, supply chain, ... ).
2. Unfortunately, Microsoft is leaving ADP platform. As a matter of fact,
you will see ADPs in ACCESS 2007, but just for the purpose of compatibility.
From now, ADPs don't work with SQL 2005 as far as building purpose is
concerned.
3. So, I am going back to ODBC and ADO with ACCESS .mdb. And problems come.
The one I met with DoCmd.RunSql (or CurrentDb.execute) is one amongst many.
For instance, links refreshing is quite an issue. If you have to frequently
create/kill tables in SQL Server, links management gets complicated.
Thanks for your suggestion, about my example, of selecting two fields as
unique identifier. I confess I didn't think of it. But the problem is
elsewhere : when you receive, from transactional systems, data in tables wit
h
no primary key (and this frequently happens), it would not be reasonable to
add a unique identifier when creating odbc link, as you may never be sure
about the existence of duplicates.
With ADPs, everything is more simple : you work with the database "as it
is", without interfering with risky adds. Roughly speaking, "what you see is
what you get". And of course the direct manipulation of SQL objects in
ACCESS, with no code writing, allows fast developments.
4. As a conclusion, I would say it is possible to find a solution, more or
less satisfying, to this RunSQL problem (In fact, the definite solution will
be to avoid this method). But the real fact is that there is galaxy between
ADPs and ODBC/ADO (in ACCESS world of course) ...
What is rather strange is that Microsoft has, for years, presented ADPs as
THE solution for client/server ACCESS apps.
Once again, thanks very much for your help and interest in these questions.
"Charles Wang[MSFT]" wrote:
> Hi,
> I am interested in this issue. Would you mind letting me know the result o
f
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Have a great day!
> Charles Wang
> Microsoft Online Community Support
>|||Hi Jason,
Appreciate your detailed response. I do understand your concerns. Just one
point that you mentioned I cannot understand clearly.
You said, "From now, ADPs don't work with SQL 2005 as far as building
purpose is concerned.". Did you mean that ADPs cannot work with SQL 2005
from now on? As far as I know, both Access 2003 and Access 2007 can work
well with SQL 2005 Express, Standard and Enterprise edtions.
Did you encounter some issue when you use ADP to work with SQL 2005? If so,
please post the issue here and I can assist you on it.
I performed a test with ADP and managed to update a SQL Server 2005 Express
table with primary key. Also, I managed to execute DoCmd.RunSQL on a no
primary key table in ADP:
I created a table in SQL 2005 Express:
CREATE TABLE [dbo].[Employee](
[First_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[Last_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
)
INSERT INTO Employee VALUES('F1','L1','F1 L1')
INSERT INTO Employee VALUES('F2','L2','F2 L2')
INSERT INTO Employee VALUES('F1','L1','F1 L1')
Then in ADP, I managed to run the query:
DoCmd.RunSQL "Update Employee SET Description='FX LX' WHERE First_Name =
'F1'"
For a table with no primary key, I think it is a by design limitation in
Access database (.mdb) and I have submited a feedback for this.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
1. The DoCmd.RunSQL method works fine with tables linked (ODBC) to SQL 2000
on a first machine. On this machine, SQL2005 is not installed.
2. On a second machine, with SQL2000 and SQL2005Express installed,
DoCmd.RunSQL fails with tables linked to SQL2000 or SQL2005Express. In both
cases, I get error '3024' 'File C:\Documents and Settings\ ... \dbo.mdb not
found'.
I checked SQL2005 configuration with the SQL2005 configuration tools, and it
seems ok.
Apart from the RunSQL method, the linked tables can be accessed normally.
Any idea ? Thanks in advance.Hi,
My understanding of your issue is that:
Your Acess application failed with tables linked to SQL Server 2000 or SQL
2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
\dbo.mdb not
found'.
If I have misunderstood, please let me know.
I performed a test but unfortunately I didn't reproduce your issue. My test
was based on the following steps:
1. Create an Access database in Acess 2003
2. Create a SQL Server 2000 database to the File DSN
4. Add linked tables from the ODBC File DSN
5. Create a Form in Acess 2003
6. Add code in the click event:
Dim strSQL As String
strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "'
where ProductID=" & Me.comProductId
DoCmd.RunSQL strSQL
MsgBox "OK"
7. Save the database
Firstly, I opened the database file and triggered the click event of the
Form on my local computer with SQL Server 2000 and SQL Server 2005 without
any problem, then I copied the .mdb file to another machine with SQL Server
2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
From your error message, it seems that your dbo.mdb file cannot be found.
Please check that.
Also, could you please tell me more detailed on your scenario? It is better
that you could describe your whole process so that I could better
understand your issue and probably reproduce your issue according to your
steps.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
Thanks for your testing and fast reply.
Your scenario is quite similar to mine and I can't see why results are
different.
In fact, it looks like if access to the tables concerned is denied. So I am
suspecting SQL 2005 security. Before going further in tests, I will have a
closer look at SQL 2005 schemas, which I am new in as they did not exist in
SQL 2000.
When I have completed this, I will let you know more.
Thanks to you.
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My tes
t
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "
'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Serve
r
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is bette
r
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Charles,
Finally, I got it. The issue is this :
1. When, in ACCESS, you create a link to a SQL table which hasn't got a
primary key, a dialog box appears in second, asking for a field unique
identifier. If you do not select a field, the link is however created and yo
u
can access normally to the table from ACCESS. But the Docmd.RunSql method
does not work. What is disturbing in that case is that the table link is
created however.
2. On another hand, if you select a unique identifier while the source table
hasn't got any primary key, it leads to incoherent behaviour such as tables
appearing different in ACCESS from their real content in SQL.
3. My conclusion is : If you have source tables without primary key (which
of course can happen), you have to choose between A/ Not using Docmd.RunSql
(or CurrentDb.Execute) or B/ Having dangerous behaviour in your app.
I would say it sounds more or less buggy.
I think you will understand my pain when I say it is a real waste that
Microsoft is abandonning ADP platform. With ADPs you can directly manipulate
SQL objects in ACCESS, such as tables, views, procs, ... (and what is more:
most often without writing any code). And this trouble could not happen.
I would be very glad to know your feeling about all this.
Once more, many thanks for your help.
"Charles Wang[MSFT]" wrote:
> Hi,
> My understanding of your issue is that:
> Your Acess application failed with tables linked to SQL Server 2000 or SQL
> 2005 Express. The error was '3024' 'File C:\Documents and Settings\ ...
> \dbo.mdb not
> found'.
> If I have misunderstood, please let me know.
> I performed a test but unfortunately I didn't reproduce your issue. My tes
t
> was based on the following steps:
> 1. Create an Access database in Acess 2003
> 2. Create a SQL Server 2000 database to the File DSN
> 4. Add linked tables from the ODBC File DSN
> 5. Create a Form in Acess 2003
> 6. Add code in the click event:
> Dim strSQL As String
> strSQL = "Update dbo_Products SET ProductName='" & Me.Text1.Text & "
'
> where ProductID=" & Me.comProductId
> DoCmd.RunSQL strSQL
> MsgBox "OK"
> 7. Save the database
> Firstly, I opened the database file and triggered the click event of the
> Form on my local computer with SQL Server 2000 and SQL Server 2005 without
> any problem, then I copied the .mdb file to another machine with SQL Serve
r
> 2000 and SQL Server 2005 Express, ran it again, and it also worked fine.
> From your error message, it seems that your dbo.mdb file cannot be found.
> Please check that.
> Also, could you please tell me more detailed on your scenario? It is bette
r
> that you could describe your whole process so that I could better
> understand your issue and probably reproduce your issue according to your
> steps.
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi Jason,
Thanks for your detailed explanation on this issue which make me totally
understand your issue.
Yes, I reproduced your issue according to your steps. It is indeed an by
design issue.
Please refer to:
276035 PRB: ADOX Creates Read Only Linked Tables to Access Databases
http://support.microsoft.com/defaul...kb;EN-US;276035
If you are very concerned on this issue, I recommend that you give
Microsoft feedback which will be routed to the related team via the link:
http://www.microsoft.com/office/com...ype=suggestion&
lang=en&cr=US&cat=en-us-office&pt=3a4e9862-cdce-4bdc-8664-91038e3eb1e9
Also, ADP is still available in Access 2007
http://technet2.microsoft.com/Offic...a1c-446a-8ff2-2
21769a58ba51033.mspx?mfr=true
For now, you may assign the primary key in your Access database file when
you add the linked table due to no better alternative resolutions on this
issue.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
Thanks for your reply. Just one comment about your suggestion of assigning a
field unique identifier to a link, while source table hasn't got a primary
key.
1. Let us consider a SQL table with no primary key :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Mary
2. In ACCESS (.mdb), create an odbc link to this table, selecting empl_Name
as unique identifier. The link is created without any warning.
3. Then, you can have a look at your table in ACCESS. You will see :
empl_Name empl_FirstName
-- --
BROWN Bob
COPPERFIELD Jim
TAYLOR Evans
BROWN Bob
A bit confusing ...|||Hi Jason,
Thanks for your detailed response.
In this case, you can choose the two columns empl_Name and empl_FirstName
as the unique record identifier.
Please feel free to let me know if you have any other questions or
concerns.
Have a great day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support|||Charles,
Thanks very much for your interest in my issue.
My context is the following :
1. I have been developing for long with ADPs, doing the "hard job" in SQL
Server, and using ACCESS as front end. I considered this way of doing as
highly productive. My field could be described as the construction of
analytical relational databases fed with data coming from transactional
systems (finance, supply chain, ... ).
2. Unfortunately, Microsoft is leaving ADP platform. As a matter of fact,
you will see ADPs in ACCESS 2007, but just for the purpose of compatibility.
From now, ADPs don't work with SQL 2005 as far as building purpose is
concerned.
3. So, I am going back to ODBC and ADO with ACCESS .mdb. And problems come.
The one I met with DoCmd.RunSql (or CurrentDb.execute) is one amongst many.
For instance, links refreshing is quite an issue. If you have to frequently
create/kill tables in SQL Server, links management gets complicated.
Thanks for your suggestion, about my example, of selecting two fields as
unique identifier. I confess I didn't think of it. But the problem is
elsewhere : when you receive, from transactional systems, data in tables wit
h
no primary key (and this frequently happens), it would not be reasonable to
add a unique identifier when creating odbc link, as you may never be sure
about the existence of duplicates.
With ADPs, everything is more simple : you work with the database "as it
is", without interfering with risky adds. Roughly speaking, "what you see is
what you get". And of course the direct manipulation of SQL objects in
ACCESS, with no code writing, allows fast developments.
4. As a conclusion, I would say it is possible to find a solution, more or
less satisfying, to this RunSQL problem (In fact, the definite solution will
be to avoid this method). But the real fact is that there is galaxy between
ADPs and ODBC/ADO (in ACCESS world of course) ...
What is rather strange is that Microsoft has, for years, presented ADPs as
THE solution for client/server ACCESS apps.
Once again, thanks very much for your help and interest in these questions.
"Charles Wang[MSFT]" wrote:
> Hi,
> I am interested in this issue. Would you mind letting me know the result o
f
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Have a great day!
> Charles Wang
> Microsoft Online Community Support
>|||Hi Jason,
Appreciate your detailed response. I do understand your concerns. Just one
point that you mentioned I cannot understand clearly.
You said, "From now, ADPs don't work with SQL 2005 as far as building
purpose is concerned.". Did you mean that ADPs cannot work with SQL 2005
from now on? As far as I know, both Access 2003 and Access 2007 can work
well with SQL 2005 Express, Standard and Enterprise edtions.
Did you encounter some issue when you use ADP to work with SQL 2005? If so,
please post the issue here and I can assist you on it.
I performed a test with ADP and managed to update a SQL Server 2005 Express
table with primary key. Also, I managed to execute DoCmd.RunSQL on a no
primary key table in ADP:
I created a table in SQL 2005 Express:
CREATE TABLE [dbo].[Employee](
[First_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[Last_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
)
INSERT INTO Employee VALUES('F1','L1','F1 L1')
INSERT INTO Employee VALUES('F2','L2','F2 L2')
INSERT INTO Employee VALUES('F1','L1','F1 L1')
Then in ADP, I managed to run the query:
DoCmd.RunSQL "Update Employee SET Description='FX LX' WHERE First_Name =
'F1'"
For a table with no primary key, I think it is a by design limitation in
Access database (.mdb) and I have submited a feedback for this.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
Monday, March 26, 2012
Running T-SQL query against MS Access database
Is it possible to run a T-SQL query against an Access Database?
Such as a query that is not syntactically correct in Access...
select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anSQLServerColumn1] = [anAccessColumn1],
[anSQLServerColumn2] = SUBSTRING([anAccessColumn2],1,1)
from [anAccessDatabaseTable]')
I have a program with large amounts of SQL code such as the above, and I do not want to have to rewrite it all in Access compliant syntax.
I can make it work only if I rewrite the above as...
select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anAccessColumn1] AS [anSQLServerColumn1],
Left$([anAccessColumn2],1,1) AS [anSQLServerColumn2]
from [anAccessDatabaseTable]')
Kind Regards,
Laughton JacksonIf you are using OfficeXP (not sure about 2000 or ealier versions) you can open your database and go to the menu and click on Tools then options. Click on the Table/Queries tab and in the lower righ-hand corner check the box below the phrase "SQL Server Compatible Syntax (ANSI 92)" checkbox is marked "This database." Check the box and click OK.
This should help keep you from rewriting at least most of it.|||This option is not available in 2000.
Also, This Access dataase is being used by other legacy programs and this may affect these program's use of the database.
I was hoping this was possible by using the sp_addlinkedserver, sp_serveroption procedures.
Any other suggestions?
Thanks
Laughton Jackson|||Wish I did. Will keep you in mind and if anything comes up will post again.
Happy Hunting
Such as a query that is not syntactically correct in Access...
select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anSQLServerColumn1] = [anAccessColumn1],
[anSQLServerColumn2] = SUBSTRING([anAccessColumn2],1,1)
from [anAccessDatabaseTable]')
I have a program with large amounts of SQL code such as the above, and I do not want to have to rewrite it all in Access compliant syntax.
I can make it work only if I rewrite the above as...
select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anAccessColumn1] AS [anSQLServerColumn1],
Left$([anAccessColumn2],1,1) AS [anSQLServerColumn2]
from [anAccessDatabaseTable]')
Kind Regards,
Laughton JacksonIf you are using OfficeXP (not sure about 2000 or ealier versions) you can open your database and go to the menu and click on Tools then options. Click on the Table/Queries tab and in the lower righ-hand corner check the box below the phrase "SQL Server Compatible Syntax (ANSI 92)" checkbox is marked "This database." Check the box and click OK.
This should help keep you from rewriting at least most of it.|||This option is not available in 2000.
Also, This Access dataase is being used by other legacy programs and this may affect these program's use of the database.
I was hoping this was possible by using the sp_addlinkedserver, sp_serveroption procedures.
Any other suggestions?
Thanks
Laughton Jackson|||Wish I did. Will keep you in mind and if anything comes up will post again.
Happy Hunting
Wednesday, March 21, 2012
Running Stored Proc inside of Access and getting error
Running Stored Proc inside of Access and getting "The stored procedure
executed successfully but did not return records". When I run in Query
Analyzer it runs just fine. The Stored Proc is as follows:
CREATE PROCEDURE MAINT_EditorActivitybyTime (@.Enter_Start_Date_
smalldatetime, @.Enter_End_Date_ smalldatetime)
AS
DROP TABLE TEMP_MA_Node
--stored proc to create one table from MA_Node and MA_Node_Status10
DECLARE @.Temp_Node_Status10 table(NodeID bigint, NodeName nvarchar(100),
TypeID int, Status tinyint, OwnerID int, Url nvarchar(2048), Path
nvarchar(1000))
INSERT INTO @.Temp_Node_Status10
SELECT NodeID, NodeName, TypeID, Status, OwnerID, Url, Path
FROM MA_Node_Status10
SELECT *
INTO TEMP_MA_Node
FROM @.Temp_Node_Status10
INSERT INTO TEMP_MA_Node
SELECT NodeID, NodeName, TypeID, Status, OwnerID, Url, 'Live'
FROM MA_Node
SELECT dbo.MA_Change.CreateTime, dbo.MA_User.Email,
dbo.MA_Change.ChangeNodeID, dbo.MA_Change.DraftAction, TEMP_MA_Node.NodeName
,
TEMP_MA_Node.TypeID, TEMP_MA_Node.Status
FROM dbo.MA_Change INNER JOIN
dbo.MA_User ON dbo.MA_Change.ModifyUser =
dbo.MA_User.UserID LEFT OUTER JOIN
TEMP_MA_Node ON dbo.MA_Change.ChangeNodeID =
TEMP_MA_Node.NodeID
WHERE (dbo.MA_Change.CreateTime BETWEEN @.Enter_Start_Date_ AND
@.Enter_End_Date_)
ORDER BY dbo.MA_User.Email, dbo.MA_Change.CreateTime
GO
It also returns data in Query Analyzer like this:
2006-01-10 11:40:00.927 andy1221 198643 2 asdfasdf 4 0
2006-01-10 11:41:35.553 andy1221 198644 2 Lycos
Directory>Reference>Education>Distance Learning 4 0
2006-01-10 11:44:39.963 andy1221 198644 4 Lycos
Directory>Reference>Education>Distance Learning 4 0
2006-01-10 11:45:02.637 andy1221 30064 4 Reference Education Distance
Learning 4 0Hi Andy,
use
SET NOCOUNT ON
at the beginning of the SP
and
SET NOCOUNT OFF
as the last statetement of your proc.
If you use the sp like yours you will recieve multiple recordsets in Access.
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm
executed successfully but did not return records". When I run in Query
Analyzer it runs just fine. The Stored Proc is as follows:
CREATE PROCEDURE MAINT_EditorActivitybyTime (@.Enter_Start_Date_
smalldatetime, @.Enter_End_Date_ smalldatetime)
AS
DROP TABLE TEMP_MA_Node
--stored proc to create one table from MA_Node and MA_Node_Status10
DECLARE @.Temp_Node_Status10 table(NodeID bigint, NodeName nvarchar(100),
TypeID int, Status tinyint, OwnerID int, Url nvarchar(2048), Path
nvarchar(1000))
INSERT INTO @.Temp_Node_Status10
SELECT NodeID, NodeName, TypeID, Status, OwnerID, Url, Path
FROM MA_Node_Status10
SELECT *
INTO TEMP_MA_Node
FROM @.Temp_Node_Status10
INSERT INTO TEMP_MA_Node
SELECT NodeID, NodeName, TypeID, Status, OwnerID, Url, 'Live'
FROM MA_Node
SELECT dbo.MA_Change.CreateTime, dbo.MA_User.Email,
dbo.MA_Change.ChangeNodeID, dbo.MA_Change.DraftAction, TEMP_MA_Node.NodeName
,
TEMP_MA_Node.TypeID, TEMP_MA_Node.Status
FROM dbo.MA_Change INNER JOIN
dbo.MA_User ON dbo.MA_Change.ModifyUser =
dbo.MA_User.UserID LEFT OUTER JOIN
TEMP_MA_Node ON dbo.MA_Change.ChangeNodeID =
TEMP_MA_Node.NodeID
WHERE (dbo.MA_Change.CreateTime BETWEEN @.Enter_Start_Date_ AND
@.Enter_End_Date_)
ORDER BY dbo.MA_User.Email, dbo.MA_Change.CreateTime
GO
It also returns data in Query Analyzer like this:
2006-01-10 11:40:00.927 andy1221 198643 2 asdfasdf 4 0
2006-01-10 11:41:35.553 andy1221 198644 2 Lycos
Directory>Reference>Education>Distance Learning 4 0
2006-01-10 11:44:39.963 andy1221 198644 4 Lycos
Directory>Reference>Education>Distance Learning 4 0
2006-01-10 11:45:02.637 andy1221 30064 4 Reference Education Distance
Learning 4 0Hi Andy,
use
SET NOCOUNT ON
at the beginning of the SP
and
SET NOCOUNT OFF
as the last statetement of your proc.
If you use the sp like yours you will recieve multiple recordsets in Access.
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm
Running SSIS package
My boss want to close access to xp_cmdshell for any logins including sa.
He afraid of possible fraud when somebody having sa login will be able to make operations on the company's network.
He afraid of possible fraud when somebody having sa login will be able to make operations on the company's network.
In this situation (see above) I have two questions:
1. Is it possible to run SSIS package by SQL Job without corresponding utility?
2. Is there any replacement of the osql.exe utility in order to run file containing sql script? I mean may be SSIS can load the file and run content of this file by SQL Task?
Let's discuss.
Any ideas will be greately appreciated.
Vitaliy
Yes - to both questions.
Running SSIS package
My boss want to close access to xp_cmdshell for any logins including sa.
He afraid of possible fraud when somebody having sa login will be able to make operations on the company's network.
He afraid of possible fraud when somebody having sa login will be able to make operations on the company's network.
In this situation (see above) I have two questions:
1. Is it possible to run SSIS package by SQL Job without corresponding utility?
2. Is there any replacement of the osql.exe utility in order to run file containing sql script? I mean may be SSIS can load the file and run content of this file by SQL Task?
Let's discuss.
Any ideas will be greately appreciated.
Vitaliy
Yes - to both questions.
Tuesday, March 20, 2012
Running SQL Server Stored Procedures through access
Hi,
Can someone help me with this problem.
I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:
Dim cn, cmd
Set cn = CreateObject("ADODB.Connection")
cn.Open "SQL" //Data Source Name
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "LoadApplicants" //Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Execute
for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?lets see the code for the sp|||There is definitely a reason, although I can't see what it is yet.
My first guess would be an object ownership problem... I'd prefix any object names that don't contain a period (.) with "dbo." to make them valid "two part names" for SQL Server. This may not be your problem, but it is the best place I can think of to start.
-PatP|||I would explicitly dim your cn and cmd objects.
Dim cn as new ADODB.connection
Dim cmd as new ADODB.command
At the bottom of your function, make sure you destroy the objects
cn.close
Set cn = Nothing
Set cmd = Nothing
Try using a OLEDB connection instead of a DSN.
Are there any parameters for this stored procedure? I do not see them here.
Maybe also try fully qualifying the stored procedure name because you might be hitting the wrong proc.
[databasename].[owner (usually dbo)].procedurename.|||a quick check that has worked for me has been to create an access db PROJECT (*.adp)
set the datasource to your sql server and the db that you want to connect to and try to access your stored procedures
if it works you can assume (to a degree) that you have parity.
Can someone help me with this problem.
I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:
Dim cn, cmd
Set cn = CreateObject("ADODB.Connection")
cn.Open "SQL" //Data Source Name
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "LoadApplicants" //Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Execute
for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?lets see the code for the sp|||There is definitely a reason, although I can't see what it is yet.
My first guess would be an object ownership problem... I'd prefix any object names that don't contain a period (.) with "dbo." to make them valid "two part names" for SQL Server. This may not be your problem, but it is the best place I can think of to start.
-PatP|||I would explicitly dim your cn and cmd objects.
Dim cn as new ADODB.connection
Dim cmd as new ADODB.command
At the bottom of your function, make sure you destroy the objects
cn.close
Set cn = Nothing
Set cmd = Nothing
Try using a OLEDB connection instead of a DSN.
Are there any parameters for this stored procedure? I do not see them here.
Maybe also try fully qualifying the stored procedure name because you might be hitting the wrong proc.
[databasename].[owner (usually dbo)].procedurename.|||a quick check that has worked for me has been to create an access db PROJECT (*.adp)
set the datasource to your sql server and the db that you want to connect to and try to access your stored procedures
if it works you can assume (to a degree) that you have parity.
Running SQL Server job from Access
I rewrote several Access programs to run as a single SQL stored procedure on
SQL Server. A job created in DTS executes the SQL stored procedure. This
job is executed from a batch file as listed below:
.....
Dim RetVal
Dim strPathFile
strPathFile = "C:\BTExe.cmd"
RetVal = Shell(strPathFile,1)
......
As soon as the code is submitted it returns the return value. Is there any
way to have the job operate as if it was being run within Access ? For
example, display the hour glass until the job completes. Is there a
recommended procedure to run jobs from Access on SQL server, which prevents
the user from using the system until the job completes ?"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:143795DE-B03D-418E-99D3-A946461A3016@.microsoft.com...
>I rewrote several Access programs to run as a single SQL stored procedure
>on
> SQL Server. A job created in DTS executes the SQL stored procedure. This
> job is executed from a batch file as listed below:
> .....
> Dim RetVal
> Dim strPathFile
> strPathFile = "C:\BTExe.cmd"
> RetVal = Shell(strPathFile,1)
> ......
> As soon as the code is submitted it returns the return value. Is there
> any
> way to have the job operate as if it was being run within Access ? For
> example, display the hour glass until the job completes. Is there a
> recommended procedure to run jobs from Access on SQL server, which
> prevents
> the user from using the system until the job completes ?
>
Why don't you just run the stored procedure directly from Access. You can
do this with a pass-through query or in code.
David|||Can you give an example of the code and would the procedure run the same wa
y
a procedure runs in Access in that the user cannot use the system until the
procedure is complete ?
"David Browne" wrote:
> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:143795DE-B03D-418E-99D3-A946461A3016@.microsoft.com...
> Why don't you just run the stored procedure directly from Access. You ca
n
> do this with a pass-through query or in code.
> David
>|||Hi rmcompute
Here is a real live example. Access will stop when it hits cmd.execute and
not continue until the stored Procedure executes.
Sub DoSomething
dim cmd as ADODB.Command
dim conn as ADODB.Connection
Set cmd = New ADODB.Command
'Assuming I want to connection to the SQLAlerts database and my sa
password is "sapassword".
strconnect = GetConnectionString(strSQLServerName, "SQLAlerts",
"sa", "sapassword", False)
set conn = New ADODB.Connection
conn.ConnectionTimeout = 15
conn.Open strconnect
Set cmd.ActiveConnection = conn
cmd.CommandText = " record_sql_server_database_backup_histor
y" '
My Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@.sql_server_database_id",
adInteger, adParamInput, , lngSqlServerDatabaseID)
cmd.Parameters.Append cmd.CreateParameter("@.run_date", adDBDate,
adParamInput, , Now)
cmd.Parameters.Append cmd.CreateParameter("@.last_full_backup",
adDBDate, adParamInput, , dtLastFullBackup)
cmd.Parameters.Append
cmd.CreateParameter("@.last_incremental_backup", adDBDate, adParamInput, ,
dtLastIncrementalBackup)
cmd.Parameters.Append
cmd.CreateParameter("@.last_transactionlog_backup", adDBDate, adParamInput, ,
dtLastTransactionLogBackup)
cmd.Execute
END SUB
Function GetConnectionString(strSQLServerName As String, strDatabaseName As
String, strUsername As String, strPassword As String, blnNTAuthentication As
Boolean)
If blnNTAuthentication Then
GetConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=" &
strDatabaseName & ";Data Source=" & strSQLServerName
Else
Dim strPassword 'As String
GetConnectionString = "Provider=SQLOLEDB.1;Password=" &
strPassword & ";Persist Security Info=True;User ID=" & strUsername &
";Initial Catalog=" & strDatabaseName & ";Data Source=" & strSQLServerName
End If
End Function
-Dick Christoph
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:06EB646F-DD8F-40C6-AEA2-B612E6D7A3AB@.microsoft.com...
> Can you give an example of the code and would the procedure run the same
> way
> a procedure runs in Access in that the user cannot use the system until
> the
> procedure is complete ?
>
> "David Browne" wrote:
>
SQL Server. A job created in DTS executes the SQL stored procedure. This
job is executed from a batch file as listed below:
.....
Dim RetVal
Dim strPathFile
strPathFile = "C:\BTExe.cmd"
RetVal = Shell(strPathFile,1)
......
As soon as the code is submitted it returns the return value. Is there any
way to have the job operate as if it was being run within Access ? For
example, display the hour glass until the job completes. Is there a
recommended procedure to run jobs from Access on SQL server, which prevents
the user from using the system until the job completes ?"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:143795DE-B03D-418E-99D3-A946461A3016@.microsoft.com...
>I rewrote several Access programs to run as a single SQL stored procedure
>on
> SQL Server. A job created in DTS executes the SQL stored procedure. This
> job is executed from a batch file as listed below:
> .....
> Dim RetVal
> Dim strPathFile
> strPathFile = "C:\BTExe.cmd"
> RetVal = Shell(strPathFile,1)
> ......
> As soon as the code is submitted it returns the return value. Is there
> any
> way to have the job operate as if it was being run within Access ? For
> example, display the hour glass until the job completes. Is there a
> recommended procedure to run jobs from Access on SQL server, which
> prevents
> the user from using the system until the job completes ?
>
Why don't you just run the stored procedure directly from Access. You can
do this with a pass-through query or in code.
David|||Can you give an example of the code and would the procedure run the same wa
y
a procedure runs in Access in that the user cannot use the system until the
procedure is complete ?
"David Browne" wrote:
> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:143795DE-B03D-418E-99D3-A946461A3016@.microsoft.com...
> Why don't you just run the stored procedure directly from Access. You ca
n
> do this with a pass-through query or in code.
> David
>|||Hi rmcompute
Here is a real live example. Access will stop when it hits cmd.execute and
not continue until the stored Procedure executes.
Sub DoSomething
dim cmd as ADODB.Command
dim conn as ADODB.Connection
Set cmd = New ADODB.Command
'Assuming I want to connection to the SQLAlerts database and my sa
password is "sapassword".
strconnect = GetConnectionString(strSQLServerName, "SQLAlerts",
"sa", "sapassword", False)
set conn = New ADODB.Connection
conn.ConnectionTimeout = 15
conn.Open strconnect
Set cmd.ActiveConnection = conn
cmd.CommandText = " record_sql_server_database_backup_histor
y" '
My Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@.sql_server_database_id",
adInteger, adParamInput, , lngSqlServerDatabaseID)
cmd.Parameters.Append cmd.CreateParameter("@.run_date", adDBDate,
adParamInput, , Now)
cmd.Parameters.Append cmd.CreateParameter("@.last_full_backup",
adDBDate, adParamInput, , dtLastFullBackup)
cmd.Parameters.Append
cmd.CreateParameter("@.last_incremental_backup", adDBDate, adParamInput, ,
dtLastIncrementalBackup)
cmd.Parameters.Append
cmd.CreateParameter("@.last_transactionlog_backup", adDBDate, adParamInput, ,
dtLastTransactionLogBackup)
cmd.Execute
END SUB
Function GetConnectionString(strSQLServerName As String, strDatabaseName As
String, strUsername As String, strPassword As String, blnNTAuthentication As
Boolean)
If blnNTAuthentication Then
GetConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=" &
strDatabaseName & ";Data Source=" & strSQLServerName
Else
Dim strPassword 'As String
GetConnectionString = "Provider=SQLOLEDB.1;Password=" &
strPassword & ";Persist Security Info=True;User ID=" & strUsername &
";Initial Catalog=" & strDatabaseName & ";Data Source=" & strSQLServerName
End If
End Function
-Dick Christoph
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:06EB646F-DD8F-40C6-AEA2-B612E6D7A3AB@.microsoft.com...
> Can you give an example of the code and would the procedure run the same
> way
> a procedure runs in Access in that the user cannot use the system until
> the
> procedure is complete ?
>
> "David Browne" wrote:
>
Running SQL query in HUGE database
hi All, I am currently dealing with a number of tables each with over 40,000 records. I have these tables in Access and i'm planning to do some SQL query on them.
The problem is that due to then large table size, the query is running extremely slow and I'm not sure if using Access SQL is the most viable option.
I have something like:
there are three tables, T1 , T2 and T3.
T1: T2 T3
ID Value ID Value ID Value
1 100 2 5 3 1
2 200 3 5 4 1
3 300 4 5
4 400
My job is to add up all the corresponding values in the three tables and come out with something like this :
Results
ID Value
1 100
2 205
3 306
4 406
So you see the three tables have different number of records and If a record in T1 is not found in T2 or T3, I still want to keep the original value in T1. And for each table there are some 10,000 records!!!
Any advice on how to go about doing this? Some other alternatives I cuold think of is to copy the three tables to Excel and use formula, but in reality I have large number of such files so doing it manually is very time consuming.
Thanks!Your query would be:
select id, sum(value)
from
( select id, value from t1
union all
select id, value from t2
union all
select id, value from t3
)
group by id
order by id;
Whether this is too much data for Access to handle, I don't know. It is certainly a pretty small amount of data for a DBMS such as SQL Server or Oracle.|||thanks I just tried it and it works very well.
however I forgot to add a point that T2 and T3 might contain records that do not exist in T1 (say Id=5)
but I ONLY want records that exist in T1.
What should I do?
Thanks!|||In that case, perhaps an outer join is more appropriate?
select t1.id, t1.value+coalesce(t2.value,0)+coalesce(t3.value,0)
from t1
left outer join t2 on t2.id = t1.id
left outer join t3 on t3.id = t1.id;
The problem is that due to then large table size, the query is running extremely slow and I'm not sure if using Access SQL is the most viable option.
I have something like:
there are three tables, T1 , T2 and T3.
T1: T2 T3
ID Value ID Value ID Value
1 100 2 5 3 1
2 200 3 5 4 1
3 300 4 5
4 400
My job is to add up all the corresponding values in the three tables and come out with something like this :
Results
ID Value
1 100
2 205
3 306
4 406
So you see the three tables have different number of records and If a record in T1 is not found in T2 or T3, I still want to keep the original value in T1. And for each table there are some 10,000 records!!!
Any advice on how to go about doing this? Some other alternatives I cuold think of is to copy the three tables to Excel and use formula, but in reality I have large number of such files so doing it manually is very time consuming.
Thanks!Your query would be:
select id, sum(value)
from
( select id, value from t1
union all
select id, value from t2
union all
select id, value from t3
)
group by id
order by id;
Whether this is too much data for Access to handle, I don't know. It is certainly a pretty small amount of data for a DBMS such as SQL Server or Oracle.|||thanks I just tried it and it works very well.
however I forgot to add a point that T2 and T3 might contain records that do not exist in T1 (say Id=5)
but I ONLY want records that exist in T1.
What should I do?
Thanks!|||In that case, perhaps an outer join is more appropriate?
select t1.id, t1.value+coalesce(t2.value,0)+coalesce(t3.value,0)
from t1
left outer join t2 on t2.id = t1.id
left outer join t3 on t3.id = t1.id;
Tuesday, February 21, 2012
Running SP from Access 2000 with using SP owner qualifier
Hi there,
Can anyone tell me how to call a SQL Server Stored Procedure from an MS Access form with having to use the owner.spname style. I just want to call spname from my form.
I know it has to do with user permissions and roles, but no matter what I do I have to still use the owner.spname style.
Thanks,
Brian.are you using ADP?
if you're not, move it to ADP-- it is an awesome platform.
(ADP handles SP a lot better than MDB)|||Thanks for the suggestion Aaron, but I am using ADP and it's Access 2000.
I think the issue is on the server side with SQL Server, but one of the lads working (same privileges apparently) with me doesn't have this issue ... it's weird and greatly annoying.
Cheers ...|||instead of dealing with buggy input parameters i usually just use this syntax in the recordsource property of an ADP form or report:
EXEC sp_colums 'sysobjects'
is that what you're looking for?|||That works a treat Aaron, thanks a mill. Now life is just so much easier :)
Can anyone tell me how to call a SQL Server Stored Procedure from an MS Access form with having to use the owner.spname style. I just want to call spname from my form.
I know it has to do with user permissions and roles, but no matter what I do I have to still use the owner.spname style.
Thanks,
Brian.are you using ADP?
if you're not, move it to ADP-- it is an awesome platform.
(ADP handles SP a lot better than MDB)|||Thanks for the suggestion Aaron, but I am using ADP and it's Access 2000.
I think the issue is on the server side with SQL Server, but one of the lads working (same privileges apparently) with me doesn't have this issue ... it's weird and greatly annoying.
Cheers ...|||instead of dealing with buggy input parameters i usually just use this syntax in the recordsource property of an ADP form or report:
EXEC sp_colums 'sysobjects'
is that what you're looking for?|||That works a treat Aaron, thanks a mill. Now life is just so much easier :)
Subscribe to:
Posts (Atom)