Peter Chan
asked on
Problem to run against Sql server DB
Hi,
I actually want to connect to Sql server DB, from Excel VBA codes, using these
t917.png
I actually want to connect to Sql server DB, from Excel VBA codes, using these
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CBRHKMA;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
...
s = "delete from [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=PC336_W7_HKMA;Trusted_Connection=Yes;" _
& "DATABASE=CBRHKMA].T41 " _
& "where [Reporting Date]=convert(datetime,'" & Mid(strLine, pos0, 8) & "',112)"
...
but I get the error attached. why?t917.png
When using Jet drivers you need to provide aa valid file as source. You should use a direct ODBC connection without that Jet stuff.
ASKER
Thanks. what to adjust in above?
ASKER
Can you please show with more details? thanks.
The reason you are getting this error is because of
Data Source=CBRHKMA
Because you haven't got a folder path, it is interpreting this as CBRHKMA.xls in the current directory.
For more information around connecting to SQL Server from Excel, see https://www.experts-exchange.com/questions/28509282/Problems-with-Excel-connections-to-SQL-Server.html
If you want some straight-forward code, go to http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm and "Show source code" of "SQL Server Data Import to Excel using ADO".
Data Source=CBRHKMA
Because you haven't got a folder path, it is interpreting this as CBRHKMA.xls in the current directory.
For more information around connecting to SQL Server from Excel, see https://www.experts-exchange.com/questions/28509282/Problems-with-Excel-connections-to-SQL-Server.html
If you want some straight-forward code, go to http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm and "Show source code" of "SQL Server Data Import to Excel using ADO".
ResolutionTo work around this issue, carefully verify not only the name and spelling of the source table (or tables) named in your query, but the name and spelling of the Excel path and file name supplied in your ADO connection string.
refer
http://support.microsoft.com/kb/316809/en-us
refer
http://support.microsoft.com/kb/316809/en-us
ASKER
Many thanks all. Can I know exactly what I should adjust, to my codes in above?
We don't know your exact connection requirements and strings, so no.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all.
will it be fine, if I have the codes like
supposing that there is no syntax error above, and PC336_W7_HKMA is server name, and CBRHKMA is schema name?
will it be fine, if I have the codes like
Dim cn As ADODB.Connection
Set cn = CreateObject("ADODB.Connection")
'strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CBRHKMA;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
strCon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=PC336_W7_HKMA;" & _
"InitialCatalog=CBRHKMA;"
cn.Open strCon
...
s = "delete from T41 " _
& "where [Reporting Date]='" & date_part & "'"
cn.Execute s
...
supposing that there is no syntax error above, and PC336_W7_HKMA is server name, and CBRHKMA is schema name?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.