Change Access Db to server on connection on CR9

I am using VB6 and CR9.

I have some CRs using Access as DB and I am converting them to conect to SQL server DB. What I did now is to change their connection to SQL at design time. But it took long time to reinput the fields on the report since DB has been changed. I wonder if there is better way to do that?

Thanks a lot for any input.try "Database" set datasource location (you will build the new connection) click on table names in both windows, click update. If it finds a match it will map your report to the new location and you won't need to change to many formulas.

Cellset.open hangs, query works fine in Management Studio?

Hi All,

We are experiencing a very strange Cellset behaviour.

Trying to open a cellset in VBA (same in VB6) in Excel 2003, using ADOMD.Catalog, accessing a AS2005SP2, the system 'sometimes' hangs, depending on the 'dynamic' MDX query string. While the query works fine in the SQL Management Studio.

For example, this MDX works fine:

select descendants {[Measures].[NumberOfA]} on columns, [Time].[Month].[200502] on rows

from [DWH]
where {([Client].[Hierarchy].[Group].&[G000879])}

This one causes Excel (and VB6) to freeze, but nicely returns a result in SQL Management Studio!!!?

select descendants {[Measures].[NumberOfA]} on columns, [Time].[Month].[200501] on rows

from [DWH]
where {([Client].[Hierarchy].[Group].&[G000879])}

There is nothing wrong with the data, it's more connection related (I expect...)?

Does somebody have any advice?

PS: we have msxml6,asoledb9 installed

It is a firewall issue. See also: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1558402&SiteID=1

Cheers,

Tom

Catch MSSQL triggers in VB6

Hi
Is there a way to run VB code when a trigger is executed ? Maybe to define a VB event that will occur when a trigger is executed ??
I've tried googling...
Thanks,
Inon.The simplest way to run VB code from a trigger is to create a character mode executable (one with no GUI component). The trigger can start this running on the server using master.dbo.xp_cmdshell calls.

While it is possible to contrive an example where the server launches an application on the client, it is neither easy nor practical. I personally wouldn't try it.

-PatP

Catch Duplicate records

I have a table that I am inserting records to via VB6 using an ADO command.
If I try to insert a duplicate record I get the error message
-2147217873 Access Primary Key violation etc
This error doesn't jump to my error handler so how can I catch it? is there
something I can do from with SQL? or am I missing something in VB
ThanksThe best way to handle the error is to prevent it from happening in the firs
t
place. That can be done by, for example, checking to see if the value exists
before you try to insert it.
You can certainly catch this error in VB and I would propose that is the bes
t
place to do it.
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:%239gwgADTFHA.3544@.TK2MSFTNGP10.phx.gbl...
>I have a table that I am inserting records to via VB6 using an ADO command.
> If I try to insert a duplicate record I get the error message
> -2147217873 Access Primary Key violation etc
> This error doesn't jump to my error handler so how can I catch it? is the
re
> something I can do from with SQL? or am I missing something in VB
> Thanks
>|||If u use stored procedure
u can return value from it
e.g
....
.....
...
INSERT INTO ....
......
RETURN @.@.ERROR
in VB u must check the return value if return value != 0
then an error occuerd. In case everything ok zero will returnd
Message posted via http://www.webservertalk.com|||Thanks, I have tried this
declare @.Stockcode char(30)
declare @.Barcode char(20)
declare @.Qty int
set @.Stockcode = '123456'
set @.Barcode = '1234567890123'
set @.Qty = 12
INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
RETURN @.@.ERROR
GO
but I get the error
A RETURN statement with a return value cannot be used in this context.
What am I doing wrong?
"E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
> If u use stored procedure
> u can return value from it
> e.g
> ....
> .....
> ...
> INSERT INTO ....
> ......
> RETURN @.@.ERROR
>
> in VB u must check the return value if return value != 0
> then an error occuerd. In case everything ok zero will returnd
> --
> Message posted via http://www.webservertalk.com|||Again, prevent the error from happening by checking for the value. Given tha
t
you did not specifiy the PK I'll use the Force and guess:
Create Table dbo.OuterBarCodes
(
StockCode Char(30)
, BarCode Char(20)
, Quantity Int
, Constraint PK_OuterBarCodes Primary Key (StockCode, BarCode)
)
If Not Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
If you really want to know whether the value exists then do this:
If Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Begin
Raiserror('Duplicate StockCode and BarCode', 16, 1)
Return
End
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:ODBIBhDTFHA.560@.TK2MSFTNGP10.phx.gbl...
> Thanks, I have tried this
> declare @.Stockcode char(30)
> declare @.Barcode char(20)
> declare @.Qty int
> set @.Stockcode = '123456'
> set @.Barcode = '1234567890123'
> set @.Qty = 12
>
> INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
> RETURN @.@.ERROR
> GO
> but I get the error
> A RETURN statement with a return value cannot be used in this context.
> What am I doing wrong?
> "E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
> news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
>