Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Database Query within VB
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Database Query within VB
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Database Query within VB
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a Database Query within VB
Thanks Dick,
That was much quicker and only took one line of code -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import New Database Query (Union Query) in Spreadsheet | Excel Discussion (Misc queries) | |||
database query not showing foxpro database How I import data | New Users to Excel | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |