access and Sql

I changed the archive of a program in VB6 that used
Access 97 Database via DAO USING JET 3.5 passing it to SQL
server (sqloledb1 driver) and using ado 2.7 and with
surprise (The file structure and data is not grown) I find
now the program slower than before using access file' It
is possible? Sql server is slower to access the file than
access' or i make something wrong"davidex" <anonymous@.discussions.microsoft.com> wrote in message
news:318901c3b033$a560efc0$a601280a@.phx.gbl...
> I changed the archive of a program in VB6 that used
> Access 97 Database via DAO USING JET 3.5 passing it to SQL
> server (sqloledb1 driver) and using ado 2.7 and with
> surprise (The file structure and data is not grown) I find
> now the program slower than before using access file' It
> is possible? Sql server is slower to access the file than
> access' or i make something wrong
Don't multi-post. See thread in .programming.
David

Accelerating MS SQL Client

Does a product exist that might solve the following problem?

I have an application developed in VB6 that accesses data residing on a
central Microsoft SQL server in our datacenter. While I would argue
that this application stinks, I have been assigned to deploy it to the
laptops of about 100 people. These people travel to sites and connect
to the SQL server using a VPN connection and MDAC 2.8. In testing, I
found that the performance of this application suffers when connecting
to SQL via VPN, and since this application seems to hammer SQL when
loading menus in the least optimum way possible, the program will hang
while waiting for data.

Is there software I can install on the users' laptops that might
"accelerate" the connection to the SQL server? Perhaps this software
might be an alternative to the Microsoft-provided SQL client? I saw a
product called ZCache that seems to provide some caching functionality
for Oracle and MySQL databases, but nothing for MS SQL.Joe (josephtermine@.hotmail.com) writes:
> Does a product exist that might solve the following problem?
> I have an application developed in VB6 that accesses data residing on a
> central Microsoft SQL server in our datacenter. While I would argue
> that this application stinks, I have been assigned to deploy it to the
> laptops of about 100 people. These people travel to sites and connect
> to the SQL server using a VPN connection and MDAC 2.8. In testing, I
> found that the performance of this application suffers when connecting
> to SQL via VPN, and since this application seems to hammer SQL when
> loading menus in the least optimum way possible, the program will hang
> while waiting for data.
> Is there software I can install on the users' laptops that might
> "accelerate" the connection to the SQL server? Perhaps this software
> might be an alternative to the Microsoft-provided SQL client? I saw a
> product called ZCache that seems to provide some caching functionality
> for Oracle and MySQL databases, but nothing for MS SQL.

That sounds like about an impossible thing for me. Then again, if there is
such a product for Oracle or MySQL, I guess it should be possible for
MS SQL Server. Yet then again, the accellerating effect of that product
may apply only to certain usage patterns.

Of course, if one analyses what this product is upto, it is possible
that you could implement a local cache, but it would certainly be
far cheaper to fix the application itself. It sounds if there are
tons of roundtrips and server-side cursors in it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Joe" <josephtermine@.hotmail.com> wrote in message
news:1108413195.029686.70090@.z14g2000cwz.googlegro ups.com...
> Does a product exist that might solve the following problem?
> I have an application developed in VB6 that accesses data residing on a
> central Microsoft SQL server in our datacenter. While I would argue
> that this application stinks, I have been assigned to deploy it to the
> laptops of about 100 people. These people travel to sites and connect
> to the SQL server using a VPN connection and MDAC 2.8. In testing, I
> found that the performance of this application suffers when connecting
> to SQL via VPN, and since this application seems to hammer SQL when
> loading menus in the least optimum way possible, the program will hang
> while waiting for data.
> Is there software I can install on the users' laptops that might
> "accelerate" the connection to the SQL server? Perhaps this software
> might be an alternative to the Microsoft-provided SQL client? I saw a
> product called ZCache that seems to provide some caching functionality
> for Oracle and MySQL databases, but nothing for MS SQL.

If you just connect VB6 bound datagrids to sql server and that's your lot
then you'll potentially get a hell of a lot of IO.
Plus the ones in the box didn't work so well, maybe they're patched or third
party or whatever.
It does sound that the app was developed without considering what's likely
over something like a 28k connection.

I've used vpn.
It can be very slow.
It can also have a limit on concurrent users, depending on how you're
vpn-ing.

Personally, I would have thought caching some data in a local database of
some sort a must.
At least for the sort of app this sounds like.
Whether that would best be an access mdb, full msde installation or whatever
would depend on the details.
vb.net and xml on the laptops would be another alternative but quite likely
involving a complete rewrite.

--
Regards,
Andy O'Neill|||I suspect that this application does use the VB6 grids out of the box,
and these are known to be resource-intensive. The newer VB.NET grids
seem to perform better in disconnected environments.

Anyway, there doesn't seem to be much we can do if we don't have access
to the VB6 source code.

Might there be a VPN accelerator we could try? Would this help us much?|||There are tons of round-trips with this application, but as we don't
have access to the VB6 source (this is an off-the-shelf product) there
might not be much we can do to fix the inefficiencies or implement a
local cache. There's a lot to think about.|||Joe (josephtermine@.hotmail.com) writes:
> There are tons of round-trips with this application, but as we don't
> have access to the VB6 source (this is an off-the-shelf product) there
> might not be much we can do to fix the inefficiencies or implement a
> local cache. There's a lot to think about.

The one thing I can think would be replication. Assuming that you can
update from the app, that would be merge replication.

And, no, I have experience of merge replication. (And not much of other
sorts of replication either.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 15 Feb 2005 10:33:18 -0800, Joe wrote:

> I suspect that this application does use the VB6 grids out of the box,
> and these are known to be resource-intensive. The newer VB.NET grids
> seem to perform better in disconnected environments.
> Anyway, there doesn't seem to be much we can do if we don't have access
> to the VB6 source code.
> Might there be a VPN accelerator we could try? Would this help us much?

Perhaps this could help?
http://sqlrelay.sourceforge.net/

It looks complicated, but it might suit you.

Replication of the database to a local MSDE might be another choice.|||"Joe" <josephtermine@.hotmail.com> wrote in message
news:1108492398.045929.319930@.l41g2000cwc.googlegr oups.com...
>I suspect that this application does use the VB6 grids out of the box,
> and these are known to be resource-intensive. The newer VB.NET grids
> seem to perform better in disconnected environments.

I used to use flexgrid to display data, user clicks on a row and gets a
bunch of text/combo boxes to work on it .
I worte my own code to update.
This was because of the bugs in the datagrid.
You might want to investigate those.
I forget but there are circs when updates just didn't happen.

WIth dotnet I use the datagrids and they're good.
The dataadaptor and (disconnected) dataset functionality is very useful.
There would be issues meant added code in an app where a salesman wants to
use the thing completely disconnected from the database.

> Anyway, there doesn't seem to be much we can do if we don't have access
> to the VB6 source code.

Well... that's a mistake someone's made right there.

> Might there be a VPN accelerator we could try? Would this help us much?

No idea mate.

--
Regards,
Andy O'Neill

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