Open Access and Run a query from Excel Macro

Hi Experts,
I would like to run a macro from Excel that does the following things:
1) Runs a .bat file from a network share to open Access front end (unless already open)
Current is [Shell "\\sbs\EMS_APP\Launch bat\OT_Sandbox.bat"]    This works but is it all I need for good programming? How to I make sure the app is not already open for example?
2) Run Access query named "qryPasteExcel"
I've been reading related posts but they seem to be too advanced or too specific for my level.
I have more to do here but I will enter as a new question.
Thanks to all for any additional explanations that may help me learn.
-TH
thutchinsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
1. Your Access front end will create .ldb (.laccdb) file. If it is in folder with front-end - Access is opened (or was not properly closed - in this case you can try at first delete this file in your batch file)
2. Access can be started with command line parameters:
http://office.microsoft.com/en-us/access-help/startup-command-line-options-HP005188300.aspx
You can start any macro with /X option
0
Eric FlammSenior ConsultantCommented:
What does "qryPasteExcel" do? Why not just open a data connection from Excel to the Access database and get your data that way? Here's a blog post with some useful links: Steven Thomas. I would not  recommend the Shell command/batch file combination - there are too many ways for it to fail based on security issues for the PC that's running the code.

The link in the previous paragraph has a link to a more detailed post about importing data that's worth reading. If you provide more details on your application and what you're trying to accomplish, we might be able to provide a more detailed response.
0
thutchinsonAuthor Commented:
Yes, I can tell from your responses that I did not provide enough detail. Sorry.  

The Excel worksheets contain new sales records.  After I clean up the sales data in Excel, I copy the new records into a table in the Acces db.  I use the query named "qryPasteExcel" to open a table in datasheet mode (with no records returned).  Then I copy the new sales records into the blank query output datasheet to"import" the records to the table. The .bat file copies the .accdb file down to the local c: drive from a network drive and opens the front end.  
The Macro is started by running it from Excel (Developer->Macros->Run).

So, very simply, I want to run the bat file from an Excel Macro (which copies down opens the .accdb) then I want to run the query that opens the empty datasheet.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Eric FlammSenior ConsultantCommented:
If you set up a link (in the Access database) to the worksheet (in Excel), then any changes you make in Excel will be available to Access. So if you just have new sales records in Excel, they will also show up in the linked table in Access, and you can run an append query to add them to the main table in Access. You could also run a delete query in Access to clear out the Excel worksheet for the next set of records.

In Access, select the External Data tab, click Excel in the Import group, and select the 3rd choice ("Link to the Data Source..."). Browse to your Excel workbook and select the worksheet that will contain the new sales records. Now, anything you change in the worksheet will be visible in Access, and you don't have to run a batch job or copy files across the network.
0
thutchinsonAuthor Commented:
Thanks for your comments eflamm.
Yes, I have many linked Excel tables and append queries that I use to update and add new and changed records just as you describe.  This is a special situation, however.  If I try to explain why I wish to import this way- this time, it will only confuse the issue.  Besides, I want to know how to do it.

Can you help me with the question as I defined it?  From an Excel Macro: 1)  Run a bat file. 2) Open Access and run a query?
0
Eric FlammSenior ConsultantCommented:
Why not use the Scripting library to get the file:
Public Sub CopyAFile(ServerFN As String)
'Copy a file from a known server path to the local hard drive using the Office Object Library
    Dim fs As Scripting.FileSystemObject    'requires reference to Microsoft Scripting Runtime
    Dim fromPath As String, toPath As String
    fromPath = "\\servername\servershare\serverfolder\" 'e.g. \\myserver\sourceFiles\myFiles\
    toPath = "MyDrive\MyFolder\" 'e.g., C:\Temp\
    Set fs = New Scripting.FileSystemObject
    fs.CopyFile fromPath & ServerFN, toPath & ServerFN, True
    Set fs = Nothing
End Sub

Open in new window

If you also add a reference to Access, then you can open Access:
Public Sub OpenAccess()
    Dim myApp As Access.Application
    Set myApp = New Access.Application
    myApp.Visible = True 'could be False - faster, but you can't see what's happening
    myApp.OpenCurrentDatabase "c:\xxx\Database1.accdb"
    myApp.DoCmd.OpenQuery "Query1"
    myApp.CloseCurrentDatabase
    myApp.Quit
    Set myApp = Nothing
End Sub

Open in new window

You could combine the above procedures, or just call them 1 at a time from another procedure.

This avoids running a batch file and should not require elevated privileges on the local machine.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thutchinsonAuthor Commented:
Hi eflamm,

I like where you're going here except that it may be over my head.  I've been playing with this all morning and can't get either procedure to run.
The first sub "CopyAFile" is not allowing me to Call it or run it from a macro.  The second one "OpenAccess" is giving me a compile error "User-defined type error" on line2 Dim myApp As Access.Application.
0
thutchinsonAuthor Commented:
I will award the points but I don't have my solution.
Hopefully I can figure this out another way or construct and submit the question in a different way.
0
Eric FlammSenior ConsultantCommented:
I'll apologize for the lack of timely follow-up - lots of Holiday obligations (ongoing until next week). If you do re-post your question, you might try attaching a copy of your Excel workbook, so the Experts here can try an debug your macro. I know I was able to call the routines I posted from an Excel macro attached to a button on the worksheet, but my sample workbook didn' t have any other worksheets or code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.