Edwards - PYTHON AND ACCESS: Creating Reports Using HTML AND RDLC

Python And Access Creating Reports Using HTML AND RDLC Richard Edwards Introduction You only live once. JUST DONT DIE ON ME. What do you have, that I dont have? Probably, a lot more time on this planet than I do. But dont feel sorry for me just because I am 71 and trying to help you learn more about different languages and coding conventions. This book is about working with Python. It is also about working with Access to connect to a database, select a table and then mold the data into HTML and RDLC Reports.

Want more, go elsewhere. Want less, go elsewhere. Want code to create HTML and RDLC solution objects, you are at the right place. And that covers the introduction flyby. Getting your Python Environment Ready Normally, you would expect me to tell you where to download Python for Windows. Of course, that is coming.

But not all versions work with installing the win32com.client and vice versa. And since this book relies heavily on the win32com.client, it is important that both the Python version and the pywin32 versions below match each other in bit and version numbers. Heres the link for the above files and heres the link for the list of Python for windows installs. I have 3.78 using 32-bit and 3.9 using 64-bit and at the same time. Both as you can see on the next page are working just fine. Access Why cant we just have fun writing code? You know, I remember when I bought a book a week trying to keep up with the ever-changing technologies and was glad to get just one chapter that helped me have a better understanding of what my customers were up against.

One chapter. The book cost $50. Did I complain? No. Why? Because I had enough respect as a writer to appreciate the never seen hours and hours of research and code testing that went into that one chapter that I desperately need. Did not matter what it cost, either. I just knew that the subject and content was exactly what I was looking for.

Now, I come along, charge bare minimum and everyone wants to complain. Perhaps, I should charge more. This might sound like a metaphorical contradiction considering what is about to follow but I am not trying to make Accesss users experience much more satisfying but your experience as a programmer. After all, it is you, if you have the skills, best suited to judge the Access users needs and create a solution to satisfy those needs. As I see it, my job is to explain to you what the code does and then explain how it can be applied. It is then up to you to do the same and apply what you have learned to weave it into their Access security blanket.

Problem is, unless you are a passionate user of Access as an application yourself, it is next to impossible to provide compelling for them blindly. With that said, heres the Mr. Bah Humbugs guide to the universe of Access.

Yep, thats true Mr. Humbug. Why waste your time trying? Psst, did you notice how big your butt is getting as you sit on your butt doing nothing and telling everyone within listening distance to not even try? You must be a tester.

Lets face it, out of the box solutions are boring addons, sound more like drone coding for customers not happy with just the product doing the job it was supposed to do, and make you feel like you are working for Microsoft without any benefits from them for doing so. In fact, the only real reason why you should be providing a service where you are generating code to cement Access with Excel or Access and Outlook or Access and Sql Server as your own business venture is because youre working for Microsoft since Microsoft is the only company really profiting when such appeasements make their customers happy. There is no reason in the world why the only company who makes money from you enhancing their product is Microsoft. After all, dont you have to purchase Office 365 from Microsoft just to create interactive programs so you can have the ability to create them? I rest my case on that.

I call it the blanket offering. Do what you do best, put it out on the chopping block and as for feedback.

Sound like a plan? Good!, heres the concept: Many programmers just dont have the time to spend learning how and why the pieces of the puzzle do, indeed, work with each other to generate something bigger and better than the program was designed to do out of the box. My goal here is to take work out the kinks so to speak and supply you with the solutions and the work arounds. The result: saving you time effort and tons of frustration in the process. Hopefully, this is where I believe I can help you. So, heres the plan: We are going to use HTML and RDLC Reports to create Reports for Access. Thats it. Thats it.

Lets go to work. HTML Reports No such thing as boring At least, not in my world. There are two different kinds of tables: With Gridlines Without Gridlines There are four different kinds of renderings: Bound Cloaked Dynamic Static There are four different Orientations: Multi-Line Horizontal Multi-line Vertical Single-Line Horizontal Single-Line Vertical There are only different Orientations for Bound: Multi-Line Horizontal Single-Line Horizontal Single-Line Vertical There are four different Additional tags: Div Span Textarea Textbox Lets start with Static. Static Code Totally written by Python and not inside html And that is the best way to describe it. Below is the code: import win32com.client import string Tablename = "Products" TableType = "Table" Orientation = "Multi-Line Horizontal" ControlType = "None" svc = win32com.client.GetObject("winmgmts:\\\\.\\root\\cimv2") objs =svc.ExecQuery("Select * from Win32_Process where Name='MSACCESS.EXE'") for obj in objs: try: obj.Terminate() except: print("Found Access and killed it.") ws = win32com.client.Dispatch("WScript.Shell") DBName = ws.CurrentDirectory + "\\Accadwproduct.accdb" oAccess = win32com.client.Dispatch("Access.Application") oAccess.OpenCurrentDatabase(DBName) db = oAccess.CurrentDB() rs = db.OpenRecordset(Tablename) filename = ws.CurrentDirectory + "\\" + Tablename + ".hta" fso = win32com.client.Dispatch("Scripting.FileSystemObject") txtstream = fso.OpenTextFile(filename, 2, True, -2) txtstream.WriteLine("") txtstream.WriteLine(" ") txtstream.WriteLine("") txtstream.WriteLine(" ")

txtstream.WriteLine("th")

txtstream.WriteLine("{")

txtstream.WriteLine(" COLOR: white;")

txtstream.WriteLine(" BACKGROUND-COLOR: MidnightBlue;")

txtstream.WriteLine(" FONT-FAMILY:font-family: Cambria, serif;")

txtstream.WriteLine(" FONT-SIZE: 12px;")

txtstream.WriteLine(" text-align: left;")

txtstream.WriteLine(" white-Space: nowrap;")

txtstream.WriteLine("}")

txtstream.WriteLine("td")

txtstream.WriteLine("{")

txtstream.WriteLine(" COLOR: navy;")

txtstream.WriteLine(" BACKGROUND-COLOR: #eeeeee;")

txtstream.WriteLine(" FONT-FAMILY: font-family: Cambria, serif;")

txtstream.WriteLine(" FONT-SIZE: 12px;")

Next page