Access data update problem

I have developed an Access 2002 fornt end for an SQL
database. When an form is run, it opens a limited set of
data that is updatable - the sql table is updated directly
from the access table, query or form. This works fine on
our intranet. When accessed from the internet, however, I
am only able to to update data in the table; when I run
the query or the form, the keyboard beeps when I try to
update any value. I have run the analyzer on the query,
and it shows that the dataupdatable value is false (inside
our intranet, the value shows true), but I cannot figure
out how to change this.
I have simplified the query to only draw information from
one table, but cannot update the data in the query
datasheet view or on a resulting form, only on the
original data table.Access is not designed for or intended to be used as the front end for
an Internet application. I'd look at using ASP.NET or some other more
suitable technology for the Internet.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 6 Feb 2004 14:40:42 -0800, "Don" <don@.ameritest.net> wrote:

>I have developed an Access 2002 fornt end for an SQL
>database. When an form is run, it opens a limited set of
>data that is updatable - the sql table is updated directly
>from the access table, query or form. This works fine on
>our intranet. When accessed from the internet, however, I
>am only able to to update data in the table; when I run
>the query or the form, the keyboard beeps when I try to
>update any value. I have run the analyzer on the query,
>and it shows that the dataupdatable value is false (inside
>our intranet, the value shows true), but I cannot figure
>out how to change this.
>I have simplified the query to only draw information from
>one table, but cannot update the data in the query
>datasheet view or on a resulting form, only on the
>original data table.|||Hi Don,
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
From the information you provided, you have an Access 2002 front end and a
SQL Server backend. I assume it is a ADP. You found every thing is fine on
intranet but when you run it in a internet environment, you found you could
only able to update data in the table but cannot update from query or form,
right?
Well, could you please provide some informaiton, which is helpful for our
analysis:
As you mentioned that you cannot change the value from the query and the
form, beside the keyboard beep, is there any message you got? You mentioned
that you could update the the record in table, you could please provide the
information of how you modified the record and could you confirm that the
modification is made on the SQL Server side? As a ADP, could provide the
information of the connection of it? You could get the information by
choose from the menu, 'File'->'Connection', then get the information of
Server Name, the authentication mode and related logins you use and the the
database you connect to? This information is much helpful to us.
I am standing by to you reply and thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Access connection to SQL Server

I have had requests form users who would like to use MS Access to connect to
a SQL Server. Are there any security or other issues that would be relevant
to this approach?
RodRod,
This can be slow when linking to and working with large SQL tables (i.e.,
many rows).
HTH
Jerry
"Rod Snyder" <rod@.rcsnyder.com> wrote in message
news:efs3voFxFHA.2880@.TK2MSFTNGP12.phx.gbl...
>I have had requests form users who would like to use MS Access to connect
>to a SQL Server. Are there any security or other issues that would be
>relevant to this approach?
> Rod
>|||Hi Rod,
You can use an ODBC link which would require setting up user accounts and
respective permissions/roles to use and manipulate (update/delete/insert/run
)
sql server objects like Databases, tables, stored procedures.
For large tables, it would be more efficient to retrieve data into MS Access
by calling a Sql Server Stored Procedure with parameters using COM ADO and
populate tables in Access with the retrieved data. You would write the
routines in Access in a code module using VBA. But you would still have to
define a user role for connecting to the Sql Server. For example, you don't
want people connecting to Sql Server using an SA account (system
administrator). That would give everyone access to everything, and they
could do anything. Instead, you have to define a user role which would limi
t
access to respective databases like User1, who can read tbl1, tbl2, ... can
run SP1, SP2, ... in database1 but not database2
User2 can read/write to tables and run SPs in database1 but not database2,
and so on.
Rich
"Rod Snyder" wrote:

> I have had requests form users who would like to use MS Access to connect
to
> a SQL Server. Are there any security or other issues that would be relevan
t
> to this approach?
> Rod
>
>sql

Access ComboBox Wont Populate

I'm using SQLServer2000/Access 2000 .adp. The .adp has a form with 2 combo boxes. The 1st combo box is bound to a stored procedure and loads fine when the form loads. I then set the 2nd combo box's RowSource passing the value of the first into the stored proc:

Me.secondComboBox.RowSource = "EXEC dbo.proc_My2ndProc '" & Me.cboFirstComboBox & "'"
Me.cbosecondComboBox.reQuery

Why won't it work? I've set a form's RecordSource using this methof, and it works great?

Thanks,
CarlDummy me, I didn't have the RowSourceType set to Table/View/StoredProc, all's well. I figured it out by creating a new form, and it was working, so I went to see what was different.

Access ADP,Server 2000, inconsistent behavior with output paramete

I am getting inconsistent responses from SQL Server 2000 and do not know why
.
I populate the controls on an unbound form by executing a command object tha
t
calls a sproc that returns a recordset.
On SQL Server 2000 the srpoc returns the recordset used to fill the form's
controls plus an output parameter indicating a record was received. The
output parameter is the value of @.@.ROWCOUNT. If the value of the output
parameter = 0, the user gets an appropriate message concerning the failure t
o
retrieve the data.
The server on my desktop returns both the recordset for populating the form
and the value of @.@.ROWCOUNT from the database. No problem.
However, when I connect to an identical dataase on a server in Chicago using
a VPN, all I get back is the recordset. The value of the output parameter is
always zero, telling me no record was selected.
However, the record was selected as the data populates the form.
I can easily branch to the needed message using "If rst.BOF and rst.EOF
then..." but I'd like to know why the output parameter does not get returned
or is always zero when I use the VPN to connect to the database.
If I use a command object to execute a sproc that does not return a
recordset, I do get back any desired output parameters. This inconsistent
behavior only appears when the executed command object returns a recordset.
Is there a setting on the Chicago server causing the problem?
malcolmHow are you sure that these two databases are really identical?
One possibility would be that the server in Chicago has a different order
for fields inside a table. This could cause some trouble with ADP is you are
using things like Select * instead of Select field1, field2, ... .
When you change the connection, the Tables and the Views/SP/Functions
windows should be refreshed but we never know what may have happened. One
way to be sure would be to use the Refresh command for *BOTH* the Tables and
the Views/SP/Functions windows and/or to recompile the ADP file.
Another possibility would be a permission problem or a logon account that is
not mapped to the same role.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"malcolm" <ramartower@.access312.com> wrote in message
news:00FCB54F-AEA4-43EB-A712-3B6FC3B68DA5@.microsoft.com...
>I am getting inconsistent responses from SQL Server 2000 and do not know
>why.
> I populate the controls on an unbound form by executing a command object
> that
> calls a sproc that returns a recordset.
> On SQL Server 2000 the srpoc returns the recordset used to fill the form's
> controls plus an output parameter indicating a record was received. The
> output parameter is the value of @.@.ROWCOUNT. If the value of the output
> parameter = 0, the user gets an appropriate message concerning the failure
> to
> retrieve the data.
> The server on my desktop returns both the recordset for populating the
> form
> and the value of @.@.ROWCOUNT from the database. No problem.
> However, when I connect to an identical dataase on a server in Chicago
> using
> a VPN, all I get back is the recordset. The value of the output parameter
> is
> always zero, telling me no record was selected.
> However, the record was selected as the data populates the form.
> I can easily branch to the needed message using "If rst.BOF and rst.EOF
> then..." but I'd like to know why the output parameter does not get
> returned
> or is always zero when I use the VPN to connect to the database.
> If I use a command object to execute a sproc that does not return a
> recordset, I do get back any desired output parameters. This inconsistent
> behavior only appears when the executed command object returns a
> recordset.
> Is there a setting on the Chicago server causing the problem?
> --
> malcolm

Absolutely page-bottom alignment on "report footer": Impossible?

Relative newb to SSRS here, but the answer to this question evades me; answers and insight are appreciated.

Report in question is an invoice form. It requires an absolutely bottom-of-page aligned footer that has databound elements.

This is so that whatever page that footer finally appears on will print in such a way that the address will align in a windowed envelope.

Ironically, Books Online gives this exact scenario in explaining headers and footers in SSRS, but they cleverly don't explain how an absolutely bottom-of-page-aligned and data-bound footer can be made to happen. Headers at absolute page top is obviously no problem. Footers at page bottom, not so much.

So, this is not a "page footer"--page footers are employed in the body. Also this footer is databound, so a page footer as it's known in SSRS is out the window anyway.

Most of the time this will print on a single page, but if it breaks to multiple pages, that footer needs to go all the way to the absolute bottom.

I grasp that the "report footer" for SSRS is just what appears at the end of any repeating controls that you've implemented in your body. Because SSRS uses this kind of repeating-control based idiom rather than a section-based idiom as Crystal does, this kind of (what I would consider very basic) positioning control is looking fairly impossible right now.

Among what I've tried:

--Page footer (can't; databound)

--Specifying a page break after the pre-footer controls, and/or a page break before the controls that make up the footer in their properties. This leads to unpredictable results with blank printed pages (as many as 8 for what previews as a 2-page report, how silly is that?).

--Putting in a page-height rectangle as part of the footer (with and without the page breaks mentioned above), with the idea of forcing a basically blank page at the end of the report so that the footer will go to the bottom. SSRS will go ahead and break the page anyway on long elements like that, which again leads to the "footer" being printed in the middle or top of the final page, or whereever it happens to fall.

I may be having to explain to my client that you can't get there from here, and they may have to redesign their report. Does anyone have any insight?

Thank you for your time in reading this.

Hi there Matty,

I think i got the idea that you want to print out the address of the customer at the bottom of the page (ideally the report page footer) but it won't allow you to have any databound values because the databound values are only for the body right ?

If that is the case, there is a common way to get around this.

This is what I do and it does work. Make a couple of textboxes (tb1 and tb2) in the Body of the report. Set the Hidden properties to True for the textboxes.

Next, drag or code into the tb1 and tb2 the dataset values; Name or address or whatever.

In the report footer or page footer, Set your textboxes and named them tbName and tbAddress.

in the expression builder for tbname, put this "=ReportItems!tb1.value" and in tbAddress put "=ReportItems!tb2.value"

When you run the report, tb1 and tb2 will be invisible (you can even send them to the back, hiding behind the table or something, but always make sure that the Hidden property = true). yet your page footer should have the values assigned to them.

I hope this helps and is according to what you are trying to describe.

Bernard Ong

|||

Kind of. The method you describe is pretty much what Books Online says about it. Key to the problem here is that this footer should only appear on the last page, which you get around by examining the page number using hidden fields as you describe, then making it visible as needed.

Problem is, though, that SSRS appears to 'reserve' the space for the header or footer whether it's visible or not, or you've specified Print On Last Page/First Page or not. It just doesn't render elements in that region but it does save the space.

This particular "footer" is literally 1/3 of a portrait page, so I can't have that kind of blank space being held on pages where it's not in use. I've also noticed issues in preview when you have invisible elements in use; although it'll print OK, the display is wildly off.

Very much appreciate your response, though. If you had a workaround up your sleeve for these additional issues with that method, then I'd be golden! :D

|||

As you said, set PrintOnLastPage=true for page footer and then use top padding to print it at the bottom of the page. You can also use expression to set the passing.

Shyam

|||Thank you, but unfortunately that also does not address the space reservation noted above that is critical to this particular use case. Use of the page footer here as the report footer denies it from a differently-formatted use in the report body when it spans pages, and it also causes the space to be reserved on the body pages; this particular report cannot use just 2/3s of the page on every page besides the last one.

Absolutely page-bottom alignment on "report footer": Impossible?

Relative newb to SSRS here, but the answer to this question evades me; answers and insight are appreciated.

Report in question is an invoice form. It requires an absolutely bottom-of-page aligned footer that has databound elements.

This is so that whatever page that footer finally appears on will print in such a way that the address will align in a windowed envelope.

Ironically, Books Online gives this exact scenario in explaining headers and footers in SSRS, but they cleverly don't explain how an absolutely bottom-of-page-aligned and data-bound footer can be made to happen. Headers at absolute page top is obviously no problem. Footers at page bottom, not so much.

So, this is not a "page footer"--page footers are employed in the body. Also this footer is databound, so a page footer as it's known in SSRS is out the window anyway.

Most of the time this will print on a single page, but if it breaks to multiple pages, that footer needs to go all the way to the absolute bottom.

I grasp that the "report footer" for SSRS is just what appears at the end of any repeating controls that you've implemented in your body. Because SSRS uses this kind of repeating-control based idiom rather than a section-based idiom as Crystal does, this kind of (what I would consider very basic) positioning control is looking fairly impossible right now.

Among what I've tried:

--Page footer (can't; databound)

--Specifying a page break after the pre-footer controls, and/or a page break before the controls that make up the footer in their properties. This leads to unpredictable results with blank printed pages (as many as 8 for what previews as a 2-page report, how silly is that?).

--Putting in a page-height rectangle as part of the footer (with and without the page breaks mentioned above), with the idea of forcing a basically blank page at the end of the report so that the footer will go to the bottom. SSRS will go ahead and break the page anyway on long elements like that, which again leads to the "footer" being printed in the middle or top of the final page, or whereever it happens to fall.

I may be having to explain to my client that you can't get there from here, and they may have to redesign their report. Does anyone have any insight?

Thank you for your time in reading this.

Hi there Matty,

I think i got the idea that you want to print out the address of the customer at the bottom of the page (ideally the report page footer) but it won't allow you to have any databound values because the databound values are only for the body right ?

If that is the case, there is a common way to get around this.

This is what I do and it does work. Make a couple of textboxes (tb1 and tb2) in the Body of the report. Set the Hidden properties to True for the textboxes.

Next, drag or code into the tb1 and tb2 the dataset values; Name or address or whatever.

In the report footer or page footer, Set your textboxes and named them tbName and tbAddress.

in the expression builder for tbname, put this "=ReportItems!tb1.value" and in tbAddress put "=ReportItems!tb2.value"

When you run the report, tb1 and tb2 will be invisible (you can even send them to the back, hiding behind the table or something, but always make sure that the Hidden property = true). yet your page footer should have the values assigned to them.

I hope this helps and is according to what you are trying to describe.

Bernard Ong

|||

Kind of. The method you describe is pretty much what Books Online says about it. Key to the problem here is that this footer should only appear on the last page, which you get around by examining the page number using hidden fields as you describe, then making it visible as needed.

Problem is, though, that SSRS appears to 'reserve' the space for the header or footer whether it's visible or not, or you've specified Print On Last Page/First Page or not. It just doesn't render elements in that region but it does save the space.

This particular "footer" is literally 1/3 of a portrait page, so I can't have that kind of blank space being held on pages where it's not in use. I've also noticed issues in preview when you have invisible elements in use; although it'll print OK, the display is wildly off.

Very much appreciate your response, though. If you had a workaround up your sleeve for these additional issues with that method, then I'd be golden! :D

|||

As you said, set PrintOnLastPage=true for page footer and then use top padding to print it at the bottom of the page. You can also use expression to set the passing.

Shyam

|||Thank you, but unfortunately that also does not address the space reservation noted above that is critical to this particular use case. Use of the page footer here as the report footer denies it from a differently-formatted use in the report body when it spans pages, and it also causes the space to be reserved on the body pages; this particular report cannot use just 2/3s of the page on every page besides the last one.|||

I too have a business requirement to put 'footers at the bottom' and have found (like a lot of others here) that SSRS doesn't make this easy using the built-in footer section. The real show stopper is that once you declare the size of the footer section at design time, that's what you get on each and every page.

The breakthrough for me was to recognize that footers as I needed them are really report content. When I took that approach, it became a solvable problem involving three items: the preceding content, the offset, and the notes (as I call them so as not to be confused with SSRS footers).

The offset is implemented as a subreport that has an invisible, variable length table. The length of the table is determined by a parameter, cleverly called the 'offset'. The invisible table acts as filler between the preceding content and the notes so that the notes are bottom justified.

The report gets the offset parameter by calling into business logic that uses:

the data for the preceding content,

the data for the notes content,

the page layout, and

the starting position of the preceding content

about store procedure

i have write several store procedure for export data,
most of the code such as :insert into Table1(col1,col2)
from select (col1,col2) form Table2,and i execute it in
sql analyser,and the analyser will show "x rows affected"
and now ,i want write a app with C#,call these store
procedures ,and want to konw
1,how many rows affected,analyser can return this?
2.how many rows selected,must i rewrite the store
procedure? such as add a new select count(*) from table2?
but this will decrease the perfermance
thank youCheck out SET NOCOUNT ON/OFF and @.@.ROWCOUNT in SQL Server Books Online.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you|||Frank..
One way some people do this is by returning a return status... Normally a
return status of 0 means the stored procedure was successfull, and a
negative return status means an error... Some people use a positive return
status to indicate how many rows were affected /select by the sp...
After the insert/select etc capture @.@.rowcount into a local variable..
declare @.error int, @.rowcount int
set nocount on
update ....
select @.rowcount = @.@.rowcount, @.error = @.@.error --I always capture
errors also
return @.rowcount
I always Set nocount ON as the first executable statement in an sp as well..
To use the return status
declare @.ret_status int
exec @.ret_status = myproc
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you

about select between help pls

well i have 2 input date form and to.

"date_from" varchar variable

and "date_to" varchar variable

where in i want to select only the dates between the two variables only.

example

date_from -date_to contains= "200702"-"200705"

and everything that starts from 200702 to 200705 will be output.

can someone help me or give some similar examples regarding this?

thanks...

Select * from Transaction Where Date_From >= (Cast(Year(TransactionDate) as varchar) + Cast(Month(TransactionDate) as varchar)) AND Date_To <= (Cast(Year(TransactionDate) as varchar) + Cast(Month(TransactionDate) as varchar))|||

My suggestion is not to use the BETWEEN operator, but instead use the >= and < operators. This, along with adding 1 day to the upper bounds of the "to" date, will help to avoid the pitfalls you can encounter when trying to work around the time portion of dates.

Try something like this:

DECLARE @.date_from datetime

DECLARE @.date_to datetime

SELECT @.date_from = '20070201', @.date_to = '20070530' -- note the the time portion of the date will default to midnight 00:00:00.0000

SELECT @.date_to = DATEADD(d, 1, @.date_to) -- add 1 day to the ending date

SELECT

someColumns

FROM

someTable

WHERE

myDate >= @.date_from AND myDate < @.date_to

|||

how about this one?

Public Sub read_records(ByVal nyutancd As String, ByVal date_from_new As String, ByVal date_to_new As String)

'配送依頼のデータ取得
'SQL作成
db = New TDataCenter.db.TDataCenter("venus", "venus", "threesupport", "postgres", "postgres")

date_from = ""
date_to = ""
syain_name = ""
product = ""

Dim objReader As PgSqlDataReader

'objReader = db.GetSQLReader(String.Format("Select distinct urr_urinenget,urr_etancd,urr_scd,urr_urikbn,urr_zurgsuu,urr_zurgkin,urr_zsirgkin,urr_curgsuu,urr_curgkin,urr_csirgkin,urr_gurgsuu,urr_gurgkinn,urr_gsirgkin,urr_turgkin,urr_tsirgkin,urr_kousindate from urr")))
objReader = db.GetSQLReader(String.Format("Select distincturr_urinenget,sn_syainnm,sy_snm,urr_urikbn,kt.kt_nm fromurr inner join sy on sy.scd = urr.urr_scd inner join sn on sn.syaincd = urr_etancd inner join kt on kt.kt_cd = urr.urr_urikbn and kt.kt_kbn='cm07'where urr_urinenget between {0} and {1} group by urr_urinenget,sn_syainnm,sy_snm,urr_urikbn, kt.kt_nm ", TDataCenter.db.TDataCenter.SingleQuatedStr(date_from_new), TDataCenter.db.TDataCenter.SingleQuatedStr(date_to_new)))

do you think it will work?

|||

natasha_arriell:

do you think it will work?

That approach is insecure. Do not use placeholders and string substitution; always use parameters.

I have no idea what processing SingleQuatedStr does to those string values. I have no idea what those string values contain. And I have no idea what sort of data is stored in the urr_urineget column. Your best bet is to use some edge case data and try it yourself to see if you are getting the desired result.

about search query

I would like to write query which can
1. ignore whether the search keyword is Upper case or lower Case
2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea

1. ignore whether the search keyword is Upper case or lower Case

SQL Queries are case insensitive.

2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea

No idea. I am unable to understand your question.
|||

sorry for my unclear idea!
What I want to do:
If people input the word in (a), the ColA contain word (b) will also be selected.
For example, select count(*) from tableA where ColA like '%compare%' , or sth like that

(a) (b)
1.CASW Casw

2.compare comparing

1. Since the keyword is in '', the case is sensitive, so Casw should not be select
2. Similary, since search compare will include provide result which have comparing,
Thx

|||I am sorry. I am still unable to understand your requirement. Can you try and xplain a little more?|||Thx for your patient!
Table A
Col_1 Col_hobby
Tom football, piano, reading, playing computer game, compare
Sam Football, piano, reading, comparing
When people use the query
select * from tableA where Col_hobby like '%Football%'
Only Tom record is selected, since the string in '' is sensitive the case. Any way to make it insensitve?
select * from tableA where Col_hobby like '%compare%'
Any way to make the query get the sam record?


|||Use the SQL Lower function to make the field in lower case and the searched keyword in lower case, that way you can get the desired result.|||you should get both records without having to worry about case.|||

ndinakar wrote:

you should get both records without having to worry about case.

Are you sure about that? I have tried several times a query like this:
select * from tableA where Col_hobby like '%Football%'
and it doesnt return the rows where col_hobby has the value 'football', but it does return the values with 'Football'|||

DECLARE @.tTABLE (colAvarchar(10), colBvarchar(100))

INSERTINTO @.tVALUES ('Tom','FOOTBALL,PIANO')

INSERTINTO @.tVALUES ('Sam','football,piano,volleyball')

SELECT*FROM @.tWHERE colBLIKE'%football%'
-------------

(1 row(s) affected)

(1 row(s) affected)

colA colB

---- ------------------------------

Tom FOOTBALL,PIANO

Sam football,piano,volleyball

(2 row(s) affected)

|||Thanks for the followup ndinkar. I always that my query didnt worked.

About login into sql server via asp.net

I want to design a form with C# that will able me to log in to my sql server with my sql login name and password.This should be the entry point, which is like the "SqlWebAdmin", Where i login first then have a view of databases that i have permission. The problem first is how to login into the sql server via ASP .net without knowing prior which database is mine?Any help plz?Most apps put their connect information in web.config, and read it out at run time.

About Crystal Report 9

hi everybody,
I am using VB6 and CrystalReport9. so how can i call a report
from my VB form? please tell me as soon as possible with example.
bye
bappyCheck developer documentation of Crystal. They have given pretty good examples. Look for CrystalDevHelp file.

Thanks|||hi,

Thanks for your replay. but i want Crystal Report Control OCX that have in Crystal 8.5. This control is easy to use for me. so please tell me have any option to use that OCX? or i have to use crystal report viewer control please let me clear about that.

bye

thanks again

bappy|||What if you use Crystal Report Viewer Control? I am not sure whether it's a OCX or OLE ? but what if you use this control (viewer) ? Does it involve lots of code change because version 10 uses Crystal Viewer control Extensively. So you should be prepared for that migration also in future.

Thanks|||I have come across this just now :

Retired Developer APIs

Crystal Reports ActiveX Control (crystl32.ocx)
The Crystal Reports ActiveX control is no longer supported and no longer available as of Crystal Reports version 9.

Thanks|||hi dilemma,
thanks for your replay. now i am trying to use viewer control.

bye

bappy|||Best of Luck , let me know if you find any problem.

Thanks|||Hi dilemma,

Thanks for your replay. Actually now I am using Oracle9i and Crystal 9. but here is a problem. I can build a report by using Crystal 9 in VB Project but when I am trying to show report form then an error generate the message is Login failed. I know that I have to pass user name, password, TNS name at runtime but I dont know about syntax. So can you tell me about that syntax or send me an example?

Please help me as soon as possible

Thanks for supporting me.

Bappy|||You need to code something like this.

How to connect to an OLEDB data source
This example demonstrates how to connect to an OLEDB (ADO) data source, change the data source, and change the database by using the ConnectionProperty Object, as well as how to change the table name by using the Location property of the DatabaseTable Object,. CrystalReport1 is created using an ODBC data source connected to the pubs database on Microsoft SQL Server. The report is based off the authors table.

' Create a new instance of the report.
Dim Report As New CrystalReport1

Private Sub Form_Load()
' Declare a ConnectionProperty object.
Dim CPProperty As CRAXDRT.ConnectionProperty
' Declare a DatabaseTable object.
Dim DBTable As CRAXDRT.DatabaseTable

' Get the first table in the report.
Set DBTable = Report.Database.Tables(1)

' Get the "Data Source" property from the
' ConnectionProperties collection.
Set CPProperty = DBTable.ConnectionProperties("Data Source")

' Set the new data source (server name).
' Note: You do not need to set this property if
' you are using the same data source.
CPProperty.Value = "Server2"

' Get the "User ID" property from the
' ConnectionProperties collection.
Set CPProperty = DBTable.ConnectionProperties("User ID")

' Set the user name.
' Note: You do not need to set this property if
' you are using the same user name.
CPProperty.Value = "UserName"

' Get the "Password" property from the
' ConnectionProperties collection.
Set CPProperty = DBTable.ConnectionProperties("Password")

' Set the password.
' Note: You must always set the password if one is
' required.
CPProperty.Value = "Password"

' Get the "Initial Catalog" (database name) property from the
' ConnectionProperties collection.
' Note: You do not need to set this property if
' you are using the same database.
Set CPProperty = DBTable.ConnectionProperties("Initial Catalog")

' Set the new database name.
CPProperty.Value = "master"

' Set the new table name.
DBTable.Location = "authors2"

Screen.MousePointer = vbHourglass
' Set the report source of the viewer and view the report.
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
Screen.MousePointer = vbDefault
End Sub

Thanks