Unreliable Access Database while using Excel VBA

smartpat19

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

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

JonXL

Active Member
Joined
Feb 5, 2018
Messages
469
Office Version
  1. 365
  2. 2016
Platform
  1. 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
112
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
469
Office Version
  1. 365
  2. 2016
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
112
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
112

ADVERTISEMENT

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
57,664
Office Version
  1. 365
Platform
  1. 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
112
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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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,133,427
Messages
5,658,733
Members
418,467
Latest member
sc356448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top