Access limitations and switching to SQL database from Access database

Good Afternoon,

I created a bank cheque application in VB.NET about a year ago for staff to easily input information in to fields and when they hit a button, it prints the cheque and saves the information they inputted in to a database (Microsoft Access 2013). Before this they were typing up the cheques on an old fashion type writer. The application has been doing what it's suppose to do for about a year now.

However Just yesterday one of the applications selections for a specific unit stopped working, while the cheque is still being printed. The cheque information that the user inputted, is not being saved to the designated table in the access database.

Below is the code that is used to save the information the user inputted in to the database.

Private Sub SaveHistory_Record()

        Dim conn As New OleDbConnection
        Dim cmd As New OleDbCommand
        Dim sSQL As String = String.Empty

            'get connection string declared in the modFunctions.vb and assing it to conn variable
            conn = New OleDbConnection(sConnString)
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text

            'Using textbox tag property to identify if the data is new or existing.
            If Me.txtMUnit.Text = "ABC" Then

                sSQL = "INSERT INTO tblABCHist ( [ChqNum], [ChqDate], [Amount], [PayTo], [Reference], [DebitAcc], [CreditAcc], [Currency], [PayeeBank], [Unit])"
                sSQL = sSQL & "  VALUES(@ChqNum, @ChqDate, @Amount, @PayTo, @Reference, @DebitAcc, @CreditAcc, @Currency, @PayeeBank, @Unit)"
                cmd.CommandText = sSQL

            ElseIf Me.txtMUnit.Text = "DEF" Then

                sSQL = "INSERT INTO tblDEFHist ( [ChqNum], [ChqDate], [Amount], [PayTo], [Reference], [DebitAcc], [CreditAcc], [Currency], [PayeeBank], [Unit])"
                sSQL = sSQL & "  VALUES(@ChqNum, @ChqDate, @Amount, @PayTo, @Reference, @DebitAcc, @CreditAcc, @Currency, @PayeeBank, @Unit)"
                cmd.CommandText = sSQL


                MsgBox("Unit not defined in code (frmMain: Line 190)." & vbNewLine & "Contact administrator for assistance.", MsgBoxStyle.Critical, "ERROR")

            End If

            'set paramaters
            cmd.Parameters.Add("@ChqNum", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMChqNum.Text)) > 0, Me.txtMChqNum.Text, DBNull.Value)
            cmd.Parameters.Add("@ChqDate", OleDbType.VarChar).Value = IIf(Len(Trim(Me.DateTimePickerM.Text)) > 0, Me.DateTimePickerM.Text, DBNull.Value)
            cmd.Parameters.Add("@Amount", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMAmt.Text)) > 0, Me.txtMAmt.Text, DBNull.Value)
            cmd.Parameters.Add("@PayTo", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMPayTo.Text)) > 0, Me.txtMPayTo.Text, DBNull.Value)
            cmd.Parameters.Add("@Reference", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMRef.Text)) > 0, Me.txtMRef.Text, DBNull.Value)
            cmd.Parameters.Add("@DebitAcc", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMDbtAccNum.Text)) > 0, Me.txtMDbtAccNum.Text, DBNull.Value)
            cmd.Parameters.Add("@CreditAcc", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMCrdAccNum.Text)) > 0, Me.txtMCrdAccNum.Text, DBNull.Value)
            cmd.Parameters.Add("@Currency", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMCurr.Text)) > 0, Me.txtMCurr.Text, DBNull.Value)
            cmd.Parameters.Add("@PayeeBank", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMBnkPayee.Text)) > 0, Me.txtMBnkPayee.Text, DBNull.Value)
            cmd.Parameters.Add("@Unit", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtMUnit.Text)) > 0, Me.txtMUnit.Text, DBNull.Value)

            'MsgBox("Data has been saved.")

        Catch ex As Exception




        End Try

    End Sub

Open in new window

When my application gets to the above code to save the information in to the database, the user is presented with a message box that contains an "Overflow" exception. When they click OK to this box, the cheque prints but as said before the information is not saved in the databases table.

overflow exception
Within the Access database I have 3 tables, 2 for the history (Unit ABC and Unit DEF) and one for something else. When I compare the tables, 2 contain about 200 records. The third table contains  2275 records. Which is the table being affected for the issue I am explaining here.

What I have done as a test, Is i have retrieved the backup of this database from 3 days ago, and pointed my cheque application to it. I've then added a few entries (5 entries) and it works, no problems whatsoever saving information to this table. But if i continue to add 2 more entries, on the second additional, I get the Overflow exception again.

So this is telling me that there must be some sort of limitation on the records I can have in an Access database.

I have tried googling to see what the limitations are with Access 2013 but only come up with the maximum size of the database being 2Gb. My database is far from that size as its only 932k, so I am not sure what is going on here. But the problem is NOT with my code but with the Access database itself. Database size is not an issue as you can see. I also have some code in my application, that upon closing the application I have it doing a compact and repair on the database.

So I need some assistance.

Firstly, if possible, I need to some sort of code that would bypass this overflow exception for now and save the information the user inputted in to the problem table. Like i said, if possible.

Secondly, reason for the above is because I am considering switching to a SQL database instead of Microsoft Access for this applications database, so that there are less limitations. I basically need staff to be able to continue using the application until I figure it out. Now my programming skills are very very rusty and pretty much beginner. Took me quite a while just to make this application as simple as it is.

So the second thing I am asking for here is if someone can tell me what code would to switch the application to look at SQL instead of access. I'm sure parameters and connection strings all in my code would need changing but to what?

Please note that I don't want to use that database connection wizard thing in Visual Studio, that helps you set the connection up. I want the connection to be all code based and not wizard based.

I have already created my SQL database and imported the data from the Access Database in to it. Its just the code now I would like assistance in.

Kind Regards,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Access tables can hold millions of rows so you aren't even close.  The constraint is simply the maximum database size of 2g.  Each data type though is specifically limited to what it can contain. The problem is most likely with a particular data field.  examine all the datatypes and data values to see where there might be a disconnect.  The most likely culprit is that you have used Integer as the data type for the check number and it needs to be long integer.  Integer holds numbers smaller than ~ 32K and given that you probably started with some check number larger than 1, you could have reached the limit.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kyle AbrahamsSenior .Net DeveloperCommented:
Most data types should be long over int in my experience with access.

Confirmed that this is most likely a counter problem and not a size limitation in access.
What I forgot to mention and what you probably don't know is that you are not actually using Access at all.  In fact, Access does not even need to be loaded on the computers that run this application.  You are using ACE.  ACE is the database engine that you are working with.  Everyone calls it Access but that is because they don't actually know any better.  Access is the RAD tool that is used to create application interfaces.  It can Link to tables in ANY ODBC compliant database.  Access is dependent on Jet (.mdb) or ACE(.accdb) ONLY to hold its own objects.  So Access uses Jet/ACE to store forms/reports/code.  But data can be anywhere.  Jet and ACE are completely independent of Access.  Anything you need to do to manage a Jet/ACE database can be done with ADO or with DDL.  The only time Access, the RAD tool comes into play is if you want a GUI to manipulate the database.  So think of Access as SSMS for Jet/ACE.

Access frequently gets compared to SQL Server.  That is a nonsensical comparison similar to comparing an apple to a horse.  Horses eat apples but that is as far as it goes.  Access is a RAD tool that creates data-centric applications.  SQL Server CANNOT do that.  SQL Server is a database engine.  Access CANNOT do that.  So Access and SQL are NOT competitors as they are always presented.  They are actually complementary tools.  Access works very well with SQL Server and with an SQL Server or other RDBMS BE becomes infinitely scalable no matter what the bad press says.  If you read between the lines, ALL the bad press directed at Access is actually directed at Jet/ACE.  Access is an excellent development tool that is much maligned due to ignorance and poor marketing by Microsoft.  The limitations of Access as a development tool are two.
1. It does not support multiple developers.  You can do it but it is extremely difficult to have multiple people working at the same time.
2. Distribution is awkward

Beyond that, if you have a client-side application to develop, nothing will be easier or faster to use than Access.  I have clients who run their entire businesses using various Access applications - usually linked to SQL Server or Oracle or DB2.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Firstly, if possible, I need to some sort of code that would bypass this overflow exception for now and save the information the user inputted in to the problem table. Like i said, if possible. >>

  Not possible really.   You can add an error handler, but your not going to be able to save the data.    Overflow is just that; the value won't fit in the field size your using.

 An integer for example can only hold a number up to 32767.

 Your problem is not the database, but the programming.

Kyle AbrahamsSenior .Net DeveloperCommented:
An integer for example can only hold a number up to 32767.

That's true for Access / ACE.  

In .Net it's 2147483647

Which is why I would say the issue is the database.
AndyAinscowFreelance programmer / ConsultantCommented:
A couple of points.
Have you actually tested that the failure is in that function - or just assumed it is.
I would also try a compact/repair on the database where the data is stored.  As others have said you should not have run into a limit imposed by access itself with the number of records.
KevinAuthor Commented:
@PatHartman - Thank you very much Pat, that was exactly the problem. I was using 'Integer" on a few of the columns in the table. Once I changed to 'long Integer' it started working again. I'm gonna go through everything again and make changes where needed. I also appreciate the lengthy information. I had no idea about this, and am happy you mentioned it. I'll just leave everything as is using ACE :)
You're welcome.  At least one more person now knows what Access Is and what it isn't.  This is a hard torch to carry and Microsoft does not help.  The problem goes back to Access version 2.0 in the early 90's when MS decided to ship Jet in the Access box so that users didn't have to install it separately if it wasn't already installed.  Early versions of Windows and Office programs used Jet to manage data so Windows installed Jet into the 2000's.  I don't remember when it stopped or even if it did but the Access team took Jet in around 2005 and converted it to ACE for release with A2007.  Now the Access team maintains ACE but I think the SQL Server team still has Jet.

Don't ever believe anything you read that compares "Access" to SQL Server.It was written by someone who has no clue what Access is.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.