?
Solved

use VB to query database

Posted on 2006-07-17
21
Medium Priority
?
202 Views
Last Modified: 2010-04-30
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).
0
Comment
Question by:rmtogether
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 5
21 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17125425
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
 

Author Comment

by:rmtogether
ID: 17125497
I got some error, do I need add some component into VB before using ADODB?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17125519
>I got some error
Please tell us the error number, description, and the line that generates it.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:rmtogether
ID: 17125592
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17125627
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
 

Author Comment

by:rmtogether
ID: 17125683
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17125731
>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
 

Author Comment

by:rmtogether
ID: 17126007

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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17126032
>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
 

Author Comment

by:rmtogether
ID: 17126065
I have try them but still have problem, could you please help me write this connection string? thanks in advance.
0
 
LVL 5

Expert Comment

by:Davidshc76
ID: 17126860
----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
 

Author Comment

by:rmtogether
ID: 17127302
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
 
LVL 5

Expert Comment

by:Davidshc76
ID: 17127328
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
 

Author Comment

by:rmtogether
ID: 17127357
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
 
LVL 5

Expert Comment

by:Davidshc76
ID: 17127393
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
 

Author Comment

by:rmtogether
ID: 17127431
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
 
LVL 5

Expert Comment

by:Davidshc76
ID: 17127444
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
 

Author Comment

by:rmtogether
ID: 17127613
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
 
LVL 5

Accepted Solution

by:
Davidshc76 earned 2000 total points
ID: 17127642
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17127647
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17127707
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month13 days, 21 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question