OK, my database is split into front and back end, In the back end overnight from various sources I import 10 tables that the front end needs to pull data from.
The code i use to do this works like a dream with no issues..
I seem to be constantly updating VBA code in the back end, but unfortunately i cant do this whilst someone else is in the database, so i was wondering how easy is it to create the module in the front end and then import the updated module when i import the data tables overnight ?
The code i use to do this works like a dream with no issues..
Code:
Function GetLatestData()
Dim Index As Recordset
Dim i As Integer
Dim TableSet As Integer
If CurrentDb.Name = "Backend.mdb" Then
Set Index = CurrentDb.OpenRecordset("SELECT * FROM tblImportTables;")
If Not Index.EOF Then
Index.MoveLast
TableSet = Index.RecordCount
Index.MoveFirst
While Not Index.EOF
i = i + 1
WaitOn i & " of " & TableSet, "Getting Latest Data . . ."
If ObjectExists(Nz(Index("Table_Name"), "")) Then
DoCmd.DeleteObject acTable, Nz(Index("Table_Name"), "")
End If
DoEvents
DoCmd.TransferDatabase acImport, "Microsoft Access", LiveDB, acTable, Nz(Index("Table_Name"), ""), Nz(Index("Table_Name"), ""), False
DoEvents
Index.MoveNext
Wend
End If
Index.Close
Set Index = Nothing
Waitoff
End If
DoEvents
End Function
I seem to be constantly updating VBA code in the back end, but unfortunately i cant do this whilst someone else is in the database, so i was wondering how easy is it to create the module in the front end and then import the updated module when i import the data tables overnight ?