Function RelinkTables() Dim td As TableDef, db As Database Dim strFolder As String, arTmp As Variant, strBe As String Set db = CurrentDb strFolder = Left(db.Name, InStrRev(db.Name, "\", , vbTextCompare)) For Each td In db.TableDefs If Left(td.Connect, 9) = ";DATABASE" Then 'linked ? If Not fValidTable(td.Name) Then 'bad ? arTmp = Split(td.Connect, "\") td.Connect = ";DATABASE=" & strFolder & arTmp(UBound(arTmp)) td.RefreshLink End If End If Next td End Function Function fValidTable(TableName As String) As Boolean Dim lCount As Long On Error Resume Next lCount = DFirst("1", TableName) fValidTable = (Err.Number = 0) End Function
Pour remplir une ListBox MS Access avec des données provenant d'un recordset, il suffit généralement de définir une rowSourceType = Table et de donner le nom de la table ou de la requête. Mais dans certains cas, il peut être préférable de fournir ces valeurs sous forme d'une ValueList. C'est le travail de la fonction ci-dessous. Sub FillList(ByRef lstBox As ListBox, strQry As String) 'fill combo with value list from recordset 'to be used when recordset is too slow to requery for sorting Dim rs As Recordset, strSrc As String, i As Integer Set rs = CurrentDb.OpenRecordset(strQry, dbOpenSnapshot) lstBox.RowSourceType = "Value list" lstBox.ColumnCount = rs.Fields.Count ReDim arSrc(rs.RecordCount * rs.Fields.Count) Do Until rs.EOF For i = 0 To rs.Fields.Count - 1 strSrc = strSrc & rs.Fields(i) & ";" Next i rs.MoveNext Loop lstBox.RowSource = Left(strSrc, Len(strSrc) - 1) End Sub
Commentaires
Enregistrer un commentaire