Open Access Database from Excel

ExcelChampion

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
A problem that is troubling me is the issue of trying to open an Access Database from Excel. I have the following code:

Code: 
    myPath = "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
    myFile = ThisWorkbook.Path & "\Research_Lookup_Tool.mdb"
    taskID = Shell(myPath + " " + myFile, vbNormalFocus)

which will work only if the myFile path contains no spaces. If one of the folders contains a space in the name, such as, "My Folder" the macro fails to open Access properly and results in Access giving an error message.

Has anyone got a work-around for this?

Regards.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Hello,

I have two work-arounds for you this evening. :)

Work around #1? ShellExecute():

Code: 
Private Declare Function ShellExecute _
    Lib "shell32.dll" Alias "ShellExecuteA" ( _
    ByVal hwnd As Long, ByVal lpOperation As String, _
    ByVal lpFile As String, ByVal lpParameters As String, _
    ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub foo()
Call ShellExecute(0, "Open", "test DB.mdb", vbNullString, _
    "C:\Documents and Settings\Me\My Documents", 1)
End Sub
Work-around #2? Use Ole Automation and the OpenCurrentDatabase Method.

There's an example in the help file, for Access, and you can bind with Access and use it, like so:

http://www.mrexcel.com/board2/viewtopic.php?p=417829#417829

Or:

Code: 
Public Declare Function ShowWindow& Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal nCmdShow As Long)

Sub bar()
Dim appAccess As Access.Application
Set appAccess = New Access.Application

With appAccess
    .OpenCurrentDatabase _
        "C:\Documents and Settings\Me\My Documents\test DB.mdb"
    .Visible = True
    Call ShowWindow(.hWndAccessApp, 3)
    .UserControl = True
End With

Set appAccess = Nothing
End Sub
Good hunting. ;)
 
Upvote 0
You are welcome.

I too would go with ShellExecute() if I simply wanted to open the DB for presentation purposes, hand it over to the end-user, and be done with it.

If I needed programmatic control over the DB being opened, as I did in my linked thread, then I'd go with OLE Automation.

Glad to hear you're up and running. :)
 
Upvote 0
NateO,
Thank for your help thus far.

I am querying a DB that is on a shared drive. I noticed that the query runs quite a bit faster if the DB is open. So, I wanted code to open the DB in the background simply so the query runs faster.

To do this, I create an instance of Access, and then use your code to open the file, and then quit the App once the code has run:-

Code: 
Option Explicit

Private Declare Function ShellExecute _
                          Lib "shell32.dll" Alias "ShellExecuteA" ( _
                              ByVal hwnd As Long, ByVal lpOperation As String, _
                              ByVal lpFile As String, ByVal lpParameters As String, _
                              ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub AccessDatabase_Execute()
    Dim appAC As Object
    
    Set appAC = CreateObject("Access.Application")
    Call ShellExecute(0, "Open", "Research Lookup Tool.mdb", vbNullString, ThisWorkbook.Path, 1)
    Call Run_Query
    appAC.Quit
End Sub

I'm also looking at the link you gave me and sorting through that. Admittedly, I only know the basics of Access, but it looks like with data sets getting larger and larger in my company it's going to be a necessity to learn it inside and out.

Any more info you can provide me is always welcome.

Thanks!
 
Upvote 0
NateO,
Run_Query is the same as running a "New Database Query" from the Data menu. What I've learned is that this process is quite efficient if running one or two queries. But if say, the user wants to query a few hundred, or a few thousand part numbers, then the process is not so efficient, to say the least. I could put those part numbers in an Excel sheet and use a Lookup function, and it would run fatser than the "New DB Query". But, given that I have over 100K parts, putting the data in a worksheet seems, for lack of a better term, unprofessional (not to mention I would have to break up the data).

To make a long story short, I'm just learning my way around Access/SQL queries. So any helpful hints will certainly help.

Oh, and the reson this is being done in Excel...? Because the boss said so.
 
Upvote 0
Hello,

Are these parameter queries from the same data set? If so, you really should check out ADO and Filter the larger Recordset:

http://msdn.microsoft.com/library/en-us/ado270/htm/mdprofilter.asp

http://msdn.microsoft.com/library/en-us/ado270/htm/mdprofilterx.asp

Opening and closing a Recordset over and over again is slow... Open it once and Filter what you want.

Filtering Recordsets with ADO is cool. CopyFromRecordset only returns the Filtered Recordset, the returned RecordCount is that of the Filtered Recordset. But, and here's where it gets good, the entire Recordset is still in tact!! :cool:

You don't even have to remove the Filter to re-Filter the Recordset with a new set of criteria. I just wrote a monstrosity of an Access/Excel interface, it generates 78 Excel-based reports in 4 minutes and it heavily leverages Filtered ADO Recordsets to utilize subsets of a huge Recordset.

It's even more complex than it should be because of our kludgey systems, but here's an example of me Filtering a Recordset in a loop, passing parameters from an array and a 2nd Recordset:

Code: 
Do Until rsList.EOF
    For i = LBound(varArr) To UBound(varArr)
        rs.Filter = "Field1 = '" & rsList.Fields(0).Value & _
            "' And Field3 = '" & varArr(i) & "'"
        Let recCount = rs.RecordCount
        With varWorkbooks(i)
            With .Worksheets("Data")
                .Range(varRangeArr(i)).ClearContents
                .Range("a3").Offset(maxRecords(i) - recCount). _
                    CopyFromRecordset rs
This beats opening 78 distinct Recordsets with SQL and parameters; fast. Learned a few other tricks while writing this bad-boy, too... :cool:

Not sure if this is applicable to what you're talking about, but some food for thought, perhaps. :)
 
Upvote 0
It's all applicable, or at least it will be at some point I'm sure.

Thanks, Nate! Let me give it a whirl...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Seeking a macro complicated?
Replies
1
Views
311
mikemcbain
M
A
  • Question
Merge/ combine password protected data from multiple folders
Replies
0
Views
500
Ani_Excel
A
R
  • Solved
Incorporating my VBA code (that saves all sheets from an excel workbook to CSV) into a looping VBA program (just have myself stumped!)
Replies
3
Views
922
Anthony47
A
E
  • Question
Copying Data from Multiple Workbooks to a Single Master File (Tab)
Replies
3
Views
2K
Flaiban
F
A
  • Question
Importing cell values from multiple workbooks in one folder
Replies
2
Views
766
Albertu95
A

Forum statistics

Threads
1,214,615
Messages
6,120,538
Members
448,970
Latest member
kennimack

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