{"id":92,"date":"2011-08-04T01:21:16","date_gmt":"2011-08-04T06:21:16","guid":{"rendered":"http:\/\/kratzindustries.com\/CodeRedBlog\/?p=92"},"modified":"2014-03-27T04:36:21","modified_gmt":"2014-03-27T04:36:21","slug":"on-accessing-access-from-64-bit","status":"publish","type":"post","link":"https:\/\/burnt-traces.com\/?p=92","title":{"rendered":"On Accessing Access from 64 Bit"},"content":{"rendered":"

One skill they say is needed for software development is problem solving, and as a developer myself I know this to be true. It often seems like not a day will go by without encountering something outside of the original plan. Just the other day I was testing an application, which simply reads some data from a MS Access database. The database is part of a purchased software product, so I don’t really have any choice in the matter, it must be Access. Then up pops an exception The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.<\/code> <\/p>\n

Well some internet research quickly found that the default Jet database driver is not 64 bit compatible. The official resolution? Force your application to be 32 bit and all will be well. Well, not today. Although I hate leaving such decisions up dumb incompatibility issues, in this case I couldn’t make my app 32 bit. The application is an add-on extension to AutoCAD, which, if your running 64 bit windows, will only install as 64 bit. That means that any assemblies it loads, like mine, must also run 64 bit. <\/p>\n

So, here I though I was up that darn creek again without my paddle. But low and behold I found my original research was a little dated, there was a new 64 bit driver that could be used for Access databases. It turns out, that even though Microsoft really wanted to steer developers away from using Access in favor of SQL Express, they had to make a driver so that the MS Office suite could be 64 bit. Ok, cool. So I download the new driver, but of course, it doesn’t install. It complains that I must first uninstall my 32 bit version of Office before installing the driver. Ok, I definitely don’t want to go down this road, because not only does it mean making sure that this new driver gets installed on the users PC, but that the correct version of Office is on there as well.<\/p>\n

Well, there I sat, convinced that this would either not work or be one of those things I regret doing much later. Then a little inspiration. I though, maybe I just need a separate assembly that can run 32 bit? Well that’s not possible, the 32\/64 bit decision is made for the whole process, not just an assembly. It would have to be a separate executable.
\nI started to feel good about this, a separate ‘proxy’ executable to interact with access for me. The interprocess communication wouldn’t be bad, I would just have to send it SQL statements and it could send me back the results as a DataTable<\/code>. The DataTable<\/code> object has its own ReadXML<\/code> and WriteXML<\/code> functions, so the matter of moving the object would be simple.<\/p>\n

Ok, so a separate executable. That would mean another project in my solution and in source control. It would also have to get deployed with the client with a known location so it can be started when its needed. Not really that big of a hurdle, but I wanted to try for something else anyways. More internet research turned up some example of using the CodeDOMProvider<\/code> class to compile and emit an executable file. I liked that concept, create the executable on demand from a short piece of code. Wrap all this up in a single class and I’m happy.<\/p>\n

First thing to do was write the proxy code. I really only needed two operations from the database. The ‘Execute Scalar’ option to return a singe value, and an option to fill a DataTable<\/code> object with results from an query. If I used the System.Diagnostics.Process<\/code> to start the proxy process, I could read and write to the processes standard input and standard output. That way the proxy could use the Console.In<\/code> and Console.Out<\/code> streams to handle its communication with the client process.<\/p>\n

So I pass the proxy the connection string on the command line, so it can create a connection object to be used later. Then it reads from standard input. I will send it commands and end the commands with the null character. Once it reads the command, either ‘GetDT’, ‘Scalar’, or ‘Die’, it then looks for the query to written to standard input, again terminated with the null character. It executes the query, then either writes the singe value to standard out for scalar, or it writes the XML representation of the DataTable<\/code> object. The calling process reads the result and we are done.<\/p>\n

Now the client class was designed to be created, used, and then closed. The constructor, which also contains the source code to the proxy as a simple string constant, compiles the proxy code and generates the executable file. The constructor then runs the executable, saving the Process<\/code> object so the standard in and out can be used later. The object then defines a function for each command it can run in the proxy. The function sends the commands and retrieves the result from the proxy’s standard output. When you are done using the object, simply call the Close<\/code> method to send the proxy the ‘Die’ command. When the proxy receives this command the process exists. <\/p>\n

See the full class code below:<\/p>\n

\r\nImports System.CodeDom.Compiler\r\n\r\n\r\nPublic Class Access64Bridge\r\n\r\n    Private Proc As Process\r\n\r\n\r\n    Public Sub New(ByVal ConnectionString)\r\n\r\n        'just use the temp folder for the exe location\r\n        Dim output As String = My.Computer.FileSystem.SpecialDirectories.Temp & "Access64Bridge.exe"\r\n\r\n        Dim CDP As CodeDomProvider = CodeDomProvider.CreateProvider("VB")\r\n        Dim params As New CompilerParameters\r\n        params.OutputAssembly = output\r\n        params.GenerateExecutable = True\r\n        'Compiler options, most important are x86 flag and winexe\r\n        '\/target:winexe doesn't show a console window\r\n        params.CompilerOptions &= "\/platform:x86 \/optioninfer \/optionstrict- \/target:winexe"\r\n        'Add references required by code\r\n        params.ReferencedAssemblies.Add("System.Data.dll")\r\n        params.ReferencedAssemblies.Add("System.Xml.dll")\r\n        params.ReferencedAssemblies.Add("Microsoft.VisualBasic.dll")\r\n\r\n        'CDATA xml trick from the internet, good way to do multiline \r\n        'string literal like some other languages allow\r\n        Dim Source As String = <![CDATA[\r\n        Imports System\r\n        Imports System.Data\r\n        Imports Microsoft.VisualBasic\r\n\r\n        Class Test\r\n        Public Shared Sub Main(ByVal args() As String)\r\n            Try\r\n                Dim connstr As String = args(0)\r\n            Dim conn = New\r\n                OleDb.OleDbConnection(connstr)\r\n\r\n                Do\r\n                    'Get Command\r\n                    Dim Comm As String = ReadInputValue()\r\n                    Select Case Comm\r\n                        Case "GetDT"\r\n                            'Next thing in is the SQL\r\n                            Dim SQL As String = ReadInputValue()\r\n\r\n                            Dim DT As DataTable = New DataTable()\r\n                            Dim DAObjOLE As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter\r\n                            Dim CommandObj As New OleDb.OleDbCommand(SQL, conn)\r\n\r\n                            conn.Open()\r\n                            DAObjOLE.SelectCommand = CommandObj\r\n                            DAObjOLE.Fill(DT)\r\n                            conn.Close()\r\n\r\n                            DT.TableName = "Output"\r\n                            'Serialize data table to the\r\n                            output()\r\n                            DT.WriteXml(Console.Out,\r\n                            XmlWriteMode.WriteSchema)\r\n                            'End with the null char\r\n                            Console.Out.Write(vbNullChar)\r\n                        Case "Scalar"\r\n                            'Next thing in is the SQL\r\n                            Dim SQL As String = ReadInputValue()\r\n                            Dim CommandObj As New OleDb.OleDbCommand(SQL, conn)\r\n\r\n                            conn.Open()\r\n                            Dim Value As Object = CommandObj.ExecuteScalar\r\n                            conn.Close()\r\n\r\n\r\n                            Console.Out.Write(Value.ToString)\r\n                            Console.Out.Write(vbNullChar)\r\n                        Case "Die"\r\n                            Return\r\n                        Case Else\r\n                            'Unrecognized command, host possibly is gone, just end\r\n                            Return\r\n\r\n                    End Select\r\n\r\n                Loop\r\n            Catch ex As Exception\r\n                MsgBox("Access 64 to 32 bit Bridge: " &\r\n                ex.Message & ex.StackTrace)\r\n            End Try\r\n        End Sub\r\n\r\n        Public Shared Function ReadInputValue() As String\r\n            Dim Value As New Text.StringBuilder\r\n            Dim ch As Integer = Console.In.Read\r\n            While ch <> 0 And ch <> -1\r\n                Value.Append(Chr(ch))\r\n                ch = Console.In.Read\r\n            End While\r\n            Return Value.ToString\r\n        End Function\r\n    End Class\r\n\r\n        ]]>.Value\r\n\r\n        Dim Result As CompilerResults = CDP.CompileAssemblyFromSource(params, Source)\r\n        Dim X As New System.Diagnostics.ProcessStartInfo\r\n        'Must redirecte standard in and out so we can use it\r\n        X.RedirectStandardOutput = True\r\n        X.RedirectStandardInput = True\r\n        'Path of executable to run\r\n        X.FileName = output\r\n        'Required for redirection\r\n        X.UseShellExecute = False\r\n        'Connection string surrounded by double quotes so it comes\r\n        'into the proxy as a single argument\r\n        X.Arguments = """" & ConnectionString & """"\r\n        'Start Process\r\n        Proc = Process.Start(X)\r\n\r\n\r\n    End Sub\r\n\r\n    ''' <summary>\r\n    ''' Reads a value from the access bridge program\r\n    ''' <\/summary>\r\n    ''' <returns><\/returns>\r\n    ''' <remarks><\/remarks>\r\n    Private Function ReadValue() As String\r\n        Dim Value As New Text.StringBuilder\r\n        Dim ch As Integer = Proc.StandardOutput.Read\r\n        'use null termniated messages, -1 means the stream is dead\r\n        While ch <> 0 And ch <> -1\r\n            Value.Append(Chr(ch))\r\n            ch = Proc.StandardOutput.Read\r\n        End While\r\n        Return Value.ToString\r\n    End Function\r\n\r\n    ''' <summary>\r\n    ''' Runs sql and returns the first result\r\n    ''' <\/summary>\r\n    ''' <param name="Sql"><\/param>\r\n    ''' <returns><\/returns>\r\n    ''' <remarks><\/remarks>\r\n    Public Function ExecuteScalar(ByVal Sql As String) As Object\r\n        'Command\r\n        Proc.StandardInput.Write("Scalar")\r\n        'Null terminate\r\n        Proc.StandardInput.Write(vbNullChar)\r\n        'Sql\r\n        Proc.StandardInput.Write(Sql)\r\n        'Null termniate\r\n        Proc.StandardInput.Write(vbNullChar)\r\n\r\n        Return ReadValue()\r\n    End Function\r\n\r\n    ''' <summary>\r\n    ''' Executes the SQL on the bridge and returns the resulting DataTable\r\n    ''' <\/summary>\r\n    ''' <param name="Sql"><\/param>\r\n    ''' <returns><\/returns>\r\n    ''' <remarks><\/remarks>\r\n    Public Function CreateDT(ByVal Sql As String) As DataTable\r\n        'Command\r\n        Proc.StandardInput.Write("GetDT")\r\n        'Null terminate\r\n        Proc.StandardInput.Write(vbNullChar)\r\n        'Sql\r\n        Proc.StandardInput.Write(Sql)\r\n        'Null termniate\r\n        Proc.StandardInput.Write(vbNullChar)\r\n\r\n        Dim Value = ReadValue()\r\n        Dim DT As New DataTable\r\n        DT.ReadXml(New IO.StringReader(Value))\r\n        'Accept changes else all rows will be new\r\n        'New rows are removed from the collection when deleted\r\n        DT.AcceptChanges()\r\n        Return DT\r\n    End Function\r\n\r\n    Public Sub Close()\r\n        'Command\r\n        Proc.StandardInput.Write("Die")\r\n        'Null terminate\r\n        Proc.StandardInput.Write(vbNullChar)\r\n    End Sub\r\nEnd Class\r\n\r\n\r\n<\/pre>\n
You never know what your going to have to do to get the job done.<\/p>\n","protected":false},"excerpt":{"rendered":"
One skill they say is needed for software development is problem solving, and as a developer myself I know this to be true. It often seems like not a day will go by without encountering something outside of the original plan. Just the other day I was testing an application, which simply reads some data… Read More »On Accessing Access from 64 Bit<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","footnotes":""},"categories":[5],"tags":[6,10,18,88],"class_list":["post-92","post","type-post","status-publish","format-standard","hentry","category-vb-net","tag-access","tag-codedom","tag-ipc","tag-vb-net"],"_links":{"self":[{"href":"https:\/\/burnt-traces.com\/index.php?rest_route=\/wp\/v2\/posts\/92","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/burnt-traces.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/burnt-traces.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/burnt-traces.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/burnt-traces.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=92"}],"version-history":[{"count":1,"href":"https:\/\/burnt-traces.com\/index.php?rest_route=\/wp\/v2\/posts\/92\/revisions"}],"predecessor-version":[{"id":329,"href":"https:\/\/burnt-traces.com\/index.php?rest_route=\/wp\/v2\/posts\/92\/revisions\/329"}],"wp:attachment":[{"href":"https:\/\/burnt-traces.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=92"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/burnt-traces.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=92"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/burnt-traces.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=92"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}