change code to be literal

[Kurt]

Hello Everyone,

I would like to change this code to specifically get a month of code let's say June.

Sub get_applications()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Dim Month$
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\From Dyer.mdb;"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
        "SELECT Apps FROM track apps Where Month = June"
            'Month = InputBox("Please input the Month" & _
            '"(for example, 'August').")
    comm.Parameters(0) = ""
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

I also want to take out the input box.

Any suggestions?

TIA

Kurt

 

[Kurt]

The code works now

Hello Norie,

Again a BIG THANKS! The code works. I just need to reference the server now which I know need to be placed in the Data Source part of this line:

    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source= CHECNET\NEO3" + ThisWorkbook.Path + "\From Dyer.mdb;"

I will let you know the outcome. However the good news is the SQL syntax is now correct!

Have a great day!

Kurt

 

[Kurt]

not a valid file name

Hello Norie,

What is the naming convetion for the Data Source??

Sub get_applications()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source= \CHECSQLDEV1\NEO3" + ThisWorkbook.Path + "\From Dyer.mdb;"
    Set comm.ActiveConnection = conn
    strSQL = "TRANSFORM Sum([track apps].Apps) AS SumOfApps "
    strSQL = strSQL & "SELECT dbo_SMT_Branches.BranchTranType "
    strSQL = strSQL & "FROM [track apps] LEFT JOIN dbo_SMT_Branches ON [track apps].Branch = dbo_SMT_Branches.BranchNbr "
    strSQL = strSQL & "GROUP BY dbo_SMT_Branches.BranchTranType "
    strSQL = strSQL & "PIVOT [track apps].MONTH;"
    comm.CommandText = strSQL
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

Thanks,

Kurt

 

[Norie]

Kurt

That's not something I know, I'm not particularly familiar with ADO, which is what you appear to be using.

If you are just wondering how to refer to a mapped drive then perhaps you just need to use the UNC address for it.

Perhaps if you recorded a macro when you imported the query via Data>Get External Data... you might get some pointers.

 

[Kurt]

Again a big thanks

Hello Norie,

BIG THANKS!

I am almost there.

I am trying your suggestions.

Have a very good day!

Kurt

 

[Kurt]

Can anyone else clue me in

Hello All,

This is the last part of the puzzle:

    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source= CHECSQLDEV1\NEO3\CHEC\knichols" + ThisWorkbook.Path + "\From Dyer.mdb;"

What am I doing wrong here? Yes I looked at our server paths per Norie's suggestion.

TIA

Kurt

 

[Norie]

Kurt

What error/problem are you encountering?

Did you try my suggestion of recording a macro?