use VB to query database

I have a table in MS Access (or MS SQL server). the table has two fields like below.

709.0 1220.0
391.0 1181.0
295.0 303.0
289.0 1303.0
2714.0 303.0
2745.0 1303.0
261.0 1254.0
And so on…

how can I use sql query in VB to query some result (ex. mean, max, avg in field 1) and then assign to a variable. something like below.

Dim max_num as double
max_num = select max(field1) from table1

I am familiar with SQL query, but I don't know how to connect to Access and then get query result from Access (or MS SQL server).
rmtogetherAsked:
Who is Participating?
 
Davidshc76Connect With a Mentor Commented:
Set rsDump = New ADODB.Recordset
rsDump.CursorLocation = adUseClient
rsDump.Open "select max(field1) as max_num, min(field1) as min_num from table1", g_objconn

MsgBox "Your max_num value is " & rsDump!max_num
MsgBox "Your min_num value is " & rsDump!min_num

For avg or sum : ---
The field must be in integer........
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
VB and Access VBA will not let you assign variable = SQL statement, as SQL Server SP's do, so you have to do this...

Dim cn as ADODB.Connection
Set cn = {Your Access database}

Dim rs as adodb.recordset
Set rs = New adodb.recordset

rs.Open "select max(field1) as max_num from table1", cn

msgbox "Your max_num value is " & rs!max_num
0
 
rmtogetherAuthor Commented:
I got some error, do I need add some component into VB before using ADODB?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I got some error
Please tell us the error number, description, and the line that generates it.
0
 
rmtogetherAuthor Commented:
my code is like following in VB6 and after I run Command3. VB return me "compile error user-defined type not defined"

Private Sub Command3_Click()
    Dim cn As adodb.Connection
    Set cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;Persist Security Info=False"
   
    Dim rs As adodb.Recordset
    Set rs = New adodb.Recordset
   
    rs.Open "select max(field1) as max_num from table1", cn
   
    MsgBox "Your max_num value is " & rs!max_num
End Sub
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Let's try it this way...

Dim cn as adodb.connection
Set cn = New adodb.commection

cn.Connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;Persist Security Info=False"
cn.CursorLocation = adUseClient  'or adUseServer
cn.open

'The rest of the code goes here.

See http://www.connectionstrings.com for more info.
0
 
rmtogetherAuthor Commented:
I still got same error. do I need add any component before use your code?

I mean add component from VB menu
 project-->component
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>VB return me "compile error user-defined type not defined"
Go to Projects menu, References..., and in the References dialog make sure the 'Microsoft ActiveX Data Objects {Some version number} Library' checkbox is checked.
0
 
rmtogetherAuthor Commented:

I added the 'Microsoft ActiveX Data Objects 2.8. but there is another error. it looks like connection string erro. the error message is "compile error type mismatch"


my code is below:

Private Sub Command3_Click()
    Dim cn As ADODB.Connection
    Set cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\db1.mdb;Persist Security Info=False"
     
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
   
    rs.Open "select max(field1) as max_num from table1", cn
   
    MsgBox "Your max_num value is " & rs!max_num
End Sub
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Set cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\db1.mdb;Persist Security Info=False"
This line sets your connection object to a string.  Try the code about four comments up.
0
 
rmtogetherAuthor Commented:
I have try them but still have problem, could you please help me write this connection string? thanks in advance.
0
 
Davidshc76Commented:
----Library for ADO------
Projects menu, References..., and in the References dialog make sure the 'Microsoft ActiveX Data Objects {Some version number} Library' checkbox is checked

Dim g_objconn As ADODB.Connection
Dim rsDump as Adodb.Recordset

Set g_objconn = New ADODB.Connection
g_objconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= App.Path "\db1.mdb";Persist Security Info=False"

Set rsDump = new ADODB.Recordset
rsDump.CursorLocation = adUseClient
rsDump.Open "select max(field1) as max_num from table1", g_objconn

MsgBox "Your max_num value is " & rsDump!max_num

0
 
rmtogetherAuthor Commented:
hi, Davidshc76

there is a syntax error in the line

g_objconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= App.Path "\db1.mdb";Persist Security Info=False"
0
 
Davidshc76Commented:
You can also use
g_objconn.Open "Provider=Microsoft.Jet.OLEDB.3.5;Data Source= App.Path "\db1.mdb";Persist Security Info=False"

Make sure that the database (db1.mdb) is in your application path (location for your application)
make sure you have latest jet install
0
 
rmtogetherAuthor Commented:
hi, Davidshc76

I guess this line has problem. After I copy to VB, the code color of this line shows red color.  not even start run it.
0
 
Davidshc76Commented:
Try this ..............

Set g_objconn = New ADODB.Connection
g_objconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb" & " ;Persist Security Info=False"
0
 
rmtogetherAuthor Commented:
thanks,

but there is another error in the line

rsDump.Open "select max(field1) as max_num from table1", g_objconn

it is run time error, the error message is : no value given for one or more required parameters
0
 
Davidshc76Commented:
Database Name : Db1.mdb
TableName : table1
Fields : field1

Make sure all the field, databasename, tablename is CORRECT ....

rsDump.Open "select max(field1) as max_num from table1", g_objconn
0
 
rmtogetherAuthor Commented:
thanks Davidshc76

it works ok now. by the way. the code only return MAX value. how can I get other values like MIN, AVG, SUM...etc ? I know how to wirte sql statement, but don't know how to add into your code. thanks in advance
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You should be able to get all of those from the same SQL statement

Borrowing from rmtogether's comment above...

rsDump.Open "select max(field1) as max_num, min(field1) as min_num, avg(field1) as avg_num, sum(field1) as sum_num from table1", g_objconn

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Hey guys...

Just for future reference, it is considered poor EE etiquette to nearly duplicate another expert's comments without at least referencing their contribution (http:Q_21922308.html#17126860 are pretty near duplicates of http:Q_21922308.html#17125425 and http:Q_21922308.html#17125731).  Especially when the nearly-duplicating expert receives all points.

Thanks in advance.
-Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.