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 tables. Show all posts
Showing posts with label tables. 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
>
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.
========================================
==============
Wednesday, March 28, 2012
RunningValue doesnt do anything useful for me
I put =RunningValue(Fields!FieldName.Value, Sum, Nothing) in a tables group
footer. It displays the same total on every page.
I put in a new details line on the table. It works fine. But prints itself
all over page rather than once at bottom of each page.
Help !
Chris BrooksbankChris,
At the bottom of this posting is an example of how to use a running value in
a group footer.
Please let me know if this is what you are attempting to achieve. This
sample counts the number of items in the group footer and the table footer.
Note that the Group footer accumulates as expected while the same expression
shows the total when use in the table footer.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Brooksbank" <NoSpam@.Ta.com> wrote in message
news:eTUNnuBZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> I put =RunningValue(Fields!FieldName.Value, Sum, Nothing) in a tables
group
> footer. It displays the same total on every page.
> I put in a new details line on the table. It works fine. But prints itself
> all over page rather than once at bottom of each page.
> Help !
> Chris Brooksbank
>
RunningValueInGroupFooterSample.rdl
----
--
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>23</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Customer ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>22</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>21</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ProductID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>ProductID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>UnitPrice</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!UnitPrice.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CustomerID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>20</ZIndex>
<rd:DefaultName>CustomerID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!CustomerID.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>19</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>18</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>UnitPrice_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Group Footer : Count in this group</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!UnitPrice.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox16</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Group Footer : Running Count of
Items</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!UnitPrice.Value,
Count, "table1")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<CanGrow>true</CanGrow>
<Value>Group Footer : Running Count of
Items</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!UnitPrice.Value,
Count, Nothing)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox19</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Footer>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Table Footer : Running Count </Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>16</ZIndex>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!UnitPrice.Value, Count,
Nothing)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.375in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>5.70834in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.CustomerID, Customers.CompanyName,
Orders.OrderID, Orders.OrderDate, [Order Details].ProductID, [Order
Details].UnitPrice,
[Order Details].Quantity
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||Bruce:
Thank you so much for the sample but this sample does not sum at the end of
the PAGE. It summed by company_id on each page.
At the end of each page I need to have a running sum of that Page not just
only by company_id. Because one page may contains multiple company_id.
Thanks,
John
"Bruce Johnson [MSFT]" wrote:
> Chris,
> At the bottom of this posting is an example of how to use a running value in
> a group footer.
> Please let me know if this is what you are attempting to achieve. This
> sample counts the number of items in the group footer and the table footer.
> Note that the Group footer accumulates as expected while the same expression
> shows the total when use in the table footer.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Chris Brooksbank" <NoSpam@.Ta.com> wrote in message
> news:eTUNnuBZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> > I put =RunningValue(Fields!FieldName.Value, Sum, Nothing) in a tables
> group
> > footer. It displays the same total on every page.
> >
> > I put in a new details line on the table. It works fine. But prints itself
> > all over page rather than once at bottom of each page.
> >
> > Help !
> >
> > Chris Brooksbank
> >
> >
> RunningValueInGroupFooterSample.rdl
> ----
> --
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>23</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Customer ID</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>22</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Company Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>21</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="ProductID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>ProductID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!ProductID.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="UnitPrice">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>UnitPrice</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!UnitPrice.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>Northwind</DataSetName>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="CustomerID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>20</ZIndex>
> <rd:DefaultName>CustomerID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!CustomerID.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="CompanyName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>19</ZIndex>
> <rd:DefaultName>CompanyName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CompanyName.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>18</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!CompanyName.Value</GroupExpression>
> </GroupExpressions>
> <PageBreakAtEnd>true</PageBreakAtEnd>
> </Grouping>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox13">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox13</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="UnitPrice_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>UnitPrice_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox15">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox15</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Group Footer : Count in this group</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox14">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox14</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Count(Fields!UnitPrice.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox16">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox16</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Group Footer : Running Count of
> Items</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=RunningValue(Fields!UnitPrice.Value,
> Count, "table1")</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox10</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox18">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>14</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>Group Footer : Running Count of
> Items</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox20">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>13</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>=RunningValue(Fields!UnitPrice.Value,
> Count, Nothing)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox19">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>textbox19</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Footer>
> </TableGroup>
> </TableGroups>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox7">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>17</ZIndex>
> <rd:DefaultName>textbox7</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Table Footer : Running Count </Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox17">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>16</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>=RunningValue(Fields!UnitPrice.Value, Count,
> Nothing)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox9">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>15</ZIndex>
> <rd:DefaultName>textbox9</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Footer>
> <TableColumns>
> <TableColumn>
> <Width>2.375in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.66667in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.66667in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>2in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=localhost;initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>5.70834in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CompanyName">
> <DataField>CompanyName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="OrderID">
> <DataField>OrderID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="OrderDate">
> <DataField>OrderDate</DataField>
> <rd:TypeName>System.DateTime</rd:TypeName>
> </Field>
> <Field Name="ProductID">
> <DataField>ProductID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="Quantity">
> <DataField>Quantity</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT Customers.CustomerID, Customers.CompanyName,
> Orders.OrderID, Orders.OrderDate, [Order Details].ProductID, [Order
> Details].UnitPrice,
> [Order Details].Quantity
> FROM Customers INNER JOIN
> Orders ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID</CommandText>
> <Timeout>30</Timeout>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>
footer. It displays the same total on every page.
I put in a new details line on the table. It works fine. But prints itself
all over page rather than once at bottom of each page.
Help !
Chris BrooksbankChris,
At the bottom of this posting is an example of how to use a running value in
a group footer.
Please let me know if this is what you are attempting to achieve. This
sample counts the number of items in the group footer and the table footer.
Note that the Group footer accumulates as expected while the same expression
shows the total when use in the table footer.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Brooksbank" <NoSpam@.Ta.com> wrote in message
news:eTUNnuBZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> I put =RunningValue(Fields!FieldName.Value, Sum, Nothing) in a tables
group
> footer. It displays the same total on every page.
> I put in a new details line on the table. It works fine. But prints itself
> all over page rather than once at bottom of each page.
> Help !
> Chris Brooksbank
>
RunningValueInGroupFooterSample.rdl
----
--
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>23</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Customer ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>22</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>21</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ProductID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>ProductID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>UnitPrice</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!UnitPrice.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CustomerID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>20</ZIndex>
<rd:DefaultName>CustomerID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!CustomerID.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>19</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>18</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>UnitPrice_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Group Footer : Count in this group</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!UnitPrice.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox16</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Group Footer : Running Count of
Items</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!UnitPrice.Value,
Count, "table1")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<CanGrow>true</CanGrow>
<Value>Group Footer : Running Count of
Items</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!UnitPrice.Value,
Count, Nothing)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox19</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Footer>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Table Footer : Running Count </Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>16</ZIndex>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!UnitPrice.Value, Count,
Nothing)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.375in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>5.70834in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.CustomerID, Customers.CompanyName,
Orders.OrderID, Orders.OrderDate, [Order Details].ProductID, [Order
Details].UnitPrice,
[Order Details].Quantity
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||Bruce:
Thank you so much for the sample but this sample does not sum at the end of
the PAGE. It summed by company_id on each page.
At the end of each page I need to have a running sum of that Page not just
only by company_id. Because one page may contains multiple company_id.
Thanks,
John
"Bruce Johnson [MSFT]" wrote:
> Chris,
> At the bottom of this posting is an example of how to use a running value in
> a group footer.
> Please let me know if this is what you are attempting to achieve. This
> sample counts the number of items in the group footer and the table footer.
> Note that the Group footer accumulates as expected while the same expression
> shows the total when use in the table footer.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Chris Brooksbank" <NoSpam@.Ta.com> wrote in message
> news:eTUNnuBZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> > I put =RunningValue(Fields!FieldName.Value, Sum, Nothing) in a tables
> group
> > footer. It displays the same total on every page.
> >
> > I put in a new details line on the table. It works fine. But prints itself
> > all over page rather than once at bottom of each page.
> >
> > Help !
> >
> > Chris Brooksbank
> >
> >
> RunningValueInGroupFooterSample.rdl
> ----
> --
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>23</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Customer ID</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>22</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Company Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>21</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="ProductID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>ProductID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!ProductID.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="UnitPrice">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>UnitPrice</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!UnitPrice.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>Northwind</DataSetName>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="CustomerID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>20</ZIndex>
> <rd:DefaultName>CustomerID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!CustomerID.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="CompanyName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>19</ZIndex>
> <rd:DefaultName>CompanyName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CompanyName.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>18</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!CompanyName.Value</GroupExpression>
> </GroupExpressions>
> <PageBreakAtEnd>true</PageBreakAtEnd>
> </Grouping>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox13">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox13</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="UnitPrice_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>UnitPrice_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox15">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox15</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Group Footer : Count in this group</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox14">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox14</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Count(Fields!UnitPrice.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox16">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox16</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Group Footer : Running Count of
> Items</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=RunningValue(Fields!UnitPrice.Value,
> Count, "table1")</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox10</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox18">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>14</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>Group Footer : Running Count of
> Items</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox20">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>13</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>=RunningValue(Fields!UnitPrice.Value,
> Count, Nothing)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox19">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>textbox19</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Footer>
> </TableGroup>
> </TableGroups>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox7">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>17</ZIndex>
> <rd:DefaultName>textbox7</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Table Footer : Running Count </Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox17">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>16</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>=RunningValue(Fields!UnitPrice.Value, Count,
> Nothing)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox9">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>15</ZIndex>
> <rd:DefaultName>textbox9</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Footer>
> <TableColumns>
> <TableColumn>
> <Width>2.375in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.66667in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.66667in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>2in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=localhost;initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>5.70834in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CompanyName">
> <DataField>CompanyName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="OrderID">
> <DataField>OrderID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="OrderDate">
> <DataField>OrderDate</DataField>
> <rd:TypeName>System.DateTime</rd:TypeName>
> </Field>
> <Field Name="ProductID">
> <DataField>ProductID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="Quantity">
> <DataField>Quantity</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT Customers.CustomerID, Customers.CompanyName,
> Orders.OrderID, Orders.OrderDate, [Order Details].ProductID, [Order
> Details].UnitPrice,
> [Order Details].Quantity
> FROM Customers INNER JOIN
> Orders ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID</CommandText>
> <Timeout>30</Timeout>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>
Friday, March 23, 2012
Running Total cursor
I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
You shouldn't need a cursor. Try this instead,
first create a view to give the totals up to
the current date.
CREATE VIEW AmountTotals
AS
SELECT a.Code_id,
a.Amount,
a.Date,
(SELECT SUM(b.Amount)
FROM Amount b
WHERE a.Code_id=b.Code_id
AND b.Date <= a.Date) AS RunningTotal
FROM Amount a
GO
Now you can update all of your ReachedDates
using this view
UPDATE ID
SET ReachedDate=(SELECT MIN(Date)
FROM AmountTotals
WHERE RunningTotal >= 100
AND AmountTotals.Code_id=ID.Code_ID)
you have to create a for update, insert, delete trigger on
table amount which shall automatically mark the flag when the amount is reached. trigger fires automatically
|||Hey Mark. Works like a charm. Thank you so much.|||Yeah, right. And freeze the server like magic! The best and faster solution its using cursors. I can say because I use "the magic" in some points of my app, and Im having performance problems with it.
Its really sad that microsoft dont release real solutions for simple problems like that.
Running Total cursor
I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
You shouldn't need a cursor. Try this instead,
first create a view to give the totals up to
the current date.
CREATE VIEW AmountTotals
AS
SELECT a.Code_id,
a.Amount,
a.Date,
(SELECT SUM(b.Amount)
FROM Amount b
WHERE a.Code_id=b.Code_id
AND b.Date <= a.Date) AS RunningTotal
FROM Amount a
GO
Now you can update all of your ReachedDates
using this view
UPDATE ID
SET ReachedDate=(SELECT MIN(Date)
FROM AmountTotals
WHERE RunningTotal >= 100
AND AmountTotals.Code_id=ID.Code_ID)
you have to create a for update, insert, delete trigger on
table amount which shall automatically mark the flag when the amount is reached. trigger fires automatically
|||Hey Mark. Works like a charm. Thank you so much.|||Yeah, right. And freeze the server like magic! The best and faster solution its using cursors. I can say because I use "the magic" in some points of my app, and Im having performance problems with it.
Its really sad that microsoft dont release real solutions for simple problems like that.
Wednesday, March 21, 2012
Running Stored procedure as....
I need to use SET IDENTITY_INSERT on several tables in one
of the Stored Procedures. The stored procedure is
executed by an application user that can not be given DBO,
db_ddladmin or any higher permissions. This makes the SET
statement fail as the user does not have the need
authority.
My question: Is there a way that I can have this
particular stored procedure run under higher authority?
That is who ever runs this stored procedure it will always
execute under the authority of a fixed given user.Sorry that functionality is not availble in the current version of SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
of the Stored Procedures. The stored procedure is
executed by an application user that can not be given DBO,
db_ddladmin or any higher permissions. This makes the SET
statement fail as the user does not have the need
authority.
My question: Is there a way that I can have this
particular stored procedure run under higher authority?
That is who ever runs this stored procedure it will always
execute under the authority of a fixed given user.Sorry that functionality is not availble in the current version of SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Tuesday, March 20, 2012
Running sql script from Setup deployment .NET
Posted - 04/21/2004 : 09:26:34
----
--
Friends
I want to run sql script for creating of database which
contains tables and stored procedures by setup and deployment project
in VS.NET. I have created customaction and passing the string
containing script to the ExecuteNonQuery() procedure to run the script
The problem is where ever GO statement is there in the script it
is giving problem. I read somewhere that
" Your script must not include any go statements. These are used only by Que
ry Analyzer or the osql.exe utility. If they are present in your script when
using it with ADO.NET (as in this demo), then the ExecuteNonQuery will fail
."
If i remove Go from the script then it is giving exception again
telling that "Incorrect syntax near the keyword Procedure"
where ever create Procedure statement is there
Please help me in this regard.Sudhapec,
from BOL: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and
CREATE VIEW statements cannot be combined with other statements in a batch.
Batches are delimited by the keyword GO in a script, hence your problem. I
guess you could create mult
iple scripts - one main one and a separate script for each of the above comm
ands. Another (easier) alternative would be to not use ADO.NET for this step
, but OSQL. You could have a batch file which runs osql with your complete s
cript.
HTH,
Paul Ibison|||Or, read the script, line by line, and each time you encounter GO, execute w
hat you have in the buffer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:8781168A-8324-4DE5-ACDA-C975D4B21EEF@.microsoft.com...
> Sudhapec,
> from BOL: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREAT
E VIEW statements cannot
be combined with other statements in a batch. Batches are delimited by the k
eyword GO in a script, hence your
problem. I guess you could create multiple scripts - one main one and a sepa
rate script for each of the above
commands. Another (easier) alternative would be to not use ADO.NET for this
step, but OSQL. You could have a
batch file which runs osql with your complete script.
> HTH,
> Paul Ibison|||Below a link to a VB.Net code example that uses the technique Tibor
mentioned:
<http://groups.google.com/groups?hl=...threadm=mK%252c
.2084%24Cm3.67%40newsread3.news.pas.earthlink.net&rnum=8&prev=/groups%3Fas_
q
%3Dsqlcommand%2520go%26safe%3Dimages%26i
e%3DUTF-8%26oe%3DUTF-8%26as_uauthors
%3DDan%2520Guzman%26lr%3D%26hl%3Den>
Hope this helps.
Dan Guzman
SQL Server MVP
"sudhapec" <sudhapec.153vii@.mail.webservertalk.com> wrote in message
news:sudhapec.153vii@.mail.webservertalk.com...
> Posted - 04/21/2004 : 09:26:34
> ----
--
> Friends
> I want to run sql script for creating of database which
> contains tables and stored procedures by setup and deployment project
> in VS.NET. I have created customaction and passing the string
> containing script to the ExecuteNonQuery() procedure to run the script
> The problem is where ever GO statement is there in the script it
> is giving problem. I read somewhere that
> " Your script must not include any go statements. These are used only
> by Query Analyzer or the osql.exe utility. If they are present in your
> script when using it with ADO.NET (as in this demo), then the
> ExecuteNonQuery will fail."
> If i remove Go from the script then it is giving exception again
> telling that "Incorrect syntax near the keyword Procedure"
> where ever create Procedure statement is there
> Please help me in this regard.
>
> --
> sudhapec
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message190433.html
>
----
--
Friends
I want to run sql script for creating of database which
contains tables and stored procedures by setup and deployment project
in VS.NET. I have created customaction and passing the string
containing script to the ExecuteNonQuery() procedure to run the script
The problem is where ever GO statement is there in the script it
is giving problem. I read somewhere that
" Your script must not include any go statements. These are used only by Que
ry Analyzer or the osql.exe utility. If they are present in your script when
using it with ADO.NET (as in this demo), then the ExecuteNonQuery will fail
."
If i remove Go from the script then it is giving exception again
telling that "Incorrect syntax near the keyword Procedure"
where ever create Procedure statement is there
Please help me in this regard.Sudhapec,
from BOL: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and
CREATE VIEW statements cannot be combined with other statements in a batch.
Batches are delimited by the keyword GO in a script, hence your problem. I
guess you could create mult
iple scripts - one main one and a separate script for each of the above comm
ands. Another (easier) alternative would be to not use ADO.NET for this step
, but OSQL. You could have a batch file which runs osql with your complete s
cript.
HTH,
Paul Ibison|||Or, read the script, line by line, and each time you encounter GO, execute w
hat you have in the buffer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:8781168A-8324-4DE5-ACDA-C975D4B21EEF@.microsoft.com...
> Sudhapec,
> from BOL: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREAT
E VIEW statements cannot
be combined with other statements in a batch. Batches are delimited by the k
eyword GO in a script, hence your
problem. I guess you could create multiple scripts - one main one and a sepa
rate script for each of the above
commands. Another (easier) alternative would be to not use ADO.NET for this
step, but OSQL. You could have a
batch file which runs osql with your complete script.
> HTH,
> Paul Ibison|||Below a link to a VB.Net code example that uses the technique Tibor
mentioned:
<http://groups.google.com/groups?hl=...threadm=mK%252c
.2084%24Cm3.67%40newsread3.news.pas.earthlink.net&rnum=8&prev=/groups%3Fas_
q
%3Dsqlcommand%2520go%26safe%3Dimages%26i
e%3DUTF-8%26oe%3DUTF-8%26as_uauthors
%3DDan%2520Guzman%26lr%3D%26hl%3Den>
Hope this helps.
Dan Guzman
SQL Server MVP
"sudhapec" <sudhapec.153vii@.mail.webservertalk.com> wrote in message
news:sudhapec.153vii@.mail.webservertalk.com...
> Posted - 04/21/2004 : 09:26:34
> ----
--
> Friends
> I want to run sql script for creating of database which
> contains tables and stored procedures by setup and deployment project
> in VS.NET. I have created customaction and passing the string
> containing script to the ExecuteNonQuery() procedure to run the script
> The problem is where ever GO statement is there in the script it
> is giving problem. I read somewhere that
> " Your script must not include any go statements. These are used only
> by Query Analyzer or the osql.exe utility. If they are present in your
> script when using it with ADO.NET (as in this demo), then the
> ExecuteNonQuery will fail."
> If i remove Go from the script then it is giving exception again
> telling that "Incorrect syntax near the keyword Procedure"
> where ever create Procedure statement is there
> Please help me in this regard.
>
> --
> sudhapec
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message190433.html
>
Subscribe to:
Posts (Atom)