CODEWITHSUNDEEP
sql-servervbams-access
JennyW
JennyW

Reputation: 106

Is there a way to replace all local tables with an import process?

I need to give remote users working on sites with no network connection access to the data from our sql server. I want to update local tables in their fe but I need to be able to do this in code, so they can trigger the update process each time they return to the office.

I have a group of engineers working on building sites with absolutely no network access. I have created a stripped down version of the fe for them that contains only the forms, tables and reports they need to work on site, complete their reports, and then return to the office and 'upload' their completed reports. I believe from what I've read so far that the best way to do this is create a local copy of the tables in their fe, this will give them the data they need, and I can use an append query to upload the completed report information when they return to the office. I have created the local tables by using the import process to import the tables from our sql server, and I saved that import. To refresh them I am deleting the tables, then running the saved import.

I've researched and found lots of code for doing this update of a local table where it is just one table, but I want to do it with all the tables and I keep hitting an error when trying to delete the tables before reimporting them.

my code is still a work in progress on test, so a lot is commented out as I work through each step to get it working as I want. 

Private Sub cmdSynch_Click()
'check if there is data to upload to Compass **needs updating**
'If IsNull(DCount("IDfield", "table")) Then

    'there's no update data, pull all data from Compass
    Call CopyDB

    're-open the main menu
    DoCmd.OpenForm "Main Menu"

'Else
    'upload the the data from here to Compass first, then refresh
    'DoCmd.RunSQL "qryNametbc"  ' will be an append query
    'if there were no errors. clear the updates table
    'Dim strSQLDelete As String

    'strSQLDelete = "DELETE * FROM dbo_table_name;"  ' needs updating ***
    'DoCmd.SetWarnings False
    'DoCmd.RunSQL strSQLDelete
    'DoCmd.SetWarnings True

    'then pull all the data from Compass
    'Call CopyDB

'End If

End Sub

CopyDB simply calls the functions CloseFormReports and DeleteTables, which are both held in a module, not on the form vba. 

Public Function CloseFormsReports()
'Close all open forms
On Error GoTo errHandler
Do While Forms.Count > 0
DoCmd.Close acForm, Forms(0).Name
Loop
Do While Reports.Count > 0
DoCmd.Close acReport, Reports(0).Name
Loop
Exit Function
errHandler:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Function

Public Function DeleteTables()
  Dim dbs As DAO.Database
  Dim i As Integer
  Set dbs = CurrentDb
  DoEvents
  ' Loop backwards through relations
  For i = dbs.Relations.Count - 1 To 0 Step -1
    dbs.Relations.Delete dbs.Relations(i).Name
  Next i
  ' Loop backwards through tabledefs
  For i = dbs.TableDefs.Count - 1 To 0 Step -1
    If Left(dbs.TableDefs(i).Name, 4) <> "MSys" Then
      dbs.TableDefs.Delete dbs.TableDefs(i).Name
    End If
  Next i
  Set dbs = Nothing
End Function

I get "runtime error 3211: the database engine could not lock table 'xxxx' because it is already in use by another person or process." I have added in a step to try and resolve this by closing all open forms before deleting the tables, but I am still getting "runtime error 3211" for a table that is the record source of a subform on the navigation form (Main Form). I think this might be because Main Form is where the update process is triggered by clicking on a command button (cmdSynch). Main Form successfully closes but i'm guessing is still 'active' because the vba code behind the form is still being processed. Is there a way to resolve this?

Upvotes: 0

Views: 77

Answers (1)

JennyW
JennyW

Reputation: 106

As best as I can tell, there is no way to delete all tables and reimport them from a user action on a form, as the action itself blocks the deletion process.
I have worked around this by moving the synchronisation process to happen before login, which is actually slicker from the user perspective and also automates it so there is no issue with engineers remembering to run the update process. It's a better solution than my original plan.

Upvotes: 1

Related Questions