Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 27th, 2009, 11:03 PM
Authorized User
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default Running a MS Access query from Excel

I need to run an append query in MS Access from Excel.

The research I have done goes on about doing far more complex things in Access from an Excel macro, but not how to just run a query.

I want to use DAO (I think).

I think I start with

dim db as DAO.workspace
dim qry as DAO.queryDef


Then I get really shaky. Maybe something like:
Set db = "C/desktop/richard/rdb.mdb" 'set the name of the Access db
Set qry = "TempRP" 'set the name of the query
Run qry


I have little doubt that this will not work as set out but cannot find in my books or on line how to run a query in Access from Excel.

Am I close? What changes will make this actually work?

Does Access need to be open for this to work?

If I need to first open the Access application, do I need to worry about the standard warning that security is set high and deal with options to say it is okay to trust this database?
 
Old August 28th, 2009, 01:42 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Can you check if the following gives you some hint:

http://vbadud.blogspot.com/2009/03/h...using-vba.html

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old August 28th, 2009, 06:01 PM
Friend of Wrox
 
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Set reference to Microsoft DAO 3.6 Object Library
You need to know your connection string options which are somewhat finicky but the code would look something like:
Code:
Dim dbConnect As Database, wsEngine As Workspace, rsSource as Recordset
Set wsEngine = CreateWorkspace("ODBCWorkspace","[User Name]","[Password]",dbUseODBC)
Set dbConnect = wsEngine.OpenDatabase("[Name]",[Options],[ReadOnly] -True Or False-,"[Connect]" -The DB connection String-)
Set rsSource = dbConnect.OpenRecordset("Select * From TableName Where Conditions")
Do While Not rsSource.EOF
  msgbox rsSource("FieldName1").value
  ***Whatever else you want to do with recordset***
  rsSource.MoveNext
Loop
rsSource.Close
dbConnect.Close
wsEngine.Close
Set dbConnect = Nothing
Set wsEngine = Nothing
Hope this helps point in the right direction. Be sure to read up on createworkspace command in the office help file.
 
Old September 14th, 2009, 12:02 PM
Authorized User
 
Join Date: Jan 2009
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Default

I like code in the most simple form possible. Here is a simple version of DAO that I use:

Code:
Dim dba As Database 
Set dba = OpenDatabase("C:\desktop\richard\rdb.mdb") - This opens the database
dba.Execute "TempRP" -Executes the query
dba.close - Closes the database
If you get the security warning you can simply deal with it as is happens or insert a line of code before the set line that reduces the security level. Research the following: msoAutomationSecurityLow

Hope this helps
 
Old September 14th, 2009, 12:39 PM
Authorized User
 
Join Date: Sep 2009
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have done this recently and was having problems with using DBO .... what I ended up doing was this:
Start recording a macro
Go to your Data tab and Get External Data -> from other sources -> MS Query
go through and specify your data, etc
select post to excel
after it's done doing its thing, stop recording
go into VBA and, if necessary modify the code to get the exact data you're looking for

I'm definitely no Expert in VBA or Excel, but that's what has worked for me :)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Change the format of MS Excel Cells from MS Access hewstone999 Access VBA 0 March 7th, 2008 07:19 AM
Running an Access Query from ASP arholly Access ASP 0 January 25th, 2008 03:31 PM
Problem running SQL query on Access DesignsOnline.co.uk Access ASP 1 November 26th, 2006 06:09 PM
problem running ms access application method Access 1 March 23rd, 2005 09:16 AM
Exporting data from MS Excel sheet to Ms Access ajindal General .NET 1 January 17th, 2005 03:00 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.