Execute an Access query with a button in Excel

M

mrholiday

Board Regular
Joined
Jan 27, 2006
Messages
57
Dear friends,

I wonder if may be somebody has an idea of how to write the VB code to do run an Access query using an Excel VB command button?

The goal is it, after I have finished working on my Excel table, that I can push a button on the Excel sheet which launch an saved Access query.

If I would do the job manualy I would need to open the Access DB , go to Queries and double click my Query.


Example:
I have an Excel DB (Table1.xls) with a Table "MyExcelData" on Sheet1

I have as well an Access DB (DB1.mdb)
- with the same table format "MyAccessData" as "MyExcelData"
- with a linked table to my "MyExcelData"
- with a Update Query "MyQuery" (updating selected changes from the linked "MyExcelData" to "MyAccessData"


Instead of double clicking on the "MyQuery" in Access, I like to only click on a button in Excel, without having to open or touch to Access DB.

I thank you for any tip of how to program correctly a sub like this

Private Sub CommandButton1_Click()
Execute."MyQuery".located in C:\my Documents\ DB1.mdb
End Sub


Jerry




In Access I created an Update Query (MyQuery) which updates any changes
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Here is an example data query. The Aug94 is the stored query in Access.
Code: 
Sub Test()
  Dim mdbPath As String, dbName As String, cmdText As String
  Dim rngDestination As String
  'mdbPath = "E:\ADO\NWind2003.mdb"    'change the path here to suit your needs
  'mdbPath = "c:\myfiles\edrive\excel\ado\NWind2003.mdb"
  mdbPath = "//matpc10/ExcelVBAExamples/ado/NWind2003.mdb"
  dbName = "NWind2003_1"              'change the database name here to suit your needs
  cmdText = "Aug94"                   'change the stored SQL here to suit your needs
  rngDestination = "A1"               'change the destination range here to suit your needs
  
  'Clear previous data
  Cells.Delete

  InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
  
  'Insert other data to the right of A1 with a blank column separating the two
  rngDestination = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 2).Address
  cmdText = "Sales by Category"
  InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
End Sub

Sub InsertTableWithStoredSQL(mdbPath As String, dbName As String, _
  cmdText As String, rngDestination As String, _
  Optional bFieldNames = True)

    With ActiveSheet.QueryTables.Add(Connection:=Array( _
                                                 "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & mdbPath & ";Mode=ReadWrite;Extended Properties=""" _
       , """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Datab" _
       , "ase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";J" _
       , "et OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Co" _
       , "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("" & rngDestination & ""))
        .CommandType = xlCmdTable
        .CommandText = Array(cmdText)
        .Name = dbName
        .FieldNames = bFieldNames
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = mdbPath
        .Refresh BackgroundQuery:=False
    End With
End Sub
 
Upvote 0
Hello Kenneth,

Thanks for you quick reply, but I think I forgot to mention something important.

Actually I want to upload Data from Excel to Access and not from Access to Excel.

The stored query in Access is used to run an Update query within Access tables. (one of them is the linked table in my Excel file)

My normal Job is it:
1) to modify the data in Excel
2) Open Access (including a table with is already linked to my Excel file)
3) Double click on a query which updates several Access table from my Linked Excel file table

I looking to get rid of point 2) and 3) by replacing with a Command button on an Excel sheet

Private Sub CommandButton1_Click()
Execute."MyQuery".located in C:\my Documents\ DB1.mdb
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Excel Table - How to Append to Access Database Using VBA
Replies
1
Views
104
Micron
M
L
  • Solved
Trouble running an access query in excel vba
Replies
2
Views
155
Legal Tender
L
R
  • Solved
VBA Queries refresh and Save data as TXT
Replies
2
Views
159
Artik
Artik
L
  • Solved
Querying access db from excel - 32 bit to 64 bit conversion
Replies
2
Views
723
JEC
JEC
F
  • Question Question
Manipulate Access table or query from excel using excel vba
Replies
7
Views
632
Micron
M

Forum statistics

Threads
1,223,927
Messages
6,175,444
Members
452,642
Latest member
acarrigan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top