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
 

smartpat19

Board Regular
Joined
Sep 3, 2014
Messages
112
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.
Thank you for the note and information. The Access db is on a network server but with everyone working remotely we are using a VPN to connect to the network. (sounds like this might be a part of the issue).

I do have some validation in my VBA but I can for sure add more.

Is there a method to upload quicker? I think this would be a big benefit since everyone reports on the same day and having the table locked the shortest amount of time would be ideal.

Thank you again.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
I don't know exactly how I would go about this. I might load the data into a temp file (even a csv or text file). then upload it from the text file (all at once). Or I might put all the Excel data into an array and then see what I can do with the array (my memory fails me but I think it might be possible to load a recordset from an array - even if you can't, you can still do all the validation and everything else on the array first, then when it comes time to load the data you are working with an in-memory object and can do it most quickly that way).

Really I don't think you should be using Access over a VPN connection. Wrong tool for the job. You need a regular server-based database for this as any error or failure in the connection will likely corrupt your access database.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,018
Members
416,892
Latest member
Bensch

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