Macro for running SQL on access database

[nn992]

Hello everyone,

I am trying to write a VBA code which will contain my SQL code, and that code is to be applied on a table in access, and export results in another table in access document...

any ides/help/guidelines

 

[Joe4]

Are you trying to do this from Excel VBA or Access VBA?
If Excel VBA, why are you running it from Excel instead of Access?

If from Access VBA, sounds like you want a Make Table Query.
The way I usually go about this is to create an example of a query that does what I want in Access using the Query Builder.
Then, switch the query to SQL View. This shows the SQL code of that query. So that is what the SQL code that you want to create in VBA needs to look like.

Note, typically you would only do it in this way if you query may dynamically change. If it is always the same, you could just create the Make Table Query in Access Query Builder, and run it from there (or call it from a Macro or VBA).

 

[nn992]

Hi, thanks for response.
I am trying to do it in excel VBA, because I want to have all my buttons there.

And I know the trick to get SQL code, but I dont know how to write a code structure eg. "what goes where"

So what I exactly want is: In excel VBA, macro which is running a SQL query on Access table, and export results in another table in access file

 

[Joe4]

What is the relation here between Excel and Access?
If the data is in Access, what role in Excel playing?
Note that Access forms are much easier to create than Excel.
If this is something that could be entirely contained in Access, I would recommend that.

 

[nn992]

The problem is that I am connecting to datawarehouse with excel, and only excel can connect (I dont know why, but other programmes it does not work)

relation between excel and access - excel is running sql queries from data warehouse and dumping data in access. So what I want to do now is to make connection between excel and the vba table, so I can run sqls from excel, apply the script on access table, and export resulting query into another table.

 

[Joe4]

Personally, I also find it very clunky to try to control Access from Excel (and vice versa). Where I have had good luck in the past is to do what I want in Access, and then put that in a Access startup script (either by creating a macro called "AutoExec", or putting VBA code that runs on the Load event of a startup Form), and then just have my Excel VBA open Access. Doing that will run the code that I need and get the results need.