[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

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).
0
rmtogether
Asked:
rmtogether
  • 9
  • 7
  • 5
1 Solution
 
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
 
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
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.

 
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
 
Davidshc76Commented:
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:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now