CODEWITHSUNDEEP
sql-serverexcelvbassmsadodb
Jimmy Sgrazzutti
Jimmy Sgrazzutti

Reputation: 99

Using both Excel and Microsoft SQL Server Connection Strings

My task is to add new records from an excel table to a Microsoft SQL Server table, and to do this, I was planning on using ADODB objects; however, my SQL statement is not executing, and I think it has something to do with my connection strings.

In my code, I wrote down the SQL statement that I plan on using in the end, but when I tried:

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

(a simple select statement) it didn't even work.

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

Just a side note: the table is named the same thing as the database

For this code, I have gotten three different errors:

  1. The Microsoft Access database engine could not fin the object 'Area' Make sure the object exists and that you spell its name and the path name correctly. (And I did not misspell Hubspot_Data)

  2. External table is not in the expected format.

  3. The Microsoft Acess database engine cannot open or write to the file (My File Path)'\My Documents\Provider=SQLOLEDB.XLSX'. It is already opened exclusively by another use, or you need permission to view and write its data.

Clearly the computer is going to the wrong place to retrieve the table it needs, and I have no idea where I went wrong. Thanks for the help.

Upvotes: 2

Views: 2739

Answers (2)

Barry
Barry

Reputation: 63

First of all you need 2 connections - one for SQLSvr and one for Excel. Then query your source (Excel) and do a separate insert into SQLSvr. You are not going to be able to mix these into one query.

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

'---Connecting to the Data Source---
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


'---Run the SQL SELECT Query---
sql = "SELECT * FROM [Sheet1$]"
Set rs = cn.Execute(sql)

Do  'the insert. Each rs(n) represents an Excel column.
   sInsertSQL = "INSERT INTO table VALUES(" & rs(0) & ";" & rs(1) & ";" & rs(2) & ")"
   conSQL.Execute sInsertSQL
   rs.MoveNext
Loop Until rs.EOF

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

Upvotes: 2

MrMalith
MrMalith

Reputation: 1372

get properties of your database from "SQL Server Object explorer" and copy the exact same connection string. then copy it to the "appsettings.json" file of your project. It looks like this : 

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

then you need to create an object in your connection string and open a connection to the database using that object, then write your SQL query to the database

Upvotes: 0

Related Questions