create and drop table via vb

Hi,
How can i create and drop table in MS SQL Server 2000 via VB6? I think I should use ADOX object, but I don't know exactly how...
The following code uses ADO connection object and returns with runtime error "incorrect syntax near AS":

Dim db as ADODB.Connection
'... open connection to database

Dim strCmd As String

strCmd = "CREATE TABLE tmp_tbl AS SELECT * FROM tbl"

db.Execute strCmd

Thank you in advanceThe code you are using works on an Oracle database. The syntax for SQL Server is "SELECT...INTO...FROM".

SELECT * INTO newTable FROM oldTablesql

CRAXDRT.Application.OpenReport - VERY slow

I have a CR 8.0 report that's been running against MySQL through VB6 for several years now, without ANY problems.

Just a few weeks ago we changed to new servers, and all my reports are running just fine, except one, which also happens to be the biggest (~2.9M). The report is running from a network drive, although I've tried it on a local drive with no real difference in perforamnce. Here's the code I'm using to open the report:

Dim cr As New CRAXDRT.Application
Dim report As CRAXDRT.report

Set report = cr.OpenReport(fName)

Again, nothing special. This has run quite well until now. The query itself that the report generates is quite large, but executes against the MySQL server in around .2 seconds, which isn't nearly the delay I'm seeing.

This call to OpenReport takes close to 20 seconds. I'm sure this has something to do with the server change, but without information, there isn't anything that I can do do fix it...

Any ideas?I forgot to add: The report runs very fast in the Crystal Report IDE.|||Dear ejj,

Have you by any chance found the solution yet ?

I'm experiencing exactly the same problem as you described. We recently migrated our NT4 domain to a WIN2003 domain (with some new servers) and after that some reports have become slow when calling the openreport function.

The only difference is that I'm using CR9 and the report is also opening slow in the CR Developer.

Best regards,
TheDaoka|||Dear ejj,

Have you by any chance found the solution yet ?

I'm experiencing exactly the same problem as you described. We recently migrated our NT4 domain to a WIN2003 domain (with some new servers) and after that some reports have become slow when calling the openreport function.

The only difference is that I'm using CR9 and the report is also opening slow in the CR Developer.

Best regards,
TheDaoka

Well, I fixed it, but never found what the problem was.

Thourgh trial and error I removed stuff from the report until it got faster. There was a subreport in the report that I deleted and the whole thing spead up. I then re-created the SAME subreport with the SAME query and magically everything got faster.

I can't explain it. Same database, same data, same schema, etc. But, it works now. :confused:|||Hi Ejj,

I just found this in the latest hotfixes for CR9:

ADAPT00212276 Patch ID: 35846238
Description:
When a report contains subreports that are imported through UNC paths, the report is slow to open when the network
connection is down.
Crystal Reports checks to see if the UNC pathname exists, before it checks to see if the user wants to re-import the
subreport. This causes an uncecessary delay in opening the report.
New Behavior:
The order of operations when opening a report is reversed: the new algorithm checks the pathname after the user
chooses to re-import.

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

I have some reports with subreports which were imported from a server that no longer exists.

I installed the latest CR dll's and now it's back to normal.

Best regards,
TheDaoka|||TheDaoka -

Thanks for the information! Don't know why I never came across that in my search, but that sounds exactly like the problem I was having.

Glad you found it! Thanks!

CR8.5 and VB6 - I dont want it to create the form!

Every time I add a new Crystal Report to my VB6 project it used to ask if I wanted to add a form with the viewer control on it automatically (it also asked something else, but I can't remember what). I got sick of hitting "No" then "OK" so I checked the "don't ask me this question again" style box.

Now, every time I add a report it also adds a form to my project with the viewer control. I don't want it to do this, as it's annoying removing the useless form and it is screwing with my source control. And it does it even though when i checked the "don't ask me... " i told it not to create the form.

So I've checked all the settings I can find and I can't find anything to stop it happening. Does anyone have a quick fix to this before I go trawling through the registry to find a solution?update - i trawled the registry, found some likely candidates, but none of them worked. It's real frustrating, so any help will be appreciated, but I can live with it (I've lived with worse)

CR10 and vb6

Hi to all,
Im still a newbie in cr10, Im making a report from my database using vb6 as it's front end. I have a question regarding filters in CR10 since in data environment as we know we can do this dte1.rsCmd.Filter or
dte1.rsCmd.Requery

How do i do this in CR10? I have a customized filters ( a series of cbo boxes to filter search in database) in my form..

Any help is welcome guys. I really need help

Thanks in advance!Hi again,
I hope i stated my question clear. Im looking for a way to filter my database by using the vb6 cbo boxes and printing it using crystal report 10.. Im using MySQL as my database. Any ebooks or tuts, suggestions are welcome.|||You can use selection formula
CR.selection formula=Column='Some value'"

or

CR.SQLQuery="YourQuery"

CR XI and VB6 prints some blank rows

I've been trying to move some more complex reports from VB DataReports to CR.

I have a CR report that is linked at runtime to a ADO recordset. Although the recordset has some NULL data, entire rows of information will come up blank. If I put in a null substitution in the fields, the substitution shows up. The correct number or rows are printed in the report, just some are blank.

Here some basic facts:
1. If I link to a VB6 DataReport, all data is displayed.
2. I setup a form with a listView to print out the exact recordset that I'm linking and all the data is present.
3. If I just run the query in SQL Enterprise Manager, it returns the correct data.
4. It's not random, it is always the same records (not matter how their selected) that show up blank.

Is there something that would tell CR to ignore a whole line if some or any of the values are null?Sorry...this appears to be a ADO issue...my mistake.

CR prints no record although there is records in dbase

Dear all,

I found a problem and this makes me frustrated.
I'm using VB6 and CR 8, When I saved a transaction and print invoice, there is no data printed on CR (I'm sure the there is a record saved in database). The big matter is it's happened sometimes.
It runs ok for several transaction and happen again.
I'm really confused, I've tried to close all connection before printing invoice. But it still happened.
Please let me know what should I do.
Urgent..

Thank you.One thing that gave good result to me, was :
In VB6, before process the report:
Close the tables and database & execute DoEvents
ReOpen the database and tables (if needed) after preview or print the report

CR Out of Memory Error

I am using VB6(SP6) / CR(8.0.1) / WISE INSTALLER(8.1) to distribute a small program. The data is all contained in MS Access 2000 tables and I am using ADO to create each report recordset.

In CR designer I use a TTX file to design the report. This TTX file contains all colums from the recordset and their descriptions i.e. Name, Type, Length and example data.

I create a distribution CD using Wise Installer. Once installed on a Win98, Win2000, Win XP computer everything runs great until the user attempts to run any of the CR reports. On screen the SQL query runs and then calls the CR report. The CR viewer appears and immediately we get an "Out of Memory" error response from CR.

My only remedy is to install MS Office Pro or MS Access 2000-2002. I have use (paid for support) at VB, CR and Wise and received no fix for this problem. VB says it CR, CR says its VB and Wise simply doesn't know?

Thanks in advance for any help on this problem...Maybe you have a memory leak in your VB program?|||Don't think this is the problem... as installing access or office pro solves the problem. This could not fix a memory leak inside the vb program. This problem only occurs if we install to a new computer..

Thanks for your reply

CR 8.5, VB6, Crviewer how to preset Copies

Ok, I know how to use printout feature and can set number of copies this is fine when going directly to printer, But I have endusers that want to preview the report befre printing.

They however make an easy mistake, the document they are previewing has a requirement of 3 copies when printed. The preview has no obvious way to set that and of course startup with 1 copy. I know I could add a line to run a printout of 2 copies but the previewer form is used by more than one report.

I just want to know is there an easy way to set the copies in the previewer print.

Thanks in advance.This code goes into the VB form that your CRViewer is located. I named mine CRViewer. From the Left drop down list in your code window, choose CRViewer (or whatever you named it), in the right Dropdown list, choose PrintButtonClicked.

Private Sub CRViewer_PrintButtonClicked(useDefault As Boolean)

Dim intCopies As Integer

useDefault = False

intCopies = 3

Report.PrintOut False, intCopies

End Sub

CR 8.5 - (almost) blank first page

Hi,

Having a problem with crystal reports 8.5 (from within VB6). searched and searched, to no avail, so now I post... :)

i have a report with the following layout:
-Report header
-Page header
-Group header 1
-detail
-group footer 1
-page footer
-report footer

Problem is, if group 1 will not fit on the first page of the report, then the first page will print with only the report header and report footer on it - nothing else.

I've tried unchecking all the "Keep together" options on the sections but it doesn't change a thing. any advice people? i found an article talking about this problem and how V10 fixes it, but surely there is a workaround (or at least something). Frankly, I don't care if it puts half a section on a page if it will fit complete on the next - i just don't want this silly blank first page printing.Have you remembered checking off the Keep Group together option in both the Change Group Options and Format Section Menues? Doing this for all groups has worked for me this far.

- Jukka|||Great stuff - it works. Thanks heaps for actually finding this post and replying to it. I'd given up!

Counting fields with data

I call a Crystal Reports 8 report from VB6. In the report there is eight fields as part of each record. Some of those eight fields will contain data and some won't. Random order.

How can I keep count of how many fields has data in them for each record. I need to display a count at the end of the record i.e. 3/8 or 0/8 etc.

I think the idea would be to create a counter that can be initialised to 0 for each record and as each field is displayed it must increment the counter but only if there is data displayed in the field so if 3 fields (any 3 of the 8 fields) contain data the counter will be 3 for that particular record and the counter can then be displayed.

Can this be accomplished and how?

Bezziewrite a formula like

numbervar a;
a:=0;
if not isnull(databasefield1) then
a:=a+1;
if not isnull(databasefield2) then
a:=a+1;
a;|||Thanks sraheem.|||Another way is to create a Running Total for each field and add them together at the end of the report.

Counting Connections

I have a VB6 program that uses ADO to communicate with a SQL Server database
(and an Access database optionally). Is there a way that I can tell how
many connections that there are at any one time? I'd like to do this to
Access as well if possible?
Rick Ledermanselect count(*) from master.dbo.sysprocesses
where SPID > 50
The 'where SPID > 50' restriction eliminates system connections. I have no
clue how to do this in Access.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rick Lederman" <rick@.soaringsoftware.com> wrote in message
news:e3wkopEwEHA.3808@.TK2MSFTNGP15.phx.gbl...
> I have a VB6 program that uses ADO to communicate with a SQL Server
database
> (and an Access database optionally). Is there a way that I can tell how
> many connections that there are at any one time? I'd like to do this to
> Access as well if possible?
> Rick Lederman
>|||Goeff,
Thanks, that works great, now to see if I can find how to do it in Access.
Rick
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23MUhVvEwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> select count(*) from master.dbo.sysprocesses
> where SPID > 50
> The 'where SPID > 50' restriction eliminates system connections. I have
no
> clue how to do this in Access.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rick Lederman" <rick@.soaringsoftware.com> wrote in message
> news:e3wkopEwEHA.3808@.TK2MSFTNGP15.phx.gbl...
> database
>|||"Rick Lederman" <rick@.soaringsoftware.com> wrote in message
news:e3wkopEwEHA.3808@.TK2MSFTNGP15.phx.gbl...
> I have a VB6 program that uses ADO to communicate with a SQL Server
database
> (and an Access database optionally). Is there a way that I can tell how
> many connections that there are at any one time?
By one instance of your application on one computer, or by all copies of
your application on all computers?

> I'd like to do this to Access as well if possible?
With VB6 and ADO you can control the number of connections in use. In
Access, if you use ADO you can also control the connection count in use.
There is absolutely no way to tell how many connections will be used (or are
being used) by Access if you are relying on Jet to manage the database
access. The query that Geoff provided will give you an idea of how many
connections are use at any given time.
Steve|||Try using a Pass-through query in Access to get the info.
-Sue
On Mon, 1 Nov 2004 15:13:53 -0500, "Rick Lederman"
<rick@.soaringsoftware.com> wrote:

>Goeff,
>Thanks, that works great, now to see if I can find how to do it in Access.
>Rick
>"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
>news:%23MUhVvEwEHA.1984@.TK2MSFTNGP14.phx.gbl...
>no
>