Commit SQL Temp storage

[pwrgek]

With help from

http://www.referee-assistant.com/calcontrol.htm.....

I have created a calender for my database. The problem is the amount of time it takes to update after each input. My understanding is that you can use a stament that will store the information in a table localy, IE on the users machine, and then update the main table located on another machine when for example they exit the database. Any help links examples that you may have would be great. I have never set one up to do this but work well with examples.

All users use the same front-end and all data is stored in a seprate database Back-end. There are about 54 useres. This is on a local server not webbase and is in Access 2000. I hope I answered all of the questions

As always this is a great group and I thank you for all of the help.

 

[SiJP]

So, at run time you need to:

1) Create a temporary table & Populate this with data

Chuck this code into the bit where you update your calandar.. to store it locally..

    On Error Resume Next
    
    'variables..
    Dim tdf As TableDef
    Dim rs As DAO.Recordset
    
    'create the temp table
    Set tdf = CurrentDb.CreateTableDef("tmpTable")
    
    With tdf
    .Fields.Append .CreateField("fldName1", dbText)
    .Fields.Append .CreateField("fldName2", dbText)
    .Fields.Append .CreateField("fldName3", dbText)
    CurrentDb.TableDefs.Append tdf
    End With
    
    'create a recordset for the table
    Set rs = CurrentDb.OpenRecordset("tmpTable", dbOpenDynaset)
    
    'populate the tmp table with your data
    With rs
    .AddNew
    rs!fieldName1 = strYourVariable1
    rs!fieldName2 = strYourVariable2
    rs!fieldName3 = strYourVariable3
    .Update
    End With

2) Catch the form closing & transfer the detail of the temp table to another table, then delete the temp table.

Private Sub Form_Unload(Cancel As Integer)

 'upload info into
 DoCmd.RunSQL ("SELECT * INTO dbo_My_SQL_Linked_Table FROM tmpTable")

 'now delete the table.
 Call fDeleteTable

End Sub

Public Function fDeleteTable()
Dim tdfNew As TableDef

On Error Resume Next

With CurrentDb
    For Each tdfNew In .TableDefs
        If tdfNew.Name = "tmpTable" Then
        DoCmd.DeleteObject acTable, tdfNew.Name
        End If
    Next tdfNew
End With

End Function

G-d I should get paid for this.. Oh wait.. I do

You'll need to put in appropriate error handing and test this code (written pretty much on the fly, bar a few cribs from existing functionailty in DB's and some rippage from MS help )

HTH's

------------------------
Hit any User to continue