VBA Push Excel to SQl Server 2014

[shorton]

I am in a corporate environment with IT managed SQL servers. "My" 2014 SQL server is setup and functioning fine.

We want to be able to do a push of data from Excel on a desktop PC to the remote Windows server via VBA. We have some data sources that come in Excel and we already have to open the files and do some Excel cleanup, so it would be convenient to kick off an upload to the SQL server from excel on the desktop vs. pulling them from the server. The files and who updates them may change locations so I don't want a persistent link to the Excel file.

I've found lots of examples on how to do it, and I'm close. but when trying I get this error:

The OLE DB Provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not contain the table "WLS$". The table either does not exist or the current user does not have permission on that table.

Some trial and error and it appears "WLS$" is the Excel sheet name. My connection via VBA seems to setup and connect OK.

That code is:

SQLServerPath = "SOMEPATH\MYSERVER"
  
strConn = "Provider=SQLOLEDB;Data Source=" & SQLServerPath & ";Initial Catalog=MYDB;Integrated Security=SSPI;Persist Security Info=True;" 

cn.Open strConn  

strSQL = " INSERT INTO MYDB.dbo.MYTABLE  ([Account])  SELECT [Account]  FROM  OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\data\wls12.xlsx;Extended Properties=Excel 12.0')...[WLS$]"  

cn.Execute sSQL

I get the error at that last line.

Figuring the "data source" may be from the perspective of the remote server (is it?), I also tried to point the string that way:

strSQL = " INSERT INTO MYDB.dbo.MYTABLE ([Account]) SELECT [Account] FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=\\MyPCName\data\wls12.xlsx;Extended Properties=Excel 12.0')...[WLS$]"

When remoted into the server and logged is as myself, I can access that directory as listed in the string above. But then I get a slightly different error:

The OLE DB Provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

I've read every example on the internet I think, tried OpenRowSet instead, no luck. Any suggestions?

 

[shorton]

For what it's worth, I rebuilt the connection string using MS tools. It came up with this:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MYDB;Data Source=MYSERVER\MYSQLSERVER

Also with some trial and error I found that if I use a folder for the "data Source" that the Windows server can see, or exists on the server, then I get error #1. If I use any other folder name, then I get the second error. I guess that means the Data Source is from the perspective of the server, not Excel VBA on the PC.

So I created a folder on the server X:\sqltest, and gave full control permission to "everyone". Still fails.

 

[shorton]

...

 

[shorton]

Well, having my own conversation here LOL, but update... I got it mostly working. Functional anyway, I can expand it now.

I got past the errors. Used :

Created a folder X:\sqlpush on the Windows Server that has SQL server on it.
Copied the source file there.

icacls x:\ /grant mydomain/myidR:W) to set permissions on the folder, on the M$ Server where SQL Server is installed.

Verified the sheet name, file name, folder names, etc. in the string.

Created a table named WLS3 with 1 column "Job_Number", saved it.

In the xlsx, there is one sheet (Sheet1). There are 20 rows of data including a header row. Cell A1 heading is "Job_Number" There is more than one column of data.

My strSQL string is:
delete from dbo.WLS3; insert into [WLS3] select [Job_Number] FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=x:\sqlpush\wls12.xlsx;Extended Properties=Excel 12.0;Extended Properties=Excel 12.0')...[Sheet1$]

That works. Phew.

 

[shorton]

So I got close to getting this to work, but still having one last problem...

If I place the excel file literally ON the Windows Server that SQL server is installed and run this it works:

strSQL = "delete from dbo.WLS3; insert into [WLS3] select [Job_Number] as [JobNumber] FROM 
OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=x:\sqlpush\wls12.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]"

But I need the excel file to be on the PC running the VBA. I have a folder on the local desktop that has had the permissions opened up to everyone for testing. I can see it from the server, I can access it and write to it from Windows explorer when remoted into the server. I've logged onto the server remotely as a different userID that does not have logon rights on my PC, I can still access and see the remote folder on the desktop. So it appears the permissions are OK.

But when attempting to do the same pulling the excel file from the desktop/network drive using this SQL statement, it fails. I get the same error all of this started with, the same error as if I point the string to a non-existent folder. Anyone know what the problem is? Syntax of the remote PC path ok?

strSQL = "delete from dbo.WLS3; insert into [WLS3] select [Job_Number] as [JobNumber] FROM 
OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=\\MYPCNAME\MYSHAREDOPENFOLDERNAME\wls12.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]"