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:
-
Private Sub GetData(dbName As String, sYear As String)
-
-
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:
-
Sub GetData(ByVal Year As String, ByVal FullFilePath As String)
-
-
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 »