0

I have an Access Database which I populate using a pass through query.
I get specific data from an SQL database and dumps it on Access.
This Access DB is updated weekly but I cannot simply append new data from the previous week because all other past weeks also updates.

What I did is truncate the Access DB and then load everything again.
When I do that, I need to Compact and Repair the DB so that the size doesn't bloat.

My question is, is it ok to do that? Currently I am using the logic posted in this answer.
I have not encountered any problems yet but I just want to make sure and get our access guru's thought about it. Also I'm planning on doing a scheduled run on our server to do the task.
Just need to make sure that it will not get corrupted easily (what is the chance of corrupting the file in the first place?).

If you'll ask, why do I need to do this? Users of data have no access on SQL server.
So I have to pull data for them so they can just connect to Access DB instead.

Just in case you need the code:

Dim sqlDelete As String
Dim sqlAppend As String

sqlDelete = "DELETE * FROM dbo_Table;"

sqlAppend = "INSERT INTO dbo_Table (Col1,Col2) SELECT Col1,Col2 FROM passThrough;"

With DoCmd
    .SetWarnings False
    .RunSQL sqlDelete

    .RunSQL sqlAppend
    .SetWarnings True
End With

Application.SetOption "Auto Compact", True
L42
  • 19,427
  • 11
  • 44
  • 68
  • I'd have an issue doing something regularly that contains *Repair* in the name, as that has really negative connotations (*why do you need to repair something unless it's broken*). Why don't your users have SELECT capabilities for SQL Server? – Ken White Jul 04 '17 at 04:00
  • @KenWhite `Why don't your users have SELECT capabilities for SQL Server?` I ask that exact same question too. However, I think it has something to do with network traffic and job scope (as directed by company). Server is half way across the globe as well. – L42 Jul 04 '17 at 05:17

2 Answers2

2

If you need to truncate the data and load again I would recommend to move all tables, which should be truncated and all temporary tables to separate database. After separating it will be possible to replace this database with unnecessary data by empty database file when the application starts. Just save a copy of empty database and copy this file over existing database with old data. This should be done before opening any form/recordset based on tables from temp database. It will work faster than C&R each time and more reliable, because sometimes C&R may damage the file.

Template database file also can be stored in main database in Memo field

Sergey S.
  • 6,296
  • 1
  • 14
  • 29
  • I think I am getting your point but still half way processing it. Mind elaborating your answer a bit more? :) – L42 Jul 04 '17 at 05:19
  • That's what I would do. – Gustav Jul 04 '17 at 05:55
  • I edited a little bit the answer, please let me know if you need additional explanations. – Sergey S. Jul 04 '17 at 06:01
  • Oho! That is very simple and neat. I'll go ahead and try it then. – L42 Jul 04 '17 at 06:44
  • I have implemented this, and it works fine. Although your answer does not directly answer my question, I'll accept your post until another answer comes by. – L42 Jul 06 '17 at 03:30
0

I will refer this forum post: http://www.utteraccess.com/forum/index.php?showtopic=1561733

Alfie Jin
  • 9
  • 1