Here’s the deal. Access database on a network share. Very, very, very complex set of queries (queries on queries on queries etc, etc). Ross, can you automate my reporting spread sheet?
So while the spreadsheet work is straight forward, the hard part here is getting the data into the spread sheet. I started off just writing some standard SQL and using ADO to pull a data set back. I soon realised, that the query had to be much more complex. In fact it was such a horrible set of logic, that I didn’t want to touch it with a barge pole!
What I thought I would try next is calling the query that the database used to produce a report, but return the dataset to my rs, and stick that in to Excel. That required the use of parameters that where getting fed into the underlying queries from a form in the database at run time. This was something I had not done with ADO before. A colleague showed me the way, basically:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbName & ";"
Set rs = New ADODB.Recordset
Dim qdf As New ADODB.Command
qdf.ActiveConnection = cn
qdf.CommandText = "SELECT * FROM [Some_Query];"
qdf.Parameters.Append qdf.CreateParameter("Forms!Billing!Combo8", adVarChar, adParamInput, 1, "")
qdf.Parameters(0) = ("*")
qdf.Parameters.Append qdf.CreateParameter("Forms!Billing!Combo12", adVarChar, adParamInput, 1, "")
qdf.Parameters(1) = ("*")
qdf.Parameters.Append qdf.CreateParameter("Forms!Billing!Combo18", adVarChar, adParamInput, 1, "")
qdf.Parameters(2) = ("*")
qdf.Parameters.Append qdf.CreateParameter("Forms!Billing!Combo14", adVarChar, adParamInput, 1, "")
qdf.Parameters(3) = ("*")
qdf.Parameters.Append qdf.CreateParameter("Forms!Billing!Combo16", adVarChar, adParamInput, 4, "")
qdf.Parameters(4) = (sYear)
qdf.Parameters.Append qdf.CreateParameter("Forms!Billing!Combo6", adVarChar, adParamInput, 1, "")
qdf.Parameters(5) = ("*")
Set rs = qdf.Execute
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End sub
This code worked!!!! Success? Joy? The gratitude of an over worked account? Not quite.
The next snag was an Access classic “Undefined function”. The function that caused the error was MonthName, which produces a string indicating the specified month. This is not supported via DAO, so now what!?!?!
Here’s an inelegant solution. Copy the database, it’s a front end back end job, so just the UI/ reporting part; keep the linked tables and get ride of the stuff you don’t need. Add some code to call the required query in access and pass the parameters, you can do this with code like this:
Dim qdf As QueryDef
Dim rst As Recordset
Set qdf = CurrentDb.QueryDefs("final charges by project period and person")
qdf.Parameters(0) = "*"
qdf.Parameters(1) = "*"
qdf.Parameters(2) = "*"
qdf.Parameters(3) = "*"
qdf.Parameters(4) = sYear
qdf.Parameters(5) = "*"
Set rst = qdf.OpenRecordset
'Make Text File
Dim F As Integer
Dim I As Integer
F = FreeFile
Open sFullFilePath For Output As #F
'Print Columns' Headers
For I = 1 To rst.Fields.Count
Print #F, rst.Fields(I - 1).Name & ",";
Next I
Print #F,
'Print Data
Do While Not rst.EOF
For I = 1 To rst.Fields.Count
Print #F, rst.Fields(I - 1).Value & ",";
Next I
Print #F,
rst.MoveNext
Loop
Close (F)
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing
End Sub
You’ll notice that this also prints out a text file (nice!), So in Excel automate this copy of the mbd, (which you stick on the same local PC as the excel spreadsheet, right!!!) then collect the text file in to Excel and there you have it! Perfection, no way, works, yes. Why the local copy? Well I didn’t what to “effect” the org db, and I didn’t what to automate over a slow network, I’m note sure if it’s a valid, but the thought of performing automation on a file saved on a network drive, doesn’t fill me with warm fuzzy feeling!
This may well be a contender for a WTF, but I’m WTF’d if I can think of a better way to solve the particular problem. Any ideas, ever done anything similar.
Request an Excel related blog post
15 April 2009 by Ross McLean
Do you have an idea for a blog post? If you have a request, then add a comment, if it fits in with what I know about I’ll try and do a post on it. If you want to write the post your self let me know!!!
Lets see how this goes!
(Dick, I will not blog about if statements!!!)
Tags: Blogs, Comment
Categories: General • 8 Comments »