FredixBlog

Usually we would just need to refresh the existing linked tables, but in some cases, it is necessary to create a linked table from scratch.
This is useful when linking a text file or Excel sheet which name changes over the time.

public sub ReconnectTable(sTable, sConnect)
Dim db as Database
Dim td As TableDef

on error goto Err_Reconnect
   Set db = currentdb()

on error resume next
' Delete the table
   db.TableDefs.Delete sTable
on error goto Err_Reconnect

' Create the table def.
   Set td = db.CreateTableDef(sTable)
' Connect the table
   td.Connect = ";DATABASE=" & sConnect & ";"
' Set the source table name
   td.SourceTableName = sTable
' Add the table def to the tabledefs collection
   db.TableDefs.Append td
' refresh the link
   td.RefreshLink

db.close

exit sub

Err_Reconnect:
   msgbox "Error while reconnecting table " & sTable & ": " & error$

end sub

• Gibson plus solide que Fender?
• Get Jet Connection String
• Display a counter and remaining time in a loop
• Truncate long path names
• How can I e-mail a file from Microsoft Access?
• Running total on a form
• Clear all line breaks
• Coordinates of the third point
• Message to log file
• Copy a file

It is sometimes needed to show the running total of the records. On a report, there is no problem as this feature in built-in. But on a form, a bit of coding is needed.
Here is a function that will work on any form. The most of it is that you can sort or filter the records, the running total still works because it works with the form’s recordsetclone.
Parameters of the function:
[Amountfield] is the field name we want to totalize
[Keyfield] is the field name of the primary key of your form’s table or query, or simply an unique field.

Public Function RunTotal(AmountField, KeyField)
Dim SumRs As Recordset
Dim frm As Form
Dim RunningValue As Double
Dim IsCancelled As Boolean

Set frm = Forms(AmountField.Parent.Name)
Set SumRs = frm.RecordsetClone

With SumRs
    ' Setting first amount
    RunningValue = 0
    On Error Resume Next
    .FindFirst "True"
   
    ' Summing all other
    While .Fields(KeyField.Name) <> frm.Controls(KeyField.Name) And Not IsCancelled
        RunningValue = RunningValue + .Fields(AmountField.Name)
        On Error Resume Next
        .FindNext "True"
        If Err <> 0 Then IsCancelled = True
        On Error GoTo 0
    Wend
       
    ' Do the current one
    RunningValue = RunningValue + .Fields(AmountField.Name)
End With

Set SumRs = Nothing
RunTotal = RunningValue
End Function

• AddReference()
• How can I e-mail a file from Microsoft Access?
• Create a linked table with VBA
• Dirty Flag for unbound form
• Custom Autonumber Access 2000
• Create ODBC DSN

This function creates a reference to the specific library.

Function AddReference(chNomFichier As String) As Boolean
Dim ref As Reference

     On Error GoTo Erreur_AddReference
     Set ref = References.AddFromFile(chNomFichier)
     AddReference = True

    Sortie_AddReference:
     Exit Function

    Erreur_AddReference:
     MsgBox Err & ": " & Err.Description
     AddReference = False
     Resume Sortie_AddReference
End Function

• Running total on a form
• Replicate a database
• Répartir les tâches du crontab unix
• Create a linked table with VBA
• Clear all line breaks
• Relink all tables
• Copy a file