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.
'add outlook
Application.VBE.ActiveVBProject.References _
.AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 0, 0
'GUID for Outlook
'add excel
Application.VBE.ActiveVBProject.References _
.AddFromGuid "{00020813-0000-0000-C000-000000000046}", 0, 0
'GUID for Excel
'add word
Application.VBE.ActiveVBProject.References _
.AddFromGuid "{00020905-0000-0000-C000-000000000046}", 0, 0
'GUID for word
'add access
'Application.VBE.ActiveVBProject.References _
'.AddFromGuid "{00020430-0000-0000-C000-000000000046}", 0, 0
'GUID for access
errhandler:
If Err.Number <> 0 Then
MsgBox “could not link reference:” & Err.Description
Resume Next
End If
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!
Uncategorized: .TransferSpreadsheet 2007 access Access 2007 DoCmd.TransferSpreadsheet DoCmd.TransferText TransferText
by Derek
leave a comment
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!
News: 5.99 access cell cellular data i internet iphone mobile phone shadow smart smartphone t t-mobile t-zones tmobile unlimited web windows wm zones
by Derek
4 comments
Unlimited Data Plan from T-Mobile works with iPhone for 9.99!
So you don’t need 3G? You want email, some light web, google maps, pandora, low quality youtube, and iheartradio (or mobile streaming radio). Then T-Mobile’s Unlimited Edge plan is perfect for you.
Most people are tearing their hair out after giving up their much loved T-Zones by mistake, or for temporarily switching a phone, where T-Zones wouldn’t work correctly. Then you wanted to go back, and viola it was no longer an option. Most people tried the Web2Go for 9.99 and got the not compatible screenie, yikes! WTF?!
Behold T-Mobile Internet for Phones. 9.99 for 100 MBs, yup that sucks. But as everyone knows, if T-Mobile doesn’t have a plan for your phone, this is what your going to be offered. However, if you do a little digging, you will soon find another 9.99 Data Plan, with UNLIMITED access. This plan uses the Internet2.VoiceStream.Com APN for access.
The plan name is the T-Mobile Unlimited Shadow data access plan. Thats right, the T-Mobile Shadow. A WINDOWS SmartPhone, with a Fav 5s home screen. Since this phone is really not pushed in to the SmartPhone market, I guess T-Mobile thought no one would notice this 9.99 UNLIMITED SMARTPHONE dataplan floating around.
How can you get it? The easiest way, is to simply log into your account, @ T-Mobile.com, select the “This is Not my Phone” option, and switch your phone to a T-Mobile Shadow. Go to Service Options, and viola 9.99 unlimited shadow data plan is now available to be added to your plan!
Sure its $4 more, but it beats whining, pleading, and endless calls to get your T-Zones back, plus it works!
.net VB.NET: c# c#.net development https vb VB.NET wcf winforms wsdl
by Derek
leave a comment
WCF and WSDL and HTTPS and WinForms
So everytime I bring a post to this blog, I try to make sure it something that other people don’t just post willy nilly. So here is an interesting problem I had the other day, and the series of searches and trials, and time spent to bring this to some poor soul looking for an answer.
The problem: You are using .NET 3.0 or 3.5, and you need to connect to an older Web Service. Specifically, you need to connect to a WSDL that is secured. The WebService address could be something such as: https://www.here.com/service.wsdl
When you use Visual Studio to make this connection it does many things incorrectly. The big error you are likely to encounter, less any specifics about the WSDL you are trying to connect to is:
Type TEXT/XML is not valid, Type Application/XML was expected.
In one easy little new Binding, we can make this all go away. For the novices here is a detailed explanation.
Take this custom binding here
Stick it into your Bindings section in App.Config and point the EndPoint to use that binding. You should be off and running in NO TIME!
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
VB.NET linux: basic debian intrepid linux mono mono-basic mono-develop mono-develop-basic monobasic monodevelop ubuntu
by Derek
leave a comment
Mono Basic 2.4 Debian/Ubuntu build.
Mono is the Open Source .NET framework for Linux and Mac and Windows. If you have been looking for Mono Basic 2.4 package to install on Debian/Ubuntu since apparently there is no good mono 2.0 or VB.NET support, here you go! x86, and x86_64 builds provided, with original package sources.
X86
Get the original RPM Here built in the Fedora Channels here
Get the Alien rebuild for Debian/Ubuntu here
X86_64
Get the original RPM Here built in the Fedora Channels here
Get the Alien rebuild for Debian/Ubuntu here
Leave any questions in the comments.
VB.NET: .net dts execute execution remote server sql ssis VB.NET
by Derek
leave a comment
Remote execution of SSIS packages from an application server, setting custom parameters.
Generally I am known on this website for bringing infrequent, but wise tips to as many developers as I can. *wink* *wink* Here is an attempt to ramp up my efforts to help educate people, convert as much C# code to VB, and create some real Microsoft development enthusiasts.
This post will cover, SQL Server Integrated Services (SSIS), and namely one question, with several requirements. You want to execute an SSIS package remotely, perhaps from an ASP.NET webpage? You have a need to do this adhoc, with multiple users. You need to set variables inside the package, hell why else would you go through all the hassle to run this on demand?!
Ok so this sounds like a big issue. First, this is not a beginners SSIS post. If you are looking for how to set custom parameters through execution of SSIS this is not the place to start. However that said, I will provide a brief overview of how it works.
SSIS allows you to use the DTEXEC command (a windows executable, and SQL server command) to execute SSIS packages stored in various locations (local, ssis package store). The following option in a command line allows you to set a variable at runtime.
/SET "\package.variables[VariableName]“;”Value”By setting these commands at runtime, you just increased the power of your SSIS capabilties 10 fold. You can now user the power and localization of the SQL server to bear the grunt work of many data tasks, while you leave your application server’s resources left open for other user crunching. Now we are on the path for removing mindless data routines out of code, into a proper visual designer, and enchancing out application performance by offloading data routines to something that is dedicated to data!
Ok with that little bit of education out of the way lets continue! We’re assuming that you understand SSIS and SQL Server enough, to be able to Create an SSIS package, upload it to your server and execute it from a SQL Agent Job. This includes making a proxy. In addition to this, you will need a user that belongs to msdb, and can execute dts jobs. (dtsadmin)
Now for the code! This is converted, revamped from dtRemoteExec in C# found on codeplex. There were a few issues I found with this process in daily use. I have started to modify (it is by no means perfect) this code, which can be used in enterprise wide applications. Eventually I would like to have the code build the DTS Exec string for you, but this is still needed.
Here is a little explanation of how things work first! I almost forgot. You will need a DTS execution string like this.
"/SQL "\Maintenance Plans\SSISPACKAGE" /SERVER "b3studios" /USER username /PASSWORD password /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /SET "\package.variables[ExportLocation]“;”C:\Export”
I prefer to store the package with in the SSIS Package store. HOWEVER!!! Do not use a /DTS for calling the package, which is the default when using SSIS Package Store. This will almost certinly fail, or produce sporatic results. Instead, I prefer to execute the package from the SQL Server, using the /SQL command, let me know your thoughts in the comments.
Ok wheew mouth full, and I should rewrite a lot of this! But I’m too busy to do so! Here is the code, dubbed RemoteSSIS, based on dtexecremote, and rewritten in VB.NET
Here are the calls you will use from your application.
Dim oRemote As New RemoteSSIS
oRemote.sqlServer(False) = GetServer()
oRemote.useProxy("ProxyName") = True
oRemote.username = "Username"
oRemote.password = "Password"
oRemote.dtsCommand = DtsCommand
If oRemote.CreateJob() Then
oRemote.RunJob()
End If
If oRemote.iscomplete Then
Return oRemote.ClearJob()
End If
Here is the code you can add to any module in your project. Add the following references,
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlSever.SMO
Microsoft.SqlSever.SmoEnum
Microsoft.SqlServer.SqlEnum
Imports System.Data
Imports System.Data.Sql
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent
Imports Microsoft.SqlServer.Management.Common
Public Class RemoteSSIS
#Region "Variables"
'entry variables
Private strSqlServer As String = Nothing
Private strDtsCommand As String = Nothing
Private strProxyName As String = Nothing
Private boolUseProxy As Boolean = False
Private boolSecureConnection As Boolean = False
Private strUsername As String = Nothing
Private strPassword As String = Nothing
Private jobCreated As Boolean = False
Private jobRan As Boolean = True
Private jobCleared As Boolean = True
Private strjobStatus As String = Nothing
Private boolisfinished As Boolean = False
'server objects
Private serverConn As ServerConnection = New ServerConnection
Private svr As Server
Private js As JobServer
Private jb As Job
#End Region
#Region "Properties"
Public Property sqlServer(ByVal secureConnection As Boolean) As String
Get
Return strSqlServer
End Get
Set(ByVal value As String)
strSqlServer = value
End Set
End Property
Public Property dtsCommand() As String
Get
Return strDtsCommand
End Get
Set(ByVal value As String)
strDtsCommand = value
End Set
End Property
Public Property useProxy(Optional ByVal ProxyName As String = Nothing) As Boolean
Get
Return boolUseProxy
End Get
Set(ByVal value As Boolean)
boolUseProxy = value
strProxyName = ProxyName
If boolUseProxy = True And strProxyName = Nothing Then Throw New Exception("can not set use proxy to true, and proxy to Nothing")
End Set
End Property
Public Property username() As String
Get
Return strUsername
End Get
Set(ByVal value As String)
strUsername = value
End Set
End Property
Public Property password() As String
Get
Return strPassword
End Get
Set(ByVal value As String)
strPassword = value
End Set
End Property
Public ReadOnly Property iscomplete() As Boolean
Get
Return boolisfinished
End Get
End Property
Public ReadOnly Property jobStatus() As String
Get
Return strjobStatus
End Get
End Property
#End Region
#Region "Public Methods"
Public Function CreateJob() As Boolean
Try
'create a new sqlserver object
serverConn.ServerInstance = strSqlServer
serverConn.LoginSecure = boolSecureConnection
serverConn.Login = strUsername 'these should be moved over to the config file
serverConn.Password = strPassword 'these should be moved over to the config file
'create a connection to the server
svr = New Server(serverConn)
'set the jobserver
js = svr.JobServer
'create a unique job
Dim jobName As String = "dtexecRemote_temp_job_" + Guid.NewGuid.ToString
'create a new job catagory
Dim jc As JobCategory = New JobCategory(js, "dtexecRemote")
'set the job type to local
jc.CategoryType = CategoryType.LocalJob
'refresh the job category to see if it exists
jc.Refresh()
If Not jc.State = SqlSmoState.Existing Then
jc.Create()
End If
'create a new job
jb = New Job(js, jobName)
'set the category
jb.Category = jc.Name
'create the job
jb.Create()
'add the ssis goodies next
jb.ApplyToTargetServer(svr.Name)
'create a jobstep pointing to the package
Dim JobStep As JobStep = New JobStep(jb, "run package")
'add the DTS command to the jobstep
JobStep.Command = strDtsCommand
JobStep.ProxyName = strProxyName
'tell the agent to run an ssis job and actions to take
JobStep.SubSystem = AgentSubSystem.Ssis
JobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
JobStep.OnFailAction = StepCompletionAction.QuitWithFailure
'create the job
JobStep.Create()
jobCreated = True
Return True
Catch ex As Exception
jobCreated = False
Trace.Write(ex.Message)
Return False
End Try
End Function
Public Sub RunJob()
jb.Start()
While (jb.CurrentRunStatus = JobExecutionStatus.Executing)
Threading.Thread.Sleep(TimeSpan.FromSeconds(2))
jb.Refresh()
End While
Do Until Not jb.LastRunOutcome = CompletionResult.InProgress And Not jb.LastRunOutcome = CompletionResult.Unknown
Threading.Thread.Sleep(TimeSpan.FromSeconds(2))
jb.Refresh()
Loop
Dim outcome As CompletionResult = jb.LastRunOutcome
boolisfinished = True
strjobStatus = outcome.ToString
End Sub
Public Function ClearJob() As Boolean
Try
If jb.LastRunOutcome = CompletionResult.Succeeded Then
jb.Drop()
Return True
Else
Return False
End If
Catch ex As Exception
Trace.Write(ex.Message)
Return False
End Try
End Function
#End Region
End Class
Wow along night…. I’ve been working on this crazy like!
Wow a long day, and I still need to throw some laundry in. I’ve decided on my next project, and unlike most I don’t want this one to be vapor ware. I’ve finally found a good rhythm with work, and my desire to code outside of it, as long as I get projects that interest me.
Today’s development on broadcatchR is going well. Thanks Josh for the name! Hopefully he gets time to work on this project. Right now I am currently working on getting a XMLTV feed going. Yes, that is right if anyone on the Internets stumbled across this I am working on getting some sort of XMLTV feed going. It maybe free to the Internets, but it maybe select cities.
I am currently working out the details. This will take some time, but I do have something rough working, and I hope to have the Twin Cities, programming guides for the next few weeks available at b3studios, through broadcatchR.
broadcatchR.com carries my latest blog post about broadcatchR related news, let me know what you think about the cross post? I’m lazy. 
Welcome to broadcatchR!
This is the first post for my new project, broadcatchR.com Please check in for more on this project soon! I will have it live and going while I develop it. You can read the blog postings on http://www.b3studios.com/derek!