Best way to run lots of SQL commands? 
Author Message
 Best way to run lots of SQL commands?

After having gone through the manuals and such I am a little lost on how to
simply program some SQL commands on a dataset, and manipulate the results.

I am using Visual Basic 6.0 and the databases are currently in Access 2000.

One approach is to use Opendatabase command with a Query Def.  But apparently
the source cannot be a microsoft jet database, which I believe is what Access
is. Then again, I am not sure.



Wed, 21 May 2003 09:51:10 GMT  
 Best way to run lots of SQL commands?

I'm just learning VB6 too.  Heres my first successful attempt and populating
a FlexGrid using SQL.  I run the populate function on the form_load of the
main form.

Private Sub populate()
    Data1.DatabaseName = "c:\vb\vb.mdb"
    Dim mysql$
    mysql$ = "SELECT * from Employees"
    Data1.RecordSource = mysql
    Data1.Refresh
    ShowRec
End Sub

Also, remember that the datasource of the grid is "Data1".

HTH,
Paul

----------


Quote:
> After having gone through the manuals and such I am a little lost on how
to
> simply program some SQL commands on a dataset, and manipulate the results.

> I am using Visual Basic 6.0 and the databases are currently in Access
2000.

> One approach is to use Opendatabase command with a Query Def.  But
apparently
> the source cannot be a microsoft jet database, which I believe is what
Access
> is. Then again, I am not sure.



Wed, 21 May 2003 10:06:48 GMT  
 Best way to run lots of SQL commands?
Access uses querydef objects okay and it is also a jet system - so the
opendatabase function works very nicely provided you have a reference to DAO
in your project. Running queries is pretty simple - you don't actually have
to create a querydef object unless you want to store the query for all time
in the database or if you are using it as a through query.

To do this create your SQL string and then enter;

db.execute [SQL String],[options]

where db is a defined database variable.

To create the querydef object, again create your string then;

Set qd = db.CreateQueryDef("MyQueryName",[SQL String])

Where qd is a defined querydef object and db is the same as before.

qd.Execute [Options]

runs the query.

Hope this is of some help.

Cheers,

Davy


Quote:
> After having gone through the manuals and such I am a little lost on how
to
> simply program some SQL commands on a dataset, and manipulate the results.

> I am using Visual Basic 6.0 and the databases are currently in Access
2000.

> One approach is to use Opendatabase command with a Query Def.  But
apparently
> the source cannot be a microsoft jet database, which I believe is what
Access
> is. Then again, I am not sure.



Fri, 23 May 2003 03:00:00 GMT  
 Best way to run lots of SQL commands?
Thanks. So the OpenDatabase command will work the most efficiently? I want to
return the resuls so I believe the execute command will not work(?).

I have to calculate 20 averages across at most 100,000 records (most are
about 10,000).  I can either loop through the database as in more traditional
programming or I can use SQL. After a quik test, It took less than a second
to calculate an average on a 100,000 record file, so I thought what the hell,
I will go this route.  But I wanted to find the most efficient way to do SQL
using VB and Access.


hme0>:

Quote:
>Access uses querydef objects okay and it is also a jet system - so the
>opendatabase function works very nicely provided you have a reference to DAO
>in your project. Running queries is pretty simple - you don't actually have
>to create a querydef object unless you want to store the query for all time
>in the database or if you are using it as a through query.

>To do this create your SQL string and then enter;

>db.execute [SQL String],[options]

>where db is a defined database variable.

>To create the querydef object, again create your string then;

>Set qd = db.CreateQueryDef("MyQueryName",[SQL String])

>Where qd is a defined querydef object and db is the same as before.

>qd.Execute [Options]

>runs the query.

>Hope this is of some help.

>Cheers,

>Davy



>> After having gone through the manuals and such I am a little lost on how
>to
>> simply program some SQL commands on a dataset, and manipulate the results.

>> I am using Visual Basic 6.0 and the databases are currently in Access
>2000.

>> One approach is to use Opendatabase command with a Query Def.  But
>apparently
>> the source cannot be a microsoft jet database, which I believe is what
>Access
>> is. Then again, I am not sure.



Fri, 23 May 2003 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. dropdownlists and grids, which ways is the best?

2. good ways to use recordsets without autocommit?

3. Better ways to schedule?

4. good ways to use recordsets without autocommit?

5. Better ways to put text into textbox??

6. Best way to run command line?

7. Best Way to Run a Command Line Program

8. Changing SQL in a child command at run time

9. Run Sql Commands on XML data

10. Running SQL Scripts via Command Object

11. adding lots and lots of pictures to the executable

12. Lots and Lots of Controls slow things down....

 

 
Powered by phpBB® Forum Software