Unreliable Access Database while using Excel VBA

smartpat19

Board Regular
Joined
Sep 3, 2014
Messages
102
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.

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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

JonXL

Active Member
Joined
Feb 5, 2018
Messages
344
Office Version
365, 2016
Platform
Windows
Any reason you're doing this from Excel instead of Access?

And when it breaks, what exactly gets broken?
 

smartpat19

Board Regular
Joined
Sep 3, 2014
Messages
102
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.
Error From Excel.png
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
344
Office Version
365, 2016
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,675
Office Version
2013
Platform
Windows
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

Board Regular
Joined
Sep 3, 2014
Messages
102
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

Board Regular
Joined
Sep 3, 2014
Messages
102
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,666
Office Version
365
Platform
Windows
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

Board Regular
Joined
Sep 3, 2014
Messages
102
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,675
Office Version
2013
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,588
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top