vb.net using access 2013

Question

I have a vb.net 2010 desktop application that updates an Access 2013 database. After a point, the database was not updating.
I then ran the 'repair and compact' option once and that did not solve the issue. I ran the 'repair and compact' about 3 to 5 times.
After 3 to 5 times, the Access 2013 database was updated when connecting to the vb.net 2010 desktop application.

My concern is that I had to run the 'repair and compact' so many times. I am concerned that there will be more issues with the Access 2013 database in the future.

Thus do I have a valid concern? If so, can you tell me how I can do to solve the problem so that it does not occur again?

If there is nothing to be concerned about, is running the 'compact and repair' that many times ok?

Answer 1

Hi midnight_car,

>>After a point, the database was not updating.

What do you mean updating an Access database? What's code you are using to update the database? What's the detail performance that the database was not updated?

>>I then ran the 'repair and compact' option once and that did not solve the issue.

How do you repair and compact the database? Do you open the database and run the option in File->Info or you use any code to repair and compact the database? If you use code, what's the code?

Best Regards,

Terry

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

We would need to see your "update" code. There is no reason the database would not be updated (without an error). That is, unless there is an issue with your code or you are hitting the maximum database size for Access (2GB).

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 3

I believe I reached the maximum size of the database since I did a 'repair and compact' on each unique users database and that solved the problem.

Is there a way to keep track of when the size of the database is getting too large? If so, can you tell me what I can do keep track of the size of the access database? 

Is there some kind of a process that I can use to keep track of the size of the database and/or a process that can do the repair and compact at a scheduled time?

Answer 4

Hi midnight_car,

You could use FileLen method to get the size of the database file and then do want you want according to the file size.

Here is the example.

Dim filepath As String = "C:\Users\v-guaxu\Documents\Database1.accdb"
        Dim filesize As Long
        filesize = FileLen(filepath)
        Dim limitsize As Long
        limitsize = CLng(2) * 1024 * 1024 * 1024
        If filesize > limitsize Then
            'do the operation you want
        End If

Best Regards,

Terry

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

If you are exceeding the maximum database size for an Access database you may want to consider using an alternative database, such as SQLite.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 6

Are there any additional ways to see the size of the database besides writing the code in the vb.net app? Can I use some sql directly in the access databases? If so, what sql could I use?

Answer 7

Hi midnight_car,

As far as I know, there is no such built-in SQL which could do this. Why do you need get the size using SQL?

You could try to refer to Hans's workaround in below thread.

#SQL statement to find size of MS access database 

You could create the function for getting size in a standard module.

Function GetFileSize() As Long
    GetFileSize = FileLen(CurrentDb.Name)
End Function

And then you could use SQL to call the function to get the file size.

SELECT GetFileSize() AS FileSize

Best Regards,

Terry

---

MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:[e-mail]">[e-mail]</a>.

Answer 8

I want to run the repair and compact function that Access 2016 has if the need arises. I had to complete this task 2 weeks ago on my user's access databases since the updated data was not being saved to the database.

Answer 9

Hello,

You could try to use Application.CompactRepair to compact and repair the access. Please note that the access database should not be current database or opened by any other use since this method will open the file exclusively.

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].