Public Function ADO_Open_Access_File(ByVal bdd As String, Optional ty_file As Byte = 0) As Boolean ' Ouverture d'un projet
Dim trav As ADODB.Recordset ' Zone de travail
Dim Ado_Sql As ADODB.Recordset
Dim i As Integer, j As Integer
Set ADO_File.File = New ADODB.Connection ' Initialisation de la connection
ADO_File.File.Provider = "Microsoft.Jet.Oledb.4.0"
Select Case ty_file
Case 0 ' Fichier access
ADO_File.File.ConnectionString = bdd
Case 1 ' Fichier SQL
ADO_File.File.ConnectionString = "Driver={SQL Server};" & _
"Server=192.168.xxx.xxx;" & _
"Address=192.168.xxx.xxx,1433;" & _
"Network=DBMSSOCN;" & _
"Database=" & bdd & ";" & _
"Uid=sa;" & _
"Pwd=*******"
End Select
ADO_File.File.ConnectionString = bdd
On Error GoTo erreur
ADO_File.File.Open ' Ouverture de la connection, du projet
On Error GoTo 0
Set trav = ADO_File.File.OpenSchema(adSchemaTables) ' Recupération du chémat du projet
trav.MoveFirst ' se placer au debut de la table
i = -1: j = -1
While Not trav.EOF
Select Case trav.Fields(3) ' Controle du type de Module
Case "TABLE", "VIEW" ' Table ou requete
i = i + 1
ReDim Preserve ADO_File.Table_Name(i)
ReDim Preserve ADO_File.Table_Type(i)
ADO_File.Table_Name(i) = trav.Fields(2)
ADO_File.Table_Type(i) = trav.Fields(3)
End Select
trav.MoveNext ' Avance d'un Module
Wend
trav.Close ' Fermeture
Set trav = Nothing ' Vide la mémoire
ReDim ADO_Module(UBound(ADO_File.Table_Name)) ' Redimentionne les modules pour acceder aux champs
For i = 0 To UBound(ADO_File.Table_Name)
Set ADO_Module(i).Module = New ADODB.Recordset ' Initialiser la connection avec la table
If ADO_File.Table_Type(i) = "TABLE" Then
ADO_Module(i).Module.Open "[" & ADO_File.Table_Name(i) & "]", ADO_File.File, adOpenDynamic, adLockOptimistic ' Ouvrir la Table
Else
ADO_Module(i).Module.Open "[" & ADO_File.Table_Name(i) & "]", ADO_File.File, adOpenStatic, adLockReadOnly ' Ouvrir une requete
ADO_Module(i).Nb = ADO_Module(i).Module.RecordCount
End If
ADO_Module(i).Field_Nb = ADO_Module(i).Module.Fields.Count ' recuperer le nombre de champs
For j = 0 To ADO_Module(i).Field_Nb - 1 ' Récuperer tous les champs
ReDim Preserve ADO_Module(i).Field_Name(j)
ReDim Preserve ADO_Module(i).Field_Type(j)
ADO_Module(i).Field_Name(j) = ADO_Module(i).Module.Fields(j).Name ' le Nom
ADO_Module(i).Field_Type(j) = ADO_Module(i).Module.Fields(j).Type ' Le Type
Next j
If ADO_File.Table_Type(i) = "TABLE" Then
Set Ado_Sql = New ADODB.Recordset
Ado_Sql.Open "SELECT * FROM [" & ADO_File.Table_Name(i) & "]", ADO_File.File, adOpenStatic, adLockReadOnly
ADO_Module(i).Nb = Ado_Sql.RecordCount
End If
Next i
ADO_Open_Access_File = True
GoTo fin
erreur:
ADO_Open_Access_File = False
fin:
End Function