September 1, 2015 at 3:45 pm
Hi guys, Not sure if I picked the correct sub-forum, please point me if not.
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 to the 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.
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:\temp\wls12.xlsx;Extended Properties=Excel 12.0')...[WLS$]"
cn.Execute sSQL
I get the error after that line.
Anyone know how to work out this error and get this working?
Thanks,
Scott
September 8, 2015 at 11:18 am
shorton2 (9/1/2015)
Hi guys, Not sure if I picked the correct sub-forum, please point me if not.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 to the 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.
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:\temp\wls12.xlsx;Extended Properties=Excel 12.0')...[WLS$]"
cn.Execute sSQL
I get the error after that line.
Anyone know how to work out this error and get this working?
Thanks,
Scott
Just to make sure everyone is on the same page...
You're preparing the statement
INSERT INTO MYDB.dbo.MYTABLE ([Account]) SELECT [Account] FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\temp\wls12.xlsx;Extended Properties=Excel 12.0')...[WLS$]
and attempting to execute it on the SQL Server. If the spreadsheet isn't in the temp directory on the C drive of the SQL server, then this code isn't going to work.
You're telling SQL Server to go out and find this spreadsheet, open it as a table, and import it into the database. I'm not going to say you can't do it that way, although I wonder if SQL Server will be able to open the Excel spreadsheet if you already have it open in Excel.
Instead, what I normally see done is a push to SQL Server. Some people will do it one record at a time, so in pseudo-code
for each row in the spreadsheet
insert into MyTable (col) VALUES (Cell1)
next row
You probably have to do some checks like making sure the cells have data in them.
I'm paraphrasing the process here, because I've only had to dig into VBA twice in the past couple of years, and I don't have the specific syntax down in front of me, but that would be your basic idea.
September 8, 2015 at 11:52 am
Thank you. I figured it out and got past the errors once I figured out it was looking for the Excel file from the perspective of the SQL server, not the PC running the VBA.
September 10, 2015 at 6:33 am
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\MYOPENFOLDERNAME\wls12.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]"
September 10, 2015 at 6:47 am
shorton2 (9/10/2015)
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] FROMOPENDATASOURCE('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] FROMOPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=\\MYPCNAME\MYOPENFOLDERNAME\wls12.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]"
It depends on what account you're running SQL Server under. For example, if SQL Server is running under Local System or Local Service account, that account cannot access anything off of the local system. I have seen people run their SQL Server under the NETWORK SERVICE account to be able to access files on remote computers. If you use NETWORK SERVICE, you probably still have to grant the computer account access to the share. The computer account will be the computer name with a $ at the end of it.
The other option is to use an active directory account to run your SQL Server Service from. Appropriate permissions can be granted that way.
Of course, this is all just a guess without me knowing what account you're using to run SQL Server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply