help converting excel macro to access module

[dvenn]

Need some help here...


I ahve the following macro in excel that gets information from a database.. I think import the information into access (a new process). I would like to modify this so that it will dump directly to a table versus the whole export > import

here is the excel macro

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=ULTRASelect", _
Destination:=Range("B1"))
.CommandText = Array( _
"SELECT Vu_Magent.user_name, Vu_Magent.start_time, " & _
"Vu_Magent.template_name, Vu_Magent.criteria_name, " & _
"Vu_Magent.score, Vu_Magent.scared" & Chr(13) & "" & _
Chr(10) & "FROM DVLADM.Vu_Magent Vu_Magent" & Chr(13) & "" & _
Chr(10) & "WHERE (Vu_Magent.start_time>{ts ", _
"'2006-01-01 00:00:00'}) AND (Vu_Magent.start_time<{ts " & _
"'2006-02-12 00:00:00'}) AND ((Vu_Magent.criteria_name = " & _
"'2005a Performance Elements'))")
.Name = "Query from ULTRASelect"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


_______
now on a side note with this.. is it possible in access to have a input box for the values '2006-01-01 00:00:00' & '2006-02-12 00:00:00' ? something like in the queries between [start] & [end].

_____
Since I'm on side notes...

I have 3 modules currently in VBA (each has 1 sub)... how can I set these to run via a macro or query instead of having to go into the VBA editor and running from there?

[XLGibbs]

When you say you want the data dumped into a table...do you mean an Access table based on the same query?

You can use parameters in MS Query, same as Access by placing [] around the required prompt..

For example instead of specifying a start time as criteria, you would put [StartTime] in the criteria..
when you run the query a input box pops up and asks you for StartTime

You can also link this parameter to a form..

Forms!Form1.TextBox1

Where the form is named Form1 and the textbox containing your criteria is named TextBox1. if the form is not open, the input box will ask you for the criteria..



To run your code from excel, you can go to Toolbars>Customize...and add a button to a toolber, and assign it the macro to run.

Also you can hit Alt + F8 which opens a window which shows the macros available and you can choose "Run" after selecting one of them...

You can put all 3 subroutines in one module as well, although this does not matter to anything of import most likely in your case.

Clarify what you want on the QueryTable code above...and i will advise accordingly..

[dvenn]

Sorry I was not specific enough..

Yes I want to populate an access table 'tblQA' based on the posted query.

____
Nice to know about the brackets in MS Query (no form is necessary).

____
The modules have been merged into one module (i had them in different ones while I tested them). The porblem I am having is within access.. I have the module now with the 3 subs but I can't figure out how to trigger them without going into the module and running from within the VBA editor.

Hope this helps clarify

[XLGibbs]

here is the make table SQL statement for VBA in Access (should be okay, could not test it)

Sub maketable()

Dim strCONN As Connection

Set strCONN = New Connection
strODBC = "ODBC;DSN=ULTRASelect"

strCONN.Open strODBC

strSQL = _
"SELECT Vu_Magent.user_name, Vu_Magent.start_time, Vu_Magent.template_name, Vu_Magent.criteria_name," & _
"Vu_Magent.score, Vu_Magent.scared " & _
"Into tblQA " & _
"FROM DVLADM.Vu_Magent Vu_Magent " & _
"WHERE Format(Vu_Magent.start_time," & Chr(34) & "hh:mm" & Chr(34) & ") betwen > [Start Time] AND < [End Time] AND " & _
"Vu_Magent.criteria_name =" & Chr(34) & "2005a Performance Elements" & Chr(34)

strCONN.Execute strSQL

strCONN.Close

End Sub


Try looking at the MACRO option in the database option window (above modules). You can specify how your code runs there....

You would still ned to kick of the Macro by hitting run from the macro window..but this is an easy way to lay out procedures because you can do a lot in the Macro builder....open tables transfer tables, all kinds of stuff.

You can make one that has one statement of RunCode where you specify which code you want to run..then you just open the macro window and execute it...it will run your code without having to open the VB editor..

hope that helps....the VBA above is "close" but likely not perfect. There are way easier ways to do this in Access.

Establish a link to the source table by going to Link Tables> and browse to the ODBC Sources...you should see the machine/user data sources in the ODBC source list....select the appropriate Data Source and pick the table you need.

Then go to the Query design view and show that table....Drag the fields you need into view...use [Brackets] for criteria and change the query to Make Table ! Query...you then name it. Then all you have to do in VBA is...

DoCmd.OpenQuery YOURQUERYNAME here....and the table will be built...



one thing with the table query above...I am assuming that you have access to the tables...

[Norie]

Why not just create a link to or import the table/query via File>Get External Data...?

[XLGibbs]

I covered that as well Norie. He did want to import/export as in his post.

As I said essentially the same thing, which is far easier...

Establish a link to the source table by going to Link Tables> and browse to the ODBC Sources...you should see the machine/user data sources in the ODBC source list....select the appropriate Data Source and pick the table you need.

Then go to the Query design view and show that table....Drag the fields you need into view...use [Brackets] for criteria and change the query to Make Table ! Query...you then name it. Then all you have to do in VBA is...

[Norie]

:oops:

Sorry about that, didn't fully read the post as the formatting on the thread was all knackered up.:)

[XLGibbs]

No worries Norie.http://vbaexpress.com/forum/images/smilies/023.gif

Always good to have someone else double checking to make sure the obvious is not ignored (which I tend to do all too often )

[dvenn]

I tried using the link external but when access opens it fails to intilize the connecitons making any queries I wrote on the table fail

unless I want to re-link everytime.. (it's not something wrong it's the way the connection is to 'ULTRASelect' (BTW way I HATE THAT THING!!! :bug: ) but I still need to get data out of it.. :banghead: .

The only way I've ever successfully connnected and extracted data from this datasource was via the above code... but now I am getting it in excel saving and then importing into access.. trying to save a step of two and see if I can get it directly into access

____

In regards to the module code.. is there a reference I need to load.. I am getting an error...

Compile error:
Invalid use of New keyword
on
Set strCONN = New Connection

[XLGibbs]

oops, you may not need that line(?).......I don;t use Access vba for connectrion strings as much...I know in excel you need to have the reference to the DAO Object library (most recent) and/or the Microsoft ActiveX Data objects (most recent) library

[XLGibbs]

PS. A quick trick ....

Go to a folder where your Access DB resides...and right click to create a notepad document.

Rename the document whatever you choose (for this example Test)....and change the extension to ".udl"

Double click this file and you can build your connection string in there with intuitive screens.

When you are done, you can change the extension back from .udl to .txt and open the file.

You will see in the text document the proper connection string.

You can also simply reference the test.udl file to set the connection string

strPath = "C:\Thepathwherethefileis"

StrConn = "File Name="& strPath & "\test.udl"