Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Access from Excel
Using Excel VBA, I want to do the following:
1. Open a specific Form on an Access database. 2. Assign 4 integer variables from Excel to the 4 fields on the Access form. These fields will determine the results of the query to be run (see next step). 3. Run an Access query. This query uses other Access queries to get its results. 4. Copy and paste the results of the query to an Excel worksheet. Once I get the data into Excel I can do the rest |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Access from Excel
I can't answer your specific question but I beleive a more efficient way to
accomplish the same thing is to use Excel's "Get External Data" feature using your MS Access database as the data source and MS Query. If you are unfamiliar as to how to do this, check out http://www.rdg.ac.uk/ITS/info/traini...s/excel/query/ For detailed instructions. "John Bigness" wrote: Using Excel VBA, I want to do the following: 1. Open a specific Form on an Access database. 2. Assign 4 integer variables from Excel to the 4 fields on the Access form. These fields will determine the results of the query to be run (see next step). 3. Run an Access query. This query uses other Access queries to get its results. 4. Copy and paste the results of the query to an Excel worksheet. Once I get the data into Excel I can do the rest |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Access from Excel
"John Bigness" wrote in message
... : Using Excel VBA, I want to do the following: : 1. Open a specific Form on an Access database. : 2. Assign 4 integer variables from Excel to the 4 fields on the Access form. : These fields will determine the results of the query to be run (see next : step). : 3. Run an Access query. This query uses other Access queries to get its : results. : 4. Copy and paste the results of the query to an Excel worksheet. : : Once I get the data into Excel I can do the rest : Why use a form to fill fields to then run the query, run the query directly from excel and set your cell values to the query result. Something like this. Private Sub Fill_Data() Dim cnnDB As ADODB.Connection Dim myrs As ADODB.Recordset Dim MySql As String 'create the sql string MySql = "SELECT * " & _ 'or you can use specific values MySql = MySql & "FROM Your_Query " & _ MySql = MySql & "WHERE (((parameter_1)=" & JobNo & ") AND ((parameter_2)=" & TaskNo & "));" ' Initialize Connection object Set cnnDB = New ADODB.Connection With cnnDB .Provider = "Microsoft.Jet.OLEDB.4.0" .Mode = adModeRead .Properties("Jet OLEDB:Database Password") = "" .Open "full path and name of database" Set myrs = New ADODB.Recordset Set myrs = cnnDB.Execute(MySql) End With Cells(8, 2).Value = myrs(1).Value 'client Cells(9, 2).Value = myrs(2).Value 'contact Cells(10, 2).Value = myrs(3).Value 'address cnnDB.Close Set cnnDB = Nothing End Sub Paul D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel controlling Outlook | Excel Programming | |||
Controlling a web page from within excel | Excel Programming | |||
Controlling Outlook from Excel Using VBA | Excel Programming |