Unreliable Access Database while using Excel VBA

[smartpat19]

Hi,

I have built a macro that feeds data into an access table from excel. This saves our team lots of time and copy and paste errors. However, the access database has been unreliable and keeps breaking. Do I need to change the way I connect to the access table to improve reliability? Six users will be connecting with the table about 20 times each in one day. See below for the VBA code.

              Dim db As database
                Dim rs As DAO.Recordset
                
                Set db = DAO.OpenDatabase("R:\Development Team Database\Development Team.accdb")
                
                'cost
                Set rs = db.OpenRecordset("Monthly Project Cash Flow", dbOpenTable)
                
                Dim thisrow As Long
                
                lastrow = b.Range("M65536").End(xlUp).Row
                
                For thisrow = 2 To lastrow
                
                rs.AddNew
                rs.Fields("Report_Name") = b.Range("U" & thisrow).Value
                rs.Fields("Project_Number") = b.Range("V" & thisrow).Value
                rs.Fields("Cash_Flow_Month") = b.Range("M" & thisrow).Value
                rs.Fields("Cost 1") = b.Range("N" & thisrow).Value
                rs.Fields("Cost 2") = b.Range("O" & thisrow).Value
                rs.Fields("Cost 3") = b.Range("P" & thisrow).Value
                rs.Fields("Cost 4") = b.Range("Q" & thisrow).Value
                rs.Fields("Cost 5") = b.Range("R" & thisrow).Value
                rs.Fields("Cost 6") = b.Range("T" & thisrow).Value
                rs.Fields("Cost 7") = b.Range("S" & thisrow).Value
                
                rs.Update
                
                Next thisrow
                
                rs.Close
                db.Close

 

[JonXL]

Any reason you're doing this from Excel instead of Access?

And when it breaks, what exactly gets broken?

 

[smartpat19]

Any reason you're doing this from Excel instead of Access?

And when it breaks, what exactly gets broken?

Yes, Its from excel because each user is submitting the information for reporting and the macro verifys via message boxs that the information is correct that is being submitted to the access database. Unless you think there is a better option.

I have attached the error that excel shows. When you open access it immediately goes into repairing the database.

 

[JonXL]

Yes, Its from excel because each user is submitting the information for reporting and the macro verifys via message boxs that the information is correct that is being submitted to the access database. Unless you think there is a better option.

Build the input form in Access. This is exactly the kind of thing Access is made for.

It would likely also resolve this error.

 

[xenou]

The typical advice for anything access-related is to split your front-end and back-end. Not sure how many rows you are inserting generally (a handful, a few thousand, a few million?)

 

[smartpat19]

The typical advice for anything access-related is to split your front-end and back-end. Not sure how many rows you are inserting generally (a handful, a few thousand, a few million?)

I have no reports setup in access. Adding data to the tables in access which a power BI report reads off of. The access database becomes unstable when adding data using the macro.

Each macro run will add a few hundred rows.

 

[smartpat19]

Build the input form in Access. This is exactly the kind of thing Access is made for.

It would likely also resolve this error.

This would require all the users to learn Access correct? is it easy to add a few hundred rows of data? The excel macro allows for users to have an easy submit without having to leave their worksheets.

 

[Joe4]

This would require all the users to learn Access correct?

Nope, not at all. A well-designed Access database is Form driven, and you typically only give users access to the Forms (much like Excel forms, but actually a little easier to work with, as you can bind the form variable to the field in the table/query, whereas in Excel, you actually have to write VBA code to write values from the Form to the spreadsheet).

So to users, it would just look like any other Entry Forms. They just populate it and hit submit.

I created many of these for some very non-technical people years back. Truth be told, most of them actually had no idea that they were using Microsoft Access. They just use it like any other simple entry form.

 

[smartpat19]

Thank you for the response. I understand what you are saying. The current setup merges three tables of data based on dates and then submits to access and this piece saves a lot of time for the analysts. Is there a solution to make the access table more stable without using an access form?

 

[xenou]

I wouldn't say I see anything unusual in your access setup. So first, split the front-end and back-end if that is not already done (this is the #1 best thing that can be done to protect against corruption). It isn't clear if your code is causing corruption (seems unlikely) or merely getting the error due to the db being corrupted (by something else). If anyone is connecting remotely that is another common cause (access is not a server based db and shouldn't be used for remote connections).

Going row by row is not ideal in my opinion but should work. This is probably the slowest option and would lock the table the longest. Anytime you are connecting Excel to Access there is always the ugly possibility of mismatched or invalid data (excel is free form text and Access is not and requires specific datatypes for every field). I would suggest all data be validated for correctness before attempting to update the database.

Might be a good idea to wrap some error catching around this so you can be sure you close the recordset properly if there are any runtime errors.