Visual Basic&database, Best way to run lots of SQL commands?

Best way to run lots of SQL commands?

Author	Message
David Wrig #1 / 4	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

Paul Whit #2 / 4	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

David Brow #3 / 4	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

David Wrig #4 / 4	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

Page 1 of 1

[ 4 post ]