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

 

[smartpat19]

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.

 

[xenou]

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.