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

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

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