Visual Studio 2022 Crashes when Adding SqlDataSource using any MS Access Database in ASP.NET C# Web Project

Question

When adding a "SqlDataSource" using any version of MS Access database the app simply crashes when you "Test Connection". This happens in the designer when using the wizard and creating a "New Connection". The data source is "Microsoft Access Database File (OLE DB)" by default.

Once the database name is selected eg *.ACCDB and you click "Test Connection" it simply restarts VS2022 every time. The provider applied in 'Advanced' is "Microsoft.ACE.OLEDB.12.0". No security is applied or required as it is on the machine and run locally. I have seen this issue posted for every version of VS for the past 12 years or more and it has never been resolved for the wizard at least. If you avoid clicking 'Test Connection' and just click OK it gives you the "Multiple-step OLE DB operation generated errors...No work was done." message.

If you then go down the ODBC rabbit hole using DSN's etc you end up with new incompatibility issues with the supposed '32/64' bit drivers. The best advice you can find on that issue is to uninstall all access drivers and then revert back to the 2010 drivers which most likely require a 'quiet' install to have any chance of install success. This creates another issue since some apps need the 64x access drivers for some need 32x. Even though access is a bit ancient clients 'get' access and can use the files whereas most clients don't 'get' SQL. Hence the reason I am trying to use the access datasource.

Answer 1

I have some web projects, and they work ok, and some don’t.

I not been able to determine the exact reason why.

Edit: However, Following below, in the vast cases, VS does work fine and does not crash.

However, do note that around 2017, they changed the default for visual studio, and when you choose “any” CPU, it now runs as x64 bits by default (and it USED to run as x32 bits).

Keep in mind that if your force your project to x64 bits, then you MUST install access x64 bits for the database operations to work. However, the test connection will ALWAYS fail in this case. (because Visual Studio is STILL a x32 bit program).

If you using access x32 data engine, then force your project to run as x32 bits, and test connection will work. (in fact, it should work regardless).

So, FOR SURE test connection is out of the question if you installed access x64, and you can ONLY test/run the applcation for a valid connection, the test connection will not work, and will always report false.

In Summary:

You can’t use any CPU anymore. You have to force this issue. In the past – any cpu would result in a x32 running instance of your web app. (but, these days, most web servers are x64 bits).

So, if using access x32, the force (set) your project to x86. This will at least allow it to run. And test connection should work.

If using access x64, then force (set) your project to x64. This again will at least allow it to run. And test connection WILL NOT work – it can’t, since VS is x32 bits.

It also gets even worse.

Around access 2013-2016, if you installed the access runtime or access (and NOT the ACE data engine), then the ACE data engine was NOT exposed to 3rd party use – including VS.

They have recent reversed this decision, and now installing access runtime DOES expose the ACE data engine. (but, to be sure, with the last few version s of access, I would specific download + install the ACE data engine, and NOT the access runtime). this one:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Also, you want to check are you using the ACE data engine or the JET data engine?

JET – older legacy, only mdb files, and ONLY x32 bit version exists.

ACE – newer version, can read “mdb” files, and comes in x32 and x64 bit flavors.

And are you using x32, or x64 version???

So, when you attempt to build the connection (say using the connection builder – settings), then make sure you select the correct version (ACE or JET). And this is a REALLY good check, since if your missing the particular engine, you not see it as a choice) (this holds true in regards to x32 bit versions of both engines).

So, when you setup the connection, you REALLY need to check this setting:

So, lets build a new connection:

So, we select Access database in above.

Then we back to this:

YOU NEED to click on advanced, since that THEN lets you select JET or ACE.

So, we now see this:

BUZZER!!!! - WRONG!!!

I don't want to use JET, I am using a accdb file - that can't be opened by JET, so we change that provider to ACE.

this:

So, note how I choose ACE.

Now, back on previous panel, I am able to use browse.

And I am ALSO able to use test connection (since I have access x32 installed).

AGAIN: if you using x64 bits, you can NOT and NEVER use test connection, since VS is a x32 bit application. However, when you hit f5 to run the applcation, then such connections will work, ASSUMING you forced the project to run as x64 bits.

since I am using access x32, then of course I now must set the project to this:

Now, above should allow you to setup a valid connection.

but, I do find that some wizards in some projects STILL will cause a barf of VS, and it will shut down. But, lets try the wizard for a gridview - see if it works.

and then this:

Hey, we still going!!! - so we get this then:

(if we get to above - then VS did not crash).

So, we keep going:

Ok, so now the wizard created the grid for me. I remove the "ID" column, add in a image control, and I have this:

        <asp:GridView ID="GridView1" 
            runat="server" AutoGenerateColumns="False" 
            DataKeyNames="ID" DataSourceID="SqlDataSource1"
            CssClass="table" >
            <Columns>
                <asp:BoundField DataField="Fighter" HeaderText="Fighter"  />
                <asp:BoundField DataField="Engine" HeaderText="Engine"    />
                <asp:BoundField DataField="Thrust" HeaderText="Thrust"    />
                <asp:BoundField DataField="Description" HeaderText="Description"  />
                <asp:BoundField DataField="FirstFlight" HeaderText="FirstFlight"  />
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:ImageButton ID="btnImage" 
                            runat="server"
                            ImageUrl = '<%# Eval("ImagePath") %>' />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>

But, the system generated the above gridview - I in fact spent time deleting extra junk.

And when I run this, I now see this:

Hey, NOT bad for a lowly little access developer like me. I have NOT yet written ONE line of code. So, right out of mom's basement type solution here!

So, keep the above rules in mind, and make sure you attempt to set the provider (ACE, or JET) and also make sure you force the bit size of hte project.

And if you using Access x64, then you can't use test conneciton - it will ALWAYS fail, since as noted, VS is a x32 bit program.

But, also keep in mind that recent change. While VS is x32 bits, it is able to run + even debug as x64 applications.

So, the wizards should work, but I doubt the above wizard can get to the point of displaying the table, and selecting the table WHEN you using x64 bit access.

You CAN build the connection as per above, but the wizards to fill out the database and say produce the above grid? That will only work with x32 bit access, since as noted, VS is a x32 bit program.

So, the wizards to generate say the gridview? I doubt that will and can work if you trying to use access x64 bits.

In that case, you CAN still build and setup the connection string in the settings as I did per above (but, don't use test connection - it can't work for access x64).

So, in above, I would have to either have both x32 and access x64 installed. (topic for another post). But, then I would dump the sql datasource in the page (and I don't use those anyway).

So, I would remove the datasource on the page, and write the code to load the grid like this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadGrid()
    End If

End Sub

Sub LoadGrid()
    Using conn As New OleDbConnection(My.Settings.Access4444)
        Using cmdSQL As New OleDbCommand("SELECT * FROM Fighters", conn)
            conn.Open()
            Dim rstTable As New DataTable
            rstTable.Load(cmdSQL.ExecuteReader)
            GridView1.DataSource = rstTable
            GridView1.DataBind()
        End Using
    End Using
End Sub

And, to be fair, you did tag this as c#, so the above code would thus be:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadGrid();
    }

    void LoadGrid()
    {
        using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.Test4444))
        {
            using (OleDbCommand cmdSQL = new OleDbCommand("SELECT * FROM Fighters", conn))
            {
                conn.Open();
                DataTable rstData = new DataTable();
                rstData.Load(cmdSQL.ExecuteReader());
                GridView1.DataSource = rstData;
                GridView1.DataBind();
            }
        }
    }

ALSO, remember during development, you ALSO have to set hte bit size of the web server you are going to use during development. that setting is found here: Project properties->web