Utilisation des chaînes de connexion Excel et Microsoft SQL Server

Question

Ma tâche consiste à ajouter de nouveaux enregistrements d'un tableau Excel à un tableau Microsoft SQL Server, et pour ce faire, je prévoyais d'utiliser des objets ADODB ; cependant, ma requête SQL ne s'exécute pas, et je pense que cela a quelque chose à voir avec mes chaînes de connexion.

Dans mon code, j'ai écrit la requête SQL que j'ai l'intention d'utiliser à la fin, mais quand j'ai essayé :

    sql = "SELECT * FROM [Provider=SQLOLEDB;Data Source=hpwfh-ssql01; _ 
Initial Catalog=HPW DataIntegrated Security=SSPI;Trusted_Connection=Yes].Hubspot_Data"

(une simple instruction select), ça n'a même pas fonctionné.

Sub update1()

    Dim cn, rs As Object, path As String, name As String, sql As String, file As String

    path = "T:\Marketing\Data Analytics\Hubspot data for SQL"
    name = "Hubspot_Data"
    file = path & "\" & name & ".xlsx"

    Set cn = CreateObject("ADODB.Connection")

    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .connectionstring = "Data Source=" & file & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;Readonly=false;IMEX=0"";"
        .Open
    End With

    sql = "INSERT INTO [Provider=SQLOLEDB;Data Source=hpwfh-ssql01;Initial Catalog=Hubspot_Data;Integrated Security=SSPI;Trusted_Connection=Yes].Hubspot_Data " & _
"SELECT * FROM (SELECT * FROM [Provider=SQLOLEDB;Data Source=hpwfh-ssql01;Initial Catalog=Hubspot_Data;Integrated Security=SSPI;Trusted_Connection=Yes].Hubspot_Data" & _
"EXCEPT SELECT * FROM [hubspot-crm-exports-sql-data-20$])"

    Set rs = cn.Execute(sql)

End Sub

Juste une note : la table porte le même nom que la base de données

Pour ce code, j'ai obtenu trois erreurs différentes :

1. Le moteur de base de données Microsoft Access n'a pas pu trouver l'objet 'Area'. Assurez-vous que l'objet existe et que vous orthographiez correctement son nom et le nom du chemin. (Et je n'ai pas mal orthographié Hubspot_Data)

2. La table externe n'a pas le format attendu.

3. Le moteur de base de données Microsoft Access ne peut pas ouvrir ou écrire dans le fichier (Chemin de mon fichier)'\Mes Documents\Provider=SQLOLEDB.XLSX'. Il est déjà ouvert exclusivement par un autre utilisateur, ou vous avez besoin d'autorisation pour voir et écrire dans ses données.

De toute évidence, l'ordinateur va au mauvais endroit pour récupérer la table dont il a besoin, et je ne sais pas où j'ai commis une erreur. Merci pour l'aide.

Answer 1

Tout d'abord, vous avez besoin de 2 connexions - une pour SQLSvr et une pour Excel. Ensuite, interrogez votre source (Excel) et effectuez une insertion distincte dans SQLSvr. Vous ne pourrez pas mélanger ces opérations dans une seule requête.

Sub SelectInsert()
Dim cn As Object, rs As Object, sql As String
Dim conSQL As Object, sInsertSQL As String

'---Connexion à la source de données---
Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With

Set conSQL = CreateObject("ADODB.Connection")
With cn
    .Provider = "SQLOLEDB"
    .ConnectionString = "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;"
    .Open
End With

'---Exécuter la requête SQL SELECT---
sql = "SELECT * FROM [Sheet1$]"
Set rs = cn.Execute(sql)

Do  'l'insertion. Chaque rs(n) représente une colonne Excel.
   sInsertSQL = "INSERT INTO table VALUES(" & rs(0) & ";" & rs(1) & ";" & rs(2) & ")"
   conSQL.Execute sInsertSQL
   rs.MoveNext
Loop Until rs.EOF

'---Nettoyage---
rs.Close
cn.Close
conSQL.Close
Set cn = Nothing
Set conSQL = Nothing
Set rs = Nothing
End Sub

Answer 2

Obtenez les propriétés de votre base de données à partir de "Explorateur d'objets SQL Server" et copiez exactement la même chaîne de connexion. Ensuite, copiez-la dans le fichier "appsettings.json" de votre projet. Cela ressemble à ceci :

"connectionStrings": {
    "ApiDbConnectionString": "Server=(localdb)\\mssqllocaldb;Database=ApiDB;Trusted_Connection=True;"
  }

ensuite, vous devez créer un objet dans votre chaîne de connexion et ouvrir une connexion à la base de données en utilisant cet objet, puis écrire votre requête SQL à la base de données