2

thanks for your time good people!

I am adapting an Access 2010 client to work with a new SQL Express Server 2008 back end. The back end is up and running. No problems there. Filestream is configured and working fine.

My problem relates to acessing the filestream BLOB data I've stored in the SQL Express Server 2008 instance from my client. Specifically accessing the data through the win 32 OpenSqlFilestream API as detailed here: https://msdn.microsoft.com/en-us/library/cc645940.aspx

I know I cannot access this API within VBA. I have to write all the relevant code in a different language. Therefore I plan to write a mini-program in VB.NET which will consist of a pop up form that allows users to add, edit, delete etc the files stored through filestream.

I want to open this mini VB.NET program from within Access 2010 as a pop up form.

How do i do this? I have never written or compiled a program in vb.NET, furthermore I don't know how I can then call this program from within my VBA code in the Access 2010 client.

Please help.

Thanks,

Patrick Quigley

Patrick Quigley
  • 404
  • 2
  • 12
  • 1
    Are you overcomplicating this? ADO can read/write FILESTREAM data trivially: ([E.g.](https://www.experts-exchange.com/questions/26923910/How-to-save-and-read-files-stored-as-FileStream-in-SQL-Server-2008-from-MS-Access-2007-vba.html)) - If for whatever reason you can't do this you would build the VB.Net code into a COM Visible assembly DLL and call it from VBA via a reference. – Alex K. Oct 05 '16 at 10:47
  • Hi Alex, I've tried using adodb.stream to read the BLOB data. I ran into a problem there of having no idea what each file extension or original (before being added to a filestream column) file name was. If I am able to access that information I'd be able to use ADO sure. However, the second suggestions sounds good. Can you point me towards resources for building a COM visible assembly DLL? I know how to add the reference after it's written but, no idea how to write one myself. – Patrick Quigley Oct 05 '16 at 11:32
  • 1
    You can do that with a select query: `select file_strm_col.PathName() from your_table where id=x` – Alex K. Oct 05 '16 at 11:55
  • PathName() is no good unfortunately. PathName() returns the current path of the file in the file container. Based on what is returned, the file is given its own name by the filestream system. For example if I insert the file 'mypdf.pdf' into a filestream enabled column, PathName() will return a string which doesn't contain 'mypdf' or '.pdf'. I'd need to know the original file name of 'mypdf.pdf'. – Patrick Quigley Oct 07 '16 at 06:53
  • That's as good as it gets iirc as the original file name is not exposed, how were you going to do it using the API you mention? – Alex K. Oct 07 '16 at 11:00
  • Hey Alex, I'm a few hours into building a COM viable assembly DLL as per your suggestion. Thank you for suggesting that, I didn't realize I could write my own. I'm writing it in C# using Visual Studio Community. The DLL will allow me to make use of C#'s 'SqlFileStream' class within my VBA Access client. I'll answer the question when/if I've succeeded. – Patrick Quigley Oct 07 '16 at 13:29

0 Answers0