derek:b3studios

Dynamic References in an Access Application.

So you have an Access database application. Along with this application, your company uses multiple versions of Office, and you use Excel, Word, Outlook - or any other Reference - where versioning might be a problem.

So how do you get your app to work flawlessly on Bob’s computer with Office 2000 and Mary’s computer with Office 2007? A little trick called VBE. Visual Basic Extensions! We are going to loop through our projects references, when we encounter either a reference we know is Word, Excel or Outlook - we’re going to remove it, and re-add the latest version. We don’t need to worry if the reference is broken or not, since removing and re-adding costs very little.

We identify references by using a GUID. VBA has a GUID for each reference. Even if you are using Excel 2003 or Excel 2007 - the GUID remains the same. What changes is the Major and Minor versions when loading them via VBA code. However, by specifying the versions as 0, 0 we get to force VBA to load the LATEST version. Thus making our code dynamically load whichever version is installed on the target machine.

For now, I’m only going to worry about three GUIDs. Word, Outlook and Excel. I’ll write later on how to retrieve the GUID ids, but if your clever the following code is enough for you to work from. With our new found knowledge of what we’re doing, how do we do it? Here are two functions that will help you put the mystery together.

To remove references we may have to these three COM objects.
Function RemoveOfficeReferences()
Dim chkRef As Reference
For Each chkRef In Application.References
Select Case chkRef.Guid
Case "{00062FFF-0000-0000-C000-000000000046}"
Application.References.Remove chkRef
Case "{00020813-0000-0000-C000-000000000046}"
Application.References.Remove chkRef
Case "{00020905-0000-0000-C000-000000000046}"
Application.References.Remove chkRef
End Select
Next
End Function


To add references we need for these three COM objects.

Function AddOfficeReferences()
On Error GoTo errhandler
'this bit of code will add the references the first part is the
'Function is .AddFromGuid(Guid, Major, Minor)
'Leave the Major and Minor @0 to retrieve latest version.

End Function

These are just samples, there are much more eloquent ways to do the above, but this gives you an idea! Now go out, and develop without fear of version issues!

Access 2007 .Transfer bug, the fun…….

I just spent 20 hours of my life, debugging one hell of a problem in Access 2007. I thought I would share this with you, incase anyone else maybe tearing their hair out - and the problem is similar.

Here is the setup. Access 2007 Database - Upsized/Migrated to SQL Server (2008 in my case). Everything worked fine before, exporting a Text File, or a Spreadsheet of the data. Now when you click on the button - It crashes the application. The stranger part - it doesn’t happen EVERY TIME!?

No in fact this problem, doesn’t even happen if you use the OutPutTo function instead…….

So after, all the tinkering eliminating the much more obvious offenders, what was the little damn stinker?! A UnionQuery…. A pesky UnionQuery was bombing out the .Transfer. It worked fine when I ran it, Access didn’t complain about it, nor did SQL server.

What I found was a pesky little ORDER BY on one of the UNION members. This trashed the .Transfer for some reason. SO there you have it. Remove your ORDER BY in one of your UNION statements, and life should be grand again!

Access 2007 , Tab Control , Catch a Tab Click. How?!

Ok so you are using Access 2007 for this example. You pull out a Tab Control, and want to fire an event when the tab is clicked. Ok easy, I’ll just add a method to the TabControl Click event right? But you discover it doesn’t work.. Why isn’t it working!? Well thats the problem, the tab control click event only works when you click the tab control, it doesn’t work when you click the actual tabs.

Well here is the solution, and only a one big limit. If you have a Tab Page, it SHOULD have a control on it. This will break depending on the situation if a Tab Page is blank, only because this specific event may NOT be fired.


Dim pasttab As Integer
Private Sub Detail_Paint()
'the detail paint will be fired, when the tab is changed
Dim activetab As Integer
activetab = Me.TabCtl0.Value
If Not activetab = pasttab Then
pasttab = activetab
'put code for the fired event of a 'Click here

End If
End Sub


get a free logo!

You can get a free logo here! Custom Logo Design

Remote Desktop Connection, Mac/Windows Citrix ICA Client, Or NESTED Terminal Clients, Shift Key or Caps Lock not recognized?

If you are like me, your a mac lover/user, and you live in a Windows world. By day I am a mild mannered Windows .Net Developer. By night, I dive into the underbelly of objective-c and cocoa.

When away from the office I have a need to use Windows Apps. What better way then with Remote Desktop Connection. But because I want to be able to use RDC on all my clients machines as well, why not use it through Citrix!

All was going great, when I was using this, until I tried to use a SHIFT key on OSX, through Citrix using Remote Desktop Connection.

Sometimes it would be recognized, most often not… The solution? When the RDC selection for your connection pops up, hit the Options Button. Then find the click the “Local Resources Tab”, then head down to the Keyboard Section. For the ALT+Tab, select “On the Local Computer”, log in and now the much needed Shift, and Caps Lock keys will work as desired Happy puting.

note: this will stop the ATL+Tab function from working to switch between windows. But it will stop the irritation, of not being able to properly case your words.

Finally an easy way to Twitter… How about Wordpress Support Harper? >:D

Ok so, anyone that reads this blog, should know by now how infrequently I update this. Well that will change. From my understanding everyone whose blog I read never blogs! I’m thinking about starting the infrequent bloggers club. We would inevitably meet infrequently.

But while I’m hear, let me tell you what Harper Reed did. Harper recreated one of the best twitter bots ever. It connects twitter to jabber (google talk). Why does this rock? Its as easy as sending an IM to excla.im, and bam Twitter status updated.

This rocks, because no more multiple apps across multiple platforms to make twittering easy. Try it out.

Only one question remains, Wordpress support harper? I would love this. If I could send a message from Jabber to my Wordpress blog and have it published. Perhaps I need to investigate XMPP more. Or just wish that someone else does it for me. Go internets!

Ok, time for some serious updates.

It has been a long time, but I am back, and with force my friends! I’m applying at a new firm, hopefully they will like my resume. I do not currently fit the job descriptions they have, but I believe I can be a new, and great addition to the team, and I am very interested in the type of work they do.

Anyway, that was a quick update, I have much time on my hands tonight as the baby has gone to sleep. Perhaps I will slip onto irc, and check with everyone. Oh and do I have some goodies in store, later.

Ok, so seriously…. 7 days? How about 736 days!? (update 7676 days is max)

update: the max number of days that can be gotten is 7676 days.

This is a call out to Codeweavers. Mainly Jeremy White. I hope you read my blog I read yours! I love Codeweavers. They have for the FIRST time in a long time, enticed me to move away from Open-Source strict software, and pay for a product. Its that good. They save me time in management. I don’t HAVE a lot of time. I don’t want to mess with winecfg, I don’t wanna dink with X11. Well thats the thing, I can’t dink with X11 anyway, because hey OSX 10.5 ;-/ kinda screwed the pooch on that one.

Apple X11 + Wine = No OpenGL support. Codeweavers, wove an amazing web. Crossover, and CrossOver Games 7.0, is awesome. Finally GUILDWARS, on my Mac. Here is the problem. I didn’t try CrossOver, until I got a Mac, Wine + X11 fairly stable in Linux, and actually I didn’t have a Linux gaming machine, I’ve been all ATI, and well we know how their track record is but that is another post!

So here I am on a Mac that can game, stuck Dual booting. CrossOver gives me the ability to STOP booting Windows. YAY my Mac is happy! Because VMware takes care of the need for my day job. Sweet. I download CrossOver 6.2, which is ok so so. Its a little behind, but I take the 30 day free offer, and play loads of games. Honestly it wasn’t that great, but it worked. And I heard CrossOver Games was coming out. WELL, I downloaded the demo. And boy was I sad. I have so many MORE games I want to try with CrossOver 7.0, due to the upgraded wine. You know what, they gave me a week. OH SHIT?! I thought Jeremy White had 2 kids?! And a wife!? And articles mentioned he didn’t have a lot of time!? Jeremy, WTF? 1 week to try out 7.0!?

So here it is, proof of concept, because I don’t think its fair to post, and most of the geeks will figure it out. Extend your CrossOver Games trial, past 7 days. Note: This is illegal, I am still in my 7 day trial period, as of writing this. I do plan to purchase the software as it is great, and CodeWeavers is still the bomb. But I think they were unfair this time around.

Also Jeremy, if you do stumble across this, consider helping the Mac community out with Leopard. I know you are a big fan of giving back to the community. And the one thing that some other tinkerers and hackers would like, is to have working X11, and OpenGL with wine.

Stop with the INTEL-HDA 3Stack business.

So today, I ran into great news, back in september someone released a patch for the Intel-HDA sound card, for Alsa 1.0.15!!!! What this means to you, if of course you have an Intel-HDA, is that your headphone port will not auto switch off the speakers, and it doesn’t just decrease the preamp, it works like it did on Windows!

Ok so how can you do this? Well these instructions should pretty much work on any Debian based system for sure. Since I use Dreamlinux, I’ll be talking about that distro, and Debian Stable in general.
more »

Why I’m growing more fond of Dreamlinux 2.2

I’ve begun to grow more fond of Dreamlinux 2.2 after finding out whats making it tick a little more. I appreciate the fact that its built pretty much right on Debian Stable (Etch). While the packages are out of date compared to Ubuntu, its making for a very usable and speedy/fast system, with little to no bugs found in say Gutsy Gibbon. Gusty Gibbon is bloated, hardly runs well on my laptop, and suffers some setback. This happens every other release of Ubuntu to me, by the time the next release of Ubuntu is coming out, the current 6 month cycle is just finishing up on being stable. And I’m really not too fond of sticking with a LTS from Ubuntu. They just havn’t proved them selves to be LTS people yet. Debian on the other hand, has roots that go way back.

For now, I’ll stick with a slightly out of date system, loving the Debian Stable branch.