Discussion :

[stargates01]

Bonjour à tous,

Je cale sur un problème que je pensais assez simple, mais pour lequel je n'ai pas trouvé de solution simple finalement...

Je cherche à faire un INSERT INTO sur une table située dans une base MS SQL, d'enregistrements situés dans une table de ma base ACCESS.

Existe-t-il une instruction simple en utilisant ADODB ? je n'ai pas trouvé... j'ai lu que je pouvais importer avec liaison la table SQL dans ma base ACCESS et faire l'INSERT INTO dans ACCESS mais dans mon cas ça ne va pas car la table SQL est énorme et je crains d'avoir de mauvaises performances. J'ai également lu que je pouvais utiliser l'instruction OPENROWSET dans une chaîne qui serait exécutée coté SQL mais je n'arrive pas à la paramétrer : peut-on profiter de la connection ouverte entre ACCESS et SQL pour cette instruction, ou faut-il déclarer la base access avec son adresse complète ?
Bref je n'ai trouvé comme solution que de passer pas un recordset dans lequel je charge ma table Access et j'insère un à un les enregistrement de mon recorset dans ma table SQL par une commande ADO (voir code plus bas).
Mille mercis à tous ceux qui pourront me mettre sur la voie d'une solution plus simple et performante...

Stargates

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
Private Sub UpdateTableSQL()
Dim rs As Recordset
Dim cmd As New ADODB.Command
Dim strSQL As String
strSQL = "INSERT INTO dbo.Assistant (TA_Initials, TA_Name) VALUES(?,?)"
Set rs = CurrentDb.OpenRecordset("TableAccess", dbOpenTable)
If rs.EOF Then Exit Sub
Connect
While Not rs.EOF
Set cmd = ExecSQL(cn, strSQL, rs(1), rs(2))
cmd.Execute
rs.MoveNext
Wend
Disconnect
End Sub
 
Public Function ExecSQL(cn As ADODB.Connection, strSQL As String, ParamArray Params() As Variant) As ADODB.Command
Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
Dim pr As Variant
For Each pr In Params
    Set pr = cmd.CreateParameter(, GetTypeParm(VarType(pr)), adParamInput, Len(pr), pr)
    cmd.Parameters.Append pr
Next pr
Set ExecSQL = cmd
End Function
 
Public Function GetTypeParm(typeVar As Integer) As Long
    Select Case typeVar
        Case 2: GetTypeParm = adInteger
        Case 3: GetTypeParm = adInteger
        Case 4: GetTypeParm = adSingle
        Case 5: GetTypeParm = adDouble
        Case 6: GetTypeParm = adDecimal
        Case 7: GetTypeParm = adDate
        Case 8: GetTypeParm = adVarWChar
        Case 11: GetTypeParm = adBoolean
        Case 14: GetTypeParm = adDecimal
        Case 17: GetTypeParm = adChar
        Case Else: GetTypeParm = -1
    End Select
End Function

[Jean-Philippe André]

Salut,

je crois que tu peux largement simplifier ton approche en ayant simplement ta table MSSQL en table liée et il suffira alors de faire un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO Assistant SELECT * FROM TableAccess

aux ajustements de noms et de champs près

[stargates01]

Merci de ta réponse. Ma table SQL doit pouvoir être accédée par plusieurs utilisateurs munis de la même application access (chacun ayant une base access identique, qui sert d'interface avec des formulaires, sur son disque local) : est-ce que je ne vais pas avoir un problème de conflit en mullti-user? est-ce que le fait que cette tale SQL soit liée au même moment chez plusieurs utilisateurs ne va pas finir par saturer SQL (30 utilisateurs peuvent utiliser leur appli Access en même temps) ? J'ai conscience que ta solution est la plus simple mais j'ai un doute sur les performances en multi-users.

[Paraffine]

Cela dépend des performances du réseau. Avec des tables liées volumineuses (pour Access, s'entend, de l'ordre du million de lignes) j'ai "réussi" à planter des requêtes
Il me reste quelques tables liées qui me servent de passerelle (pour faire des insertions ou des modifications, c'est une sorte de sas) et qui sont très longues à charger. Au point que j'envisage de passer par des fichiers pour les insertions (ce serait déjà fait si je connaissais mieux les commandes système).

De manière générale, Access - avec toutes ses qualités - n'est pas le champion des bases relationnelles.

Personnellement, je préconiserais de créer une procédure stockée dans MS SQL et de l'appeler depuis Access ; la sécurité de la transaction (intégrité des données, gestion des accès concurrents) revient au SGBDR qui est mieux armé pour ce faire. Plus le traitement est compliqué, plus on a intérêt à utiliser ce procédé.

En passant par des tables Access qui répertorient les éléments à ajouter, se pose le problème de la priorité et/ou de la cohérence des insertions demandées. Ces tables sont-elles locales ou dans une dorsale Access? Les utilisateurs sont-ils susceptibles de travailler sur les mêmes informations ou ont-ils chacun leur "domaine"? Les insertions demandées par un utilisateur donné doivent-elles être gérées globalement par une transaction (tout ou rien) ?

Reste le problème de la sécurité de l'accès à MS SQL (user/ mot de passe), donc attention à la définition du compte utilisé.
L'inconvénient d'une table liée étant pour sa part qu'un utilisateur distrait (ou malveillant) pourrait y accéder directement et causer du dégât.

[stargates01]

Merci Paraffine. Je pense en effet que je vais faire tous mes traitements sous MSSQL avec des procédures stockées. Mon objectif est le suivant :
J’ai aujourd’hui une appli Access qui est mono-user et je cherche à la rendre multi-user en portant les principales tables dans MS SQL Server et en distribuant l’appli access à tous les utilisateurs qui utiliseront les formulaires pour accéder aux données et faire certains traitements en local dans leur base.
J’ai notamment une table que je qualifie peut-être à tort de volumineuse (50k enregistrements) qui sera donc sous SQL server. Un des traitements consiste à enchaîner une quarantaine de query sur cette table à partir d’info saisie par l’utilisateur : création de tables intermédiaires, regroupement, jointure, etc... et finalement mettre à jour des infos dans cette table et y ajouter quelques enregistrements.
Comme ce traitement ne porte finalement que sur quelques enregistrements de la table, mon idée était d’extraire ces quelques enregistrements de la rapatrier dans une table Access, d’effectuer ma quarantaine de queries dessus, puis de recharger le résultat dans ma table SQL Server avec des requêtes update et insert. C’est pour ça que je cherchais une solution simple de remonter mes résultats dans la table sous SQL Server.
La solution d’avoir une table liée à la table SQL n’est pas performante car elle va engendrer des échanges de données volumineux à chacun de mes quarante queries.
Je pense que le plus performant est de mettre mes quarante query dans une procédure stockée sous SQL Server et de tout faire faire par SQL, mais je ne connais pas bien SQL Server et je trouvais n’intéressant de faire les traitements sur un extract de ma table dans Access..

[Paraffine]

Un des traitements consiste à enchaîner une quarantaine de query sur cette table à partir d’info saisie par l’utilisateur : création de tables intermédiaires, regroupement, jointure, etc... et finalement mettre à jour des infos dans cette table et y ajouter quelques enregistrements.

Étant donné que SQL Server est bien plus avancé dans la mise en œuvre du standard SQL, en y travaillant, il y a de grandes chances que tu puisses simplifier ta quarantaine de requêtes. Non seulement le langage est maintenant très puissant, mais il y a sur le site de Developpez.com de sacrées pointures en algèbre relationnelle! De plus tu peux créer des vues pour te faciliter la tâche. Et le traitement a toutes les chances d'être beaucoup, beaucoup plus rapide.

Il faut aussi se poser la question de l'influence des accès concurrents sur le traitement préparatoire.
Par exemple ; que se passe t-il si des enregistrements sont créés entre la requête n° 23 et la n° 24 ?

(...) mon idée était d’extraire ces quelques enregistrements de la rapatrier dans une table Access, d’effectuer ma quarantaine de queries dessus, puis de recharger le résultat dans ma table SQL Server avec des requêtes update et insert.

Quelque soit le moyen que tu retiens finalement pour définir les requêtes update et insert, dans la mesure où il faut que les requêtes de mise à jour constituent un ensemble (tout passe ou rien ne passe) il faut impérativement faire une transaction. C'est possible en Access, mais je pense que c'est incomparablement plus fiable dans une procédure stockée (plus rapide, donc moins de risque de concurrence d'accès, verrouillage des enregistrements moins longs, etc).

Access nous sauve la mise pour tout un tas d'applications, mais avec une trentaine d'utilisateurs et des opérations complexes cela vaut la peine de se pencher sérieusement sur les solutions SQL Server pour gérer les données. C'est un bon investissement ! Même avec un SGBDR bien moins efficace comme MariaDB, qu'utilise mon entreprise, cela améliore sensiblement les performances, au point que je réfléchis de plus en plus "procédures stockées", y compris pour les applications PHP.

[stargates01]

Oui, merci. Je comprends que j'ai intérêt à investir dans la maîtrise de SQL Server/transact et d'y positionner la plus grosse partie de mes traitements.C'est ce que je vais faire. Merci pour ton conseil.;

[Thumb down]

Bonjour,
tu n'as pas besoin d'ADODB!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub test()
Dim Sql As String
Sql = "INSERT INTO    [ODBC;Driver={SQL Server};SERVER=" & MyServeur & ";DATABASE=" & MyDATABASE & ";UID=" & MyUID & ";Pwd=" & MyPwd & "].[Table_1] " & _
" select * FROM Table1"
CurrentDb.Execute Sql
End Sub

[stargates01]

Merci Thumb down. Top !
Je vais explorer cette méthode. J'avais vu quelques articles sur ça mais pas d'exemple concret avec toute la chaine de connexion. Merci à toi !