locked
Read/write data from Microsoft Access Database RRS feed

  • Question

  • I need to read/write data to an Microsoft Access database within my metro app, but if I am correct that is not possible? What options do I have? Webservices? I'm not very experienced with webservices, do you need web servers/internet Connections when using such? The application itself will run mostly without any internet connection of any kind.
    Thursday, August 16, 2012 12:18 PM

Answers

  • @kolback - without a developer license you won't be able to side load to your customer.  They should be following enterprise deployment setup for this.  However, even with that said, you'd be doing your customer a disservice to develop an app that doesn't pass the Windows App Certification Kit (WACK) -- the same initial test the store uses.  Even in enterprise deployment situations where this isn't *required* we have stated that we make no commitment that this wouldn't be required in the future and that passing the WACK should always be a goal.

    The reason you are able to run it locally yes is because you have a dev environment.  Your customer would have to have some windows service hosting your web service, etc.  Then at that point you are adding too much to this IMO.

    Have you explored SQLite as an option?  It's a database that you can embed in your application and perform queries directly against.


    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)

    • Marked as answer by kolback Monday, August 20, 2012 7:40 AM
    Saturday, August 18, 2012 12:30 AM

All replies

  • mm yes.. so you need a desktop application / webapplication that expose webservices to talk with access databases

    maybe take a look at sqllite?

    Thursday, August 16, 2012 12:32 PM
  • Problem is my Access database already exists (it's an huge application in itself) and cannot be wiped out very easily. Somehow I need to extract data from my access database (preferably from inside my metro app). I will not have any internet connection available doing so, if web services demands webservers/Connections to the internet that is not an option.

    The application itself will be on a tablet and it has to be a metro app, not a desktop/web application.


    • Edited by kolback Thursday, August 16, 2012 1:36 PM
    Thursday, August 16, 2012 1:34 PM
  • if you need an embedded, queryable database, you will have to use SQLite.  Access format isn't easily modified without something like ADO or ODBC connectivity and this is not available in the .NET core profile.

    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)

    Thursday, August 16, 2012 5:01 PM
  • I am also new to Metro/VB 2012 apps for Windows 8, and need to clarify this...  Is it really true that one cannot simply bind XAML controls to records in an Access database with VB 2012?  This is so easy in Win Forms (Data Source Configuration Wizard, etc.) that I assumed it would also be relatively easy in XAML/VB 2012/Metro.  Why is it not considered a pretty important feature?  (I am perhaps missing something here, but I'm sure its rather obvious....)

    Thanks--


    Michael Halvorson

    Friday, August 17, 2012 12:08 AM
  • @Michael - you are referring to WinForms that has the full .NET Framework and all full-trust capabilities.  WinRT apps are not full trust and also the .NET profile is not the full .NET framework.  WinRT apps are deployed via a confidence model for the user with only the app using what is provided in the WinRT API surface area and whatever the app brings itself.  If someone creates an Access provider for an app to use that allows you to query the mdb/mdbx file and uses only the safe APIs for the store, then it will work.  As of right now, the approaches you'd use in a WinForms app don't apply here.  There is no ADO.NET, ODBC or Access engine that are a part of the WinRT API surface area. 

    If the access database is just used to store records (and doesn't contain access forms, etc.) then SQLite is a simple option to use as well for those same CRUD operations that your app would perform.


    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)

    Friday, August 17, 2012 1:10 AM
  • Now I have tried a little bit more using wcf service library and in my test environment reading data from Access works just fine. :) Though I'm not certain it will work on a tablet and I yet don't understanding the process itself. This is what I've done so far.

    First I have my metro app. A really simple helloworld type of app - a textbox, a button and a textblock. Also I have an WCF Service Library and if I understand things correctly this will work/apply locally and wont need an internet connection. From within my service I can use System.Data so getting data from Access is not a problem. 

        [ServiceContract]
        public interface IService1
        {
            [OperationContract]
            string GetData(string value);
    
        }


            public string GetData(string value)
            {
                // access connect
                OleDbConnection conn = 
                    new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\x\Documents\TestDatabase.accdb;");
                OleDbCommand comm = new OleDbCommand();
    
                // get data
                // return returnValue;
            }

    And then from my MainPage, add a reference to the service, and in the buttons click event just call the method GetDataAsync with parameters and collect the returning value.

            private async void Button_Click(object sender, RoutedEventArgs e)
            {
    
                ServiceReference1.Service1Client client = new ServiceReference1.Service1Client();
                var s = await client.GetDataAsync(nameInput.Text);
    
                // text block
                output.Text = s.ToString();
            }

    This works just fine on my pc with windows 8/Visual Studio installed, but Im a bit confused if this will apply on a tablet disconnected from the internet? How about my service? Will it run within my metro app? As a service locally? Can I sideload it? Etc etc. Thanks for all your input.


    • Edited by kolback Friday, August 17, 2012 10:17 AM
    Friday, August 17, 2012 10:16 AM
  • As long as your service isn't local it will work.  You can't access localhost type services without the end user doing a few technical things with their firewall.  Additionally an app won't pass store certification in these attempts.

    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)

    Friday, August 17, 2012 3:46 PM
  • I plan to sideload this application, it's designed for a specific customer, so no need for store certification (I hope). I want my service to be local, thats the point! All I need is to read data from a simple access database locally without any internet connection what so ever, I don't
    understand how this could be such a tricky... :/

    But what you are telling me my only option is to do this with web services (and because of that I'm in an dead end here...). How come I can run my service locally on my dev environment? Is it because I have Visual Studio installed? Win 8 on tablets and PCs are suppose to be the same, right?

    Saturday, August 18, 2012 12:26 AM
  • @kolback - without a developer license you won't be able to side load to your customer.  They should be following enterprise deployment setup for this.  However, even with that said, you'd be doing your customer a disservice to develop an app that doesn't pass the Windows App Certification Kit (WACK) -- the same initial test the store uses.  Even in enterprise deployment situations where this isn't *required* we have stated that we make no commitment that this wouldn't be required in the future and that passing the WACK should always be a goal.

    The reason you are able to run it locally yes is because you have a dev environment.  Your customer would have to have some windows service hosting your web service, etc.  Then at that point you are adding too much to this IMO.

    Have you explored SQLite as an option?  It's a database that you can embed in your application and perform queries directly against.


    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)

    • Marked as answer by kolback Monday, August 20, 2012 7:40 AM
    Saturday, August 18, 2012 12:30 AM
  • Thanks for reply. The existing Access database are not only for data storage, also there is an Access VBA application on top of it and it can't be replaced very easily. Data depend on that application, lot's of calculation are made on my data within that application.

    SQLLite could be an option as a temporary storage solution I guess, and then when an internet connection is available I could pass all data to my Access DataBase through an service. Or if I just use text files as temporary data storage.

    What's the reason you can't use System.Data in an metro app? Is it all about security concerns? 

    Monday, August 20, 2012 7:40 AM
  • @kolback - the scope of winrt apps is limited to the API surface area exposed.  the methods you normally used for doing what you did in .NET would have been ADO.NET.  These types of behaviors have not been readily available in some platforms like Silverlight/Phone for a bit now, preferring a service-based approach or more modern embedded databases as an alternative.

    You are correct that using local text file storage is an option here as well.  Windows.Storage provides those alternatives an looks very similar to .NET's IsolatedStorage API surface area.


    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)

    Monday, August 20, 2012 3:50 PM
  • HI Tim,

    can you tell me where I can get information on how to use sqlLite...I was also hoping to use access and an ado connection. I am disappointed that MS is not supporting this option in Windows Metro and I think this is another example of the shortsideness of MS.

    Marlon

    Tuesday, September 11, 2012 2:03 PM
  • @marlon - you can get SQLite and what is supported by them from their site http://www.sqlite.org -- if you are a .NET developer I recommend looking at the SQLite-net library for usage.  I have a walk through on my blog showing some of this: http://timheuer.com/blog/archive/2012/08/07/updated-how-to-using-sqlite-from-windows-store-apps.aspx.

    Using ADO.NET would require your app to have a DIRECT connection to your database.  Since these apps are going to be in the hands of users, you really don't want your literal database exposed over the internet.  Using a services-based approach is very common in modern technologies and this is no different.  If you create a service using WCF, you'll get strong-typed classes you can use in your Win8 application to help you be more productive.


    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)

    • Proposed as answer by marlon_1_1 Thursday, September 13, 2012 10:38 AM
    Tuesday, September 11, 2012 3:18 PM
  • HI Tim,

    I went to your url blog link and got a 404 error...did you remove your instructions?

    Marlon

    Tuesday, September 11, 2012 6:13 PM
  • @Marlon - no, it looks like the text editor here added a period to the end of the URL...just remove that.

    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)

    Tuesday, September 11, 2012 6:26 PM
  • Tim, so, do I understand correctly:

    1. The only way of connecting to an external database is through external WCF services.

    2. There is no data layer of any kind native from Microsoft to WinRT apps.

    3. A third party open source database is the only answer for local storage?

    4. So, if I want to create a cloud based solution that both desktop and tablet users can connect to, my one and only option is external hosted WCF web services?

    If 1 and 4 are correct, where can I find information about how to do this in a Win store (Win8/WinRT) type application.

    Thursday, October 18, 2012 6:52 PM