How simple is it to Run a Database Query via VB?

I have ideas in my head to connect to an Access database, extract them to a
file and run various calculations on the Query. Haven't done anything like
this before so I am happy to do it by trial and error

Is there any good web site that give practical examples

Thanks

Thanks for the Reply. What exactly is the MDAC SDK? - looked it up on the MS
site would it be essential to have it?


"AA2e72E" wrote in message
...
It is quite simple. Here's an example:

Sub xx()
Cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\nwind.mdb"
Sql = "SELECT * FROM customers"
Set adors = CreateObject("ADODB.RecordSet")
adors.Open Sql, Cnn
While Not adors.EOF ' loop through
MsgBox adors.fields("CompanyName").Value ' see a value
adors.MoveNext 'Next Record
Wend
adors.Close ' End connection
End Sub


You should download the MDAC SDK from Microsoft (its free): this contains
the documentation for ADO, which you would need.

I recently wrote some code to do just that. I am new to VB programmin
so this code is probably a bit unwieldy and not as effecient as i
could be but here it is. To get the SQL statements i just went int
the access database that I wanted to query. Designed the query i
Access (which is relatively straight forward) then switched to SQL vie
to get the statement for the query. I broke it up into parts to mak
it a little easier to read but you could just copy and past it directl
into the VB code and it should work. Anyway, here is the code tha
goes through all the connecting, querying and the filling into excel.
I cut out some parts that weren't relevant so there may be a fe
undeclared variables here and there but I think I got the importan
bits. Hope it helps.

P.S. sorry about the lack of formatting. oh well.



Public Sub QueryWarrantyDatabase()


Dim conn As ADODB.Connection
Dim WarrantyData As ADODB.Recordset

Dim SQLSelect As String
Dim SQLFrom As String
Dim SQLWhere As String
Dim SQLQuery As String

Dim DatabasePath As String


'Set DatabasePath
DatabasePath = "G:\QUALITY\Warranty Database\Warrant
Database.mdb"



'Create the connection to the Warranty Database
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.JET.OLEDB.4.0"
.Open DatabasePath
End With

'Define the SQL query string for Warranty returns
SQLSelect = "SELECT [Main Data Table].[Project Number], _
[Main Data Table].[Model Number], [Main Dat
Table].[Serial Number], [Main Data Table].[Customer], " _
& "[Main Data Table].[Description Of Failure], [Mai
Data Table].[Root Cause Of Failure], [Main Data Table].[Year O
Manufacture], " _
& "[Main Data Table].[Month Of Manufacture], [Main Dat
Table].[Year Of Return], [Main Data Table].[Month Of Return], [Mai
Data Table].[Product Line], " _
& "[Main Data Table].[Unit Age (months)], [Main Dat
Table].[Warranty/Non Warranty/Warranty Period But Non Warrant
(W,NW,WP)]"
SQLFrom = "FROM [Main Data Table]"
SQLWhere = "WHERE ((([Main Data Table].[Month Of Return])=" & Mont
& ") AND (([Main Data Table].[Product Line]) Like 'custo
brushless'));"

SQLQuery = SQLSelect & " " & SQLFrom & " " & SQLWhere

'Open a new Recordset to hold the Warranty Data
Set WarrantyData = New ADODB.Recordset

'Fill the Recordset with data returned from query
With WarrantyData
.ActiveConnection = conn
.Open SQLQuery, conn, adOpenDynamic, adLockBatchOptimistic
End With




'Update "Warranty Query" sheet with warranty data

WarrantyData.MoveFirst
Do While Not WarrantyData.EOF
With Worksheets("Warranty Query")
.Cells(NewRow, 1) = WarrantyData(0)
.Cells(NewRow, 2) = WarrantyData(1)
.Cells(NewRow, 3) = WarrantyData(2)
.Cells(NewRow, 4) = WarrantyData(3)
.Cells(NewRow, 5) = WarrantyData(4)
.Cells(NewRow, 6) = WarrantyData(5)
.Cells(NewRow, 7) = WarrantyData(6)
.Cells(NewRow, 8) = WarrantyData(7)
.Cells(NewRow, 9) = WarrantyData(8)
.Cells(NewRow, 10) = WarrantyData(9)
.Cells(NewRow, 11) = WarrantyData(10)
.Cells(NewRow, 12) = WarrantyData(11)
.Cells(NewRow, 13) = WarrantyData(12)
End With
NewRow = NewRow + 1
WarrantyData.MoveNext
Loo

--
Message posted from http://www.ExcelForum.com

Corbin

"Warranty Query" sheet with warranty data

WarrantyData.MoveFirst
Do While Not WarrantyData.EOF
With Worksheets("Warranty Query")
Cells(NewRow, 1) = WarrantyData(0)
Cells(NewRow, 2) = WarrantyData(1)
Cells(NewRow, 3) = WarrantyData(2)
Cells(NewRow, 4) = WarrantyData(3)
Cells(NewRow, 5) = WarrantyData(4)
Cells(NewRow, 6) = WarrantyData(5)
Cells(NewRow, 7) = WarrantyData(6)
Cells(NewRow, 8) = WarrantyData(7)
Cells(NewRow, 9) = WarrantyData(8)
Cells(NewRow, 10) = WarrantyData(9)
Cells(NewRow, 11) = WarrantyData(10)
Cells(NewRow, 12) = WarrantyData(11)
Cells(NewRow, 13) = WarrantyData(12)
End With
NewRow = NewRow + 1
WarrantyData.MoveNext
Loop

You might take a look at the CopyFromRecordset method. It does pretty much
what your loop does, and probably a lot quicker.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Thanks Dick,

That was much quicker and only took one line of code

--
Message posted from http://www.ExcelForum.com

Thanks, there seems to be a huge number of downloads, which is the best one
for a 'beginner


"AA2e72E" wrote in message
...
MDAC = Microsoft Data Access Component.
SDK = Software Documentation Kit.

The SDK contains, among other things, documentation for ADO (ActiveX Data
Object): you would use this to talk tio the database. Yes you would need the
SDK or will need to buy a book. "ADO 2.6 " from WROX is quite good but the
current version is 2.8 (also free).

PS: In the last post, the Access NWIND.MDB database is used, and the table
CATEGORIES is read. You can use any data source (virtually), inclusing Excel
workbooks, text files, Oracle, SQL Server , DB2 etc etc.