Solved

use VB to query database

Posted on 2006-07-17
21
180 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
  • 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
 

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now