Solved

use VB to query database

Posted on 2006-07-17
21
191 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 65

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 65

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 65

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 65

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 65

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 500 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 65

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 65

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…

739 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