here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.
Public Function chartlookup()
Dim db As DAO.Database <--believe to be the problem
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String
SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"
Set db = CurrentDb() <-- -problem
Set rs = db.OpenRecordset(SQL) <--problem
If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400
If rs.EOF = False Then
If IsNull(rs!RecNum) = False Then
NewNum = rs!RecNum + 1
Else
NewNum = 1
End If
End If
'If NewNum = 1 Then
[Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
'End If
400 End Function
basically if you typw in john smith this code would put smijo00001 into chartnumber field now i get a run-time error 91.What error are you receiving?
Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.
You have a few access specific casts there that are going to cause trouble for you.|||Change you dao to ado - using recordset and/or connection objects.|||Originally posted by Teddy
What error are you receiving?
Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.
You have a few access specific casts there that are going to cause trouble for you.
im receiving a run-time error 91 in the set rs= db.recordset(sql) statement but i know it coming from previous dim db as dao.database and dim rs as dao.recordset. i dont know how to convert this vb code to work with sql. maybe dim rs as sql.recordset and dim db as sql.database . please forgive my ignorance but i am new to sql and i dont know the syntax for sql. by the way thank you for the advice with ucase and cint not working . i tried a previous suggestion and turned dim rs as ado.recordset but the only option i get is adobe.recordset am i missing a reference .|||You need to use the ms ado 2.x library reference. Using adodb.recordset and adodb.connection - however, in your case you only need adodb.recordset.|||Hey
Who one saying that ODBC Connections not possible by DAO?
[QUOTE][SIZE=1]Originally posted by opcbriley
here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.
Public Function chartlookup()
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String
SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("costing", dbDriverNoPrompt)
.........
You may also open connection by using this code
remaining code will remain same
plz use it n reply me
thx|||You can use odbc - but don't - you are much better off using oledb (or use tds in .net). Better performance, resource usage and flexibility/functionality.|||yeah i know that OLEDB performance much better that dao but person asked abt DAO libraray n no one was there to reply by DAO libraray so I hv ginven reply by DAO library|||i appreciate all your help i was able to switch to ado connection i finally learned the syntax last night after about hour of reading. that code is working perfect now.
now on to find all my other problems that i have to change to be compatible with sql. i sure hope sql is worth all this trouble.
Showing posts with label basically. Show all posts
Showing posts with label basically. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Please help - setting order of select in a self-referencing table
Hello,
I have a table of product categories that looks like this (air code, so
could be some typos, but it's basically right)...
create table producttypes (
ptype int not null identity(1,1) primary key,
typename varchar(50) not null default '',
ParentCat int references producttypes(ptype)
)
This allows categories to contain subcategories and so on.
In order to allow me to see the hierarchy, I am using the following
code, modified from some found in "Inside SQL Server 7.0" by Kalen
Delaney.
-- SQL starts
declare @.level int, @.current int
create table #stack (depthlevel int, ptype int)
create table #orgchart (seqno int identity, orglevel int not null, ptype int
not null)
set rowcount 1
select @.level=1, @.current=ptype from producttypes where ptype=parentcat
set rowcount 0
insert into #stack (depthlevel, ptype) values (@.level, @.current)
while (@.level>0)
begin
if exists (select * from #stack where depthlevel=@.level)
begin
set rowcount 1
select @.current=ptype from #stack where depthlevel=@.level
set rowcount 0
insert into #orgchart (orglevel, ptype) select @.level, @.current
delete from #stack where depthlevel=@.level and ptype=@.current
insert into #stack select @.level+1, ptype from producttypes where parentcat=
@.current and parentcat<>ptype
if @.@.ROWCOUNT > 0 select @.level=@.level+1
end
else
select @.level=@.level-1
end
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno
drop table #stack, #orgchart
-- SQL ends
This produces a set of results that gives me all the categories. I can
use the orglevel field to tell what level I'm at, so if it changes, I
can see if I've gone up or down the hierarchy.
Now, the problem is that I can't work out how to set the order of
categories. For example, if the above SQL produces...
1 16 16 Products
2 17 16 Outdoor Toys
3 1 17 Trampolines
3 2 17 Slides
3 3 17 Swings
3 4 17 Accessories
3 5 17 Climbing Frames
3 7 17 Bicycles
you can see that the categories on level 3 are not in any obvious order.
I would like to have them in alphabetical order.
Any ideas how I would modify the above SQL to do this? TIA
Alan Silver
(anything added below this line is nothing to do with me)Try
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
The results should be sorted first by the orglevel, and results within the
same orglevel will be sorted alphabetically. The syntax reads like "order
results first by o.seqno, then by e.typename"
"Alan Silver" wrote:
> Hello,
> I have a table of product categories that looks like this (air code, so
> could be some typos, but it's basically right)...
> create table producttypes (
> ptype int not null identity(1,1) primary key,
> typename varchar(50) not null default '',
> ParentCat int references producttypes(ptype)
> )
> This allows categories to contain subcategories and so on.
> In order to allow me to see the hierarchy, I am using the following
> code, modified from some found in "Inside SQL Server 7.0" by Kalen
> Delaney.
> -- SQL starts
> declare @.level int, @.current int
> create table #stack (depthlevel int, ptype int)
> create table #orgchart (seqno int identity, orglevel int not null, ptype i
nt not null)
> set rowcount 1
> select @.level=1, @.current=ptype from producttypes where ptype=parentcat
> set rowcount 0
> insert into #stack (depthlevel, ptype) values (@.level, @.current)
> while (@.level>0)
> begin
> if exists (select * from #stack where depthlevel=@.level)
> begin
> set rowcount 1
> select @.current=ptype from #stack where depthlevel=@.level
> set rowcount 0
> insert into #orgchart (orglevel, ptype) select @.level, @.current
> delete from #stack where depthlevel=@.level and ptype=@.current
> insert into #stack select @.level+1, ptype from producttypes where par
entcat=@.current and parentcat<>ptype
> if @.@.ROWCOUNT > 0 select @.level=@.level+1
> end
> else
> select @.level=@.level-1
> end
> select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
> join producttypes as e on e.ptype=o.ptype order by o.seqno
> drop table #stack, #orgchart
> -- SQL ends
>
> This produces a set of results that gives me all the categories. I can
> use the orglevel field to tell what level I'm at, so if it changes, I
> can see if I've gone up or down the hierarchy.
> Now, the problem is that I can't work out how to set the order of
> categories. For example, if the above SQL produces...
> 1 16 16 Products
> 2 17 16 Outdoor Toys
> 3 1 17 Trampolines
> 3 2 17 Slides
> 3 3 17 Swings
> 3 4 17 Accessories
> 3 5 17 Climbing Frames
> 3 7 17 Bicycles
> you can see that the categories on level 3 are not in any obvious order.
> I would like to have them in alphabetical order.
> Any ideas how I would modify the above SQL to do this? TIA
> --
> Alan Silver
> (anything added below this line is nothing to do with me)
>|||>Try
>select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
>join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
>The results should be sorted first by the orglevel, and results within the
>same orglevel will be sorted alphabetically. The syntax reads like "order
>results first by o.seqno, then by e.typename"
Mark,
Thanks for the reply, but if you look carefully, it's not that simple.
Your suggestion will list all level 2 categories together, followed by
all level 3 and so on. This will not give the correct hierarchy as there
may be several level 2 categories each with subcategories (ie level 3).
If this isn't clear, look at the longer category listing shown later on.
This is a more complete list than the abbreviated one I showed before.
Note that the ordering is done on seqno, which is the order in which the
categories were put into the orgchart table. The ordering is not done on
the level at all.
I think I need to modify the way the categories are pulled out of the
producttypes table when they are inserted into the stack table. Trouble
is, I can't see how to control the ordering as they are being pulled one
at a time.
Thanks for the reply. Any further help would be appreciated.
Longer category listing follows...
level typename
1 Products
2 Outdoor Toys
3 Trampolines
3 Slides
3 Swings
3 Accessories
3 Climbing Frames
3 Bicycles
2 Indoor Toys
3 Snooker Tables
3 Boy's Toys
4 Hard Puzzles
3 Girl's Toys
4 Easy Puzzles
3 Board Games
3 Pets
4 Ferrets
4 Dogs
4 Cats
>"Alan Silver" wrote:
>
Alan Silver
(anything added below this line is nothing to do with me)sql
I have a table of product categories that looks like this (air code, so
could be some typos, but it's basically right)...
create table producttypes (
ptype int not null identity(1,1) primary key,
typename varchar(50) not null default '',
ParentCat int references producttypes(ptype)
)
This allows categories to contain subcategories and so on.
In order to allow me to see the hierarchy, I am using the following
code, modified from some found in "Inside SQL Server 7.0" by Kalen
Delaney.
-- SQL starts
declare @.level int, @.current int
create table #stack (depthlevel int, ptype int)
create table #orgchart (seqno int identity, orglevel int not null, ptype int
not null)
set rowcount 1
select @.level=1, @.current=ptype from producttypes where ptype=parentcat
set rowcount 0
insert into #stack (depthlevel, ptype) values (@.level, @.current)
while (@.level>0)
begin
if exists (select * from #stack where depthlevel=@.level)
begin
set rowcount 1
select @.current=ptype from #stack where depthlevel=@.level
set rowcount 0
insert into #orgchart (orglevel, ptype) select @.level, @.current
delete from #stack where depthlevel=@.level and ptype=@.current
insert into #stack select @.level+1, ptype from producttypes where parentcat=
@.current and parentcat<>ptype
if @.@.ROWCOUNT > 0 select @.level=@.level+1
end
else
select @.level=@.level-1
end
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno
drop table #stack, #orgchart
-- SQL ends
This produces a set of results that gives me all the categories. I can
use the orglevel field to tell what level I'm at, so if it changes, I
can see if I've gone up or down the hierarchy.
Now, the problem is that I can't work out how to set the order of
categories. For example, if the above SQL produces...
1 16 16 Products
2 17 16 Outdoor Toys
3 1 17 Trampolines
3 2 17 Slides
3 3 17 Swings
3 4 17 Accessories
3 5 17 Climbing Frames
3 7 17 Bicycles
you can see that the categories on level 3 are not in any obvious order.
I would like to have them in alphabetical order.
Any ideas how I would modify the above SQL to do this? TIA
Alan Silver
(anything added below this line is nothing to do with me)Try
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
The results should be sorted first by the orglevel, and results within the
same orglevel will be sorted alphabetically. The syntax reads like "order
results first by o.seqno, then by e.typename"
"Alan Silver" wrote:
> Hello,
> I have a table of product categories that looks like this (air code, so
> could be some typos, but it's basically right)...
> create table producttypes (
> ptype int not null identity(1,1) primary key,
> typename varchar(50) not null default '',
> ParentCat int references producttypes(ptype)
> )
> This allows categories to contain subcategories and so on.
> In order to allow me to see the hierarchy, I am using the following
> code, modified from some found in "Inside SQL Server 7.0" by Kalen
> Delaney.
> -- SQL starts
> declare @.level int, @.current int
> create table #stack (depthlevel int, ptype int)
> create table #orgchart (seqno int identity, orglevel int not null, ptype i
nt not null)
> set rowcount 1
> select @.level=1, @.current=ptype from producttypes where ptype=parentcat
> set rowcount 0
> insert into #stack (depthlevel, ptype) values (@.level, @.current)
> while (@.level>0)
> begin
> if exists (select * from #stack where depthlevel=@.level)
> begin
> set rowcount 1
> select @.current=ptype from #stack where depthlevel=@.level
> set rowcount 0
> insert into #orgchart (orglevel, ptype) select @.level, @.current
> delete from #stack where depthlevel=@.level and ptype=@.current
> insert into #stack select @.level+1, ptype from producttypes where par
entcat=@.current and parentcat<>ptype
> if @.@.ROWCOUNT > 0 select @.level=@.level+1
> end
> else
> select @.level=@.level-1
> end
> select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
> join producttypes as e on e.ptype=o.ptype order by o.seqno
> drop table #stack, #orgchart
> -- SQL ends
>
> This produces a set of results that gives me all the categories. I can
> use the orglevel field to tell what level I'm at, so if it changes, I
> can see if I've gone up or down the hierarchy.
> Now, the problem is that I can't work out how to set the order of
> categories. For example, if the above SQL produces...
> 1 16 16 Products
> 2 17 16 Outdoor Toys
> 3 1 17 Trampolines
> 3 2 17 Slides
> 3 3 17 Swings
> 3 4 17 Accessories
> 3 5 17 Climbing Frames
> 3 7 17 Bicycles
> you can see that the categories on level 3 are not in any obvious order.
> I would like to have them in alphabetical order.
> Any ideas how I would modify the above SQL to do this? TIA
> --
> Alan Silver
> (anything added below this line is nothing to do with me)
>|||>Try
>select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
>join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
>The results should be sorted first by the orglevel, and results within the
>same orglevel will be sorted alphabetically. The syntax reads like "order
>results first by o.seqno, then by e.typename"
Mark,
Thanks for the reply, but if you look carefully, it's not that simple.
Your suggestion will list all level 2 categories together, followed by
all level 3 and so on. This will not give the correct hierarchy as there
may be several level 2 categories each with subcategories (ie level 3).
If this isn't clear, look at the longer category listing shown later on.
This is a more complete list than the abbreviated one I showed before.
Note that the ordering is done on seqno, which is the order in which the
categories were put into the orgchart table. The ordering is not done on
the level at all.
I think I need to modify the way the categories are pulled out of the
producttypes table when they are inserted into the stack table. Trouble
is, I can't see how to control the ordering as they are being pulled one
at a time.
Thanks for the reply. Any further help would be appreciated.
Longer category listing follows...
level typename
1 Products
2 Outdoor Toys
3 Trampolines
3 Slides
3 Swings
3 Accessories
3 Climbing Frames
3 Bicycles
2 Indoor Toys
3 Snooker Tables
3 Boy's Toys
4 Hard Puzzles
3 Girl's Toys
4 Easy Puzzles
3 Board Games
3 Pets
4 Ferrets
4 Dogs
4 Cats
>"Alan Silver" wrote:
>
Alan Silver
(anything added below this line is nothing to do with me)sql
Wednesday, March 7, 2012
Pivottable 10/11 with SSAS 2005 cube 'hang' when using filter fields
Dear all,
I am running into some difficulties with an SSAS 2005 SP2 cube in combination with an OWC 11 pivottable client. Everything basically works fine.
The problem occurs when I make a selection with one or more dimensions in the 'filter' section.
For instance, if I build a pivottable which shows:
YEAR in the Colums
PRODUCT GROUP in the ROWS
CUSTOMER NAME in the ROWS
PRODUCT FAMILY in FILTER SECTION
SALES as Measure
As soon as I make a selection on YEAR (show only 2007) and PRODUCT FAMILY (show only 'bikes' and 'minibikes') , the pivottable basically hangs. On the server the memory usage of SSAS shoots up to 1.8(!) GB
After +/- 60 seconds I get a blank screen and sometimes: 'Cannot display data because of structural changes in the database'.
I tried to make the same selecion in my Excel2007 eval, which worked fine. Pivottable 10 gives the same error. The error also occurs in Visual Studio (off course also OWC11).
Altering the cube's storage settings did not help.
My cube's main facttable containts approx 2.5 mln records.
Is this a known problem, what can I do to fix it?
Many many thanks
Rex
Dear all,
After doing some research it seems that SP2 is causing the errors. When downgrading my SSAS 2005 to SP1 all problems vanished. Only problem now is the performance compared to SP2 which is significantly lower.
To really make sure it was SP2 I installed a fresh SSAS2005 machine without any servicepacks and built my cube, after intensive testing no problems.
Upgraded to SP1, no problems.
Upgrade to SP2, massive memory usage of the SSAS service and total lockup.
I also installed the cumulative 3161 post SP2 update, but this did not fix the issue.
The problem only occurs when filtering data in the pivottable's 'filter' section.
Any ideas?
|||Problem solved!!
After installing the Cumulative Hoftfixes 2 for SP2 (build 3175), released Jun 22nd the problem has disappeared and the performance of my cubes is great!
Link to the hotfix: http://support.microsoft.com/kb/936305
Subscribe to:
Posts (Atom)