PDA

View Full Version : Using Access Queries in Excel



Custos
08-13-2007, 07:25 AM
Does anyone know how to run a Access Query using Ecxel?

The problem is:

I have 3 append Queries in Access, i would like to run these append queries via Excel using Visual Baisic. The append queries deal append to an Access Table. I have used DAO to export the data to Excel and to import to Access. I am struggling to find how to execute queries which i do not wish to export.

Can anyone please help.

Thanks
Paul

rory
08-14-2007, 02:50 AM
Hi,
You will need to set a reference to one of the Microsoft ActiveX Data Objects libraries, then you can use something like:
Sub ExecuteAccessActionQuery()
' Sample demonstrating how to execute an action query in an Access db
Dim cn As ADODB.Connection, strQuery As String
Dim strPathToDB As String

' Change path as necessary
strPathToDB = "C:\Test\db1.mdb"

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strPathToDB & ";"
.Open
End With
strQuery = "qapp_Append_Query_Name"
cn.Execute strQuery, , adCmdStoredProc
cn.Close
Set cn = Nothing
End Sub

debauch
08-14-2007, 08:02 AM
Here is what I use :


Sub AppendAccessDataQuery()

If Not Connection Then
Set dboQueries = OpenDatabase("I:\storage\controls\myDatabase.mdb")
Connection = True
End If
Set dboSelectedQuery = dboQueries.QueryDefs("Append_Data_query_name")
dboSelectedQuery.Execute

MsgBox ("Daily Data has been Appended")

'rs.Close
Set rs = Nothing
'db.Close
Set db = Nothing

End Sub

Wizard
08-14-2007, 08:52 AM
A more roundabout method would be to write a simple Access macro that will run the query, then kick off the macro like so:

dBUpdate = Shell("C:\Program Files\Microsoft Office\Office\MSACCESS.EXE """ & _
"<Full Path & Name of Database>.mdb"" /x Daily Append Queries", _
vbNormalFocus)


Tricky part is getting all those quotation marks balanced, sometimes.